Running 3 stored procedures from VB.Net

[BukHix]

I have 3 stored procedures that need to be run from my code.

sp_Proc_01
sp_Proc_02
sp_Proc_03

The first deletes all the records in a database and the next two (actually there are 20 in the application) rebuild the table with new information.

How do I write code to run this series of SPs? An example or link would be greatly appreciated. Thanks.

[nswan]

search on the sqlCommand object

at least have an attempt at coding it, then we'll give you a hand!

[BukHix]

Of course I did a search before I posted (I searched here and on Google). I cannot find anything related to running stored procedures from VB.Net code. I did find a lot of info about running stored procedures that add records to tables in ASP.Net using parameters but that doesn't apply.

I would love show some code but I don't even know where to start, which is why I asked for in sample or a link to a tutorial.

Thanks anyways

[nswan]

ok,

dim myCommand as new sqlCommand

myCommand.commandType = storedproc
myCommand.commandText = "nameofstoredproc"
myCommand.connection = mySQLConnection
myCommand.ExecuteNonQuery

done from memory and not tested!

[BukHix]

I am still stumped on this.

Private Sub btnUpdateRates_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles btnUpdateRates.Click
 
        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_AC1_ST"
        cmd.Connection = strCon
        cmd.ExecuteNonQuery()
 
    End Sub

The strCon is underlined with this warning. Value of type 'String' cannot be converted to System.Data.SqlClient.SqlConnection.

I know it seems like I am asking you to finish my project for me but I don't know how else to finish this (should be) simple prodedure.

[nswan]

strcon should be a connection object not connection string

dim myConnection as new sqlConnection(strCon)

myCommand.Connection = myConnection

Nick

[BukHix]

That worked. Thank you very much. One more question. Is there a better/shorter way to do the following?

Dim cmd As New SqlCommand
        Dim con As New SqlConnection(strCon)
        cmd.CommandType = CommandType.StoredProcedure
 
        ' Clear old data |***************************
        cmd.CommandText = "sp_DeleteRates"
        cmd.Connection = con
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
        cmd.Dispose()
 
        ' AC1 using strait time |********************
        cmd.CommandText = "sp_AC1_ST"
        cmd.Connection = con
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
        cmd.Dispose()
 
        ' AC1 using over time |***********************
        cmd.CommandText = "sp_AC1_OT"
        cmd.Connection = con
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
        cmd.Dispose()
 
        ' AC2 using over time |***********************
        cmd.CommandText = "sp_AC2_OT"
        cmd.Connection = con
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
        cmd.Dispose()

I think I a need a With Cmd / End With but I am not sure where to put it to run all my seperate sps.

Thanks again.

[hellswraith]

Here,

Private Sub StartProcess()
   ' Clear old data |***************************   
   RunCommand("sp_DeleteRates", strCon)
   ' AC1 using strait time |********************
   RunCommand("sp_AC1_ST", strCon)
   ' AC1 using over time |***********************
   RunCommand("sp_AC1_OT", strCon)
   ' AC2 using over time |***********************
   RunCommand("sp_AC2_OT", strCon)
End Sub
 
 
Private Sub RunCommand(strProcedure As String, strCon As String)
        Dim cmd As New SqlCommand
        Dim con As New SqlConnection(strCon)
        cmd.CommandType = CommandType.StoredProcedure
 
        cmd.CommandText = strProcedure
        cmd.Connection = con
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
        cmd.Dispose()
End Sub