how do I call a SQL Server stored procedure?

  • Thread starter Thread starter cj
  • Start date Start date
C

cj

I need to call a stored procedure that passes back 2 strings. I don't
think I need a data adapter or a select command or a command builder.
So after I create the connection what do I do?

Dim mySqlConnection As New SqlConnection
mySqlConnection.ConnectionString = conStr
?
?
?
 
how is it passing back strings? in parameters or in a table? if its a table
with multiple rows or columns you need a data adapter.... if it is using
output parameters you need only a command object with the parameters its
coming back marked as output direction.

dim cmd as sqlclient.sqlcommand("SP_Proc",databasecon)

cmd.commandtype = commandtypes.storedprocedure

cmd.parameters.add("@retstring1",nvarchar(100))
cmd.parameters("@retstring1").direction = output

cmd.executenonquery()

debug.writeline(cmd.paramter("@restring1").value.tostring)

that isnt entirely the correct names and methods but that should show you
what to do to get an output param back if that is how you are doing it
 
I wrote it out like you showed but haven't been able to work a bug out
of it. When I have 2 parameters added (the stored procedure returns 2
strings, it doesn't take anything), I'm trapping an error "Procedure
priority_high has no parameters and arguments were supplied.

If I remark out the two parameters.add lines and run it, it passes the
executenonquery line and when I go to display in a message box the 2
strings It's supposed to return I trap the error "An sqlparameter with
parametername '@mBTN' is not contianed by this SqlParameter Collection."

Any ideas?

Code snipit

Dim HighSqlCommand As New SqlClient.SqlCommand
HighSqlCommand.Connection = mySqlConnection
HighSqlCommand.CommandType = CommandType.StoredProcedure
HighSqlCommand.CommandText = "priority_high"
HighSqlCommand.Parameters.Add("@mBTN", SqlDbType.Char = 6)
HighSqlCommand.Parameters.Add("@mUID", SqlDbType.Char = 10)

mySqlConnection.Open()
Try
HighSqlCommand.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show("Execute error: " & ex.Message)
End Try

mySqlConnection.Close()

Try
MessageBox.Show(HighSqlCommand.Parameters("@mBTN").ToString
& " " & HighSqlCommand.Parameters("@mUID").ToString)
Catch ex As Exception
MessageBox.Show("MessageBox error: " & ex.Message)
End Try
 
See this link http://www.vbdotnetheaven.com/Code/Jun2003/2102.asp
By the way ExecuteNonQuery is for "action" querries - those that don't
return any data but instead perform an action.
If you are just starting to work with stored procedures in vb.net
forget about using output parameters. Make the stored procedure return
a row of data and use that to populate a dataset or datatable in
vb.net. The link above can help with that.
Hope this helps.
Bishop
 
I'll look at the link soon. We ended up combining the two fields being
returned into one within the stored procedure and then we can pick it up
with execute scalar. I'm not really interested in returning a dataset.
I just want 2 short stings. I'd love to figure out how to get those
returned with out going in to datasets.
 
Hi CJ,

Thanks for your post!

From your description, my understanding is that the current stored
procedure uses the select statement and returns one row which contains two
columns. If I have misunderstood anything, please let me know.

If you don't want to use the DataAdapter with the DataSet, I suggest you
use the DataReader to instead of the DataAdapter. The following article
from MSDN demonstrates how to use the DataReader :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpcontheadonetdatareader.asp

If the stored procedure uses the output parameter as the returned value,
Brian's suggestion is appropriated.

Hope this will be helpful!

Regards,

Yuan Ren [MSFT]
Microsoft Online Support
 
We were using the output parameter but never could get Brian's method to
work. We switched to putting to two fields into one variable and using
the select method with execute scalar.
 
hi cj,

i mean the parameter direction in your program as brian has showed.
cmd.parameters.add("@retstring1",nvarchar(100))
cmd.parameters("@retstring1").direction = output <---- this line
Click to expand...

hth,
diego
 
I believe we did catch that but if I remember correctly it started
giving us True True. We're not sure why as we were expecting to see
000001 2326678295
 
I believe we did catch that but if I remember correctly it started
giving us True True. We're not sure why as we were expecting to see
000001 2326678295
 
hi cj,

i think the problem is with the following lines:

HighSqlCommand.Parameters.Add("@mBTN", SqlDbType.Char = 6)
HighSqlCommand.Parameters.Add("@mUID", SqlDbType.Char = 10)

vb converts the output to bit types, i don't know why it does this. try
using the following format:

HighSqlCommand.Parameters.Add("@mBTN", SqlDbType.Char, 6)
HighSqlCommand.Parameters.Add("@mUID", SqlDbType.Char, 10)


hth,

diego
 
Back
Top