How to return value from Stored Procedure to label control?


O

Oded Dror

Hi there,

I'm using ASP.Net 2.0 with SQL Server 2005 and Visual Studio 2005
I created a stored proc look like this

CREATE PROC [dbo].[usp_text]
@t varchar(50)
As
Begin
Print 'Results: ' + @t
End

When I'm execute
exec usp_text
I'm getting error
Msg 201, Level 16, State 4, Procedure usp_text, Line 0
Procedure or function 'usp_text' expects parameter '@t', which was not
supplied.

And when I execute
exec usp_text 'This is a test'
I'm getting

Results: This is a test

As expected from the stored proc

Also I created an ASP.NET VB page with

Button1 ,
ErrorMessage label,
lblText label with 'This is a test' value
lblRetuenValue label with no value

The code behind look like this

Imports System.Data
Imports System.Data.SqlClient
Partial Class Default2
Inherits System.Web.UI.Page
Dim connStr As String =
ConfigurationManager.ConnectionStrings("MyConns").ConnectionString
Public Function MyProc() As Integer
Dim con As New SqlConnection(connStr)
Try
Dim insertString As String = "Execute usp_text '" & lblText.Text
& "'"
'Dim insertString As String = "Execute usp_text"
Dim cmd As New SqlCommand(insertString, con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Catch ex As Exception
ErrorMessage.Text = ex.Message.ToString
End Try
End Function

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button1.Click
Try
Call MyProc()
Catch ex As Exception
ErrorMessage.Text = ex.Message.ToString
End Try
End Sub
End Class


When I don't submith value with the stored peroc

Dim insertString As String = "Execute usp_text"

I'm catching the error with my ErrorMessage.Text = ex.Message.ToString

But when I'm submit the value

Dim insertString As String = "Execute usp_text '" & lblText.Text & "'"

I would like to return the value into my lblReturnValue label show

Results: This is a test

How do I return the value from Stored Proc?

Thanks
Oded Dror
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top