0

I have found many posts on here which are relating to my issue however I am struggling to find out what exactly I need to change to solve my problem of updating an MS Access database from a C# application. So I apologize if people feel this post is too similar to others. Here is my update code:

private void button1_Click(object sender, EventArgs e)
{
    string ConnString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Ts\Documents\testDB.accdb");
    using (OleDbConnection conn = new OleDbConnection (ConnString))
    using (OleDbCommand cmd = conn.CreateCommand())
    {      
        cmd.CommandText = "UPDATE [Table4] SET [EmpName] = ?, [Age] = ?, [Mobile] = ?  WHERE [ID] = ?";

        cmd.Parameters.AddWithValue("@ID", OleDbType.VarChar).Value = idtextBox1.Text;
        cmd.Parameters.AddWithValue("@EmpName", OleDbType.VarChar).Value = nametextBox2.Text;
        cmd.Parameters.AddWithValue("@Age", OleDbType.VarChar).Value = agetextBox3.Text;
        cmd.Parameters.AddWithValue("@Mobile", OleDbType.VarChar).Value = mobiletextBox4.Text;
        cmd.Connection = conn;
        conn.Open();

        int rowsAffected = cmd.ExecuteNonQuery();

        conn.Close();
        Form1_Load(sender, e);
        MessageBox.Show("record updated ");
    }
}
5
  • 2
    this question doesn't have enough information on exactly what you are trying to do, what is happening / any errors. Your code shouldn't have commented out sections, you should keep it to the very minimum you can Commented Jan 4, 2020 at 7:15
  • @KeithNicholas Please stop editing the OP's code. Commented Jan 4, 2020 at 7:22
  • @GSerg sorry, no. commented out code has no place in a code example Commented Jan 4, 2020 at 7:23
  • @KeithNicholas It shows what alternatives they are trying. Even if it did not, regardless of how bad the code is, it is never to be edited, only formatted. Commented Jan 4, 2020 at 7:25
  • @GSerg that isn't a rule. Commented Jan 4, 2020 at 7:25

1 Answer 1

3

For OleDb the order of parameters is of paramount importance. The reason your update will not work, is that you are supplying the ID parameter first, whereas in your SQL it is last (comes in the WHERE clause). Move this parameter to the end and it should work.

The revised code should be something like this:

private void button1_Click(object sender, EventArgs e)
{
    string ConnString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Ts\Documents\testDB.accdb");
    using (OleDbConnection conn = new OleDbConnection(ConnString))
    {
        using (OleDbCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = "UPDATE [Table4] SET [EmpName] = ?, [Age] = ?, [Mobile] = ?  WHERE [ID] = ?";

            cmd.Parameters.AddWithValue("@EmpName", OleDbType.VarChar).Value = nametextBox2.Text;
            cmd.Parameters.AddWithValue("@Age", OleDbType.VarChar).Value = agetextBox3.Text;
            cmd.Parameters.AddWithValue("@Mobile", OleDbType.VarChar).Value = mobiletextBox4.Text;
            cmd.Parameters.AddWithValue("@ID", OleDbType.VarChar).Value = idtextBox1.Text;
            cmd.Connection = conn;
            conn.Open();

            int rowsAffected = cmd.ExecuteNonQuery();
        }
    }
    Form1_Load(sender, e);
    MessageBox.Show("record updated ");
}

I have made a couple of other minor changes, most importantly moving the Form1_Load and the MessageBox outside of the using block. In general you should keep code within using blocks to a minimum, so that the resources can be freed as soon as possible. I also removed the Close(). The OleDbConnection's destructor automatically calls Close(), so there is no specific need to call it yourself.

One other thing might be wrong. You have the ID as a varchar. Are you sure about this? It would be more usual if it were an integer. In which case, you would need to parse the idTextBox1.Text to an integer with int.Parse(), or safer with int.TryParse(), and change the parameter type as well.

Sign up to request clarification or add additional context in comments.

1 Comment

@KeithNicholas Good idea. I've updated the answer. Thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.