How can I get the result of store procedure in visual basic?
-
Hey everybody, I'm working in a store procedure with parameters and I want to show to user when some parameter's value is bad or is missed.
I check the parameter's value in the store procedure and if something is bad, stop the execution with "RETURN 50001", but I don't know how to catch the return's value.
I'm working with SqlServer 2000 and vb 6.0
Something like this:
IN SQL SERVER
sp_checksome
@parameter1 varchar2(20),@parameter2 int
as
if len(@parameter1)=0
begin
return 50001
end
IN VISUAL BASIC
private function updsome(byval strProcName as string, byval CxnConexion as ADODB.Connection,Paramarray arrParameters() as variant)
Dim objCmd As ADODB.Command
Set objCmd = New ADODB.Command
With objCmd
.CommandText = strProcName
.CommandType = adCmdStoredProc
Set .ActiveConnection = CxnConexion
For varIndex = 0 To UBound(arrParameters)
.Parameters(varIndex + 1).Value = arrParameters(varIndex)
Next
.Execute Options:=adExecuteNoRecords
How to catch the return's value?
end with
-
Here's how I would do it in VB6:
Dim strParam1 As String, intParam2 As Integer
strParam1 = ""
intParam2 = 7
Dim prm As ADODB.Parameter
Dim pCmd As ADODB.Command
Set pCmd = New ADODB.Command
With pCmd
.ActiveConnection = CxnConexion
.CommandText = "dbo.pr_checksome"
.CommandType = adCmdStoredProc
.CommandTimeout = 15
Set prm = .CreateParameter("Return", adInteger, adParamReturnValue)
.Parameters.Append prm
Set prm = .CreateParameter("inParam1", adChar, adParamInput, 5, strParam1)
.Parameters.Append prm
Set prm = .CreateParameter("inParam2", adInteger, adParamInput, 4, intParam2)
.Parameters.Append prm
End With
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Set rst = pCmd.Execute
Debug.Print pCmd.Parameters("Return").Name; pCmd.Parameters("Return").Value
Debug.Print pCmd.Parameters("inParam1").Name; pCmd.Parameters("inParam1").Value
Debug.Print pCmd.Parameters("inParam2").Name; pCmd.Parameters("inParam2").Value
Set rst = Nothing
Set pCmd = Nothing
Set CxnConexion = Nothing
... and here's how I would do it in VB.Net:
Dim cnn As New SqlConnection(CxnConexion)
Dim cmd As New SqlCommand("sp_checksome", CxnConexion)
Dim da As New SqlDataAdapter(scmd)
Dim dsSomeData As New DataSet()
scmd.CommandType = CommandType.StoredProcedure
' Add a parameter to capture the return value sent back by the
' stored procedure via the RETURN statement
With scmd.Parameters
.Add(New SqlParameter("@parameter1", SqlDbType.NVarChar)).Value = _
SomeText.text
.Add(New SqlParameter("@parameter2", _
SqlDbType.Int)).Value = _
SomeCombo.SelectedValue
.Add(New SqlParameter("ReturnValue", _
SqlDbType.Int)).Direction = ParameterDirection.ReturnValue
End With
Try
sda.Fill(dsSomeData, "SomeData")
Catch eSQL As SqlException
MessageBox.Show(eSQL.ToString, Me.Text, _
MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End Try
' Display the value
lblReturnValue.Text = scmd.Parameters("ReturnValue").Value.ToString
-
Thanks Alonzo, I'm going to test it.
-
Alonzo, I have a doubt.
The parameters are declared in the store procedure, Why do I have to declare and add the parameters in VB ? It's necessary ?
Thanks for your support.
-
Yes, that is because SQL Server automatically has a RETURN_VALUE output, shich you need to catch.
N 56°04'39.16"
E 12°55'05.25"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply