retrieving sql server error messsage from a trigger in VB6

I have a trigger that uses raiserror, ie
RAISERROR ('Error happened here.', 16, 1)

How can I obtain this error's string though VB code?

marshallgarzaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

stiemarkCommented:
On Error GoTo err_MyError

code
code
code


err_MyError:
    MsgBox prompt:="Error number: " & Err.Number & "- " & Err.Description, _
                   Buttons:=vbCritical + vbMsgBoxHelpButton, _
                   Title:="Edit Record Error", _
                   HelpFile:=Err.HelpFile, _
                   context:=Err.HelpContext
0
marshallgarzaAuthor Commented:
I'm not getting the error message I specified in the trigger, ie ''Error happened here'
0
Anthony PerkinsCommented:
Is this what you mean:

CREATE TABLE Table1 (
     ID int IDENTITY(1, 1) NOT NULL ,
     Now smalldatetime NULL)

__________________________________________

Create Trigger tr_Table_Inserted On dbo.Table1

After Insert

As

RAISERROR ('Error happened here.', 16, 1)

__________________________________________

CREATE Procedure usp_AddRow

As

Insert Table1 (Now) Values (GetDate())


Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim er As ADODB.Error

On Error GoTo ErrHandler

Set cn = New ADODB.Connection
With cn
    .ConnectionString = "Provider=SQLOLEDB.1;Password=yourpassword;Persist Security Info=True;User ID=yourusername;Initial Catalog=yourdatabase;Data Source=yourserver"
    .Open
    Set cmd = New ADODB.Command
    With cmd
        Set .ActiveConnection = cn
        .CommandText = "usp_AddRow"
        .CommandType = adCmdStoredProc
        .Execute , , adExecuteNoRecords
    End With
    Set cmd = Nothing
    .Close
End With
Set cn = Nothing

Exit Sub
ErrHandler:
If Not cn Is Nothing Then
    If cn.State = adStateOpen Then
        For Each er In cn.Errors
            Debug.Print er.Number, er.Description, er.NativeError, er.SQLState, er.Source
        Next
        cn.Close
    End If
    Set cn = Nothing
End If

If this does not work for you, let us know what version of SQL Server and Service Pack.

Anthony
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
If you are getting the error description: "Errors occurred", than check out this article from MSDN:
FIX: RAISERROR Does Not Work with SQLOLEDB Provider
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B217019

Anthony
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.