none
Update method not calling InsertCommand code RRS feed

  • Question

  • Hi All,

    I am a complete beginner to C#.  I am learning how to write a webservice which does the following:

    application
    1) a datagrid that links to my backend database (BACKOFFICE) and allows me to add/edit/delete records
    2) a timer which onTick gets all the updated records and sends them as a dataset

    webservice
    3) recieves dataset and updates webservice database (W_BACKOFFICE) based on whether records have been updated/inserted via dataAdapter.Update(dataset)

    I have got numbers 1) and 2) to work (just about!) however when the webservice receives the dataset it only works if records have been updated. If records have been inserted it throws an exception:

    System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

    I am wanting to learn so any help would be really appreciated.  The method in the webservice is pasted below:

    [WebMethod]
        public void SendData(DataSet ds)
        {
            SqlConnection cn_web = new SqlConnection("user id=sa;" +
                                           "password=xxx;server=xxx;" +
                                           "Trusted_Connection=yes;" +
                                           "database=W_BACKOFFICE;" +
                                           "connection timeout=30");
            // opening connection
            cn_web.Open();

            // ------------------------------  Create the SelectCommand. ---------------------------------- 
            SqlCommand cmd;
            SqlDataAdapter dataAdapter = new SqlDataAdapter();

            //This is getting the current data from the webservice database W_BACKOFFICE just in case anyone has modified since....
            cmd = new SqlCommand("select * from tblCustomer", cn_web);
            dataAdapter.SelectCommand = cmd;


            // ------------------------------  Create the UpdateCommand. ----------------------------------
           
            cmd = new SqlCommand("UPDATE tblCustomer " +
                                 "SET customer_name = @customer_name, customer_age = @customer_age " +
                                 "WHERE customer_id = @customer_id", cn_web);

            // connects datasource ID field with dataset ID field
            SqlParameter custID = cmd.Parameters.Add("@customer_id", SqlDbType.UniqueIdentifier);
            custID.SourceColumn = "customer_id";
            // This ensures that the existing row in the data source(w_backoffice) is updated if the value of the identifying column(s)
            // has been changed in the modified DataRow (from datatable in dataset)
            custID.SourceVersion = DataRowVersion.Original;
           
            // 4th parameter is the source column from the datatable in dataset
            cmd.Parameters.Add("@customer_name", SqlDbType.VarChar, 50, "customer_name");
            cmd.Parameters.Add("@customer_age", SqlDbType.Int, 4, "customer_age");
            dataAdapter.UpdateCommand = cmd;

            // ------------------------------  Create the InsertCommand. ----------------------------------
            //dataAdapter.InsertCommand.Connection = (cn_web);

            cmd = new SqlCommand("INSERT INTO tblCustomer (customer_id, customer_name, customer_age) VALUES (@customer_id, @customer_name, @customer_age)");

            // connects datasource ID field with dataset ID field
            SqlParameter custInsertID = cmd.Parameters.Add("@customer_id", SqlDbType.UniqueIdentifier);
            custInsertID.SourceColumn = "customer_id";
            // This ensures that the existing row in the data source(w_backoffice) is updated if the value of the identifying column(s)
            // has been changed in the modified DataRow (from datatable in dataset)
            custInsertID.SourceVersion = DataRowVersion.Default;

            // 4th parameter is the source column from the datatable in dataset
            cmd.Parameters.Add("@customer_name", SqlDbType.VarChar, 50, "customer_name");
            cmd.Parameters.Add("@customer_age", SqlDbType.Int, 4, "customer_age");
            dataAdapter.InsertCommand = cmd;

            foreach (DataRow custRow in ds.Tables[0].Rows)
            {
                custRow.SetModified();
                dataAdapter.Update(ds);

            }

        }
    Wednesday, December 12, 2007 3:17 PM

Answers

  • I found out the reason for the error, thought id share in case others can benefit....

    In my code I had a commandBuilder which updated by backoffice database using a dataset and calling the Update().  I then sent the same dataset to my webservice however because I had already passed the dataset to the Update method all the rowstates seemed to have been set to unchanged, which is pretty logical i guess.

    So now im sending the dataset to my webservice and then passing to the Update method of my commadBuilder!

    Also in my webservice I dont have to do any of that setting modified business, Im simply calling the Update method and the relevant command (InsertCommand/UpdateCommand etc) is being called.

    Ta.
    Thursday, December 13, 2007 10:24 PM

All replies

  •  

    Code Block

            foreach (DataRow custRow in ds.Tables[0].Rows)
            {
                custRow.SetModified();
                dataAdapter.Update(ds);

            }

     

     

    Don't you think this part is a bit weird? I mean you are going row by row "setting modified", ok... But then everytime you set modified, you are calling the update dataset method... I am not sure why is the error occuring but this is one thing I think, in my opinion, that has to be changed... Set the modified, but then call dataadapter.update(ds) once outside the foreach loop. maybe...

    Wednesday, December 12, 2007 3:46 PM
  • Hi Can,

    yep i did think it was weird however currently im working of examples and basically getting my hands dirty so still learning this particular language/concept.....

    (By the way i did not even want to put in custRow.SetModified() however without it i found the Update(ds) does not call UpdateCommand)

    I tried your suggestion, no luck....

    Ta.
    Wednesday, December 12, 2007 3:54 PM
  • I am taking another guess... Smile

     

    Lets assume you get the dataset to your webservice, the inserted rows are set to Added state, when you call the SetModified, all the rows are set to "Modified" so even for the Added rows, the update statement is called, since the row is not in the database, it cannot UPDATE the row... the concurrency exception.

     

    Maybe.... you can add an "if" statement: if row.rowstate !=  datarowstate.Added row.setmodified()

    Wednesday, December 12, 2007 4:11 PM
  • Mmhhh that did make sense... but didnt work!
    Wednesday, December 12, 2007 4:37 PM
  • Can anyone point me in the right direction? I am looking at many examples myself but obviously im missing a fundamental point here as i thought all you do is manually create the Commands (InsertCommand/UpdateCommand etc) and then call Update(dataset) and it calls the right one.  That does not seem to be happening.

    Thanks.
    Thursday, December 13, 2007 8:39 AM
  • I found out the reason for the error, thought id share in case others can benefit....

    In my code I had a commandBuilder which updated by backoffice database using a dataset and calling the Update().  I then sent the same dataset to my webservice however because I had already passed the dataset to the Update method all the rowstates seemed to have been set to unchanged, which is pretty logical i guess.

    So now im sending the dataset to my webservice and then passing to the Update method of my commadBuilder!

    Also in my webservice I dont have to do any of that setting modified business, Im simply calling the Update method and the relevant command (InsertCommand/UpdateCommand etc) is being called.

    Ta.
    Thursday, December 13, 2007 10:24 PM