sql server programming: calling sp from vb.net -- sql server programming

Re: calling sp from vb.net

Mike C#
9/20/2006 9:00:55 PM

Yeah, use the System.Data.SqlClient adapter instead of System.Data.Odbc.
Older ODBC drivers may not support named parameters.

[quoted text, click to view]

Re: calling sp from vb.net

Mike C#
9/20/2006 9:14:20 PM

Almost forgot - with ODBC you have to use the archaic { CALL } syntax with
question mark placeholders. Try this one on for size. Notice the {CALL
sp_TestSP (?, ?, ?)} syntax, one more reason to strongly consider moving to
the native SqlClient if possible:

Dim sqlCon As System.Data.Odbc.OdbcConnection
Dim sqlCmd As System.Data.Odbc.OdbcCommand
Try
sqlCon = New
System.Data.Odbc.OdbcConnection("DSN=Test;Trusted_Security=Yes;")
sqlCon.Open()
sqlCmd = New System.Data.Odbc.OdbcCommand("{ CALL sp_TestSP (?, ?,
?) }", sqlCon)
sqlCmd.CommandType = CommandType.StoredProcedure
sqlCmd.Parameters.Add("@field1", Odbc.OdbcType.NVarChar, 50).Value =
"Hello"
sqlCmd.Parameters.Add("@field2", Odbc.OdbcType.NVarChar, 50).Value =
"There"
sqlCmd.Parameters.Add("@field3", Odbc.OdbcType.NVarChar, 50).Value =
"Willie"
Dim rc As Integer = sqlCmd.ExecuteNonQuery()
Catch ex As Exception
Throw ex
Finally
If Not (sqlCmd Is Nothing) Then
sqlCmd.Dispose()
End If
If Not (sqlCon Is Nothing) Then
sqlCon.Dispose()
End If
End Try

[quoted text, click to view]

calling sp from vb.net

Phil Hellmuth
9/20/2006 11:26:31 PM

I hope this is the correct forum for this issue. I'm trying to call a
SQL stored procedure from vb.net using parameters, but am running into
problems. Here's pertinent SP code:

CREATE PROCEDURE dbo.sp_TestSP
( @Field1 nvarchar(11)
, @Field2 nvarchar(30)
, @Field3 nvarchar(30)
)
AS
declare @Field4 int
, @Field5 int

etc...

Here's the code that calls the sp:

cn = New System.Data.Odbc.OdbcConnection(connectionString)
Try
cn.Open()
cmdSP = New System.Data.Odbc.OdbcCommand("sp_TestSP", cn)
cmdSP.Parameters.Add("@Field1", Odbc.OdbcType.NVarChar, 11).Value =
txtField1.Text
cmdSP.Parameters.Add("@Field2", Odbc.OdbcType.NVarChar, 30).Value =
txtField2.Text
cmdSP.Parameters.Add("@Field3", Odbc.OdbcType.NVarChar,
30).Value = txtField3e.Text
rc = cmdSP.ExecuteNonQuery()

etc....

When executing the SP, I get the following error message:
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
'sp_TestSP' expects parameter '@Field1', which was not supplied.

Did I miss a step? I've tried different methods for creating parms, but
nothing seems to work. I'm banging my head against the wall on this.

Re: calling sp from vb.net

Mike C#
9/21/2006 12:10:09 AM

SqlClient is faster, and easier to use (IMHO). The SqlClient code for what
you're doing would look like a lot like the ODBC code, except you don't have
to use the { CALL sp (?, ?, ?) } format to execute a parameterized SP. You
can also do named parameters in straight queries, which ODBC does not
support either. With ODBC the order you add your parameters in matters; if
you switch around the sqlCmd.Parameters.Add statements in the ODBC version
you could end up with different results and/or exceptions. The SqlClient
supports named parameters, and they can be added in any order for SP's or
queries. Also note that the connection string options are a little
different between ODBC and SqlClient. Below is an example that works on my
local machine. This is what the code below looks like with the SqlClient:

Dim sqlCon As System.Data.SqlClient.SqlConnection
Dim sqlCmd As System.Data.SqlClient.SqlCommand
Try
sqlCon = New System.Data.SqlClient.SqlConnection("DATA
SOURCE=(local);INITIAL CATALOG=my_database;INTEGRATED SECURITY=SSPI;")
sqlCon.Open()
sqlCmd = New System.Data.SqlClient.SqlCommand("sp_TestSP", sqlCon)
sqlCmd.CommandType = CommandType.StoredProcedure
sqlCmd.Parameters.Add("@field1", SqlDbType.NVarChar, 50).Value = "Hello"
sqlCmd.Parameters.Add("@field2", SqlDbType.NVarChar, 50).Value = "There"
sqlCmd.Parameters.Add("@field3", SqlDbType.NVarChar, 50).Value =
"Willie"
Dim rc As Integer = sqlCmd.ExecuteNonQuery()
Catch ex As Exception
Throw ex
Finally
If Not (sqlCmd Is Nothing) Then
sqlCmd.Dispose()
End If
If Not (sqlCon Is Nothing) Then
sqlCon.Dispose()
End If
End Try

[quoted text, click to view]

Re: calling sp from vb.net

Phil Hellmuth
9/21/2006 12:29:37 AM

Thanks for the response. I tried your suggestion, but get the same
error. Any other thoughts?

[quoted text, click to view]

Re: calling sp from vb.net

Phil Hellmuth
9/21/2006 2:14:21 AM

That did the trick. I ran into some odd connection issues using
SqlClient, and I'm in no mood for a new round of troubleshooting.
Thanks so much.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.