asked on

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

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?

VBRocks

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

Gweep

ASKER

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.

ASKER CERTIFIED SOLUTION

VBRocks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gweep

ASKER

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...?

VBRocks

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

Gweep

ASKER

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

VBRocks

Thanks. If I run across anything, I'll let you know.