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.
Printable View
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.
Then from VB you can do something like this:Code:Create Procedure UpdateMyTable
@FieldId int
As
UPDATE MyTable SET MyField = 'Jones'
WHERE FieldId = @FieldId
Return @@ROWCOUNT
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.