SQL error code and # rows affected

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.

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.

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. :)

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.

Code:

---

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:

Code:

---

    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

---

I'll try it. Thanks Serge! :)

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