Output parameters in the data environment

[kisijo]

I am trying to extract a value from a stored procedure using the data environment. The stored procedure is adding a new record to a table, and i would like a value returned that the stored procedure is generating. I have tried setting the parameter in the data environment to a direction of Output as well as Input/Output, only to get an error message saying the parameters are conflicting with one another. When the direction is set back to input there is no problem adding the record to the table except i cant get back the value i am after. Any help would be apprecitated

Thanks
Simon

[Serge]

Sure. Here's an example. Let's say I have a Stored Procedure that will new insert a new record in a table called Customer. This procedure also will return a new generated ID of the new customer we insert (assuming that CustomerId field is an Identity field). Here's how the Stored Procedure will look like:

Create Procedure AddNewCustomer

@CustomerName varchar(50),
@Address char(20),
@City char(20),
@State char(2),
@Zip char(5),
@CustomerId int OUTPUT

AS

Begin Transaction

Insert Customer (CustomerName, Address, City, State, Zip)
Values (@CustomerName, @Address, @City, @State, @Zip)

If @@Error <> 0
    Begin
        Rollback
    End
Else
    Begin
        Commit
        Select @CustomerId = @@Identity
    End

Return

Now VB part. In this example I'm using ADO, so add a reference appropriately.

Private Sub Command2_Click()
    Dim cn As New ADODB.Connection
    Dim cm As New ADODB.Command
    Dim parInput As New ADODB.Parameter
    Dim parOutput As New ADODB.Parameter
    
    On Error Resume Next
    
    'Assuming that I have DSN already setup
    cn.Open "DSN=MyDSN"
    
    Set cm.ActiveConnection = cn
    cm.CommandType = adCmdStoredProc
    cm.CommandText = "AddNewCustomer"
    
    'CustomerName
    Set parInput = cm.CreateParameter("CustomerName", adVarChar, adParamInput, 50, "John Doe")
    cm.Parameters.Append parInput
    
    'Address
    Set parInput = cm.CreateParameter("Address", adChar, adParamInput, 20, "1122 Main Street")
    cm.Parameters.Append parInput
    
    'City
    Set parInput = cm.CreateParameter("City", adChar, adParamInput, 20, "Philadelphia")
    cm.Parameters.Append parInput
    
    'State
    Set parInput = cm.CreateParameter("State", adChar, adParamInput, 2, "PA")
    cm.Parameters.Append parInput
    
    'Zip
    Set parInput = cm.CreateParameter("Zip", adChar, adParamInput, 5, "19111")
    cm.Parameters.Append parInput
    
    'CustomerId output
    Set parOutput = cm.CreateParameter("CustomerId", adInteger, adParamOutput)
    cm.Parameters.Append parOutput
    
    cm.Execute
    
    If Err.Number <> 0 Then
        MsgBox "Error executing Stored Procedure."
        Exit Sub
    End If
    
    MsgBox "New CustomerId is: " & parOutput.Value
End Sub

That's it.