retrieving RAISERROR errors from VB code

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

retrieving RAISERROR errors from VB code

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-17 : 09:03:05

Claude writes "I am unable to properly retrieve RAISERROR errors from VB code.

The basis:
Win2000, SQL2000, MDAC 2.7 provider SQLOLEDB.1, VB6

Test proc:
CREATE PROCEDURE test ( @v as varchar(20) output )
AS
declare @err int

SELECT @v= 'before'

RAISERROR('An error',16,1)
SELECT @err=@@Error

SELECT @v= 'after'
RETURN (@Err)

From VB6:
Works:
VB error is raised with 'An error' message
p(1) = 'after'
p(0)= 50000

Doens't work:
activeConnection.errors always empty ???

I tested the cursorLocation client and server without any better results.

PRINT is not helping.
When placed before the raiserror is never raised in VB.
When place after the message is not availaible in VB

Any help would be appreciated.
Regards.
CL"

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-17 : 13:38:38

@@Error traps the error number of the MOST RECENTLY EXECUTED STATEMENT. So, in your example, it is trapping any error that occurred during the RAISEERROR command. See this article for more info: [url]http://www.sqlteam.com/item.asp?ItemID=6881[/url]

-------------------
It's a SQL thing...

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2001-12-17 : 16:59:52

Claude,

I found that the best way to trap any error from SQL Server through VB/ADO is to use the connection object "WithEvents"

eg.

'<VB>
Private withevents mCNN as ADO.Connection
'The event stubs of most use are the following
' ConnectComplete and ExecuteComplete

Private Sub mCNN_ConnectComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
On Error Resume Next

If adStatus = adStatusErrorsOccurred Then
RaiseErr "Connection Failed", pError.Description, pError.NativeError, pError.Source
End If

End Sub

'Check Execution OK
Private Sub mCNN_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
On Error Resume Next

If adStatus = adStatusErrorsOccurred Then
RaiseErr "Execution Failed", pError.Description, pError.NativeError, pError.Source
End If

End Sub

'</VB>

Hope this Helps

DavidM

"Why are you crying?"

"Because I love a Microsoft product"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources