Output parameters in the data environment

[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.