stored procedure result comparision

Discussion in 'ASP .Net' started by Viktor Popov, Jul 20, 2004.

  1. Viktor Popov

    Viktor Popov Guest

    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?

    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, Jul 20, 2004
    #1
    1. Advertising

  2. Viktor Popov

    Curt_C [MVP] Guest

    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" <> wrote in message
    news:...
    > 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?
    >
    > 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], Jul 20, 2004
    #2
    1. Advertising

  3. Viktor Popov

    Viktor Popov Guest

    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.
     
    Viktor Popov, Jul 20, 2004
    #3
  4. Viktor Popov

    Viktor Popov Guest

    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, Jul 20, 2004
    #4
  5. Viktor Popov

    Curt_C [MVP] Guest

    Convert.ToInt32(adapterStuff) == 0

    --
    Curt Christianson
    Owner/Lead Developer, DF-Software
    Site: http://www.Darkfalz.com
    Blog: http://blog.Darkfalz.com


    "Viktor Popov" <> wrote in message
    news:%...
    > 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.
    >
    >
    >
     
    Curt_C [MVP], Jul 20, 2004
    #5
  6. In SqlCommand (or similar class for other databases) setup a parameter with
    property Direction set to ReturnValue.

    Eliyahu

    "Viktor Popov" <> wrote in message
    news:...
    > 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?
    >
    > 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.
    >
    >
     
    Eliyahu Goldin, Jul 20, 2004
    #6
  7. Viktor Popov

    Viktor Popov Guest

    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.
     
    Viktor Popov, Jul 20, 2004
    #7
  8. 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" <> wrote in message
    news:...
    > 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.
    >
    >
     
    Eliyahu Goldin, Jul 20, 2004
    #8
  9. Another resource:
    http://support.microsoft.com/default.aspx?scid=kb;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" <> wrote in message
    news:...
    > 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, Jul 20, 2004
    #9
  10. Viktor Popov

    Viktor Popov Guest

    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,UserAddr,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.
     
    Viktor Popov, Jul 20, 2004
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. René de Leeuw

    result from stored procedure

    René de Leeuw, Oct 23, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    459
    René de Leeuw
    Oct 23, 2003
  2. Lacka
    Replies:
    2
    Views:
    885
    Lacka
    Dec 31, 2004
  3. Mike P
    Replies:
    0
    Views:
    3,381
    Mike P
    Jun 19, 2006
  4. jaym1212

    Result of Comparision

    jaym1212, Jan 20, 2005, in forum: C Programming
    Replies:
    9
    Views:
    327
    Lawrence Kirby
    Jan 31, 2005
  5. Michael Tan
    Replies:
    32
    Views:
    1,079
    Ara.T.Howard
    Jul 21, 2005
Loading...

Share This Page