Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

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?

0
marshallgarza
Asked:
marshallgarza
  • 2
1 Solution
 
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
 
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now