Regarding SqlRowUpdateEventHandler

4 Nov 2006 7:29 PM

Hyderabadi

Hi,

I am trying to perform concurrent updates of same record using two
connections and I want to display a message box when this happens.

I am using two connections : in One I hardcode the Update statement on
this record and In the other I use a form to modify the record values
and when save button is clicked the changes must be persisted.

I am using an SqlRowUpdateEventHandler delegate to this use, but its
not firing this event and the corresponding OnRowUpdate Method is also
not getting fired. Instead there is a new record getting added to it.

The code is as follows:

               conn = new SqlConnection(connStr);
                conn.Open();

                /// If user clicks on Save button then save the edited
record back to table
                /// through shippingDispatcher form.
                if (dr2 == DialogResult.OK)
                {
                    // Create another new connection
                    SqlConnection conn1 = new SqlConnection(connStr);
                    try
                    {
                        // Open the connection.
                        conn1.Open();

                        // Execute the update command by changing the
address field and update the orders table.
                     string upstr1 = "Update Orders set
Address='updated address' where RecordID = 1";
                        SqlCommand scmd1 = new SqlCommand(upstr1,
conn1);
                        scmd1.ExecuteNonQuery();
                    }
                    catch (SqlException se3)
                    {
         MessageBox.Show(se3.Message.ToString()+"SQL Exception while
saving edit form details");

                    }
                    finally
                    {   //Close the connection finally
                        conn1.Close();
                    }

                    //Copy the edited contents from edit form into the
datatable denoted by "Orders" in dataset.
                    dbtble = dset.Tables["Orders"];
                    dbtble.Rows[gridrow2]["Address"] =
f1.tbEdAddr.Text.Trim();
                    dbtble.Rows[gridrow2]["Driver"] =
f1.tbEdDriver.Text.Trim();
                    dbtble.Rows[gridrow2]["City"] =
f1.tbEdCity.Text.Trim();
                    dbtble.Rows[gridrow2]["State"] =
f1.tbEdState.Text.Trim();
                    dbtble.Rows[gridrow2]["TimeEntered"] = f1.TimeDate;

                    //Intialize the adapter with a Selected Command.
                    SqlCommandBuilder builder = new
SqlCommandBuilder(adapter);
                    adapter.SelectCommand = new SqlCommand("SELECT *
FROM Orders",conn);
                    //Get an update command for adapter
                    adapter.UpdateCommand = builder.GetUpdateCommand();

                    try
                    {
                        adapter.Update(dset, "Orders");
                    }
                    catch (SqlException se1)
                    {
                        MessageBox.Show(se1.Message + "Update Command
Exception");
                    }

                    // Persist the changes made to records into the
database

                    try
                    {
                        adapter.RowUpdated += new
SqlRowUpdatedEventHandler(OnRowUpdated);
                        adapter.Update(dset, "Orders");
                        adapter.RowUpdated -= new
SqlRowUpdatedEventHandler(OnRowUpdated);
                        populateDataGridView();
                        gridrow2 = 0;

                    }
                    catch (Exception sqe)
                    {
                        MessageBox.Show(sqe.Message, "Exception while
persisting data.");
                    }

                }
            }
        }

        protected void OnRowUpdated(object source,
SqlRowUpdatedEventArgs args)
        {
            if (args.RecordsAffected < 1)
            {
                MessageBox.Show("Error Updating the Record, Record
updated by other User. Plese try again after sometime.");
                args.Status = UpdateStatus.Continue;
            }
        }

4 Nov 2006 7:30 PM

Hyderabadi

Can some one Help me in this regard???