I am trying to run several stored procedures from my code and I get the error message that my sp's can't be found, but if I remove all of them but one, my code executes fine.
I've tried this way to run my sp's:
Dim oCmd2 As System.Data.SqlClient.SqlCommand
Dim oDr2 As System.Data.SqlClient.SqlDataReader
oCmd2 = New System.Data.SqlClient.SqlCommand
Try
With oCmd2
.Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
.Connection.Open()
.CommandType = CommandType.StoredProcedure
.Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate)
.Parameters.AddWithValue("@payperiodenddate", payperiodenddate)
.CommandText = "exec sp_opsum, exec sp_opintcheck ,exec sp_opexceptsum, exec sp_empsum, exec sp_empexceptsum"
oDr2 = .ExecuteReader()
oCmd2.Connection.Close()
End With
Catch ex As Exception
MessageBox.Show(ex.Message)
oCmd2.Connection.Close()
and also this way:
Dim oCmd As System.Data.SqlClient.SqlCommand
oCmd = New System.Data.SqlClient.SqlCommand
Dim adapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
Dim ds As New DataSet
Try
With oCmd
.Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
.Connection.Open()
.CommandType = CommandType.StoredProcedure
.CommandText = "exec sp_opsum @payperiodstartdate = " + payperiodstartdate + ",@payperiodenddate=" + payperiodenddate + ";" + _
"exec sp_opintcheck @payperiodstartdate = " + payperiodstartdate + ",@payperiodenddate=" + payperiodenddate + ";" + _
"exec sp_opexceptsum @payperiodstartdate = " + payperiodstartdate + ",@payperiodenddate=" + payperiodenddate + ";" + _
"exec sp_empsum @payperiodstartdate = " + payperiodstartdate + ",@payperiodenddate=" + payperiodenddate + ";" + _
"exec sp_empexceptsum @payperiodstartdate = " + payperiodstartdate + ",@payperiodenddate=" + payperiodenddate + ";"
oCmd.Connection.Close()
End With
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Try
Catch ex As Exception
MessageBox.Show(ex.Message)
oCmd.Connection.Close(
While the second one never gave me an error, I can verify that the SP's never ran either. Can someone please offer me a better way to run several SP's from my code.
Thank you
Doug