1

Please help to resolve my problem. I'm getting an error "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records" when trying to update a newly added table record in MS Access (2000 format) database (.mdb) from my application written in C#. This error is rather generic, and I tried solutions suggested on different forums, but with no success.

Here's what I do step by step:

I have a table 'TRACKS' in mdb that has these columns among others:

  • ID - type 'AutoNumber' (key column)
  • Title - 'Text'
  • FullTitle - 'Text'

  • Length - 'Date/Time'

    1. I establish connection to database and get table records this way:

      public partial class MainForm : Form
{
    public OleDbConnection dbConn = new OleDbConnection();
    public DataSet dataset = new DataSet();

    protected OleDbDataAdapter adTracks = new OleDbDataAdapter();

    protected OleDbCommandBuilder cmb;

    ArrayList arrArtists = new ArrayList();

    public MainForm(string strFileName)
    {
        InitializeComponent();
        cmb = new OleDbCommandBuilder(adTracks);
    }

    private void OnLoad(object sender, EventArgs e)
    {
        dbConn.ConnectionString = Properties.Settings.Default.dbConnectionString;
        OleDbCommand cmTracks = new OleDbCommand("Select * from Tracks", dbConn);

        OleDbDataAdapter adapter = new OleDbDataAdapter();

        try
        {
            dbConn.Open();
            adTracks.SelectCommand = cmTracks;
            adTracks.Fill(dataset, "Tracks");
        }
        catch (Exception err)
        {
            MessageBox.Show(err.Message);
            return;
        }
        finally
        {
            dbConn.Close();
        }

        cboOriginal.DataSource = dataset.Tables["Tracks"];
        cboOriginal.DisplayMember = "FullTitle";
        cboOriginal.ValueMember = "ID";
        cboOriginal.SelectedIndex = -1;

        adTracks.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);
    }
}

    2. Then I add a new record to the table with this code (txtTitle and txtGenTitle controls contain values for the record):

      DataTable dt;
DataRow dr;
int newID;

dt = dataset.Tables["Tracks"];
dr = dt.NewRow();
dr["Title"] = txtTitle.Text;
dr["FullTitle"] = txtGenTitle.Text;

dt.Rows.Add(dr);

try
{
    dbConn.Open();
    adTracks.Update(dt);
}
catch (Exception err)
{
    MessageBox.Show("Error adding new track '" + txtGenTitle.Text + "':\n" + err.Message);
    return;
}
finally
{
    dbConn.Close();
}

res = dt.Select("FullTitle = '" + txtGenTitle.Text.Replace("'", "''") + "'");
if (res.Length != 0)
{
    newID = (int)res[0]["ID"];

    // continue with newID
}

      This code is successfully executed: new record is added to the table, both local DataTable and actual table in mdb file. New auto-incremented value for key column is received in this handler:

      protected void OnRowUpdated(object sender, OleDbRowUpdatedEventArgs args)
{
    if (args.StatementType == StatementType.Insert)
    {
        OleDbCommand idCMD = new OleDbCommand("SELECT @@IDENTITY", dbConn);
        args.Row["ID"] = (int)(idCMD.ExecuteScalar());
    }
}

      The row with this ID now has RowState == Unchanged, so everything seems OK.

    3. Now I want to update some values in this newly added record (from txtLength control):

      DataTable dt;
DataRow dr;
DataRow[] res;

dt = dataset.Tables["Tracks"];
res = dt.Select("FullTitle = '" + txtGenTitle.Text.Replace("'", "''") + "'");

if (res.Length != 0)
{
    TimeSpan tsNew = TimeSpan.Zero, tsOld = TimeSpan.Zero;
    if (txtLength.Text != String.Empty) tsNew = TimeSpan.Parse(txtLength.Text);
    if (!(res[0]["Length"] is DBNull))
    {
        DateTime date = (DateTime)res[0]["Length"];
        tsOld = date.TimeOfDay;
    }

    if (tsNew != TimeSpan.Zero && (tsOld == TimeSpan.Zero || tsOld.CompareTo(tsNew) < 0))
    {
        if (tsNew != TimeSpan.Zero && (tsOld == TimeSpan.Zero || tsOld.CompareTo(tsNew) < 0)) res[0]["Length"] = txtLength.Text;

        if (String.Compare((string)res[0]["Title"], txtTitle.Text, true) != 0)
        {
            res[0]["Title"] = txtTitle.Text;
            res[0]["FullTitle"] = txtGenTitle.Text;
        }

        try
        {
            dbConn.Open();
            adTracks.Update(dt);
        }
        catch (Exception err)
        {
            MessageBox.Show("Error updating track '" + txtGenTitle.Text + "':\n" + err.Message);
            return;
        }
        finally
        {
            dbConn.Close();
        }
    }
}

      And get an error on line adTracks.Update(dt) "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records". Database is not updated, neither is DataTable.

