ExecuteScalar() error

Question

I want to create a Log In form with C#. If the username and password is correct, I do not get an error, but if it's wrong then I get an error in this line:

int count = Convert.ToInt32(cmd.ExecuteScalar().ToString());

log in form code:

if (textBox1.Text != "" & textBox2.Text != "")
{
    conn.Open();
    SqlCeCommand cmd = new SqlCeCommand("SELECT id FROM users WHERE Login = '" + textBox1.Text + "' AND Password = '" + textBox2.Text + "'", conn);
    int count = Convert.ToInt32(cmd.ExecuteScalar().ToString());
    if (count > 0)
    {
        SqlCeDataReader reader = cmd.ExecuteReader();
        reader.Read();
        int logged_id = Convert.ToInt16(reader["id"]);
        SqlCeCommand cmd1 = new SqlCeCommand("SELECT Login, Sex, Weight, Height, Age, PhyActi FROM vartotojai WHERE ID = '"+ logged_id +"'",conn);
        SqlCeDataReader reader1 = cmd1.ExecuteReader();
        reader1.Read();
        textBox9.Text = Convert.ToString(reader1["Sex"]);
        textBox10.Text = Convert.ToString(reader1["Weight"]);
        textBox12.Text = Convert.ToString(reader1["Height"]);
        textBox11.Text = Convert.ToString(reader1["Age"]);
        textBox13.Text = Convert.ToString(reader1["Phyacti"]);
        panel1.Visible = false;
        dataGridView1.Visible = false;
        MessageBox.Show("Loggen In!");
        toolStripStatusLabel1.Text = "Welcome, " + Convert.ToString(reader1["Login"]);
    }
    else
        MessageBox.Show("User Not Found!");
    conn.Close();
}

It is because your query will not return anything if username and password don't match — Aman B, Nov 09 '14 at 10:09
try using count in query SqlCeCommand cmd = new SqlCeCommand("SELECT Count(id) FROM users WHERE Login = '" + textBox1.Text + "' AND Password = '" + textBox2.Text + "'", conn); — Aman B, Nov 09 '14 at 10:09
What is the error exactly? What is the value of `cmd.ExecuteScalar()` returns? What is your `CurrentCulture`? And you should always use [parameterized queries](http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html). This kind of string concatenations are open for [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) attacks. Use _using statement_ to dispose your database connections and objects. And please, **don't store your passwords as a plain text**. — Soner Gönül, Nov 09 '14 at 10:09
Also, even though here its really not relevant, look into the difference between `&` and `&&` — SimpleVar, Nov 09 '14 at 10:13

score 1 · Accepted Answer · answered Nov 09 '14 at 10:16

If you look at the ExecuteScalar help in MSDN HERE

then you will notice that the return value in case the resultset is empty is a null reference.

In your code, when username and/or password is wrong then you are not getting any value i.e. a null reference is returned. Now, you are trying to convert a null value to an Integer and you get the error.

What you should do is that you should get the return value in a system.object variable and check if it is null. If not, then convert it to Integer else show the message "User not found.".

ExecuteScalar() error

1 Answers1