14.6 Changing Database Records

So far, you've retrieved data from a database, but you haven't manipulated its records in any way. Using ADO.NET, it is of course possible to add a record, change an existing record, or delete a record altogether.

In a typical implementation, you might work your way through the following steps:

1. Fill the tables for your DataSet using a stored procedure or SQL.

2. Display the data in various DataTable objects within your DataSet by either binding to a control or looping through the rows in the tables.

3. Change data in individual DataTable objects by adding, modifying, or deleting DataRow objects.

4. Invoke the GetChanges( ) method to create a second DataSet that features only the changes to the data.

5. Check for errors in the second newly created DataSet by examining the HasErrors property. If there are errors, check the HasErrors property of each DataTable in the DataSet. If the table has errors, invoke the GetErrors( ) method of the DataTable and get back an array of DataRow objects with errors. On each row, you can examine the RowError property for specific information about the error, which you can then resolve.

6. Merge the second DataSet with the first.

7. Call the Update( ) method on the DataAdapter object and pass in the second (changed) DataSet.

8. Invoke the AcceptChanges( ) method on the DataSet, or invoke RejectChanges( ) to cancel the changes.

This process gives you very fine control over the update to your data as well as an opportunity to fix any data that would otherwise cause an error.

In the following example, you'll create a dialog box that displays the contents of the Customer table in Northwinds. The goal is to test updating a record, adding a new record, and deleting a record. As always, I'll keep the code as simple as possible, which means eliminating many of the error-checking and exception-handling routines you might expect in a production program.

Figure 14-7 shows the somewhat crude but useful form I've built to experiment with these features of ADO.NET.

Figure 14-7. The ADO update form

This form consists of a listbox (lbCustomers), a button for Update (btnUpdate), an associated text box (txtCustomerName), and a Delete button (btnDelete). There is also a set of eight text fields that are used in conjunction with the New button (btnNew). These text fields represent eight of the fields in the Customers table in the Northwind database. There is also a label (lblMessage) that you can use for writing messages to the user (it currently says Press New, Update, or Delete).

14.6.1 Accessing the Data

First, create the DataAdapter object and the DataSet as private member variables, along with the DataTable:

private SqlDataAdapter dataAdapter; private DataSet dataSet; private DataTable dataTable;

This enables you to refer to these objects from various member methods. Start by creating strings for the connection and the command that will get you the table you need:

string connectionString = "server=(local)\\NetSDK;" +      "Trusted_Connection=yes; database=northwind"; string commandString = "Select * from Customers";

These strings are passed as parameters to the SqlDataAdapter constructor:

dataAdapter =      new SqlDataAdapter(commandString, connectionString);

A DataAdapter may have four SQL commands associated with it. Right now, we have only one: dataAdapter.SelectCommand. The InitializeCommands( ) method creates the remaining three: InsertCommand, UpdateCommand, and DeleteCommand. InitializeCommands( ) uses the AddParams method to associate a column in each SQL command with the columns in the modified rows:

