469,336 Members | 6,051 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,336 developers. It's quick & easy.

how do I call a SQL Server stored procedure?

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
?
?
?

Mar 10 '06 #1
11 15754
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.t ostring)

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
Mar 10 '06 #2
cj
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
Brian Henry wrote:
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.t ostring)

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

Mar 10 '06 #3
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

Mar 10 '06 #4
cj
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.

bishop wrote:
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

Mar 10 '06 #5
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/de...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

Mar 13 '06 #6
hi cj,

have you tried setting your parameters' direction to output.

diego

Mar 13 '06 #7
cj
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.
Yuan Ren[MSFT] wrote:
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/de...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

Mar 13 '06 #8
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


hth,
diego

Mar 13 '06 #9
cj
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

diego wrote:
hi cj,

have you tried setting your parameters' direction to output.

diego

Mar 13 '06 #10
cj
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
diego wrote:
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


hth,
diego

Mar 13 '06 #11
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

Mar 14 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by mike | last post: by
2 posts views Thread by honcho | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.