Edit data in datasets

You edit data in data tables much like you edit the data in a table in any database. The process can include inserting, updating, and deleting records in the table. In a data-bound form, you can specify which fields are user-editable. In those cases, the data-binding infrastructure handles all the change tracking so that the changes can be sent back to the database later. If you programmatically make edits to data, and you intend to send those changes back to the database, you must use the objects and methods that do the change tracking for you.

In addition to changing the actual data, you can also query a DataTable to return specific rows of data. For example, you might query for individual rows, specific versions of rows (original and proposed), rows that have changed, or rows that have errors.

To edit rows in a dataset

To edit an existing row in a DataTable, you need to locate the DataRow you want to edit, and then assign the updated values to the desired columns.

If you don't know the index of the row you want to edit, use the FindBy method to search by the primary key:

NorthwindDataSet.CustomersRow customersRow = 
    northwindDataSet1.Customers.FindByCustomerID("ALFKI");

customersRow.CompanyName = "Updated Company Name";
customersRow.City = "Seattle";;

Dim customersRow As NorthwindDataSet.CustomersRow
customersRow = NorthwindDataSet1.Customers.FindByCustomerID("ALFKI")

customersRow.CompanyName = "Updated Company Name"
customersRow.City = "Seattle"

If you know the row index, you can access and edits rows as follows:

northwindDataSet1.Customers[4].CompanyName = "Updated Company Name";
northwindDataSet1.Customers[4].City = "Seattle";

NorthwindDataSet1.Customers(4).CompanyName = "Updated Company Name"
NorthwindDataSet1.Customers(4).City = "Seattle"

To insert new rows into a dataset

Applications that use data-bound controls typically add new records through the Add New button on a BindingNavigator Control.

To manually add new records to a dataset, create a new data row by calling the method on the DataTable. Then add the row to the DataRow collection (Rows) of the DataTable:

NorthwindDataSet.CustomersRow newCustomersRow = 
    northwindDataSet1.Customers.NewCustomersRow();

newCustomersRow.CustomerID = "ALFKI";
newCustomersRow.CompanyName = "Alfreds Futterkiste";

northwindDataSet1.Customers.Rows.Add(newCustomersRow);

Dim newCustomersRow As NorthwindDataSet.CustomersRow
newCustomersRow = NorthwindDataSet1.Customers.NewCustomersRow()

newCustomersRow.CustomerID = "ALFKI"
newCustomersRow.CompanyName = "Alfreds Futterkiste"

NorthwindDataSet1.Customers.Rows.Add(newCustomersRow)

In order to retain the information that the dataset needs to send updates to the data source, use the Delete method to remove rows in a data table. For example, if your application uses a TableAdapter (or DataAdapter), the TableAdapter's Update method deletes rows in the database that have a RowState of DataRowState.

If your application does not need to send updates back to a data source, then it's possible to remove records by directly accessing the data row collection (Remove).

To delete records from a data table

• Call the Delete method of a DataRow.

  This method doesn't physically remove the record. Instead, it marks the record for deletion.

  Note

  If you get the count property of a DataRowCollection, the resulting count includes records that have been marked for deletion. To get an accurate count of records that aren't marked for deletion, you can loop through the collection looking at the RowState property of each record. (Records marked for deletion have a RowState of DataRowState.) Alternatively, you can create a data view of a dataset that filters based on row state and get the count property from there.

  The following example shows how to call the Delete method to mark the first row in the Customers table as deleted:

  northwindDataSet1.Customers.Rows[0].Delete();
  

  NorthwindDataSet1.Customers.Rows(0).Delete()
  

Determine if there are changed rows

When changes are made to records in a dataset, information about those changes is stored until you commit them. You commit the changes when you call the AcceptChanges method of a dataset or data table, or when you call the Update method of a TableAdapter or data adapter.

Changes are tracked two ways in each data row:

• Each data row contains information related to it's RowState (for example, DataRowState, DataRowState, DataRowState, or DataRowState).

• Each changed data row contains multiple versions of that row (DataRowVersion), the original version (before changes), and the current version (after changes). During the period when a change is pending (the time when you can respond to the RowChanging event), a third version — the proposed version— is available as well.

  The HasChanges method of a dataset returns true if changes have been made in the dataset. After determining that changed rows exist, you can call the GetChanges method of a DataSet or DataTable to return a set of changed rows. For more information, see How to: Retrieve Changed Rows.

To determine if changes have been made to any rows

• Call the HasChanges method of a dataset to check for changed rows.

  The following example shows how to check the return value from the HasChanges method to detect whether there are any changed rows in a dataset named NorthwindDataset1:

  if (northwindDataSet1.HasChanges()) 
  {
      // Changed rows were detected, add appropriate code.
  }
  else
  {
      // No changed rows were detected, add appropriate code.
  }
  

  If NorthwindDataSet1.HasChanges() Then
  
      ' Changed rows were detected, add appropriate code.
  Else
      ' No changed rows were detected, add appropriate code. 
  End If
  

Determine the type of changes

You can also check to see what type of changes were made in a dataset by passing a value from the DataRowState enumeration to the HasChanges method.

To determine what type of changes have been made to a row

• Pass a DataRowState value to the HasChanges method.

  The following example shows how to check a dataset named NorthwindDataset1 to determine if any new rows have been added to it:

  if (northwindDataSet1.HasChanges(DataRowState.Added)) 
  {
      // New rows have been added to the dataset, add appropriate code.
  }
  else
  {
      // No new rows have been added to the dataset, add appropriate code.
  }
  

  If NorthwindDataSet1.HasChanges(DataRowState.Added) Then
  
      ' New rows have been added to the dataset, add appropriate code.
  Else
      ' No new rows have been added to the dataset, add appropriate code.
  End If
  

To locate rows that have errors

When working with individual columns and rows of data, you might encounter errors. You can check the HasErrors property to determine if errors exist in a DataSet, DataTable, or DataRow.

1. Check the HasErrors property to see if there are any errors in the dataset.

2. If the HasErrors property is true, iterate through the collections of tables, and then the through the rows, to find the row with the error.

   private void FindErrors() 
   {
       if (dataSet1.HasErrors)
       {
           foreach (DataTable table in dataSet1.Tables)
           {
               if (table.HasErrors)
               {
                   foreach (DataRow row in table.Rows)
                   {
                       if (row.HasErrors)
                       {
                           // Process error here.
                       }
                   }
               }
           }
       }
   }
   

   Private Sub FindErrors()
       Dim table As Data.DataTable
       Dim row As Data.DataRow
   
       If DataSet1.HasErrors Then
   
           For Each table In DataSet1.Tables
               If table.HasErrors Then
   
                   For Each row In table.Rows
                       If row.HasErrors Then
   
                           ' Process error here.
                       End If
                   Next
               End If
           Next
       End If
   End Sub