Check duplication with the database ASP.NET C#

Question

1.00/5 (2 votes)

See more:

I have a details view and button and I want when I click on the button to check if the username is already in the database or not, I tried the below code but it goes to the else statement even if the username exists.

I want to show an error message instead of the error page, my question is where should i put my code .. Any Help??

ASP.NET

<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" 
        CellPadding="4" DataKeyNames="Username" DataSourceID="SqlDataSource3" 
           ForeColor="#333333" GridLines="None" Height="50px" Width="283px" 
            style="margin-top: 0px; text-align: left;" 
           onitemupdated="DetailsView1_ItemUpdated" onitemdeleted="DetailsView1_ItemDeleted" 
           oniteminserted="DetailsView1_ItemInserted" DefaultMode="Insert" 
           oniteminserting="DetailsView1_ItemInserting">
        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
        <CommandRowStyle BackColor="#E2DED6" Font-Bold="True" />
        <EditRowStyle BackColor="#999999" />
        <FieldHeaderStyle BackColor="#E9ECF1" Font-Bold="True" />
        <Fields>
            <asp:TemplateField HeaderText="Username" SortExpression="Username">
                <EditItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text='<%# Eval("Username") %>'></asp:Label>
                </EditItemTemplate>
                <InsertItemTemplate>
                    <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Username") %>' 
                        ValidationGroup="1"></asp:TextBox>
                    <asp:RequiredFieldValidator id="RequiredFieldValidator1" runat="server" 
                        ErrorMessage="*" ControlToValidate="TextBox1" ForeColor="Red" 
                        ValidationGroup="1"></asp:RequiredFieldValidator>
                </InsertItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label2" runat="server" Text='<%# Bind("Username") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Password" SortExpression="Password">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Password") %>'></asp:TextBox>
                </EditItemTemplate>
                <InsertItemTemplate>
                    <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Password") %>' ValidationGroup="1"></asp:TextBox>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" 
                        ControlToValidate="TextBox2" ErrorMessage="*" ForeColor="Red" 
                        ValidationGroup="1"></asp:RequiredFieldValidator>
                </InsertItemTemplate>
                <ItemTemplate>

                </ItemTemplate>
                <ControlStyle />
            </asp:TemplateField>
            <asp:TemplateField HeaderText="UserType" SortExpression="UserType">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("UserType") %>'></asp:TextBox>
                </EditItemTemplate>
                <InsertItemTemplate>
                    <asp:DropDownList ID="DropDownList4" runat="server" 
                        ConnectionString ="<%$ConnectionStrings: ConnectionString %>" 
                        SelectedValue='<%# Bind("UserType") %>' ValidationGroup="1">
                    <asp:ListItem>--Select--</asp:ListItem>
                    <asp:ListItem>employee</asp:ListItem>
                    <asp:ListItem>doctor</asp:ListItem>
                    <asp:ListItem>student</asp:ListItem>
                    </asp:DropDownList> 
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" 
                        ControlToValidate="DropDownList4" ErrorMessage="*" ForeColor="Red" 
                        InitialValue="--Select--" ValidationGroup="1"></asp:RequiredFieldValidator>
                </InsertItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("UserType") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:CommandField ButtonType="Button" ShowInsertButton="True" ValidationGroup="1" />
        </Fields>
        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
    </asp:DetailsView>

What I have tried:

protected void Button2_Click(object sender, EventArgs e)
     {
     SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
     string query = "select Username from [Login]";
     SqlCommand cmd = new SqlCommand(query);
     SqlDataAdapter sqlda = new SqlDataAdapter(cmd.CommandText, con);
     DataTable dt = new DataTable();
     sqlda.Fill(dt);
     int RowCount = dt.Rows.Count;

     for (int i = 0; i < RowCount; i++)
     {
     Label13.Text = ((TextBox)DetailsView1.FindControl("TextBox1")).Text;
     Label14.Text = dt.Rows[i]["Username"].ToString();


     if (Label13.Text == Label14.Text)
     {
     string message = "Username is Already Exists";
     System.Text.StringBuilder sb = new System.Text.StringBuilder();
     sb.Append("<script type = 'text/javascript'>");
     sb.Append("window.onload=function(){");
     sb.Append("alert('");
     sb.Append(message);
     sb.Append("')};");
     sb.Append("</script>");
     ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", sb.ToString());

     }

     else
     {
     string message = "SuccessFully Saved";
     System.Text.StringBuilder sb = new System.Text.StringBuilder();
     sb.Append("<script type = 'text/javascript'>");
     sb.Append("window.onload=function(){");
     sb.Append("alert('");
     sb.Append(message);
     sb.Append("')};");
     sb.Append("</script>");
     ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", sb.ToString());


     }
     }
     }

Posted 27-Apr-17 1:15am

R.M49

Updated 27-Apr-17 12:33pm

v3

Add a Solution

Comments

[no name] 27-Apr-17 7:26am

The debugger can probably tell you exactly why.

R.M49 27-Apr-17 7:31am

how to fix it

[no name] 27-Apr-17 8:37am

You do like everyone else does and use the debugger to find and fix you problem.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhipal Singh · Answer 1 · 2017-04-27T01:34:00

Solution 1

I would say the code you shared is rather kid'ish.. Anyways, here is the issue and suggestion to make it better
Issue:

SQL

select Username from [Login]

Query will return all the usernames from database. So, you are iterating the whole result set. Assuming that the username will occur just once in you table, the if condition will return true just one, and for rest of the time it will fall in else and hence the issue. To fix this, exit the loop as soon as you enter the if condition. It will terminate the loop.

I could not see the purpose of this loop unless you make us understand.

Suggestion:
Filter the result set in your SQL query by writing

SQL

select Username from [Login] WHERE Username = '<username from textbox>'

This will allow you to check the database for username without iterating the entire result set. Think of a scenario where you have 10000 users and they all are accessing your page. It will make you loop run 10000*10000 times.

Posted 27-Apr-17 1:34am

Abhipal Singh

Comments

R.M49 27-Apr-17 17:58pm

Ok I tried the below code and I tested it with button and it worked, but know I want to assign it to the insert command of the detailsview. I tried to put it in DetailsView1_ItemInserting but in case of if statement it gives me the yellow error page with PK duplication, but in case of else statement is works fine. Any Help??

Hide Copy Code

protected void DetailsView1_ItemInserting(object sender, DetailsViewInsertEventArgs e)        {            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);            string query = "select Username from [Login] where Username = '" + ((TextBox)DetailsView1.FindControl("TextBox1")).Text + "'";            SqlCommand cmd = new SqlCommand(query, con);            con.Open();            SqlDataReader dr = cmd.ExecuteReader();                        dr.Read();            if (dr.HasRows == true)            {                 Response.Write("<script>alert('Username is Already Exists')</script>");             }            else            {                Response.Write("<script>alert('SuccessFully Saved')</script>");            }             con.Close();        }

Abhipal Singh 28-Apr-17 13:35pm

Ofcourse it will give error if the username column is PK (primary key), the if clause will be true if the username exists in the database and you try to insert it again by writing insert in if.

Again, what are you trying to achieve?

R.M49 28-Apr-17 14:56pm

the if statment doesn't contain the insertstatement, it contains the error message that I wrote it. I want to display the error message that I put in dr.hasraw statment when the username is duplicated, I don't want to get an error page from the browser I want to show my error message. What I want is to show a message box if the username exists.