1

I am trying to update my ms access db with windows application and I am having a hard time. When I run it I don't get any errors but it does update like once or twice when I test it but then doesn't work again if I do it again a third time.

This is the code I use

Conn.Open();
Command.CommandType = CommandType.Text;
Command.CommandText ="UPDATE TABLE SET c_qty=@qty WHERE id = @ID";

Command.Parameters.AddWithValue("@qty", txtQty.Text);
Command.Parameters.AddWithValue("@ID", txtID.Text);
Command.ExecuteNonQuery();
Conn.Close();

I felt I was doing this right or on the right track of having it correct but seems to be more of a issue then I thought. Any help would be great

Improve this question
1
  • The OleDB provider for MS Access does not support named parameters like @ID - it only supports positional parameters. So it doesn't matter how you name your parameters - what you really need to be careful about is to specify them in the correct order in which they appear in your SQL statement (which is the case, here, in your example) Commented Jul 6, 2017 at 17:35

1 Answer 1

Reset to default
1
  • Quantity and Id are hopefully integers and you should pass them as such.
  • Also Table is a reserved word, if this really is the name of your table you should enclose it with square brackets.
  • You should also pass in the correct db types in your parameters and not use AddWithvalue which does not allow this.

Code

Conn.Open();
Command.CommandType = CommandType.Text;
Command.CommandText ="UPDATE [TABLE] SET c_qty= ? WHERE id = ?";

Command.Parameters.Add(new OleDbParameter("@qty", OleDbType.Int) {Value = int.Parse(txtQty.Text)});
Command.Parameters.Add(new OleDbParameter("@ID",  OleDbType.Int) {Value = int.Parse(txtID.Text)});
var rowsUpdated = Command.ExecuteNonQuery();
// output rowsUpdated to the log, should be 1 if id is the PK
Conn.Close();

Finally use using blocks for your Disposables. If you were to get an Exception here then connection would remain open until Garbage collection runs which means you might have a problem with other connection attempts to this Access database.

Revised with using blocks

using (OleDbConnection Conn = new OleDbConnection("connectionStringHere"))
using (OleDbCommand Command = new OleDbCommand("UPDATE [TABLE] SET c_qty= ? WHERE id = ?", Conn))
{
    Command.Parameters.Add(new OleDbParameter("@qty", OleDbType.Int) {Value = int.Parse(txtQty.Text)});
    Command.Parameters.Add(new OleDbParameter("@ID",  OleDbType.Int) {Value = int.Parse(txtID.Text)});
    Conn.Open();

    var rowsUpdated = Command.ExecuteNonQuery();
    // output rowsUpdated to the log, should be 1 if id is the PK
}

Finally OleDbCommand does not support named parameters, see OleDbCommand.Parameters

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

4 Comments

its not the name its just what I put for posting on here.. I will give your code a try
That worked. I had to tweak it to make it work but it does work now thanks for the help.
Can you verify for me if I would do this same for inserting?
@Don - correct. INSERT INTO [TABLENAME] (column1,column2,column3,...) VALUES (?,?,?,?....) and provide your parameters in order.