This may probably mean that there's some error with record ID - that it's not updated with a correct value after insertion. But that's not the case here: ID is updated in OnRowUpdated handler on step 2 with a correct ID, and also record with this ID is added to to the table mdb file. On step 3 before calling adTracks.Update, res[0] also contains correct ID value and RowState == Modified. But I still get this error. What am I doing wrong?

Adding dt.AcceptChanges() after adTracks.Update(dt) as suggested here - doesn't help.

UPDATE:

1. Trying approach suggested by krish:

I added the following lines before try/catch block on step 3:

string cmd = "UPDATE TRACKS SET Length = '" + res[0]["Length"] + "' WHERE ID = " + res[0]["ID"];
adTracks.UpdateCommand = new OleDbCommand(cmd, dbConn);

And it works! Database is updated and corresponding DataRow gets RowState == Unchanged. This is quite a workaround. But I'd still like to know why 'conventional' approach doesn't work here. Workaround is not really convenient when there's a need to update many columns. Also it seems only to be acceptable when I'm updating a single row, and I need the ability to update several rows at once.

2. Trying approach suggested by hynsey:

I replaced the line adTracks.Update(dt); on step 3 with the following code:

using (OleDbDataAdapter da = new OleDbDataAdapter ("Select * from Tracks", dbConn))
{
    OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
    da.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);
    da.Update(dt);
}

Sadly, the behavior didn't change at all - same error "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records".

Here's a code that I use on all 3 steps (compare with the original):

1. 
        public partial class MainForm : Form
        {
            public OleDbConnection dbConn = new OleDbConnection();
            public DataSet dataset = new DataSet();

            protected OleDbDataAdapter adTracks = new OleDbDataAdapter();

            ArrayList arrArtists = new ArrayList();

            public MainForm(string strFileName)
            {
                InitializeComponent();
            }

            private void OnLoad(object sender, EventArgs e)
            {
                dbConn.ConnectionString = Properties.Settings.Default.dbConnectionString;

                try
                {
                    dbConn.Open();
                    adTracks = new OleDbDataAdapter("Select * from Tracks", dbConn));
                    adTracks.Fill(dataset,"Tracks");    
                }
                catch (Exception err)
                {
                    MessageBox.Show(err.Message);
                    return;
                }
                finally
                {
                    dbConn.Close();
                }

                cboOriginal.DataSource = dataset.Tables["Tracks"];
                cboOriginal.DisplayMember = "FullTitle";
                cboOriginal.ValueMember = "ID";
                cboOriginal.SelectedIndex = -1;

                adTracks.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);
            }
        }

2.
        DataTable dt;
        DataRow dr;
        int newID;

        dt = dataset.Tables["Tracks"];
        dr = dt.NewRow();
        dr["Title"] = txtTitle.Text;
        dr["FullTitle"] = txtGenTitle.Text;

        dt.Rows.Add(dr);

        try
        {
            dbConn.Open();
            using (OleDbDataAdapter da = new OleDbDataAdapter ("Select * from Tracks", dbConn)) 
            {
                OleDbCommandBuilder cb = new OleDbCommandBuilder(da); 
                da.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);
                da.Update(dataset, "Tracks"); 
            }
        }
        catch (Exception err)
        {
            MessageBox.Show("Error adding new track '" + txtGenTitle.Text + "':\n" + err.Message);
            return;
        }
        finally
        {
            dbConn.Close();
        }

        res = dt.Select("FullTitle = '" + txtGenTitle.Text.Replace("'", "''") + "'");
        if (res.Length != 0)
        {
            newID = (int)res[0]["ID"];

            // continue with newID
        }        

