Run SQL Stored Procedure And Return Print Message vb.net

Question

I have a stored procedure with 2 parameters and which returns result is a PRINT message. I can pass the parameters in and run the stored procedure but I can't figure out how to return the result.

Imports System.Data.Sql
Imports System.Data.SqlClient

Public Class Form1
   Dim SQL As New SQLControl
   Dim cmd As New SqlCommand

   Private Sub RunSql_Click(sender As System.Object, e As System.EventArgs) Handles RunSql.Click
      Try
        If SQL.HasConnection = True Then
            SQL.SQLCon.Open()
            cmd.Connection = SQL.SQLCon
            cmd.CommandText = "Exec stored procedure " & param1.Text & "," & param2.Text
            cmd.ExecuteNonQuery()
        End If
     Catch ex As Exception
        MsgBox(ex.Message)
     Finally
        SQL.SQLCon.Close()
     End Try
  End Sub
End Class

Thanks in advance.

Community · Accepted Answer · 2017-05-23 12:23:50Z

1

You can retrieve warnings and informational messages from a SQL Server data source using the InfoMessage event of the SqlConnection object.

This has been answered in a similar question: Get the value of print statement of sql server in vb.net by using oledb

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Aug 21, 2014 at 23:04

Charles D.

873 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

LewisHill · Accepted Answer · 2014-08-21 23:53:43Z

Thanks! I did some more digging using your link and I've got it working.

Code amended below if anyone else needs an example.

Imports System.Data.Sql
Imports System.Data.SqlClient

Public Class Form1

Dim SQL As New SQLControl
Dim cmd As New SqlCommand

Private Sub RunSql_Click(sender As System.Object, e As System.EventArgs) Handles RunSql.Click

Try

    If SQL.HasConnection = True Then
        SQL.SQLCon.Open()
        cmd.Connection = SQL.SQLCon
        cmd.CommandText = "Exec stored procedure " & param1.Text & "," & param2.Text
        cmd.ExecuteNonQuery()

        AddHandler SQL.SQLCon.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)

    End If

Catch ex As Exception
    MsgBox(ex.Message)
Finally
    SQL.SQLCon.Close()
End Try
End Sub

Private Shared Sub OnInfoMessage(sender As Object, args As SqlInfoMessageEventArgs)
    Dim err As SqlError
    For Each err In args.Errors
        MsgBox(err.Message)
    Next
End Sub

End Class

Thanks again, Lewis

Collectives™ on Stack Overflow

Run SQL Stored Procedure And Return Print Message vb.net

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related