Executing more than one stored procedure at a time

I have a form that requires me to execute several stored procedures one after the other. I'm wondering how to do that in vb.net. I realize that I could just have one stored procedure with all of my queries in it, but for clarities sake, I want to keep them seperate.

Thank you

Doug

If they're always executed together, in the same order, it's simple. Most business logic would dictate that you want to combine them differently, in which case more logic is required.

DG,

I have this as my code:

Code:

---

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim oCmd As System.Data.SqlClient.SqlCommand
        Dim oDr As System.Data.SqlClient.SqlDataReader
        oCmd = New System.Data.SqlClient.SqlCommand
        Try
            With oCmd
                .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 = "sp_opsum, sp_opintcheck, sp_opexceptsum, sp_empsum, sp_empexceptsum"
                oDr = .ExecuteReader()
                oCmd.Connection.Close()
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
        End Try
    End Sub

---

and when I run my debug, the program tells me that it can't find any of the SP's, but if I remove all but one and run it, the program runs find and it run's the SP. Can you tell me what I may be doing wrong?

Thank you

Repeat everything under the first DIM, a second time, but using different connections

Code:

---

      Dim oCmd2 As System.Data.SqlClient.SqlCommand

---

Then, run #2 after #1 to see if that solves it.

So create a new sub with just a second connection and the rest of the sps? Is that what you're suggesting? (Just to clarify)

Here is the code I have now:

Code:

---

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim oCmd As System.Data.SqlClient.SqlCommand
        Dim oDr As System.Data.SqlClient.SqlDataReader
        oCmd = New System.Data.SqlClient.SqlCommand
        Try
            With oCmd
                .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 = "sp_opsum"
                oDr = .ExecuteReader()
                oCmd.Connection.Close()
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
        End Try
    End Sub
    Private Sub SPRun(ByVal sender As System.Object)
        Dim oCmd2 As System.Data.SqlClient.SqlCommand
        Dim oDr 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 = "sp_opintcheck, sp_opexceptsum, sp_empsum, sp_empexceptsum"
                oDr = .ExecuteReader()
                oCmd2.Connection.Close()
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd2.Connection.Close()
        End Try

    End Sub

---

and I just ran my code, now it doesnt error out but I'm not seeing that it actually runs the SP's either. Do I need an event to get my sub to run?

Also need odr2 and every other DIM in the sub.

Change the sub, you don't need to pass anything.

Code:

---

  Private Sub SPRun()

---

Or create a function that returns TRUE or FALSE.

Then, before the first SP ends, you'd call it.

Code:

---

SPRun()

---

or

Code:

---

Dim Val as Booolean = SPRun()

---

DG,

Thank you. I'll try that and let you know.

Ok I have edited my code and it is now as follows:

Code:

---

Private Sub SPRun()
        Dim oCmd2 As System.Data.SqlClient.SqlCommand
        Dim oDr 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 = "sp_opintcheck, sp_opexceptsum, sp_empsum, sp_empexceptsum"
                oDr = .ExecuteReader()
                oCmd2.Connection.Close()
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd2.Connection.Close()
        End Try
        SPRun()
    End Sub

---

and when I ran this, I'm still not seeing that it's running the SP's but its also not producing any errors. Any ideas or suggestions?

What about oDR2?

DG,

I actually think I'm going to go with this:

Code:

---

.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 + ";"

---

It's kind of excessive, but I think it'll get the job done.

What if one fails?

I'm not sure what would happen if the code that you gave me would do if a sp fails either. So what you're suggesting is that I use that code, create oDR2 instead of just oDR and try it that way?

Ok so I've changed my code to oDR2:

Code:

---

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 = "sp_opintcheck, sp_opexceptsum, sp_empsum, sp_empexceptsum"
                oDr2 = .ExecuteReader()
                oCmd2.Connection.Close()
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd2.Connection.Close()
        End Try
        SPRun()

---

and it's still not executing.

Call SPRun() from the END of your other SP, after closing everything, or put the each into a sub, then call them (add ERROR TRAPPING THERE)

DG,

So I modified the code again to this:

Code:

---

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

---

moved the SPRun to the load event on the page and when I debug this, I'm told that the program can't find the SP's, yet if I remove all but one of them, this will work.

Have SPRun() be the master, and call 3 more, each doing it's own oDR#, and call each one in sequence. Nothing is there BEFORE a form is loaded.

Assuming that all of the sprocs use the same parameter, would this work:

Code:

---

Private Sub SPRun()
        Dim oCmd2 As New System.Data.SqlClient.SqlCommand
        Dim commands as String() = {"sp_opsum", "sp_opintcheck", "sp_opexceptsum" , "sp_empsum", "sp_empexceptsum"}
        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)
for each strCommand as string in commands
                .CommandText = strcommand
                .Execute
next
                oCmd2.Connection.Close()
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd2.Connection.Close()
        End Try
    End Sub

---

-tg