CODEWITHSUNDEEP
c#sqlasp.netwebforms
murday1983
murday1983

Reputation: 4006

Checking Username Available Doesn't Work

I have an asp.net web forms application which when a user tabs out of the username field it checks my database to see if its available or not but the issue I am having is that it always seems to fall into the exists even if it doesn't.

I have watched many videos on it and also read many articles but I can't get it to work at all.

I have provided all my code below.

Config

<add name="PaydayLunchConnectionString1" connectionString="Data Source=********\*******;Initial Catalog=************;Integrated Security=True"
  providerName="System.Data.SqlClient" />

HTML

<asp:GridView ID="tblUsers" runat="server" AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlUsers" GridLines="None" Width="15%">
        <AlternatingRowStyle BackColor="White" />
        <Columns>
            <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
        </Columns>
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <RowStyle BackColor="#EFF3FB" />
</asp:GridView>
<asp:SqlDataSource ID="SqlUsers" runat="server" ConnectionString="<%$ ConnectionStrings:PaydayLunchConnectionString1 %>" SelectCommand="SELECT [Name] FROM [Users] WHERE [name] != 'Admin'"></asp:SqlDataSource>
<asp:Label ID="removeUserNotExist" runat="server" Text="The user entered does not exist. Please try again." Visible="false" style="color: red"></asp:Label>
<asp:Label ID="removeUserExists" runat="server" Text="The user entered exists." Visible="false" style="color: green"></asp:Label>
<div class="form-group">        
    <asp:Label runat="server" AssociatedControlID="txtRemoveUser" CssClass="col-sm-offset-2 col-sm-3 control-label">Enter Name To Be Removed</asp:Label>
    <div class="col-sm-3">
        <asp:TextBox runat="server" ID="txtRemoveUser" CssClass="form-control" AutoPostBack="true" OnTextChanged="txtRemoveUser_TextChanged" />
    </div>
</div>

Code Behind

using System.Data.SqlClient;
using System.Configuration;
using System.Data;

protected void txtRemoveUser_TextChanged(object sender, EventArgs e)
{
    if (!string.IsNullOrEmpty(txtRemoveUser.Text))
    {
        string connection = ConfigurationManager.ConnectionStrings["PaydayLunchConnectionString1"].ConnectionString;
        SqlConnection conn = new SqlConnection(connection);

        conn.Open();
        SqlCommand cmd = new SqlCommand("SELECT * FROM Users WHERE Name != @Name", conn);
        cmd.Parameters.AddWithValue("@Name", txtRemoveUser.Text);
        SqlDataReader rd = cmd.ExecuteReader();
        if (rd.HasRows)
        {
            removeUserNotExist.Visible = true;
            removeUserExists.Visible = false;
        }
        else
        {
            removeUserNotExist.Visible = false;
            removeUserExists.Visible = true;
        }
    }
}

DB Details

Table Name = Users
Columns = ID, Name, Password
Users = Test, Test2

If I enter 'Test' in the field and tab out, I get the correct message (Exists) but if i then enter 'ABC' I still get the 'Exists' message.

Upvotes: 0

Views: 32

Answers (1)

Patrick Hofman
Patrick Hofman

Reputation: 156948

If there is more than 1 user in your database, this query will always produce rows. Hence, your if statement always produces the same result:

SELECT * FROM Users WHERE Name != @Name

If you want to check if a user name exists, simply check for equality.

SELECT * FROM Users WHERE Name = @Name

If that one returns a row, the user name exists. Otherwise it doesn't.

A better solution would be to use 1 in the select, since that prevents the database to return all row data, a small performance improvement:

SELECT 1 dummy FROM Users WHERE Name = @Name

Upvotes: 1

Related Questions