Edited dataset do not update the database and return a concurrency violation error (C#)

Question

I am synchronizing data from different tables (about 20 tables) from Sybase database which I access through ODBC and a SQL Server (my main database for this project).

When I launch the function to sync the data (only one way for the moment from Sybase to SQLServer), nothing happen and i get a concurrency violation error: "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."

The table on the SQL server has 96 columns and the one on Sybase only 94. I had two columns in the SQL Server table for synchronization purpose. The SQL server table is empty before the synchronization the first time but the sybase database has already 21 rows.

You may have a look to the code below to understand it better:

            try
            {
            //Connect to the erp Sybase database through ODBC
            OdbcConnection myConnection;
            //OdbcCommand myCommand;
            string MySQLRequest = "SELECT * FROM toto";
            string tableName = "toto";

            myConnection = new OdbcConnection("dsn=blabla;UID=bla;PWD=bla;");  
            //settings of the current database
            myConnection.Open();


            //get the data from MangoERP and store it into a dataset
            OdbcDataAdapter dasyb = new OdbcDataAdapter(MySQLRequest, myConnection);
            DataSet dserp = new DataSet(); //94columns
            dasyb.Fill(dserp, tableName);
            int dserpcount=dserp.Tables[0].Rows.Count;

            //get the data from MangoPMS and store it into a dataset
            SqlDataAdapter dasql = new SqlDataAdapter(MySQLRequest, mango_pms.Properties.Settings.Default.ConnectionStringSQLServer);
            DataSet dspms = new DataSet();//96columns
            dasql.Fill(dspms, tableName);
            int dspmscount = dspms.Tables[0].Rows.Count;

            //merge the dataset together
            if (dserpcount > 0)
            {
                dspms.Tables[0].Merge(dserp.Tables[0], false, MissingSchemaAction.Ignore); 
                dspmscount = dspms.Tables[0].Rows.Count;
            }
            string dtutcnow=DateTime.UtcNow.ToString();

            for (int i = 0; i < dspmscount; i++)
            {
                if (dspms.Tables[0].Rows[i]["erpsyncdate"].ToString() == "")
                {
                    dspms.Tables[0].Rows[i]["erpsyncdate"] = dtutcnow;
                }
                if (dspms.Tables[0].Rows[i]["erpsync"].ToString() == "")
                {
                    dspms.Tables[0].Rows[i]["erpsync"] = 2; //1=pms, 2=erp  
                }
            }

            //create an SqlCommandBuilder
            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dasql); 

            //save back to pms table
            dasql.Update(dspms, tableName);

            //Close the connections
            dasyb.Dispose();
            dasql.Dispose();
            myConnection.Close();

        }
        catch
        {
            MessageBox.Show("Can not synchronize from ERP database!");
        }

Do you have any idea why I can not save the data from the dataset dspms. I checked with the Data vizualiser there is 21 rows inside (same as in the sybase databse) after merging.

Cheers, LB

score 0 · Accepted Answer · answered Sep 13 '11 at 10:47

0

This seems to be because of a Default value of the DataSet... Look at Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

answered Sep 13 '11 at 10:47

Jan Christian Selke

370
3
10

I checked many posts online including this one. I did not really get it... :( My SQL server table is empty but I do not do direct SQL requests on the server but on the dataset. I was hoping during the dataadpater update that it will insert all the 21 lines into the database... – LudoB Sep 13 '11 at 10:50
I think the Optimistic Concurrency of the dataset is OK for me since I am updating in this case the whole row but I do not see why it did not insert the rows... Can it be because I have two Primary keys in the database? – LudoB Sep 13 '11 at 10:56
This might be the issue, if one of the key fields can be changed by the merge. – Jan Christian Selke Sep 13 '11 at 11:03
The merge changed all the dspms dataset since before it was empty. Do you think it is an issue? – LudoB Sep 13 '11 at 11:10
If one of the primary key fields is changed - definitely. – Jan Christian Selke Sep 13 '11 at 11:49
I have one dataset with 94 columns and 21 rows. I have another dataset with 96 col and 0 row. I merge the 2 datasets. I changed 2 columns in the second dataset (columns 95 and 96 for all the rows) and I update in the database the second dataset which have now 21 rows and 96 columns. I got the concurrency violation error and nothing is saved. You think it's because of the PK changed. What should I do to solve this issue? – LudoB Sep 13 '11 at 12:37
This could be *a* issue. There are lots of possible reasons: Editing an Auto Increment field, Updating a row of a table without Primary Key, changing a Primary Key field, entering NULL values in non-nullable fields... – Jan Christian Selke Sep 13 '11 at 12:56
OK, I will check all the issues but do you have any idea to insert the data from the dataset to the database? – LudoB Sep 13 '11 at 13:26
I read a bit more but it seems that the update will do automatically the insert/update/delete to fill in the database... So even if the database is empty it should create new rows in the database... – LudoB Sep 14 '11 at 02:11
When i use the SQL command builder, the insert/update/delete requests are null. That's probably why it does not work. I will write the requests in a similar way than explained in this website (http://geek-goddess-bonnie.blogspot.com/2009/09/dataaccess-part-i.html) and see if it's work. – LudoB Sep 15 '11 at 04:19
Actually after a while, I decided to write it another way. So now it's working! :) Thank you for your help. – LudoB Sep 27 '11 at 09:35

Edited dataset do not update the database and return a concurrency violation error (C#)

1 Answers1