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 .ConnectionStri ng = conStr
?
?
?

[Brian Henry]

Re: how do I call a SQL Server stored procedure?

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.sqlco mmand("SP_Proc" ,databasecon)

cmd.commandtype = commandtypes.st oredprocedure

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

cmd.executenonq uery()

debug.writeline (cmd.paramter(" @restring1").va lue.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

[cj]

Re: how do I call a SQL Server stored procedure?

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.SqlCo mmand
HighSqlCommand. Connection = mySqlConnection
HighSqlCommand. CommandType = CommandType.Sto redProcedure
HighSqlCommand. CommandText = "priority_h igh"
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("@m BTN").ToString
& " " & HighSqlCommand. Parameters("@mU ID").ToString )
Catch ex As Exception
MessageBox.Show ("MessageBox error: " & ex.Message)
End Try


Brian Henry wrote:[color=blue]
> 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.sqlco mmand("SP_Proc" ,databasecon)
>
> cmd.commandtype = commandtypes.st oredprocedure
>
> cmd.parameters. add("@retstring 1",nvarchar(100 ))
> cmd.parameters( "@retstring1"). direction = output
>
> cmd.executenonq uery()
>
> debug.writeline (cmd.paramter(" @restring1").va lue.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
>
>[/color]

[bishop]

Re: how do I call a SQL Server stored procedure?

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

[cj]

Re: how do I call a SQL Server stored procedure?

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:[color=blue]
> 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
>[/color]

[Yuan Ren[MSFT]]

Re: how do I call a SQL Server stored procedure?

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/cpcontheadonetd atareader.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

[diego]

Re: how do I call a SQL Server stored procedure?

hi cj,

have you tried setting your parameters' direction to output.

diego

[cj]

Re: how do I call a SQL Server stored procedure?

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:[color=blue]
> 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/cpcontheadonetd atareader.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
>[/color]

[diego]

Re: how do I call a SQL Server stored procedure?

hi cj,

i mean the parameter direction in your program as brian has showed.
[color=blue]
> cmd.parameters. add("@retstring 1",nvarchar(100 ))
> cmd.parameters( "@retstring1"). direction = output <---- this line[/color]

hth,
diego

[cj]

Re: how do I call a SQL Server stored procedure?

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:[color=blue]
> hi cj,
>
> have you tried setting your parameters' direction to output.
>
> diego
>[/color]

[cj]

Re: how do I call a SQL Server stored procedure?

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:[color=blue]
> hi cj,
>
> i mean the parameter direction in your program as brian has showed.
>[color=green]
>> cmd.parameters. add("@retstring 1",nvarchar(100 ))
>> cmd.parameters( "@retstring1"). direction = output <---- this line[/color]
>
> hth,
> diego
>[/color]

[diego]

Re: how do I call a SQL Server stored procedure?

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