We help IT Professionals succeed at work.

Retrieving messages from SQL SERVER 2005 using VB.NET 2005?

253 Views
Last Modified: 2013-11-26
HI Experts,

I have a large application that is distributed over many workstations and at another site ... Sometimes a development computer is not available (especially working offsite) and we need a way of running queries against the database

Due to this, i am writing a quick 'Query Analyzer' clone into my program, available only to developers and people with the right access levels

Anyways, I've done this before where i basically return the results of a DataAdapter.fill into a Dataset and populate a grid with it.

However, as an added extra i also want to retrieve the 'info' messages of an update/insert/delete back to the application pretty much exactly how Query analyzer does in it's messages tab.

I.e. If i run the following code:

m_oQuery_Conn.Open()
With cm
      .CommandText = "UPDATE PRODUCT SET ABC = 1 WHERE PRODUCT_ID = 123 GO SELECT * FROM PRODUCT WHERE PRODUCT_ID = 123"
      .CommandType = CommandType.Text
      .Connection = m_oQuery_Conn
      .CommandTimeout = 180
 End With

Dim adapter As New SqlClient.SqlDataAdapter(cm)
Dim ds As New DataSet
adapter.Fill(ds)

Me.Grid_Query.DataSource = ds

This will perform the update on product '123' and also show me the results, however, when you do this in Query analyzer you also get the output:

(1 row(s) updated)

Is there any way to get these messages??

I've tried handling the connection's infomessage event, but it will only result errors and prints from what i can see.

Any ideas?

Comment
Watch Question

Top Expert 2007

Commented:
I'm not sure if this is exactly what you're looking for, but try handling the SqlConnection's SqlInfoMessages event:

    Private Sub ConnectToSql()

        Dim connectionString As String = _
            "Data Source=.\SqlExpress;Initial Catalog=Northwind;Integrated Security=True"

        Dim cn As New SqlClient.SqlConnection(connectionString)
        AddHandler cn.InfoMessage, AddressOf SqlInfoMessages

        cn.Open()
        Dim cmd As SqlClient.SqlCommand = cn.CreateCommand()
        cmd.CommandText = "PRINT 'Initial Message'"
        cmd.ExecuteNonQuery()

        cn.Close()

    End Sub

    Public Sub SqlInfoMessages(ByVal sender As Object, _
        ByVal e As SqlClient.SqlInfoMessageEventArgs)

        Console.WriteLine(" Message received: {0}", e.Message)

    End Sub



Author

Commented:
Thanks for the response, but as i said, i've already tried handling that event ... It will only return messages of a certain level, such as errors and print messages.

Top Expert 2007
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Yes ... That will return all the errors as 'messages'

But its not just the errors i want to have returned, i was wondering if there was a way of returning all the other messages aswell...?
Top Expert 2007

Commented:
I'm out of ideas Gweep...  Sorry.

Author

Commented:
Your help is appreciated anyways, if i don't get an answer in a couple of days i'll give you the points for effort :P
Top Expert 2007

Commented:
Thanks.  If I run across anything, I'll let you know.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.