returning sql print statements to windows form

[barmanvarn]

I'm fairly new to vb.net but am old hand at t-sql. I like to put print statements in my stored procedures so that I can monitor certain variables, etc, as the code executes.

I'd like to call a stored procedure from my windows form and display the print statements in a text box as they occur. Is this possible? I already know how to call the sproc. I just need to know how to capture the prints statements as they occur and display them on the form.

thanks in advance

 

[JohnH]

The InfoMessage event displays the PRINT statements, only found a C# example here: http://www.java2s.com/Code/CSharp/Database-ADO.net/HowtousetheInfoMessageevent.htm

DeveloperFusion conversion gave this VB.Net:

[FONT=Courier New]Class InfoMessage [/FONT]
 
[FONT=Courier New]Public Shared Sub InfoMessageHandler(ByVal mySender As Object, ByVal myEvent As SqlInfoMessageEventArgs) [/FONT]
[FONT=Courier New] Console.WriteLine("The following message was produced:" & Microsoft.VisualBasic.Chr(10) & "" + myEvent.Errors(0)) [/FONT]
[FONT=Courier New]End Sub [/FONT]
 
[FONT=Courier New]Public Shared Sub Main() [/FONT]
[FONT=Courier New] Dim mySqlConnection As SqlConnection = New SqlConnection("server=(local)\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;") [/FONT]
[FONT=Courier New] AddHandler mySqlConnection.InfoMessage, AddressOf InfoMessageHandler [/FONT]
[FONT=Courier New] mySqlConnection.Open [/FONT]
[FONT=Courier New] Dim mySqlCommand As SqlCommand = mySqlConnection.CreateCommand [/FONT]
[FONT=Courier New] mySqlCommand.CommandText = "PRINT 'This is the message from the PRINT statement'" [/FONT]
[FONT=Courier New] mySqlCommand.ExecuteNonQuery [/FONT]
[FONT=Courier New] mySqlCommand.CommandText = "RAISERROR('This is the message from the RAISERROR statement', 10, 1)" [/FONT]
[FONT=Courier New] mySqlCommand.ExecuteNonQuery [/FONT]
[FONT=Courier New] mySqlConnection.Close [/FONT]
[FONT=Courier New]End Sub [/FONT]
[FONT=Courier New]End Class[/FONT]

 

[barmanvarn]

Thanks John. I'll give it a shot and let you know how it goes.

 

[barmanvarn]

OK, either I'm looking at your example incorrectly, or I didn't explain what I'm doing very well.

In my stored procedure, I have several print statements. This output shows up in in the results window when running the sproc in Query Analyzer. I'd like to capture that output and return it to my windows form.

I don't want to create my print statements in .net.

 

[cjard]

i dont think johns code does that?

It basically seems that every time your Stored Proc does a PRINT command, an event called InfoMessage is raised on the client side database connection object. You simply add an event handler to that event.
Johns code adds a handler that dumps the info onto the Console Window called Output. If you ran your compiled app froma command line you would see the dos box print the messages.

If you want them on your form then you have to shove them into a form control instead.. Try this:

Public Shared Sub InfoMessageHandler(ByVal mySender As Object, ByVal myEvent As SqlInfoMessageEventArgs) 
[FONT=Courier New]ListBox1.Items.Add("The following message was produced:" & Microsoft.VisualBasic.Chr(10) & "" + myEvent.Errors(0)) [/FONT]
[FONT=Courier New]End Sub [/FONT]
[FONT=Courier New]

[/FONT]