stored procedure result comparision

[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,UserA ddr,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?

Thank you!
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 16.7.2004 a.

[Curt_C [MVP]]

yes, you should be able to get the paramaters("@Ident").value
that's not exact syntax but should get you close.

--
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"Viktor Popov" <vi****@yahoo.com> wrote in message
news:eH*************@TK2MSFTNGP10.phx.gbl...

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,UserA ddr,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?

Thank you!
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 16.7.2004 a.

[Viktor Popov]

Thanks for the reply!

This is my if statement

if(sqlAdapter.SelectCommand.Parameters["@RCOUNT"].Value == 0) , but there
is an error:

Operator '==' cannot be applied to operands of type object type 'object'
and 'int'.

I know that I'm close but.....

Could you help here?

Thanks!
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 16.7.2004 a.

[Eliyahu Goldin]

In SqlCommand (or similar class for other databases) setup a parameter with
property Direction set to ReturnValue.

Eliyahu

"Viktor Popov" <vi****@yahoo.com> wrote in message
news:eH*************@TK2MSFTNGP10.phx.gbl...

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,UserA ddr,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?

Thank you!
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 16.7.2004 a.

[Viktor Popov]

It doesn't work with SqlAdapter and SqlCommand.
Could you explain how exactly should I do that?
Thanks
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 16.7.2004 a.

[Curt_C [MVP]]

Convert.ToInt32(adapterStuff) == 0

--
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"Viktor Popov" <vi****@yahoo.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

Thanks for the reply!

This is my if statement

if(sqlAdapter.SelectCommand.Parameters["@RCOUNT"].Value == 0) , but there
is an error:

Operator '==' cannot be applied to operands of type object type 'object'
and 'int'.

I know that I'm close but.....

Could you help here?

Thanks!
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 16.7.2004 a.

[Eliyahu Goldin]

Are you replying to my message? If you are, look at the MSDN help topic
"Using Stored Procedures with a Command" . It includes an example of using
ReturnValue.

Eliyahu

"Viktor Popov" <vi****@yahoo.com> wrote in message
news:et**************@TK2MSFTNGP09.phx.gbl...

It doesn't work with SqlAdapter and SqlCommand.
Could you explain how exactly should I do that?
Thanks
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 16.7.2004 a.

[Viktor Popov]

I've read it but these SPs return rowsets. My SP return a INT. Is it the
same way of implementing?
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 16.7.2004 a.

[Eliyahu Goldin]

Another resource:
http://support.microsoft.com/default...b;EN-US;310070

ReturnValue type is designed exactly for returning values, not rowset. The
only thing that is not clear for me is how to get return value with
SqlDataAdapter. Using SqlDataReader is straightforward: setup parameter,
execute the reader, close the reader and get the parameter's value.

Eliyahu

"Viktor Popov" <vi****@yahoo.com> wrote in message
news:e9**************@tk2msftngp13.phx.gbl...

I've read it but these SPs return rowsets. My SP return a INT. Is it the
same way of implementing?
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 16.7.2004 a.

[bruce barker]

yes, look at sqlcommand. besure to decalre the parameter as an out. also the
values will not be set until after all result sets. a common approach is to
close the connection

air code:

SqlCommand cmd = new SqlCommand("prSVUSERINFO",new
SqlConnection(connectString));
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@USRNAM,SqlDbType.VarChar,20). Value = username;
....

cmd.Parameters.Add("@RCOUNT ,SqlDbType.Int,4).Direction =
ParameterDirection.Output;
cmd.ExecuteNonQuery();
int rCount = cmd.Parameters["@RCOUNT"].Value as int;
of course the way your sp is written rCount will allways be 0

-- bruce (sqlwork.com)
"Viktor Popov" <vi****@yahoo.com> wrote in message
news:eH*************@TK2MSFTNGP10.phx.gbl...

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,UserA ddr,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?

Thank you!
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 16.7.2004 a.

[Viktor Popov]

Thanks for the answer, Bruce!
I have changed the SP, I didn't know that the value of @RCOUNT is everytime
0.I thought that " if EXISTS( select * from ....)" changes the value of
@@ROWCOUNT...
I rewrite it this way:
CREATE PROCEDURE prSVUSERINFO
@USRNAM VARCHAR(20),
@USRPSW VARCHAR(20),
@NAME VARCHAR(64),
@ADDR VARCHAR(74),
@EML VARCHAR(64),
@ROLE VARCHAR(6),
@FLAG 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,UserA ddr,UserEmail,UserRole)
VALUES(@USRNAM,@USRPSW,@NAME,@ADDR,@EML,@ROLE)
SET @FLAG=0
RETURN @FLAG
END
ELSE
BEGIN
SET @FLAG=1
RETURN @FLAG
END

and I check if rCount ==0{ some code}
else{
some code}
Now everything works. Could you check the SP and if I could optimize it
somehow, please tell me:)
Do you know how I could do that check using @@ROWCOUNT?
Thank you very much!

Viktor
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 16.7.2004 a.