no row at position 0 OleDb database

Question

Im trying to add a change password feature to my program but it keeps pulling up errors. this is the code thats supposed to run when you click on save:

    private void button3_Click(object sender, EventArgs e)
    {

        OleDbConnection con = new OleDbConnection();
        con.ConnectionString = (@"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\Users\User\Desktop\esoft\gym\gym\bin\Debug\Clients.accdb");

        OleDbDataAdapter da = new OleDbDataAdapter(" SELECT COUNT(*) FROM login WHERE username='"+textBox1.Text+ "' AND password='" + textBox2.Text + "'",con);
        DataTable dt = new DataTable();

        con.Open();
        errorProvider1.Clear();
        if (dt.Rows[0][0].ToString() == "1")
        {
            if (textBox3.Text == textBox4.Text)
            {
                OleDbDataAdapter sda = new OleDbDataAdapter("UPDATE login WHERE username ='" + textBox1.Text + "', password='" + textBox2.Text + "' (password ='" + textBox3.Text + "')", con);
                sda.Fill(dt);
                MessageBox.Show("password successfully changed", "success!", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                errorProvider1.SetError(textBox3, "passwords dont match");
                errorProvider1.SetError(textBox4, "passwords dont match");
            }
        }

        else
        {
            errorProvider1.SetError(textBox1, "wrong username");
            errorProvider1.SetError(textBox2, "wrong pasword");

        }
        con.Close();
    }

    private void textBox1_TextChanged(object sender, EventArgs e)
    {

    }

the main error for now is that when trying to save, it pulls up an error stating that no rows were found at position 3. when changed to [1][5] it pulls up the same error for that position.

i changed the code using your suggestions, yet still get the same error.

  private void button3_Click(object sender, EventArgs e)
    {

        using (OleDbConnection con = new OleDbConnection(@"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\Users\User\Desktop\esoft\gym\gym\bin\Debug\Clients.accdb"))
        {


            OleDbDataAdapter da = new OleDbDataAdapter(" ExecuteScalar FROM login WHERE username='" + textBox1.Text + "' AND password='" + textBox2.Text + "'", con);
            DataTable dt = new DataTable();

            con.Open();
            errorProvider1.Clear();
            if (dt.Rows[0][0].ToString() == "1")
            {
                if (textBox3.Text == textBox4.Text)
                {
                    //   OleDbDataAdapter sda = new OleDbDataAdapter("UPDATE login SET password ='" + textBox3.Text + "' WHERE username ='" +textBox2.Text+"'");

                    OleDbCommand com = new OleDbCommand("UPDATE login SET password = '" + textBox3.Text + "' WHERE username = '" +textBox2.Text+"'",con);
                    com.ExecuteNonQuery();
                //    sda.Fill(dt);
                    MessageBox.Show("password successfully changed", "success!", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    errorProvider1.SetError(textBox3, "passwords dont match");
                    errorProvider1.SetError(textBox4, "passwords dont match");
                }

what is the purpose of this line `OleDbDataAdapter da = new OleDbDataAdapter(" SELECT COUNT(*) FROM login WHERE username='"+textBox1.Text+ "' AND password='" + textBox2.Text + "'",con);` you declare it but never execute the code to fill the `OleDbDataAdapter` and you should use `ExecuteScalar` if you are returning only a single row google the difference when you have time — MethodMan, Jan 06 '16 at 15:40

score 1 · Answer 1 · edited May 23 '17 at 12:31

You are doing a few things wrong;

Use ExecuteScalar for your SELECT COUNT(*) statement which is exactly what this for.
Even if you don't, you don't fill your dt after all.
You need to use ExecuteNonQuery for your UPDATE statement. DataAdapter is for returning some data.
You forget to use SET in your UPDATE statement.
You need to use AND or OR for multiple WHERE conditions, not ,.
You should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.
Use using statement to dispose your connection and command automatically instead of calling Close method manually.
Do not store your passwords as a plain text. Read: Best way to store password in database
password is a reserved keyword in OLE DB Provider. Based on your database manager case sensitive settings, you might need to use is as [password]. But as a better way, change it to non-reserved word even if it is.

score 1 · Answer 2 · answered Jan 06 '16 at 15:37

That's because you are missing a SET keyword. Your query

UPDATE login WHERE username ='" +

Should be

UPDATE login SET columnname = value WHERE username ='" +

Per your posted code looks like it should be

UPDATE login SET password ='" + textBox3.Text + "' WHERE username ='" +

None the less, use a parameterized query and don't concatenate user input like the way you are doing right now else you have opened the door for SQL Injection.

no row at position 0 OleDb database

2 Answers2