Thread: show Lable text = SQL Procedure Print message

Hi,

I want to store a print msg in vb.net lable text..

my stored procedure is (it works fine)

Code:

 setANSI_NULLSON
setQUOTED_IDENTIFIERON
go
ALTERprocedure [dbo].[SP_UserAdd]

--Pass the Argument for Stored Procedure
@UserID nvarchar(50),
@UserPassword nvarchar(50),
@UserType nvarchar(50),
@UserFullName nvarchar(50)
AS
--Declare the Variable for Stored Procedure
declare @UserID_Check_Dup asnvarchar(50)
begin
set @UserID_Check_Dup =(select UserID from TblMUser where UserID = @UserID)
if @UserID_Check_Dup = @UserID 
Begin
Print N'This user is already in database'
End
else
Begin
--Add new UserID 
insertinto TblMUser(UserID,UserPassword,UserType,UserFullName)
values(@UserID,@UserPassword,@UserType,@UserFullName)
Print N'User has been created successfully'
End
end

my VB.net code for click Event. actually procedure is runing fine.. and data is storing in DB but i want to show my stored procedure input message in my VB.net labl.text.....

Code:

ProtectedSub Btn_CreateUser_Click(ByVal sender AsObject, ByVal e As System.EventArgs) Handles Btn_CreateUser.Click
Try
If Trim(Txt_UserID.Text) <> ""And Trim(Txt_UserPassword.Text) <> ""And Trim(DD_UserType.Text) <> "--Select--"And Trim(Txt_UserFullName.Text) <> ""Then
objConn = New SqlConnection
objConn.ConnectionString = strConnString
objConn.Open()
Dim ds As DataSet = New DataSet()
strSQL = "exec SP_UserAdd '" & Trim(Txt_UserID.Text) & "','" & Trim(Txt_UserPassword.Text) & "','" & Trim(DD_UserType.Text) & "','" & Trim(Txt_UserFullName.Text) & "'"
da = New SqlDataAdapter(strSQL, objConn)
da.Fill(ds, "KK")
Me.Lbl_Error.Visible = False
Me.Lbl_Successful.Visible = True
  Me.Lbl_Successful.Text = ds.Tables("0").Rows(1)(1).ToString    
  Else
Me.Lbl_Successful.Visible = False
Me.Lbl_Error.Visible = True
Me.Lbl_Error.Text = "Please select all mandatory fields"
EndIf
Catch ex As Exception
Me.Lbl_Successful.Visible = False
Me.Lbl_Error.Visible = True
Me.Lbl_Error.Text = ex.Message
EndTry
EndSub

Please help any one because i am new in this vb.net..

Regards,
Krishna
9560305552

Your SqlConnection object has an InfoMessage event that you can handle and it will be raised when ever your procedure executes a PRINT statement, e.g.

Private Sub ExecuteProcedure(procedureName As String)

    Using connection As New SqlConnection("connection string here"),

          command As New SqlCommand(procedureName, connection)

        command.CommandType = CommandType.StoredProcedure



        AddHandler connection.InfoMessage, AddressOf SqlConnection_InfoMessage



        connection.Open()

        command.ExecuteNonQuery()



        RemoveHandler connection.InfoMessage, AddressOf SqlConnection_InfoMessage

    End Using

End Sub



Private Sub SqlConnection_InfoMessage(sender As Object, e As SqlInfoMessageEventArgs)

    MessageBox.Show(e.Message)

End Sub

hi,

Your code is good.. but my sql procedure run correct.. only problem in SQL procedure massage is not comming in .net lable text..

Please correct below code only for me..
da.Fill(ds, "KK")
Me.Lbl_Error.Visible = False
Me.Lbl_Successful.Visible = True
Me.Lbl_Successful.Text = ds.Tables("0").Rows(1)(1).ToString

i think.. problm is in this code only.

There's nothing in that code that even suggests that you're handling the InfoMessage event.

Now i have made changes in my sql procedure with output parameter...

ALTERprocedure [dbo].[SP_UserAdd]

--Pass the Argument for Stored Procedure
@UserID nvarchar(50),
@UserPassword nvarchar(50),
@UserType nvarchar(50),
@UserFullName nvarchar(50),
@Final_Result varchar(100)=nulloutput
AS
--Declare the Variable for Stored Procedure
declare @UserID_Check_Dup asnvarchar(50)
begin
set @UserID_Check_Dup =(select UserID from TblMUser where UserID = @UserID)
if @UserID_Check_Dup = @UserID
Begin
--Print N'This user is already in database'
--set @Final_Result = null
set @Final_Result ='This user is already in database'
End
else
Begin
--Add new UserID
insertinto TblMUser(UserID,UserPassword,UserType,UserFullName)
values(@UserID,@UserPassword,@UserType,@UserFullName)
--set @Final_Result = null
set @Final_Result ='User has been created successfully'
--Print N'User has been created successfully'
--return ('User has been created successfully')
End
select @Final_Result as FinalResult
return
end

Now i want to print in vb.net lable text from above @Final_Result (SQL proc) variable...
My VB.net code is below.. Please make changes in this below code

ProtectedSub Btn_CreateUser_Click(ByVal sender AsObject, ByVal e As System.EventArgs) Handles Btn_CreateUser.Click
Try
Me.Lbl_Error.Text = ""
Me.Lbl_Successful.Text = ""
Me.Lbl_Error.Visible = False
Me.Lbl_Successful.Visible = False
If Trim(Txt_UserID.Text) <> ""And Trim(Txt_UserPassword.Text) <> ""And Trim(DD_UserType.Text) <> "--Select--"And Trim(Txt_UserFullName.Text) <> ""Then
objConn = New SqlConnection
objConn.ConnectionString = strConnString
objConn.Open()
Const SQL AsString = "SP_UserAdd"
Dim cmd AsNew SqlCommand(SQL, objConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@UserID", Trim(Txt_UserID.Text))
cmd.Parameters.AddWithValue("@UserPassword", Trim(Txt_UserPassword.Text))
cmd.Parameters.AddWithValue("@UserType", Trim(DD_UserType.Text))
cmd.Parameters.AddWithValue("@UserFullName", Trim(Txt_UserFullName.Text))
cmd.ExecuteNonQuery()
Me.Lbl_Successful.Visible = True
Me.Lbl_Successful.Text = ""
Else
Me.Lbl_Successful.Visible = False
Me.Lbl_Error.Visible = True
Me.Lbl_Error.Text = "Please select all mandatory fields"
EndIf
Catch ex As Exception
Me.Lbl_Successful.Visible = False
Me.Lbl_Error.Visible = True
Me.Lbl_Error.Text = ex.Message
EndTry
EndSub