data not being updated to table

Question

I'm trying to implement a password change feature but it doesn't seem to want to work.

 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"))
        {
            DataTable dt = new DataTable();
            con.Open();
            errorProvider1.Clear();
            if (dt.Rows[0][0].ToString() == "1")
            {
                if (textBox3.Text == textBox4.Text)
                {

                    OleDbDataAdapter da = new OleDbDataAdapter(" COUNT (*) FROM login WHERE username= '" + textBox1.Text + "' AND [password]='" + textBox2.Text + "' ", con);
                    OleDbCommand com = new OleDbCommand("UPDATE login SET [password] = '" + textBox3.Text + "' WHERE username = '" + textBox2.Text + "'", con);
                    com.ExecuteNonQuery();



                    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");

            }

        }
    }

there is an error in the line if (dt.Rows[0][0].ToString() == "1") where it states that no data was found at that position, yet there are 5 rows in the data table.

when the code is run without the above line, as in //if (dt.Rows[0][0].ToString() == "1")

the code runs but no data is being updated in the table.

updated code again and still recived the same error:

OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM login WHERE username= '" + textBox1.Text + "' AND [password]='" + textBox2.Text + "' ", con);
            DataTable dt = new DataTable();
            da.Fill(dt);
            con.Open();
            errorProvider1.Clear();
            if (dt.Rows[0][0].ToString() == "1")

The bigger problem is you are attempting to store passwords. DON'T DO THAT!!! — Mitch Wheat, Jan 07 '16 at 06:32
You have created just a new instance of `DataTable` without assigning any data to it! — Salah Akbari, Jan 07 '16 at 06:35
and there is no query in the code snippett to fill DataTable — null1941, Jan 07 '16 at 06:40

score 2 · Answer 1 · answered Jan 07 '16 at 08:21

2

Try filling your DataTable as following -

string cmdString = "SELECT * FROM login WHERE username= '" + textBox1.Text + "' AND [password]='" + textBox2.Text + "' ";
OleDbCommand cmd = new OleDbCommand(cmdString,con);
con.Open();
var dr = cmd.ExecuteReader();

DataTable dt = new DataTable();
dt.Load(dr);
con.Close()

Now you should get your data in table, provided that your select query is correct. Make sure you use using blocks on connection and command objects to dispose these when they are out of scope.

answered Jan 07 '16 at 08:21

Yogi

9,174
2
46
61

that didn't work either :/ still getting same error (no data found) when i input a wrong username instead of displaying the error provider. and when i try to change the password of an existing username it says saved but no data is actually updated. – iboss Jan 07 '16 at 08:38
Ok. I've been messing around with the code for ages now and whatever i do i always get the same error, no data found.etc. but I've made sure that i have 5 rows in my data table and all other forms with the same connection string work fine. except for one other form that is also a update form. so whats going on here... – iboss Jan 07 '16 at 08:54

score 1 · Answer 2 · answered Jan 07 '16 at 06:34

1

you are just declaring data table,not assigning any data

DataTable dt = new DataTable();

thats why when you try to get dt.Rows[0][0].ToString() it gives error

answered Jan 07 '16 at 06:34

Muhammad Atif

1,050
1
9
21

@iboss - Update your question with changes, and the error details, that you are getting. – Yogi Jan 07 '16 at 07:03

score 0 · Answer 3 · answered Jan 07 '16 at 10:45

As you can try this:

OleDbDataAdapter custDA = new OleDbDataAdapter();
     DataSet custDS = new DataSet();
     DataTable custTable = new DataTable("Customers");
     custTable.Columns.Add("CustomerID", typeof(String));
     custTable.Columns.Add("CompanyName", typeof(String));
     custDS.Tables.Add(custTable);
     //Use ADO objects from ADO library (msado15.dll) imported
     //  as.NET library ADODB.dll using TlbImp.exe
     ADODB.Connection adoConn = new ADODB.Connection();
     ADODB.Recordset adoRS = new ADODB.Recordset();
     adoConn.Open("Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;", "", "", -1);
     adoRS.Open("SELECT CustomerID, CompanyName FROM Customers", adoConn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 1);
     custDA.Fill(custTable, adoRS);
     adoRS.Close();
     adoConn.Close();

You can follow this reference

thank you for your input, but the project is for part of my coursework and we are not supposed to use anything other than OleDb. — iboss, Jan 07 '16 at 12:36

score 0 · Answer 4 · answered Jan 08 '16 at 13:06

As noted by another, you never assign a value to the data table, that is why it is choking. Your query itself, by string concatenation will open you to SQL-Injection. Parameterize it. Finally, for your query, I would query all records for a given user ID, but get the user and password values based on only qualifying the user ID, not the password. This way, if you have more than 1 row returned, it will indicate duplicate user accounts and should get special attention. If it returns NO rows, then no such user. If it returns ONE row, then you can compare to the password entered and if matched, you have your correct user ID to run with.

starting with your

using( OleDbConnection con = ...) 
{
   // create command first.. Parameterize it.  In this case "@" is parameter indicator
   // for Access.  parmUserName is the parameter name to be applied.  I explicitly added
   // "parm" in front to ensure differentiation between the parameter and actual column.
   var cmd = new OleDbCommand( 
               @"select password from login where username = @parmUserName", con);

   // Now, add the parameter of proper data type.  The name of the parameter and it's value
   cmd.Parameters.AddWithValue("parmUserName", textBox1.Text);

   // create your data adapter now based on the command above
   var da = new OleDbDataAdapter(cmd);

   // NOW, create your data table object and have data adapter query and fill with rows.
   var dt = new DataTable();
   da.Fill(dt);

   // NOW, check results.
   if (dt.Rows.Count == 0)
      MessageBox.Show("No such user account");
   else if( dt.Rows.Count > 1)
      MessageBox.Show("Duplicate user account");
   else
   {
      // valid single record. Do the passwords match?
      if (textBox3.Text.Equals(dt.Rows[0]["password"].ToString()))
      {
         MessageBox.Show("Valid login, allow to continue");

         // Now, since it appears you are trying to UPDATE the password for the user,
         // build new UPDATE command and parameterize it in a similar fashion
         var cmdUpd = new OleDbCommand(
                        @"update login set password = @parmNewPwd where username = @parmUserName", con);
         // Now, add the parameter of proper data type.  The name of the parameter and it's value
         cmd.Parameters.AddWithValue("parmNewPwd", textBox3.Text);
         cmd.Parameters.AddWithValue("parmUserName", textBox1.Text);
         if (cmd.ExecuteNonQuery() == 1)
            MessageBox.Show("Password updated");
         else
            MessageBox.Show("Failed updating password");
      }
      else
         MessageBox.Show("Invalid password");
   }
}

FINAL NOTE. You should also look into cleaning data especially before building SQL commands. Never concatenate strings where users can manually enter data for SQL-Injection, parameterize them.

data not being updated to table

4 Answers4

Linked