Stored procedures are executed twice!

For some reason, my stored procedures are being executed twice! I have a static function that starts the SP given its name and parameters and fills in the data.

Public Shared Function RunSP(ByVal spName As String, ByRef spParams As Dictionary(Of String, String), ByRef pDataTable As DataTable) As Integer
    Dim cmd As New SqlCommand
    Dim lAdapter As SqlDataAdapter
    Try
        cmd.Connection = New SqlConnection(ConnectionString)
        cmd.Connection.Open()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = spName

        For Each item As KeyValuePair(Of String, String) In spParams
            cmd.Parameters.AddWithValue(item.Key, item.Value)
        Next

        cmd.ExecuteNonQuery()
        If Not pDataTable Is Nothing Then
            lAdapter = New SqlDataAdapter(cmd)
            lAdapter.Fill(pDataTable)
        End If
        RunSP = 0
    Catch ex As Exception
        If (Not cmd Is Nothing) Then
            cmd.Dispose()
        End If
        RunSP = -1
    End Try
End Function

      

Is there something wrong with the code? I checked with a debugger and the corresponding SPs are definitely only called once, i.e. This function only works once for a specific insert function, but two things are being inserted into the table.

+2


source to share


2 answers


You execute it once manually ( cmd.ExecuteNonQuery()

), then the adapter will execute it once ( New SqlDataAdapter(cmd)... Fill(...)

). Thus, twice. Did you mean to use a different command for the adapter?



+11


source


You execute the stored procedure twice, one in cmd.ExecuteNonQuery, and then a second time when you use lAdapter.Fill.



+5


source







All Articles