Cannot update Access database in C#

Question

I am trying to build an update button where I am able to edit the data from Textbox. I am using an Access database and I am having some problems and I cannot update it.

Bellow is my code:

private void cmdUpdate_Click(object sender, EventArgs e)
{
    try
    {
        con = new OleDbConnection(cs.DBConn);
        con.Open();

        string queryUpdate = @"UPDATE tblFixing SET Title=@Title, CodeBefore=@CodeBefore, CodAfter=@CodAfter, Exp=@Exp, Example=@Example, Notes=@Notes WHERE FixID=@FixID";
        cmd = new OleDbCommand(queryUpdate);
        cmd.Connection = con;

        cmd.Parameters.Add(new OleDbParameter("@FixID", OleDbType.WChar, 20, "FixID"));
        cmd.Parameters.Add(new OleDbParameter("@Title", OleDbType.WChar, 255, "Title"));
        cmd.Parameters.Add(new OleDbParameter("@CodeBefore", OleDbType.WChar, 0, "CodeBefore"));
        cmd.Parameters.Add(new OleDbParameter("@CodAfter", OleDbType.WChar, 0, "CodAfter"));
        cmd.Parameters.Add(new OleDbParameter("@Exp", OleDbType.WChar, 0, "Exp"));
        cmd.Parameters.Add(new OleDbParameter("@Example", OleDbType.WChar, 0, "Example"));
        cmd.Parameters.Add(new OleDbParameter("@Notes", OleDbType.WChar, 255, "Notes"));

        cmd.Parameters["@FixID"].Value = cmbID.Text.Trim();
        cmd.Parameters["@Title"].Value = txtTitulo.Text.Trim();
        cmd.Parameters["@CodBefore"].Value = rchCodBefore.Text.Trim();
        cmd.Parameters["@CodAfter"].Value = rchCodAfter.Text.Trim();
        cmd.Parameters["@Exp"].Value = rchExplicacao.Text.Trim();
        cmd.Parameters["@Example"].Value = rchExemplo.Text.Trim();
        cmd.Parameters["@Notes"].Value = rchNotas.Text.Trim();

        cmd.ExecuteNonQuery();
        MessageBox.Show("Successful updated", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);

        con.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error\nDetails: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

I debug the code and it does everything but the data does not update. What should I do?

Your string is queryUpdate, then you use cmd = new OleDbCommand(queryInsert);. Is this a typo? — Equalsk
– Equalsk, Commented Mar 28, 2017 at 14:40
If cmd.ExecuteNonQuery returns a value greater than zero, then the query worked, and you are probably looking at the wrong database. — LarsTech
– LarsTech, Commented Mar 28, 2017 at 14:42
How do your check if data is updated or not? Passing 4th Argument 'srcColumn' to the constructor of OleDbParameter is not required. — Chetan
– Chetan, Commented Mar 28, 2017 at 14:43

LarsTech · Accepted Answer · 2017-03-28 15:15:43Z

2

OleDB doesn't use named parameters. They appear in index order.

You have the @FixID parameter as the first parameter added to the collection, but when you look at the sql string, it's the last parameter in the string.

Change your parameter order:

cmd.Parameters.Add(new OleDbParameter("@Title", OleDbType.WChar, 255, "Title"));
cmd.Parameters.Add(new OleDbParameter("@CodeBefore", OleDbType.WChar, 0, "CodeBefore"));
cmd.Parameters.Add(new OleDbParameter("@CodAfter", OleDbType.WChar, 0, "CodAfter"));
cmd.Parameters.Add(new OleDbParameter("@Exp", OleDbType.WChar, 0, "Exp"));
cmd.Parameters.Add(new OleDbParameter("@Example", OleDbType.WChar, 0, "Example"));
cmd.Parameters.Add(new OleDbParameter("@Notes", OleDbType.WChar, 255, "Notes"));
cmd.Parameters.Add(new OleDbParameter("@FixID", OleDbType.WChar, 20, "FixID"));

answered Mar 28, 2017 at 15:15

LarsTech

81.9k14 gold badges161 silver badges237 bronze badges

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

Collectives™ on Stack Overflow

Cannot update Access database in C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related