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


Brian Henry wrote:
Show quoteHide quote

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.

bishop wrote:
Show quoteHide quote

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.


Yuan Ren[MSFT] wrote:
Show quoteHide quote

hi cj,

i mean the parameter direction in your program as brian has showed.

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


diego wrote:
Show quoteHide quote

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

hi cj,

have you tried setting your parameters' direction to output.

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



diego wrote:
Show quoteHide quote

Collections challenge (MRU)
Retrieve data from xml document
Determining the Browser and event.keyCode vs. event.which
difference between ME and MYCLASS
Inheritance doubt.
hair cross cursor
advice needed for a good book to make good database applications
How select from 2 ADO.NET DataTables?
Retainig location in autopostback
VB.Net & SQL2005 Stored Procedures