Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    63

    Unanswered: Checking if username exists query

    Im trying to run a query on the database to check if a particular username already exists.

    Here is my sql:

    Code:
    USE [dbTalesOfEpic]
    GO
    /****** Object:  StoredProcedure [dbo].[checkIfUsernameExists]    Script Date: 07/08/2011 18:13:17 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[checkIfUsernameExists]
    
        @username char(20)
    
    AS
    BEGIN
    
    	SET NOCOUNT ON;
    
    SELECT username FROM Accounts
    WHERE username = @username
    
    END
    And here my asp.net c# method:

    Code:
        public bool checkIfUsernameExists(string _username)
        {
            try
            {
                using (myConnection)
                {
                    openDatabaseConnection();
                    using (myCommand)
                    {
                        myCommand.CommandText = "checkIfUsernameExists";
                        myCommand.CommandType = CommandType.StoredProcedure;
                        myCommand.Parameters.Add("@username", SqlDbType.Char, 20);
                        myCommand.Parameters["@username"].Value = _username;
                        int rowsEffected = myCommand.ExecuteNonQuery();
                        if (rowsEffected == 0)
                            return false;
                        else
                            return true;
                    }
                }
            }
            catch (SqlException sqlEX)
            {
                throw sqlEX;
            }
            catch (InvalidOperationException ioeEX)
            {
                throw ioeEX;
            }
            catch (Exception ex)
            {
                throw ex;
            }
    
    
        }
    The method isnt working because "rowsEffected" is being returned as -1.

    Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    167
    I don't know enough about c# to tell you why it's failing - but I can give you a workaround that has slightly better performance: change your procedure to have an OUTPUT parameter called @exists bit like this:

    Code:
    ALTER PROCEDURE [dbo].[checkIfUsernameExists]
    @username char(20),
    @exists bit=0 OUTPUT
    AS
    BEGIN
    set nocount on
    
    select @exists=case when exists(select * from accounts where username=@username) then 1 else 0 end
    
    set nocount off
    
    END
    Then just assign @exists to a variable in you c# and bob's your uncle.
    Kit Lemmonds

  3. #3
    Join Date
    Jun 2011
    Posts
    63
    I managed to fix my code by implementing an SqlDataReader and then checking the "hasRows" property.

    Your is good too but its a little too advanced for me but thanks anyway!

  4. #4
    Join Date
    Jun 2011
    Posts
    63
    Bump
    123456

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •