SP and C# problem

[Viktor Popov]

Hi ,
I have the following SP:
CREATE PROCEDURE prSVUSERINFO
@USRNAM VARCHAR(20),
@USRPSW VARCHAR(20),
@NAME VARCHAR(64),
@ADDR VARCHAR(74),
@EML VARCHAR(64),
@ROLE VARCHAR(6),
@RCOUNT INT OUTPUT
AS
IF NOT EXISTS( SELECT * FROM BLEK.USERS A WHERE A.USERNAME=@USRNAM OR
A.USEREMAIL=@EML)
BEGIN
INSERT INTO
ESTATE.BLEK.USERS(Username,UserPass,RealName,UserAddr,UserEmail,UserRole)
VALUES(@USRNAM,@USRPSW,@NAME,@ADDR,@EML,@ROLE)
END
ELSE
RETURN SELECT @RCOUNT=@@ROWCOUNT

Could I use somehow @RCOUNT in ASP.NET/C# to find out if the username
is already in use or not? I know that it should be something like this:
if(sqlAdapter.SelectCommand.Parameters["@RCOUNT"].Value == 0) ,
but thereis an error:
Operator '==' cannot be applied to operands of type object type 'object'
and 'int'.
Is it possible to find out if the user with this name or e-mail exists whit
this SP and some C# code?

Could you help here?

 

[Sunny]

Hi,

Hi ,
I have the following SP:
CREATE PROCEDURE prSVUSERINFO
@USRNAM VARCHAR(20),
@USRPSW VARCHAR(20),
@NAME VARCHAR(64),
@ADDR VARCHAR(74),
@EML VARCHAR(64),
@ROLE VARCHAR(6),
@RCOUNT INT OUTPUT
AS
IF NOT EXISTS( SELECT * FROM BLEK.USERS A WHERE A.USERNAME=@USRNAM OR
A.USEREMAIL=@EML)
BEGIN
INSERT INTO
ESTATE.BLEK.USERS(Username,UserPass,RealName,UserAddr,UserEmail,UserRole)
VALUES(@USRNAM,@USRPSW,@NAME,@ADDR,@EML,@ROLE)
END
ELSE
RETURN SELECT @RCOUNT=@@ROWCOUNT

You can use SqlCommand.ExecuteScalar. It returns the first column of the
first row, I.e. if it is not null, you can cast it to int and check.

Btw, why you use SqlAdapter for this? Do you need the added record back?
I don't see the point.

Sunny

 

[Violeta Dabnishka]

You can actually. After creating a SqlCommand, add to its Parameters
collection a param named "@RCOUNT". Set the Direction property of the param
to ParameterDirection.ReturnValue. The code should look something like this:

// create a SqlCommand
SqlCommand cmd = new SqlCommand(...);
SqlParameter param = cmd.Parameters.Add("@RCOUNT", DBType.Integer);
param.Direction = ParameterDirection.ReturnValue;

cmd.ExecuteNonQuery(); // execute the command
System.Diagnostics.Trace.WriteLine(cmd.Parameters("@RCOUNT").Value); //
display the param value in the Output window

 

[Pieter]

Hi,

Ok , I dunno about the rest of your problem , not really keen to check it
out, but I have noticed why you get the compile error.

Your line : "if(sqlAdapter.SelectCommand.Parameters["@RCOUNT"].Value == 0)"

The value property is of type object - just typecast it to an int like so :

if(Convert.ToInt32(sqlAdapter.SelectCommand.Parameters["@RCOUNT"].Value) ==
0)

That should get it to compile

Cheers