3.
        DataTable dt;
        DataRow dr;
        DataRow[] res;

        dt = dataset.Tables["Tracks"];
        res = dt.Select("FullTitle = '" + txtGenTitle.Text.Replace("'", "''") + "'");

        if (res.Length != 0)
        {
            TimeSpan tsNew = TimeSpan.Zero, tsOld = TimeSpan.Zero;
            if (txtLength.Text != String.Empty) tsNew = TimeSpan.Parse(txtLength.Text);
            if (!(res[0]["Length"] is DBNull))
            {
                DateTime date = (DateTime)res[0]["Length"];
                tsOld = date.TimeOfDay;
            }

            if (tsNew != TimeSpan.Zero && (tsOld == TimeSpan.Zero || tsOld.CompareTo(tsNew) < 0))
            {
                if (tsNew != TimeSpan.Zero && (tsOld == TimeSpan.Zero || tsOld.CompareTo(tsNew) < 0)) res[0]["Length"] = txtLength.Text;

                if (String.Compare((string)res[0]["Title"], txtTitle.Text, true) != 0)
                {
                    res[0]["Title"] = txtTitle.Text;
                    res[0]["FullTitle"] = txtGenTitle.Text;
                }

                try
                {
                    dbConn.Open();
                    using (OleDbDataAdapter da = new OleDbDataAdapter ("Select * from Tracks", dbConn)) 
                    {
                        OleDbCommandBuildercb = new OleDbCommandBuilder(da); 
                        da.Update(dataset , "Tracks"); 
                    }
                }
                catch (Exception err)
                {
                    MessageBox.Show("Error updating track '" + txtGenTitle.Text + "':\n" + err.Message);
                    return;
                }
                finally
                {
                    dbConn.Close();
                }
            }
        }

3. In order to investigate why my initial code doesn't work, I'm providing some more details on the error:

OnRowUpdated handler is called despite the error, and I'm able to examine args parameter that is passed into handler. args.Row has RowState == Modified and args.Command has the following CommandText (I added line breaks for readability):

UPDATE Tracks SET Length = ? WHERE ((ID = ?) AND
((? = 1 AND Title IS NULL) OR (Title = ?)) AND
((? = 1 AND FullTitle IS NULL) OR (FullTitle = ?)) AND
((? = 1 AND GenreID IS NULL) OR (GenreID = ?)) AND
((? = 1 AND StyleID IS NULL) OR (StyleID = ?)) AND
((? = 1 AND SubStyleID IS NULL) OR (SubStyleID = ?)) AND
((? = 1 AND Length IS NULL) OR (Length = ?)) AND
((? = 1 AND UseOriginal IS NULL) OR (UseOriginal = ?))
AND ((? = 1 AND Version IS NULL) OR (Version = ?)) AND
((? = 1 AND TrackID IS NULL) OR (TrackID = ?)) AND
((? = 1 AND SpecPresConjunctor IS NULL) OR (SpecPresConjunctor = ?)) AND
((? = 1 AND SpecFeatConjunctor IS NULL) OR (SpecFeatConjunctor = ?)) AND
((? = 1 AND FreeRecord IS NULL) OR (FreeRecord = ?)))

Can anyone tell what is wrong with this generated command? 'GenreID', 'StyleID', etc. are other columns in the 'TRACKS' table. I don't know what all these '?' mean.

Also, when the exception occurs, here are the latest calls on the stack:

at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at *my code as above*
Community
  • 1
  • 1
kdg82
  • 11
  • 1
  • 4
  • check this site: http://blogs.msdn.com/b/spike/archive/2010/04/07/concurrency-violation-the-updatecommand-affected-0-of-the-expected-1-records.aspx – Krish Jun 03 '15 at 08:45
  • When updating datasets with DataAdapters, I tend to use the following approach: `using (OleDbDataAdapter da = new OleDbDataAdapter ("Select * from Tracks", dbConn)) { OracleCommandBuilder cb = new OracleCommandBuilder(adTracks); adTracks.Update(dataset , dataset.Tables["Tracks"]); }` – hynsey Jun 03 '15 at 08:46
  • @krish I know about this link. In my case there's no concurrency, my app is the only one that accesses database at a time and no records are deleted. So the situation described in that article is not applicable here, sadly. – kdg82 Jun 04 '15 at 11:59
  • can you try a standard SQL execution? a string update command to the database – Krish Jun 04 '15 at 12:02
  • @krish Do you mean dropping OleDbCommandBuilder and creating update command by myself ("Update Tracks Set ...")? – kdg82 Jun 04 '15 at 18:02
  • yeah execute a string SQL command to the database and see if that works. – Krish Jun 05 '15 at 07:50
  • kdg82 - did you try my approach? – hynsey Jun 05 '15 at 08:37
  • @hynsey - no, not yet. Are you talking about using "using" (no pun intended) keyword and a new instance of OleDbDataAdapter each time I need to access database? – kdg82 Jun 05 '15 at 19:39
  • @krish hynsey - I'll try what you suggested and let know about the results in my original post. Thank you! – kdg82 Jun 05 '15 at 19:41
  • Yes, my comment was not about the `using` statements, but rather the method to run the Update, by replacing this line in your code, with mine above: `adTracks.Update(dt);` – hynsey Jun 08 '15 at 10:54
  • @krish hynsey - I updated original post with more info. – kdg82 Jun 12 '15 at 20:22

