Retrieve Output from a stored Procedure

[Dave McKie]

Hi all,

Need some help again. I am trying to retrieve an output from a Stored
Procedure...not having much luck. If anyone could steer me in the
right direction, it would be appreciated. I'm using SQL 2000 and
VB.Net

the Stored Proc is working fine. The following is just a test to
return it to the program

Dim dbConn As New SqlConnection(L CDBConnect.Conn ectionString)
Dim dbCmd As New SqlCommand
Dim da As New SqlDataAdapter( dbCmd)

dbCmd.CommandTy pe = CommandType.Sto redProcedure
dbCmd.Connectio n = dbConn
dbCmd.CommandTe xt = "spRenTempTable "
dbCmd.Parameter s.Add("@MyTable ", SqlDbType.VarCh ar)

dbCmd.Parameter s("@MyTable").D irection =
ParameterDirect ion.Output
Try
dbConn.Open()
dbCmd.ExecuteNo nQuery()
MessageBox.Show ("Return Value : " &
dbCmd.Parameter s("@MyTable").V alue)
Catch ex As Exception
MessageBox.Show (ex.ToString())
Finally
dbConn.Close()
End Try
thanks for your help
Dave McKie
11/1/2004

[W.G. Ryan eMVP]

Check out the at Retrieving the Gazoutas: Understanding SQL Server Return
Codes and Output Parameters
http://www.betav.com/msdn_magazine.htm

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Dave McKie" <da*********@ya hoo.com> wrote in message
news:b6******** *************** **@posting.goog le.com...

Hi all,

Need some help again. I am trying to retrieve an output from a Stored
Procedure...not having much luck. If anyone could steer me in the
right direction, it would be appreciated. I'm using SQL 2000 and
VB.Net

the Stored Proc is working fine. The following is just a test to
return it to the program

Dim dbConn As New SqlConnection(L CDBConnect.Conn ectionString)
Dim dbCmd As New SqlCommand
Dim da As New SqlDataAdapter( dbCmd)

dbCmd.CommandTy pe = CommandType.Sto redProcedure
dbCmd.Connectio n = dbConn
dbCmd.CommandTe xt = "spRenTempTable "
dbCmd.Parameter s.Add("@MyTable ", SqlDbType.VarCh ar)

dbCmd.Parameter s("@MyTable").D irection =
ParameterDirect ion.Output
Try
dbConn.Open()
dbCmd.ExecuteNo nQuery()
MessageBox.Show ("Return Value : " &
dbCmd.Parameter s("@MyTable").V alue)
Catch ex As Exception
MessageBox.Show (ex.ToString())
Finally
dbConn.Close()
End Try
thanks for your help
Dave McKie
11/1/2004