SQL query ExecuteNonQuery(); is not working

Question

What is wrong with this simple code? The second query " in //" which is the direct value works fine but the first query execution (rowsAffected) does return a value of 0.

try
{
    using (con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + Server.MapPath("/App_Data/assets/DB.accdb") + "; Persist Security Info=False"))
    {
        con.Open();

        string query = "UPDATE tblTimeSheetDataTemp SET WBSID = @pWBSID, ProjectID = @pProjectID, FridayWorkHours = @pFridayWorkHours WHERE ID = @pid";

        //string query = "UPDATE tblTimeSheetDataTemp SET WBSID ='020501', ProjectID = '20232001', FridayWorkHours = '1' WHERE ID = 79";

        OleDbCommand sqlCmd = new OleDbCommand(query, con);

        sqlCmd.Parameters.AddWithValue("@pid", "'79'");
        sqlCmd.Parameters.AddWithValue("@pWBSID", "'020501'");
        sqlCmd.Parameters.AddWithValue("@pProjectID", "'20232001'");
        sqlCmd.Parameters.AddWithValue("@pFridayWorkHours", "1");

        int rowsAffected = sqlCmd.ExecuteNonQuery();
        con.Close();

        timesheetID.EditIndex = -1;
        populategridview();

        lblsucess.Text = query;//"Selected Record Updated";
        Lblerror.Text = "";
    } // using
}  // try
catch (Exception ex)
{
    lblsucess.Text = "";
    Lblerror.Text = ex.Message;
}  // catch

The rowsAffected which is output of ExecuteNonQuery() with the value of 0.

Answer 1

You're using OleDbCommand to query MS Access - but OleDB does NOT support named parameters (@pid).

OleDB parameters are positional, e.g. you need to provide the parameters in the same order as they appear in your query text

@pWBSID, @pProjectID, @pFridayWorkHours, @pid

Since you're not doing that, most likely, the UPDATE just doesn't find a row to update - thus the rowsAffected is 0 - because nothing was in fact updated.....

Answer 2

There are two direct problems related to the question, and two others that won't exactly break things but leave you with less-optimal code.

First, Access does not use named parameters. Instead, it matches up the query parameter by position. The result is it's as if you tried to run this code:

UPDATE tblTimeSheetDataTemp SET WBSID = '''79''', ProjectID = '''020501''', FridayWorkHours = '''20232001''' WHERE ID = '1'

This is clearly not what was intended. Also note all the extra quote marks. This is the second issue. I've shown them escaped for SQL, even though it doesn't exactly happen that way, but I wanted to emphasize what is going on. You should not include any quotes with the parameter values. ADO.Net will do that for you.

The third (non-breaking) issue is AddWithValue(). This forces ADO.Net and the database to guess at the parameter type. Often it's okay, but sometimes the guess is wrong in subtle ways. When that happens, it can force per-row conversions for every row, as well an index misses, which cuts to the core of performance. It's less common on an UPDATE statement, as long as the predicate parameters are correct, but still something to be aware of and it's a good habit to avoid it. Finally, while I see there is a using block, we can still improve it by removing the .Close() call and declaring the variable as part of the block.

Put it all together like this:

string query = "UPDATE tblTimeSheetDataTemp SET WBSID = ?, ProjectID = ?, FridayWorkHours = ? WHERE ID = ?";

using var con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + Server.MapPath("/App_Data/assets/DB.accdb") + "; Persist Security Info=False");
using var cmd = new OleDbCommand(query, con));

// Assuming the "ID" parameters without leading 0s are integers
cmd.Parameters.Add("?", OleDbType.Char, 6).Value = "020501";
cmd.Parameters.Add("?", OleDbType.Integer).Value = 20232001;
cmd.Parameters.Add("?", OleDbType.Integer).Value = 1;
cmd.Parameters.Add("?", OleDbType.Integer).Value = 79;

try
{
    con.Open();
    cmd.ExecuteNonQuery();

    timesheetID.EditIndex = -1;
    populategridview();

    lblsucess.Text = query;//"Selected Record Updated";
    Lblerror.Text = "";
}
catch (Exception ex)
{
    lblsucess.Text = "";
    Lblerror.Text = ex.Message;
}