3 Answers3

0

Replace your OnLoad method with this:

    private void OnLoad(object sender, EventArgs e)
    {
        dbConn.ConnectionString = Properties.Settings.Default.dbConnectionString;

    try
    {
        dbConn.Open();
        adTracks = new OleDbDataAdapter("Select * from Tracks", dbConn));
        adTracks.Fill(dataset,"Tracks");    
    }
    catch (Exception err)
    {
        MessageBox.Show(err.Message);
        return;
    }
    finally
    {
        dbConn.Close();
    }

    cboOriginal.DataSource = dataset.Tables["Tracks"];
    cboOriginal.DisplayMember = "FullTitle";
    cboOriginal.ValueMember = "ID";
    cboOriginal.SelectedIndex = -1;

    adTracks.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);
}

And then this code for updating the dataset: (Note I corrected an incorrect reference to adTracks that is present in my comment above.)

using (OleDbDataAdapter da = new OleDbDataAdapter ("Select * from Tracks", dbConn)) 
{
OracleCommandBuilder cb = new OracleCommandBuilder(da); 
da.Update(dataset , "Tracks"); 
}
hynsey
  • 135
  • 1
  • 6
  • Sorry, still the same results. I also replaced `adTracks.Update(dt);` on step 2 with the snippet above: `using (OleDbDataAdapter da = new OleDbDataAdapter ("Select * from Tracks", dbConn)) { OracleCommandBuilder cb = new OracleCommandBuilder(da); da.Update(dataset , "Tracks"); }` and that didn't help either. – kdg82 Jul 05 '15 at 20:34
  • Btw, using your code for `OnLoad` method as it is, gave an error on step 2 on line `adTracks.Update(dt);`: _"Update requires a valid InsertCommand when passed DataRow collection with new rows"_. So I had to add this line in OnLoad after finally clause: `cmb1.DataAdapter = adTracks;`. Then I updated code on step 2 as described in previous comment - still the same result. – kdg82 Jul 05 '15 at 20:42
  • I don't see any reference to `cmb1` anywhere in your code above? What is this? – hynsey Jul 12 '15 at 10:02
  • Sorry, it should be: `cmb.DataAdapter = adTracks;` – kdg82 Jul 13 '15 at 10:52
  • You stated in your comment "Btw, using your code for OnLoad method as it is, gave an error on step 2 on line adTracks.Update(dt);". However, my solution does not reference `adTracks`. Remove the change you made, and use my method (detailed under the bold text in my solution) for updating the data adapter. Let me know how that goes. (Note my edit) – hynsey Jul 13 '15 at 16:47
  • yes, my final code on step 2 doesn't reference `adTracks` neither - I replaced it with the code that you can see in my 1st comment to your answer-post. In case there's some misunderstanding, I've updated my original post and added code that I used to check your solution on all 3 steps. The results are still exactly the same as with the original code. I think the key to the problem is in command generated by OleDbCommandBuilder or in the .mdb file itself, or both. As you could see, it works if I use SQL command created by myself. – kdg82 Jul 27 '15 at 09:04
0

Had same issue with concurrency. Nothing worked. 2 days later I came across this article on Ms Access and datatype decimal. This article states that one should avoid using this datatype.

So I redesigned the Table using datatype Double. Saving it with a new name. Copied the data and finally renamed the new table with the old name.

In Visual Studio I deleted the Dataset.xsd and added a new Dataset

Success at last.

Please read this: https://www.fmsinc.com/microsoftaccess/database-design/decimal_data_type/index.htm

AnthonyG
  • 1
0

I experienced this problem managing an Access table with values copied-and-pasted from Excel. For some reasons, it seems that the DateTime fields copied from Excel may have a precision that Access cannot handle well.

I experienced the error updating another field of the record, via OleDbDataAdapter.Update(DataTable).

After reading the @AnthonyG's answer, I resolved updating the DateTime fields directly in the original Access table using an Access query like this:

UPDATE [MYTABLE] SET [MYDATETIMEFIELD] = ROUND([MYDATETIMEFIELD],5)

I checked that 5 is the maximum precision that works.

This problem can be checked at runtime catching the DBConcurrencyException, but at that time it cannot be resolved rounding the DateTime values of the record.

I had to save the record without using the OleDbDataAdapter, but creating manually the UPDATE SQL statement and using OleDbCommand.ExecuteNonQuery() with OleDbParameter collection, rewriting all the values including the DateTime fields, that in this case are written correctly even without apply any rounding.

Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
Iacopo Vettori
  • 1
  • 3