Stored proc return codes in Access .ADP

[sslavens]

Hey All,
I have am working on an Access Development Project (ADP).
I have developed a SQLSERVER stored proc.
I would like to pass back a return code from the procedure depending on the success and failure of specific parts of a transaction.
I know that I can pass a parameter to the procedure using the following:

Dim cmd As ADODB.Command
Dim intMyID As String

intMyID = 1

Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "EXEC Insertallchecklist " & intMyID
.CommandType = adCmdText
.Execute
End With

How do I send a value back to the application, and capture it in my code?

Any help is appreciated.

Scott

[emsik1001]

Hi

The easiest way (at least for me) is to create a stored procedure to write the result in a table and then retrevie the result from the table.

Regards
Emil

[ADezii]

Hey All,
I have am working on an Access Development Project (ADP).
I have developed a SQLSERVER stored proc.
I would like to pass back a return code from the procedure depending on the success and failure of specific parts of a transaction.
I know that I can pass a parameter to the procedure using the following:

Dim cmd As ADODB.Command
Dim intMyID As String

intMyID = 1

Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "EXEC Insertallchecklist " & intMyID
.CommandType = adCmdText
.Execute
End With

How do I send a value back to the application, and capture it in my code?

Any help is appreciated.

Scott

You can return information to a calling Program using a Stored Procedure's Return Value via the Return Statement. When a Stored Procedure processes a RETURN Statement, it unconditionally terminates and sends back the optional Return Value to the calling Program. The Return Value is really a special type of Output Parameter with the name "@return_value". Hopefully, the code below will illustrate this point:

‘Example of a Stored Procedure accepting 1 Input Parameter, namely @custid
Create Procedure <procYourProcedureName>
  @custid                 ‘1Input Parameter
As 
  If (SELECT Count(*) FROM tblCustomer WHERE CustomerID = @custid) >= 1
    ‘Success
    RETURN 0
  Else
    ‘Failure
    RETURN 1

‘Example of a Sub-Routine that retrieves the Return Value from the Procedure ‘<procYourProcedureName>
Sub <Your Sub Name>(lngCustId As Long)
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
 
Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
 
With cmd
   ActiveConnection = CurrentProject.Connection
  .CommandText = "<procYourProcedureName>”
  .CommandType = adCmdStoredProcedure
  .Parameters.Refresh
  .Parameters(“@custid”) = lngCustId      ‘lngCustId now becomes the Input Parameter for
                                                                  ‘the <procYourProcedureName> Procedure
      .Execute
End With
 
‘The Return Value is sent back via the ‘@return_value’ Parameter
If cmd.Parameters(“@return_value”) = 0 Then        ‘special type of Output Parameter
  ‘CustomerID lngCustID exists
Else
  ‘CustomerID lngCustID does not exist
End If
 
Set cmd = Nothing
Cnn.Close
Set cnn = Nothing
End Sub