out put a variable from store procedure

Hi,

Kindly assist what changes needs to be done inorder to return the output from the store procedure called @opeartorname:

        Dim con As New SqlConnection("......")
        Dim cmd As SqlCommand = con.CreateCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "SP_operator_name"
        cmd.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", DataRowVersion.Current, Nothing))
        cmd.Parameters.Add("@operatorid",SqlDbType.int).Value =int(varqamonitorid)
        con.open()
        cmd.executenonquery()
        dim returnvalueOPID as string = cmd.parameters("@RETURN_VALUE").value  <-------------------here
        label2.text= "Welcome, " & returnvalueOPID & " !"
        cmd.dispose
        cmd.parameters.clear
        con.close





create proc dbo.SP_operator_name
@operatorid int,
@operatorname varchar(100) OUTPUT    <----------------output
as
select @operatorname=operatorname from operator_master where operatorid=@operatorid
GO
EdwardPeterAsked:
Who is Participating?
 
Abirami RajendranCommented:
Try this

Dim con As New SqlConnection("......")
Dim cmd As SqlCommand = con.CreateCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "SP_operator_name"

Dim inputParam As SqlParameter = cmd.Parameters.Add("@operatorid", SqlDbType.Int)
inputParam.Direction = ParameterDirection.Input
inputParam.Value = int(varqamonitorid)

Dim returnvalueOPID As SqlParameter = cmd.Parameters.Add("@operatorname", SqlDbType.VarChar, 100)
returnvalueOPID.Direction = ParameterDirection.Output

con.Open()
cmd.ExecuteNonQuery()

label2.text= "Welcome, " & returnvalueOPID.Value & " !"
0
 
EdwardPeterAuthor Commented:
sansoftura,

Server Error in '/' Application.
--------------------------------------------------------------------------------
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30452: Operator '&' is not defined for types 'String' and 'System.Data.SqlClient.SqlParameter'.
Source Error:
Line 34:         cmd.executenonquery()
Line 35:         'dim returnvalueOPID as string = cmd.parameters("@RETURN_VALUE").value
Line 36:         label2.text= "Welcome, " & returnvalueOPID & " !"
Line 37:         cmd.dispose
Line 38:         cmd.parameters.clear
Source File: c:\inetpub\wwwroot\cardadd.aspx    Line: 36
0
 
EdwardPeterAuthor Commented:
sansoftura,

I'll repost and hope other experts will join.

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.