private void AddParams(SqlCommand cmd, params string[] cols) {   // Add each parameter   foreach (String column in cols) {     cmd.Parameters.Add(       "@" + column, SqlDbType.Char, 0, column);   } }

InitializeCommands( ) creates each SQL command in turn, using placeholders that correspond to the column argument passed to AddParams( ):

private void InitializeCommands( ) {    // Reuse the SelectCommand's Connection.    SqlConnection connection =      (SqlConnection) dataAdapter.SelectCommand.Connection;    // Create an explicit, reusable insert command    dataAdapter.InsertCommand = connection.CreateCommand( );    dataAdapter.InsertCommand.CommandText =    "Insert into customers " +       "(CustomerId, CompanyName, ContactName, ContactTitle, " +       " Address, City, PostalCode, Phone) " +       "values(@CustomerId, @CompanyName, @ContactName, " +       "   @ContactTitle, @Address, @City, @PostalCode, @Phone)";    AddParams(dataAdapter.InsertCommand,      "CustomerId", "CompanyName", "ContactName", "ContactTitle",      "Address", "City", "PostalCode", "Phone");    // Create an explicit update command    dataAdapter.UpdateCommand = connection.CreateCommand( );    dataAdapter.UpdateCommand.CommandText = "update Customers " +      "set CompanyName = @CompanyName where CustomerID = @CustomerId";    AddParams(dataAdapter.UpdateCommand, "CompanyName", "CustomerID");    // Create an explicit delete command    dataAdapter.DeleteCommand = connection.CreateCommand( );    dataAdapter.DeleteCommand.CommandText =      "delete from customers where customerID = @CustomerId";    AddParams(dataAdapter.DeleteCommand, "CustomerID"); }

The DataAdapter uses InsertCommand, UpdateCommand, and DeleteCommand to modify the table when you invoke Update( ).

Back in the constructor, you can now create the DataSet and fill it with the SqlDataAdapter object you've just created:

dataSet = new DataSet( ); dataAdapter.Fill(DataSet,"Customers");

Display the table contents by calling the PopulateLB( ) method, which is a private method that fills the listbox from the contents of the single table in the DataSet:

dataTable = dataSet.Tables[0]; lbCustomers.Items.Clear( ); foreach (DataRow dataRow in dataTable.Rows) {     lbCustomers.Items.Add(         dataRow["CompanyName"] +          " (" + dataRow["ContactName"] + ")" ); }

14.6.2 Updating a Record

The form is now displayed, and you're ready to update a record. Highlight a record and fill in a new customer name in the topmost text field. When you press Update, read the resulting name and put it into the chosen record.

The first task is to get the specific row the user wants to change:

DataRow targetRow  = dataTable.Rows[lbCustomers.SelectedIndex];

Declare a new object of type DataRow and initialize it with a reference to the specific row in the DataTable's Rows collection that corresponds to the selected item in the listbox. Remember that DataTable was declared as a member variable and initialized in the PopulateLB( ) method shown in the previous section.

You can now display the name of the company you're going to update:

lblMessage.Text = "Updating " +  targetRow["CompanyName"]; Application.DoEvents( );

The call to the static method DoEvents( ) of the Application class causes the application to process Windows messages and paint the screen with them. If you were to leave this line out, the current thread would dominate the processor and the messages would not be printed until the button handler completes its work.

Call BeginEdit( ) on the DataRow to put the row into editing mode. This suspends events on the row so that you could, if you chose, edit a number of rows at once without triggering validation rules (there are no validation rules in this example). It is good form to bracket changes on DataRows with calls to BeginEdit( ) and EndEdit( ):

targetRow.BeginEdit( );  targetRow["CompanyName"] = txtCustomerName.Text; targetRow.EndEdit( );

The actual edit is to the column CompanyName within the targetRow object, which is set to the text value of the text control txtCustomerName. The net effect is that the CompanyName field in the row is set to whatever the user put into that text box.

Notice that the column you want is indexed within the row by the name of that column. In this case, the name will match the name that is used in the database, but this is not required. When you created the DataSet, you could have used the TableMappings( ) method to change the names of the columns.

Having edited the column, you are ready to check to make sure there are no errors. First, extract all the changes made to the DataSet (in this case, there will be only one change) using the GetChanges( ) method, passing in a DataRowState enumeration to indicate that you want only those rows that have been modified. GetChanges( ) returns a new DataSet object:

DataSet dataSetChanged =      dataSet.GetChanges(DataRowState.Modified);

Now you can check for errors. To simplify the code, I've included a flag to indicate that all is OK. If you find any errors, rather than trying to fix them, just set the flag to false and don't make the updates:

bool okayFlag = true; if (dataSetChanged.HasErrors) {    okayFlag = false;    string msg = "Error in row with customer ID ";     foreach (DataTable theTable in dataSetChanged.Tables)    {       if (theTable.HasErrors)       {          DataRow[] errorRows = theTable.GetErrors( );          foreach (DataRow theRow in errorRows)          {             msg = msg + theRow["CustomerID"];          }       }    }    lblMessage.Text = msg; }

First test to see whether the new data record set has any errors by checking the HasErrors property. If HasErrors is true, there are errors; set the Boolean okayFlag to false, and then go on to discover where the error lies. To do so, iterate through all the tables in the new database (in this case, there is only one); if a table has errors, you'll get an array of all the rows in that table with errors (shown here as the errorRows array).

Then iterate through the array of rows with errors, handling each in turn. In this case, you just update the message on the dialog box. However, in a production environment you might interact with the user to fix the problem.

If the okayFlag is still true after testing HasErrors, there were no errors and you are ready to update the database:

if (okayFlag) {     dataAdapter.Update(dataSetChanged,"Customers");

This causes the DataAdapter object to create the necessary command text to update the database. Next, update the message:

lblMessage.Text = "Updated " +  targetRow["CompanyName"]; Application.DoEvents( );

You now must tell the DataSet to accept the changes and then repopulate the listbox from the DataSet:

dataSet.AcceptChanges( ); PopulateLB( );

If okayFlag is false, there are errors; in this example, we'd just reject the changes:

else      dataSet.RejectChanges( );

14.6.3 Deleting a Record

The code for handling the Delete button is even simpler. First, get the target row:

DataRow targetRow  =    dataTable.Rows[lbCustomers.SelectedIndex];

and form the delete message:

string msg = targetRow["CompanyName"] + " deleted. ";

You don't want to show the message until the row is deleted, but you need to get it now because after you delete the row it will be too late!

You're now ready to mark the row for deletion:

targetRow.Delete( );

Calling AcceptChanges( ) on the DataSet causes AcceptChanges( ) to be called on each table within the DataSet. This in turn causes AcceptChanges( ) to be called on each row in those tables. Thus the one call to dataSet.AcceptChanges( ) cascades down through all the contained tables and rows.

Next, you need to call Update( ) and AcceptChanges( ), and then refresh the listbox. However, if this operation fails, the row will still be marked for deletion. If you then try to issue a legitimate command, such as an insertion, update, or another deletion, the DataAdapter will try to commit the erroneous deletion again, and the whole batch will fail because of that delete. In order to avert this situation, wrap the remaining operations in a try block and call RejectChanges( ) if they fail:

// update the database try {   dataAdapter.Update(dataSet,"Customers");   dataSet.AcceptChanges( );   // repopulate the list box without the deleted record   PopulateLB( );   // inform the user   lblMessage.Text = msg;   Application.DoEvents( ); } catch (SqlException ex) {   dataSet.RejectChanges( );   MessageBox.Show(ex.Message); }

Deleting records from the Customers database might cause an exception if the record deleted is constrained by database integrity rules. For example, if a customer has orders in the Orders table, you cannot delete the customer until you delete the orders. To solve this, the following example will create new Customer records that you can then delete at will.

14.6.4 Creating New Records

To create a new record, the user will fill in the fields and press the New button. This will fire the btnNew_Click event, which is tied to the btnNew_Click event handling method:

btnNew.Click += new System.EventHandler (this.btnNew_Click);

In the event handler, call DataTable.NewRow( ), which asks the table for a new DataRow object:

DataRow newRow = dataTable.NewRow( );

This is very elegant because the new row that the DataTable produces has all the necessary DataColumns for this table. You can just fill in the columns you care about, taking the text from the user interface:

newRow["CustomerID"] = txtCompanyID.Text; newRow["CompanyName"] = txtCompanyName.Text; newRow["ContactName"] = txtContactName.Text; newRow["ContactTitle"] = txtContactTitle.Text; newRow["Address"] = txtAddress.Text; newRow["City"] = txtCity.Text; newRow["PostalCode"] = txtZip.Text; newRow["Phone"] = txtPhone.Text;

Now that the row is fully populated, just add it back to the table:

dataTable.Rows.Add(newRow);

The table resides within the DataSet, so all you have to do is tell the DataAdapter object to update the database with the DataSet and accept the changes:

dataAdapter.Update(dataSet,"Customers"); dataSet.AcceptChanges( );

Next, update the user interface:

lblMessage.Text = "Updated!"; Application.DoEvents( );

You can now repopulate the listbox with your new added row and clear the text fields so that you're ready for another new record:

PopulateLB( ); ClearFields( );

ClearFields( ) is a private method that simply sets all the text fields to empty strings. That method and the entire program are shown in Example 14-6.

Example 14-6. Updating, deleting, and adding records

using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; namespace ProgrammingCSharpWindows.Form {    public class ADOForm1 : System.Windows.Forms.Form    {       private System.ComponentModel.Container components;       private System.Windows.Forms.Label label9;       private System.Windows.Forms.TextBox txtPhone;       private System.Windows.Forms.Label label8;       private System.Windows.Forms.TextBox txtContactTitle;       private System.Windows.Forms.Label label7;       private System.Windows.Forms.TextBox txtZip;       private System.Windows.Forms.Label label6;       private System.Windows.Forms.TextBox txtCity;       private System.Windows.Forms.Label label5;       private System.Windows.Forms.TextBox txtAddress;       private System.Windows.Forms.Label label4;       private System.Windows.Forms.TextBox txtContactName;       private System.Windows.Forms.Label label3;       private System.Windows.Forms.TextBox txtCompanyName;       private System.Windows.Forms.Label label2;       private System.Windows.Forms.TextBox txtCompanyID;       private System.Windows.Forms.Label label1;       private System.Windows.Forms.Button btnNew;       private System.Windows.Forms.TextBox txtCustomerName;       private System.Windows.Forms.Button btnUpdate;       private System.Windows.Forms.Label lblMessage;       private System.Windows.Forms.Button btnDelete;       private System.Windows.Forms.ListBox lbCustomers;       // the DataSet, DataAdapter, and DataTable are members        // so that we can access them from any member method.       private SqlDataAdapter dataAdapter;       private DataSet dataSet;       private DataTable dataTable;       public ADOForm1( )       {          InitializeComponent( );          string connectionString = "server=(local)\\NetSDK;" +            "Trusted_Connection=yes; database=northwind";          string commandString = "Select * from Customers";          dataAdapter =              new SqlDataAdapter(commandString, connectionString);          InitializeCommands( );          dataSet = new DataSet( );          dataAdapter.Fill(dataSet,"Customers");          PopulateLB( );       }       private void AddParms(SqlCommand cmd, params string[] cols) {         // Add each parameter         foreach (String column in cols) {           cmd.Parameters.Add(             "@" + column, SqlDbType.Char, 0, column);         }       }       private void InitializeCommands( )       {          // Reuse the SelectCommand's Connection.          SqlConnection connection =             (SqlConnection) dataAdapter.SelectCommand.Connection;          // Create an explicit, reusable insert command          dataAdapter.InsertCommand = connection.CreateCommand( );          dataAdapter.InsertCommand.CommandText =           "Insert into customers " +             "(CustomerId, CompanyName, ContactName, ContactTitle, " +             " Address, City, PostalCode, Phone) " +             "values(@CustomerId, @CompanyName, @ContactName, " +             "   @ContactTitle, @Address, @City, @PostalCode, @Phone)";          AddParms(dataAdapter.InsertCommand,             "CustomerId", "CompanyName", "ContactName", "ContactTitle",             "Address", "City", "PostalCode", "Phone");          // Create an explicit update command          dataAdapter.UpdateCommand = connection.CreateCommand( );          dataAdapter.UpdateCommand.CommandText = "update Customers " +            "set CompanyName = @CompanyName where CustomerID = @CustomerId";          AddParms(dataAdapter.UpdateCommand, "CompanyName", "CustomerID");          // Create an explicit delete command          dataAdapter.DeleteCommand = connection.CreateCommand( );          dataAdapter.DeleteCommand.CommandText =             "delete from customers where customerID = @CustomerId";          AddParms(dataAdapter.DeleteCommand, "CustomerID");       }       // fill the list box with columns from the Customers table       private void PopulateLB( )       {          dataTable = dataSet.Tables[0];          lbCustomers.Items.Clear( );          foreach (DataRow dataRow in dataTable.Rows)          {             lbCustomers.Items.Add(                dataRow["CompanyName"] + " (" +                 dataRow["ContactName"] + ")" );          }                    }       protected override void Dispose(bool disposing)       {          if (disposing)          {              if (components == null)              {                  components.Dispose( );              }          }          base.Dispose(disposing);       }       private void InitializeComponent( )       {          this.components = new System.ComponentModel.Container ( );          this.txtCustomerName = new System.Windows.Forms.TextBox ( );          this.txtCity = new System.Windows.Forms.TextBox ( );          this.txtCompanyID = new System.Windows.Forms.TextBox ( );          this.lblMessage = new System.Windows.Forms.Label ( );          this.btnUpdate = new System.Windows.Forms.Button ( );          this.txtContactName = new System.Windows.Forms.TextBox ( );          this.txtZip = new System.Windows.Forms.TextBox ( );          this.btnDelete = new System.Windows.Forms.Button ( );          this.txtContactTitle = new System.Windows.Forms.TextBox ( );          this.txtAddress = new System.Windows.Forms.TextBox ( );          this.txtCompanyName = new System.Windows.Forms.TextBox ( );          this.label5 = new System.Windows.Forms.Label ( );          this.label6 = new System.Windows.Forms.Label ( );          this.label7 = new System.Windows.Forms.Label ( );          this.label8 = new System.Windows.Forms.Label ( );          this.label9 = new System.Windows.Forms.Label ( );          this.label4 = new System.Windows.Forms.Label ( );          this.lbCustomers = new System.Windows.Forms.ListBox ( );          this.txtPhone = new System.Windows.Forms.TextBox ( );          this.btnNew = new System.Windows.Forms.Button ( );          this.label1 = new System.Windows.Forms.Label ( );          this.label2 = new System.Windows.Forms.Label ( );          this.label3 = new System.Windows.Forms.Label ( );          //@this.TrayHeight = 0;          //@this.TrayLargeIcon = false;          //@this.TrayAutoArrange = true;          txtCustomerName.Location = new System.Drawing.Point (256, 120);          txtCustomerName.TabIndex = 4;          txtCustomerName.Size = new System.Drawing.Size (160, 20);          txtCity.Location = new System.Drawing.Point (384, 245);          txtCity.TabIndex = 15;          txtCity.Size = new System.Drawing.Size (160, 20);          txtCompanyID.Location = new System.Drawing.Point (136, 216);          txtCompanyID.TabIndex = 7;          txtCompanyID.Size = new System.Drawing.Size (160, 20);          lblMessage.Location = new System.Drawing.Point (32, 368);          lblMessage.Text = "Press New, Update or Delete";          lblMessage.Size = new System.Drawing.Size (416, 48);          lblMessage.TabIndex = 1;          btnUpdate.Location = new System.Drawing.Point (32, 120);          btnUpdate.Size = new System.Drawing.Size (75, 23);          btnUpdate.TabIndex = 0;          btnUpdate.Text = "Update";          btnUpdate.Click +=              new System.EventHandler (this.btnUpdate_Click);          txtContactName.Location = new System.Drawing.Point (136, 274);          txtContactName.TabIndex = 11;          txtContactName.Size = new System.Drawing.Size (160, 20);          txtZip.Location = new System.Drawing.Point (384, 274);          txtZip.TabIndex = 17;          txtZip.Size = new System.Drawing.Size (160, 20);          btnDelete.Location = new System.Drawing.Point (472, 120);          btnDelete.Size = new System.Drawing.Size (75, 23);          btnDelete.TabIndex = 2;          btnDelete.Text = "Delete";          btnDelete.Click +=              new System.EventHandler (this.btnDelete_Click);          txtContactTitle.Location = new System.Drawing.Point (136, 303);          txtContactTitle.TabIndex = 12;          txtContactTitle.Size = new System.Drawing.Size (160, 20);          txtAddress.Location = new System.Drawing.Point (384, 216);          txtAddress.TabIndex = 13;          txtAddress.Size = new System.Drawing.Size (160, 20);          txtCompanyName.Location = new System.Drawing.Point (136, 245);          txtCompanyName.TabIndex = 9;          txtCompanyName.Size = new System.Drawing.Size (160, 20);          label5.Location = new System.Drawing.Point (320, 252);          label5.Text = "City";          label5.Size = new System.Drawing.Size (48, 16);          label5.TabIndex = 14;          label6.Location = new System.Drawing.Point (320, 284);          label6.Text = "Zip";          label6.Size = new System.Drawing.Size (40, 16);          label6.TabIndex = 16;          label7.Location = new System.Drawing.Point (40, 312);          label7.Text = "Contact Title";          label7.Size = new System.Drawing.Size (88, 16);          label7.TabIndex = 28;          label8.Location = new System.Drawing.Point (320, 312);          label8.Text = "Phone";          label8.Size = new System.Drawing.Size (56, 16);          label8.TabIndex = 20;          label9.Location = new System.Drawing.Point (120, 120);          label9.Text = "New Customer Name:";          label9.Size = new System.Drawing.Size (120, 24);          label9.TabIndex = 22;          label4.Location = new System.Drawing.Point (320, 224);          label4.Text = "Address";          label4.Size = new System.Drawing.Size (56, 16);          label4.TabIndex = 26;          lbCustomers.Location = new System.Drawing.Point (32, 16);          lbCustomers.Size = new System.Drawing.Size (512, 95);          lbCustomers.TabIndex = 3;          txtPhone.Location = new System.Drawing.Point (384, 303);          txtPhone.TabIndex = 18;          txtPhone.Size = new System.Drawing.Size (160, 20);          btnNew.Location = new System.Drawing.Point (472, 336);          btnNew.Size = new System.Drawing.Size (75, 23);          btnNew.TabIndex = 25;          btnNew.Text = "New";          btnNew.Click += new System.EventHandler (this.btnNew_Click);          label1.Location = new System.Drawing.Point (40, 224);          label1.Text = "Company ID";          label1.Size = new System.Drawing.Size (88, 16);          label1.TabIndex = 6;          label2.Location = new System.Drawing.Point (40, 252);          label2.Text = "Company Name";          label2.Size = new System.Drawing.Size (88, 16);          label2.TabIndex = 8;          label3.Location = new System.Drawing.Point (40, 284);          label3.Text = "Contact Name";          label3.Size = new System.Drawing.Size (88, 16);          label3.TabIndex = 10;          this.Text = "Customers Update Form";          this.AutoScaleBaseSize = new System.Drawing.Size (5, 13);          this.ClientSize = new System.Drawing.Size (584, 421);          this.Controls.Add (this.label9);          this.Controls.Add (this.txtPhone);          this.Controls.Add (this.label8);          this.Controls.Add (this.txtContactTitle);          this.Controls.Add (this.label7);          this.Controls.Add (this.txtZip);          this.Controls.Add (this.label6);          this.Controls.Add (this.txtCity);          this.Controls.Add (this.label5);          this.Controls.Add (this.txtAddress);          this.Controls.Add (this.label4);          this.Controls.Add (this.txtContactName);          this.Controls.Add (this.label3);          this.Controls.Add (this.txtCompanyName);          this.Controls.Add (this.label2);          this.Controls.Add (this.txtCompanyID);          this.Controls.Add (this.label1);          this.Controls.Add (this.btnNew);          this.Controls.Add (this.txtCustomerName);          this.Controls.Add (this.btnUpdate);          this.Controls.Add (this.lblMessage);          this.Controls.Add (this.btnDelete);          this.Controls.Add (this.lbCustomers);       }       // handle the new button click       protected void btnNew_Click (object sender, System.EventArgs e)       {          // create a new row, populate it          DataRow newRow = dataTable.NewRow( );          newRow["CustomerID"]   = txtCompanyID.Text;          newRow["CompanyName"]  = txtCompanyName.Text;          newRow["ContactName"]  = txtContactName.Text;          newRow["ContactTitle"] = txtContactTitle.Text;          newRow["Address"]      = txtAddress.Text;          newRow["City"]         = txtCity.Text;          newRow["PostalCode"]   = txtZip.Text;          newRow["Phone"]        = txtPhone.Text;          // add the new row to the table          dataTable.Rows.Add(newRow);          // update the database          try           {            dataAdapter.Update(dataSet,"Customers");             dataSet.AcceptChanges( );            // inform the user             lblMessage.Text = "Updated!";            Application.DoEvents( );            // repopulate the list box             PopulateLB( );            // clear all the text fields            ClearFields( );          }           catch (SqlException ex)           {            dataSet.RejectChanges( );            MessageBox.Show(ex.Message);          }       }       // set all the text fields to empty strings       private void ClearFields( )       {          txtCompanyID.Text = "";          txtCompanyName.Text = "";          txtContactName.Text = "";          txtContactTitle.Text = "";          txtAddress.Text = "";          txtCity.Text = "";          txtZip.Text = "";          txtPhone.Text = "";       }       // handle the update button click       protected void btnUpdate_Click (object sender, System.EventArgs e)       {          // get the selected row          DataRow targetRow  = dataTable.Rows[lbCustomers.SelectedIndex];          // inform the user          lblMessage.Text = "Updating " +  targetRow["CompanyName"];          Application.DoEvents( );          // edit the row          targetRow.BeginEdit( );           targetRow["CompanyName"] = txtCustomerName.Text;          targetRow.EndEdit( );          // get each row that changed          DataSet dataSetChanged =              dataSet.GetChanges(DataRowState.Modified);          // test to make sure all the changed rows are without errors          bool okayFlag = true;          if (dataSetChanged.HasErrors)          {             okayFlag = false;             string msg = "Error in row with customer ID ";              // examine each table in the changed DataSet             foreach (DataTable theTable in dataSetChanged.Tables)             {                // if any table has errors, find out which rows                if (theTable.HasErrors)                {                   // get the rows with errors                   DataRow[] errorRows = theTable.GetErrors( );                   // iterate through the errors and correct                   // (in our case, just identify)                   foreach (DataRow theRow in errorRows)                   {                      msg = msg + theRow["CustomerID"];                   }                }             }             lblMessage.Text = msg;          }          // if we have no errors          if (okayFlag)          {             // update the database             dataAdapter.Update(dataSetChanged,"Customers");              // inform the user             lblMessage.Text = "Updated " +  targetRow["CompanyName"];             Application.DoEvents( );             // accept the changes and repopulate the list box             dataSet.AcceptChanges( );             PopulateLB( );          }          else  // if we had errors, reject the changes             dataSet.RejectChanges( );       }       // handle the delete button click       protected void btnDelete_Click (object sender, System.EventArgs e)       {          // get the selected row          DataRow targetRow  = dataTable.Rows[lbCustomers.SelectedIndex];          // prepare message for user          string msg = targetRow["CompanyName"] + " deleted. ";          // delete the selected row          targetRow.Delete( );          // update the database          try           {            dataAdapter.Update(dataSet,"Customers");             dataSet.AcceptChanges( );            // repopulate the list box without the deleted record            PopulateLB( );            // inform the user            lblMessage.Text = msg;            Application.DoEvents( );          }           catch (SqlException ex)           {            dataSet.RejectChanges( );            MessageBox.Show(ex.Message);          }       }       public static void Main(string[] args)        {          Application.Run(new ADOForm1( ));       }    } }

Figure 14-8 shows the filled-out form just before pressing the New button, and Figure 14-9 shows the form immediately after adding the new record.

Figure 14-8. Getting ready to add a new record

Figure 14-9. After adding the new record

Note that the new record is appended to the end of the list and the text fields are cleared.