regatding stroed procedure in asp.net

[Yoshitha]

Hi
i've created stored procedure in

sql server 2000 which returns a value

see the stroed procedure


CREATE PROCEDURE QA_Select_AdminOrRec
@Role varchar(50),
@username varchar(50),
@pwd varchar(50)

AS

if exists (select * from

QA_Admin_Recruiter where role = @role

and username=@username and pwd=@pwd)
return (1)
else
return (0)
GO

and in asp.net i called this stroed

procedure like this

Dim username As New OleDbParameter
username.OleDbType = OleDbType.VarChar
username.Value = txtUserName.Text

Dim pwd As New OleDbParameter
pwd.OleDbType = OleDbType.VarChar
pwd.Value = txtPassword.Text

Dim role As New OleDbParameter
role.OleDbType = OleDbType.VarChar
role.Value = Session("user")

cmd.CommandText = "QA_Select_AdminOrRec"

cmd.Parameters.Add(role)
cmd.Parameters.Add(username)
cmd.Parameters.Add(pwd)

cmd.ExecuteNonQuery().

here i've to check the value returned by stored procedure.

can anyone tell me how to write code to capture a value which returned by
the stroed procedure.

thanx in advance
yoshitha.

 

[Joyjit Mukherjee]

Hi,

First, create a parameter "ReturnValue", set its Direction property to
ReturnValue, add it to the parameters collection (as you are using OleDb
provider, this is going to be your first parameter), then get the value from
the SP after executing ExecuteNonQuery as follows: -

Dim RetVal as Integer

Retval = CType(Cmd.Parameters("ReturnValue").Value, Integer)

HTH

Regards
Joyjit

 

[Yoshitha]

hi

now i've included the statements what u have said in ur reply

see this



Dim retvalue As New OleDbParameter

retvalue.OleDbType = OleDbType.Integer

retvalue.Direction = ParameterDirection.ReturnValue

cmd.Parameters.Add(retvalue)

cmd.Parameters.Add(role)

cmd.Parameters.Add(username)

cmd.Parameters.Add(pwd)

cmd.ExecuteNonQuery()

res = CType(cmd.Parameters("retvalue").Value(), Integer)

after executing am getting error at lst line of code which is displayed
above. the error i got is below.

An OleDbParameter with ParameterName 'retvalue' is not contained by this
OleDbParameterCollection.

 

[Joyjit Mukherjee]

Hi,

In your SP, add the parameter, and instead of returning 1 or 0, write
Set Retvalue = 1
else
Set Retvalue = 0

@@Return Retvalue

HTH

Joyjit

 

[Hans Kesting]

hi

now i've included the statements what u have said in ur reply

see this



Dim retvalue As New OleDbParameter

retvalue.OleDbType = OleDbType.Integer

retvalue.Direction = ParameterDirection.ReturnValue

cmd.Parameters.Add(retvalue)

cmd.Parameters.Add(role)

cmd.Parameters.Add(username)

cmd.Parameters.Add(pwd)

cmd.ExecuteNonQuery()

res = CType(cmd.Parameters("retvalue").Value(), Integer)

after executing am getting error at lst line of code which is displayed
above. the error i got is below.

An OleDbParameter with ParameterName 'retvalue' is not contained by this
OleDbParameterCollection.

try this to get the value after the ExecuteNonQuery:
res = CType(retvalue.Value, Integer)

or set the name of the parameter before ExecuteNonQuery:
retvalue.ParameterName = "retvalue"


The problem is that you create a parameter with no name (and assign
it to the retvalue variable) and later you are trying to retrieve
that same parameter by name. There is no automatic link between
the variable named "retvalue" and some parameter with ParameterName
"retvalue"!