SQL Trigger and infoMessage event - vb.net/sql2000

[CattleRustler]

I am trying to incorporate an sql trigger to cause an event to be raised in my vb.net application, but it is not working at the vb app.

 

the sql trigger is working with:

CREATE TRIGGER trg_CallEngine ON tbl_customer_info 
AFTER INSERT 
AS
RAISERROR('hello world',16,1)

 

when I do a manual INSERT into my table, the trigger fires and shows a msgbox on the sql server with "hello world" printed in it. I can alter the Severity Level to not show the message box as well.

 

On the vb side I am using my sqlConnection obect's InfoMessage Event to try and trap the sql server message but the event never gets fired (break point never gets hit). The code I am using is from vb help, supposedly to do exactly what I am trying to do:

 

vb help says:

[Visual Basic]
 Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind;")

 AddHandler nwindConn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)


Private Shared Sub OnInfoMessage(sender As Object, args As SqlInfoMessageEventArgs)
 Dim err As SqlError
 For Each err In args.Errors
   Console.WriteLine("The {0} has received a severity {1}, state {2} error number {3}\n" & _
                     "on line {4} of procedure {5} on server {6}:\n{7}", _
                     err.Source, err.Class, err.State, err.Number, err.LineNumber, _
                     err.Procedure, err.Server, err.Message)
 Next
End Sub

 

I have a valid connection object that connects to the db without errors

 

I used the AddHandler code here:

Public Sub New()
       MyBase.New()

       'This call is required by the Windows Form Designer.
       InitializeComponent()

       'Add any initialization after the InitializeComponent() call

       AddHandler SqlConn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)


   End Sub

 

then my code should connect and get raised with:

Imports System.Data.SqlClient

Public Class frmMain
   Inherits System.Windows.Forms.Form

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
       Try
           SqlConn.Open()
           Button1.Text = "connected"
           Button1.Enabled = False
       Catch ex As Exception
           Button1.Text = "conn error"
           MsgBox(ex.ToString)
       End Try
   End Sub

   Private Shared Sub OnInfoMessage(ByVal sender As Object, ByVal args As SqlInfoMessageEventArgs)
       Dim err As SqlError

       For Each err In args.Errors
           Debug.WriteLine(err.Message & vbCrLf)
       Next
   End Sub

   Private Sub frmMain_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
       If SqlConn.State = ConnectionState.Open Then SqlConn.Close()
   End Sub
End Class

 

The break point set at OnInfoMessage is never hit.

Anyone have any idea what I am doing wrong? Any help is greatly appreciated.

 

Thanks in advance

James

[Nerseus]

I can't say I've tried it that way. I know I've used a general try/catch with the catch using SqlException and that DOES hit when the proc runs over RAISERROR...

 

Is the OnInfoMessage supposed to hit on a "16, 1" type of error? Or just on info-level messages?

 

-nerseus

[CattleRustler]

Nerseus

 

Thank you, I've tried with a bunch of different severity levels but still nothing. Maybe there is another way of doing what I am trying do. Let me ask the question this way:

 

Hello everyone :) I have a table in a sql server database, what I am trying to do is to have my vb.net be notified when an INSERT occurs on that table. The inserts are NOT coming from vb.net app, they are coming from elsewhere. I have been trying to use a trigger with raiserror at the sql server but I have not found a way to notify my vb app of the insert.

 

thanks

[CattleRustler]

I ran the sql profiler and verified that my trigger throws the message, I can see it in the profiler trace and have set it to be Event Viewer logged - where it appears as well. The SQL server is throwing the message but for the life of me I can't get any event to fire in vb.net app - please, if any one knows how to do this correctly and has it working in vb.net I'd love to see how you are doing it - Thanks!

 

Thanks

James

[CattleRustler]

what makes this even more funny is that the event gets raised perfectly from with an ASP.NET project, without anything other than the sqlconn object's infoMessage event block of code (no need for AddHandler or a Delegate like help says for vb.net)

 

I don't understand why this doesn't work in vb.net, and why help says it would need AddHandler in the first place. The SqlConnection class has 2 events described in the class, StateChanged (which works by the way) and InfoMessage - why would these ever need AddHandler when I am not trying to anything special but put some code in the defined event sub.

 

this is making me crazy.

[CattleRustler]

OK, figured it out... Turns out the info message from the sql server is directed to the machine/application that caused it. In all of my vb tests I was manually doing the insert from within sql itself. My test with asp.net had the insert sent from within that app, and it got the info message successfully. So now I just went back to my vb app and added code to do the insert there and I got the info message back successfully-good but bad for what I am ultimately trying to do which is have a running vb.net app be notified by the sql server that some client has inserted a record into the db....

 

oh, and btw, to have the infomessage event fire in vb.net requires NO addhandler/delegate code. Simply use the event sub of the connection object and you are good to go.

 

hope this helps someone, someday

best regards

James

mod2software, LLC