check if email is in the SQL database

[sk8ergirl]

My page have textbox , lable and one button when the user click the button it should display message if the user in the database "email has been sent" the message will show otherwiser it will show "email is not in the system"

this is what I did , but the problem is that the last email in the database will get this massage and the firsts emails get the error message

protected void Button1_Click(object sender, EventArgs e)
    {
        string email = txtBoxEmail.Text.Trim();
        lblMessage.Text = email;


        string conString = ConfigurationManager.ConnectionStrings["StringConnection"].ConnectionString;
        SqlCeConnection con = new SqlCeConnection(conString);
        string sql = "Select * from accountInfo";
        SqlCeCommand command = new SqlCeCommand(sql, con);
        con.Open();
        SqlCeDataReader dataReader = command.ExecuteReader();

        while (dataReader.Read())
        {
             string emailAddress = dataReader["email"].ToString();
            if (emailAddress.Equals(email))
            {
                lblMessage.Text = "Sent";

            }
            else
            {
                lblMessage.Text = "Invalid email";
            }

        }
        con.Close();

    }

I don't know what worng with the code

[Momerath]

Don't select all of them, just select the one you want to check and see if anything is returned. Much faster.

[sk8ergirl]

I tried doing this but still I get the same error

protected void Button1_Click(object sender, EventArgs e)
    {
        string email = txtBoxEmail.Text.Trim();
        lblMessage.Text = email;
        string conString = ConfigurationManager.ConnectionStrings["StringConnection"].ConnectionString;
        SqlCeConnection con = new SqlCeConnection(conString);
        string sql = "Select * from accountInfo";
        SqlCeCommand command = new SqlCeCommand(sql, con);
        con.Open();
        SqlCeDataReader dataReader = command.ExecuteReader();
        while (dataReader.Read())
        {
             string emailAddress = dataReader["email"].ToString();
            if (emailAddress.Equals(email))
            {
                lblMessage.Text = "Sent";
            }
            else
            {
                lblMessage.Text = "Invalid email";
            }
        }
        con.Close();
    }

[Momerath]

You misunderstand. Instead of reading every single email address, you should only ask the database if the one you want exists. The way you have it now, it will always show a failure unless it's the last email address in the database (you continue your while loop even if you've found the one you want, which means the next one will switch the 'sent' to 'Invalid email').

You could fix your problem by adding a line with just break; right after line 16, but you'd still be doing it the worst way you can.

Try using:

String email = textBoxEmail.Text.Trim();
String conString = COnfigurationManager.ConnectionStrings["StringConnection"].ConnectionString;
String sql = "SELECT COUNT(*) FROM accountInfo WHERE email = @email";

parm.Value = email;

using (SqlCeConnection con = new SqlCeConnection(conString)) {
    using (SqlCeCommand command = new SlqCeCommand(sql, con)) {
        command.Parameters["@email'].Value = email;

        con.Open();
        int count = command.ExecuteScaler();

        if (count == 0) {
            lblMessage.Text = "Invalid email";
        } else {
            lblMessage.Text = "Sent";
        }
        con.Close();
    }
}

I use parameters (as should you) because you can't trust the user, and they could potentially enter something into your text box which could delete or modify your database. Look up Sql Injection Attack for more info.

[sk8ergirl]

I added break after line 16 it worked ,but what does break; do ?

[Momerath]

break ends a loop, it tells the system that the loop is done no matter what the conditions set for ending it are.