«

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.

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" <> wrote in message
news:...
> 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.
>
>

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.

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


"Yoshitha" <> wrote in message
news:OnDeCq$...
> 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.
>
>
>
>

Yoshitha wrote:
> 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"!


--
Hans Kesting