oledbcommand ExecuteNonQuery won't update

Question

I have the following code which does a select based upon a string field and a date and if it returns anything then it tries to do an update. The select returns something, however when I do an update the variable "affected" is set to zero, Could this be because of dates? My date variable is set to (in British format) {02/06/2016 13:10:00} when I inspect it, so it does have a time component.

//  DateTime Md, string ht = these are set at this point

var conn = new OleDbConnection();
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\MyPath\\MyDb.accdb";
conn.Open();

var cmdSel = conn.CreateCommand();
cmdSel.CommandText = @"SELECT * FROM MyTable WHERE Md = @MD AND HT=@HT";
cmdSel.Parameters.AddWithValue("@MD", Md);
cmdSel.Parameters.AddWithValue("@HT", ht);

var da = new OleDbDataAdapter(cmdSel);
var ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
    var cmd = conn.CreateCommand();
    cmd.CommandText = @"UPDATE MyTable SET HS=@HS WHERE Md = @MD AND HT=@HT";
    cmd.Parameters.AddWithValue("@MD", Md);
    cmd.Parameters.AddWithValue("@HT", ht);
    cmd.Parameters.AddWithValue("@HS", hs);
    var affected = cmd.ExecuteNonQuery();
}

Is it updates in your access? What is your update command looks like after you add values? What are your column types? — Soner Gönül, Jun 05 '16 at 17:54
You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... — marc_s, Jun 05 '16 at 18:06
Also use `using` statement to dispose your connection and command automatically. — Soner Gönül, Jun 05 '16 at 18:21

score 1 · Accepted Answer · edited May 23 '17 at 10:29

Access/OleDB doesn't use named parameters as such, they are just positional placeholders. So, you must supply the parameter values in the exact order listed in the SQL. Your code with the other recommended fixes would be something like:

string sql = @"UPDATE MyTable SET HS=@HS WHERE Md = @MD AND HT=@HT";

using (OleDbConnection dbcon = new OleDbConnection(AceConnStr))
{ 
    // other code
    using (OleDbCommand cmd = new OleDbCommand(sql, dbcon))
    { 
        // add in the same order as in SQL
        // I have no idea that these are
        cmd.Parameters.Add("@HS", OleDbType.VarChar).Value = Hs;
        cmd.Parameters.Add("@MD", OleDbType.Date).Value = Md;
        cmd.Parameters.Add("@HT", OleDbType.Integer).Value = Ht;
        int rows = cmd.ExecuteNonQuery();

    }   // closes and disposes of connection and command objects
}

The results of not disposing of things which ought to be seen in this question where the user ran out of resources trying to manually insert in a loop.

You also do not need to create a DataAdapter (or DataSet) just to fill a table' you can use a DataReader to do the same thing.

myDT.Load(cmd.ExecuteReader());

oledbcommand ExecuteNonQuery won't update

1 Answers1