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

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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.