Trouble UPDATEing an Access database

Question

User1940910349 posted

I can SELECT np but I want to UPDATE and it's not working. I don't know if I am using bad code or if Access has some default setting that doesn't let you use UPDATE queries. I did disable the Action Query  check box under options in Access so that I can run the query IN Access.

Here is my UPDATE code:

    public void UpdateCount(string database, int newCount)
    {
        //Declare and Instantiate a new Database connection and Command variable, as well as a string to hold the command
        System.Data.OleDb.OleDbConnection conn = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + database + "");
        conn.Open();
        System.Data.OleDb.OleDbCommand command = new OleDbCommand();
        command = conn.CreateCommand();
        string strSQL;

        strSQL = "UPDATE tblCounter SET tblCounter.[Counter] = newCount WHERE(((tblCounter.ID) = 1))";

        command.CommandType = CommandType.Text;
        command.CommandText = strSQL;
        command.ExecuteNonQuery();
        conn.Close();
    }

---------------------

Remember, the SQL works in Access.

How can I get this to work?

 

Answer 1

User-821857111 posted

"It's not working" doesn't give a lot to go on, but my guess is that this is what you need: Solving the "Operation must use an updateable query" error.

 

Answer 2

User-1199946673 posted

My guess would be that you don't provide the value of newCount to the command:

command.CommandType = CommandType.Text;
command.CommandText = strSQL;
command.Parameters.AddWithValue("", newCount);
command.ExecuteNonQuery();

http://www.mikesdotnetting.com/Article.aspx?ArticleID=26