SQL error code and # rows affected

[WadeD]

Hi, if I call a stored procedure in SQL Server (passing it parameters), how can I get the SQL error code (same as VB error code??) and # rows affected?

Thanks in advance.

[JHausmann]

All stored procedures return status, which can be checked with err.number, I believe. I'm not sure you'll be able to get the number of affected rows, though.

[WadeD]

Anyone know definitely that the error code in vb is the same as the code returned from SQL Server, or about the # rows affected? Thanks for the quick reply John.

[Serge]

You'll have to return Error number from the Stored Procedure. If you want to return number of records effected you can use global variable @@ROWCOUNT.

Create Procedure UpdateMyTable
@FieldId int

As

UPDATE MyTable SET MyField = 'Jones'
WHERE FieldId = @FieldId

Return @@ROWCOUNT

Then from VB you can do something like this:

    Dim cm As New ADODB.Command
    Dim cn As New ADODB.Connection
    
    cn.Open "DSN=MyDSN", "sa", ""
    
    With cm
        Set .ActiveConnection = cn
        .CommandText = "UpdateMyTable"
        .CommandType = adCmdStoredProc
        'In this example I'm using MyFieldId=15
        .Parameters.Append .CreateParameter("MyFieldId", adInteger, adParamInput, , 15)
        .Parameters.Append .CreateParameter("Ret_Val", adInteger, adParamReturnValue)
        .Execute
        
        MsgBox "Number of Records effected: " & .Parameters("Ret_Val").Value
    End With
    Set cm = Nothing
    
    cn.Close
    Set cn = Nothing

[WadeD]

I'll try it. Thanks Serge!

[WadeD]

I'm getting:
Run-time error -2147217900 (80040e14)
Too many arguments were supplied for procedure UpdateMyTable.