C# SQL Express 2010. Adding and updating a record in the same run.

Question

0.00/5 (No votes)

See more:

I'm a complete noob to working with databases so I assume my question is trivial. Still I wasn't able to find an answer googling "add and update". My problem is, as the title suggests, that I'm unable to update records which I add in the same run of the program.

Please observe the code:

private static System.Data.SqlClient.SqlDataAdapter da_small;
private static DataSet ds_small;

public static void add()
        {
            System.Data.SqlClient.SqlCommandBuilder cb_small;
            cb_small = new System.Data.SqlClient.SqlCommandBuilder(da_small);

            DataRow small_short = ds_small.Tables["small"].NewRow();

            small_short[1] = "just added";

            for (int i = 2; i < 36; i++)
                small_short[i] = i.ToString();

            ds_small.Tables["small"].Rows.Add(small_short);
            
            da_small.Update(ds_small, "small");
        }

        private static int i = 0;
        
        public static void update()
        {
            System.Data.SqlClient.SqlCommandBuilder cb_small;
            cb_small = new System.Data.SqlClient.SqlCommandBuilder(da_small);

            //DataRow[] drs = ds_small.Tables["small"].Select("PlayerID='" + "just added" + "'");

            System.Data.DataRow dr = ds_small.Tables["small"].Rows[i++];
            dr[2] = "changed";
            da_small.Update(ds_small, "small");
        }

When running this with 2 calls to add(), then 1 call to update() I receive the following run time exception when calling update():

"Concurrency violation: the UpdateCommand affected 0 of the expected 1 records"

When running this for the second time with call to add() and 3 calls to update() I'm getting this exception on the third call to update(), meaning that the records which were added on the previous run were updated successfully, while the newly added record couldn't be updated.

I'm assuming that the problem is that the adapter isn't aware of the added record yet, I'm just not familiar enough (or at all) with SQL server express to understand why or to know how to fix it.

Please help, thank you.

Posted 22-Feb-11 8:46am

AmitNHB

Updated 22-Feb-11 9:10am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2011-02-22T09:16:00

Hi,

This is usually happening because optimistic concurrency approach is used by the adapter. When it generates the update statement it includes all the fields from the row to the WHERE clause. So the update looks something like the following (if the table contains 3 columns):

SQL

UPDATE MyTable
SET    Field1 = bindedNewValue1, 
       Field2 = bindedNewValue2, 
       Field3 = bindedNewValue3
WHERE Field1 = bindedOldValue1
AND   Field2 = bindedOldValue2
AND   Field3 = bindedOldValue3

Now if the values in the database have changed in some way the update won't find any records to modify resulting to the error you received. The modification could have been done by your program, trigger etc.

Check the values from the datatable to see the original and the new versions. You can use for example DataTable's GetChanges method to get the previous situation. You can also hook up to the adapter's RowUpdating event to see more easily the information before the update is made.