Executing more than one Stored Procedure at a time

[dougancil]

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

[sandeepparekh9]

.CommandText = "exec sp_opsum, exec sp_opintcheck ,exec sp_opexceptsum, exec sp_empsum, exec sp_empexceptsum"

replece , with ; and try

.CommandText = "exec sp_opsum; exec sp_opintcheck ;exec sp_opexceptsum; exec sp_empsum; exec sp_empexceptsum"

[dougancil]

Sandee,

Tried that ...

Private Sub SPRun()
        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()
        End Try
    End Sub

I still get the error that the SP's can't be found.