How to trap error message from sql server 2000

[Guest]

The customer has sql server 2000 with access 2000 client (adp).

From the client the operator start a delete followed by an insert into a sql
server table, about 150000 old records need to be deleted and then new
records are inserted. The delete and insert is done in a stored procedure
within a transaction block.

When an error occurs I do a rollback of the delete/insert operation.
I have noticed that insert sometimes create @@Error = 515. This initiates
the rollback and I return an error message "Error in insert operation, no
data is changed" as output parameter to the client.

The error message is not of much help to the operator. I need to trap the
full error text for @@error = 515. (Cannot insert the value NULL into column
...... etc) and return this message to the operator in connection with the
rollback. How can this be done?

Regards

Tore

 

[Sylvain Lafontaine]

Use your own ADO connection object to make the call and enumerate each
errors in the Errors collection. See for example:

http://support.microsoft.com/kb/168336
http://www.devx.com/tips/Tip/13483

Instead of creating your own connection object, you can also try using
directly the CurrentProject.Connection object.

 

[Sylvain Lafontaine]

And: http://www.w3schools.com/ado/ado_ref_error.asp

 

[Guest]

Thank you for your response. This is just what I needed. I am quite
impressed by this response as well as others I have seen from you.

Regards
Tore

 

[Sylvain Lafontaine]

Of course, in your case, as you are already trapping the error in your SP,
using a Command Object with Parameters or simply returning the error as part
of a Select statement should be easier to implement.

 

[Vayse]

I was going to post a similiar question when I saw this one.
Here's my code at the moment.
With cmdCopy
.ActiveConnection = CurrentProject.Connection
.CommandText = "Copy_Renewal"
.CommandType = adCmdStoredProc

Some questions:
1) If I loop through cmdCopy.ActiveConnection.Errors - does display only
errors for cmdCopy? Or could it contain errors for prior commands that were
run using CurrentProject.Connection ?
2) Can you explain what you mean by returning the error as part of the
select statement.

thanks
Vayse

 

[Sylvain Lafontaine]

A quick test seems to show that only the errors associated with the latest
call to Execute are displayed. Howerver, it would not be a bad ideal to
call Cnn.Errors.Clear everytime to make sure that everything is OK;
particularly if you want to make sure that there has been no error while
executing something.

These tests have been made using the Raiserror command but you should try
with instructions that will generate one or more errors (for example,
inserting an invalid foreign key) without stopping the SP:

Private Sub Command1_Click()

' Create a distinct connection to make sure that there is no
interference
' but CurrentProject.Connection could be directly used as well:
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open CurrentProject.BaseConnectionString

On Error Resume Next

cnn.Errors.Clear
cnn.Execute "raiserror('Calling error #1', 11, 1)"
ErrHandler_WithoutClear cnn

cnn.Execute "raiserror('Calling error #2', 11, 1)"
cnn.Execute "raiserror('Calling error #3', 11, 1)"
ErrHandler_WithoutClear cnn

cnn.Execute "Print 9"
cnn.Execute "Print 10"
ErrHandler_WithoutClear cnn

On Error GoTo 0

End Sub

Sub ErrHandler_WithoutClear(objCon As ADODB.Connection)

Dim ADOErr As ADODB.Error
Dim strError As String

For Each ADOErr In objCon.Errors
strError = "Error #" & ADOErr.Number & vbCrLf & ADOErr.Description _
& vbCr & _
" (Source: " & ADOErr.Source & ")" & vbCr & _
" (SQL State: " & ADOErr.SQLState & ")" & vbCr & _
" (NativeError: " & ADOErr.NativeError & ")" & vbCr

If ADOErr.HelpFile = "" Then
strError = strError & " No Help file available" & vbCr & vbCr
Else
strError = strError & " (HelpFile: " & ADOErr.HelpFile & ")" &
vbCr & _
" (HelpContext: " & ADOErr.HelpContext & ")" & vbCr & vbCr
End If

' -- Debut.Print here! --
Debug.Print strError
MsgBox strError
Next

' -- No clearing of the errors here! --
' objCon.Errors.Clear

End Sub


For your second question, trap the error in the SP by watching the @@error
variable; see:
http://www.code-magazine.com/Article.aspx?quickid=0305111
http://www.simple-talk.com/sql/t-sql-programming/sql-server-error-handling-workbench/

 

[Vayse]

A quick test seems to show that only the errors associated with the latest
call to Execute are displayed. Howerver, it would not be a bad ideal to

<snip?

Thanks Sylvain. You truly are a mine of information!