September 12, 2007 at 5:59 am
Can anyone provide a quick code snippet for capturing the SQL return code of a stored procedure in unbound VB code? I know it sounds like a simple thing but every search I've done to find some seems to provide extremely esoteric examples or just explains how the return code works (not how to receive it's value). The code structure I use for other proc work is like below:
Public Function GetSomeRecords(ByVal vValOne As String) As Data.DataSet
Dim oSqlConn As Data.SqlClient.SqlConnection = SqlConnection()
Try
Dim oSqlCmdList As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("SPName", oSqlConn)
oSqlCmdList.CommandType = Data.CommandType.StoredProcedure
Dim oSqlParmManager As New Data.SqlClient.SqlParameter("@ValOne", vValOne)
oSqlCmdList.Parameters.Add(oSqlParmManager)
oSqlConn.Open()
Dim oSQLDataAdapter As Data.SqlClient.SqlDataAdapter = New Data.SqlClient.SqlDataAdapter
Dim oDataSet As Data.DataSet = New Data.DataSet
oSQLDataAdapter.SelectCommand = oSqlCmdList
oSQLDataAdapter.Fill(oDataSet, "ProcData")
Return oDataSet
Catch
Return Nothing
Finally
If oSqlConn.State = Data.ConnectionState.Open Then
oSqlConn.Close()
End If
End Try
End Function
Thanks, John
September 12, 2007 at 10:26 am
Check out http://msdn2.microsoft.com/en-us/library/59x02y99(vs.71).aspx.
You basically need to create a parameter with a Directon of ReturnValue (1 per command, I'm pretty certain).
You are using a dataset here. However, in earlier versions of .Net, if you returned a SQLDataReader, you had to first make sure that you read the reader data then you could get the return value.
Russel Loski, MCSE Business Intelligence, Data Platform
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply