[RESOLVED] How do I call a Stored Procedure from a VB Sub

[capella07]

I have an Update Stored Procedure on SQL Server and I just simply want to call a Sub that "fires" off that SP.

Sub UpdateTableWithSP()
sqlConn.Open() 'connection declared globally
 
Dim sqlSP As New SqlCommand("MTFE_UpdateTable")
 
sqlSP.Connection = sqlConn
sqlSP.CommandType = CommandType.StoredProcedure
 
sqlSP.ExecuteReader()
 
sqlConn.Close()
End Sub

This doesn't seem right, how exactly is this supposed to be coded?

Thanks!

[danasegarane]

Dim sqlSP As New ADODB.Command
   Set sqlSP = Nothing
    sqlSP.ActiveConnection = sqlConn
    sqlSP.CommandType = adCmdStoredProc
    sqlSP.CommandText = "MTFE_UpdateTable"
   sqlConn.open sqlSP

[capella07]

danasegarane,

Thanks for your reply.

I copy/pasted your example into my sub and (naturally) had to change a couple of things. I made the changes according to what was available in the Intellisense list as I entered the code. Here's where I'm at, at this point:

Dim sqlSP As New SqlCommand 'You had ADODB.Command
sqlSP = Nothing
sqlSP.Connection = sqlConn 'You had "sqlSP.ActiveConnection..."
sqlSP.CommandType = CommandType.StoredProcedure 'You had "adCmdStoredProc" for the command type
sqlSP.CommandText = "MTFE_UpdateTable"
sqlConn.Open()

The biggest issue is, the sqlConn.Open line. I get a "blue squiggly" under the line when I put "sqlSP" in the parenthesis - "Overload resolution failed because no accessible 'Open' accepts this number of arguments."

Any idea how to fix that?

Thanks again!

[steve_rm]

Hello

You could try this to create a command

sqlCommand cmd = sqlConn.createCommand()

However, this is in c#
I think vb would be:

dim cmd = sqlcnn.createCommand()

Are you also sure you are initializing your connection with a connection string.
Good website:
www.connectionstrings.com

Steve

[capella07]

Thanks, Steve.

Yes, the connection string is working properly - it's used elsewhere in other Subs.

[Edit]
Also, I did try working with "createcommand", but couldn't figure out where to go with that.

[capella07]

Got it!

Actually, I found the solution in an ASP.NET book I have (& love dearly), "ASP.NET 2.0: Unleashed" by Stephen Walther, Sams Publishing, (c) 2006.

Dim sqlSP As New SqlCommand("MTFE_UpdateTable", sqlConn)
sqlSP.CommandType = CommandType.StoredProcedure
sqlConn.Open()
Dim reader As SqlDataReader = sqlSP.ExecuteReader

I simply put this code in a Sub and called the sub from a button click event- viola! Updated table!