How to: Update Records in a Database

How to: Update Records in a Databas...

Switch on low bandwidth view

Collapse All

Language Filter

This page is specific to

Microsoft Visual Studio 2008/.NET Framework 3.5

Other versions are also available for the following:

Data Access in Client and Middle-Tier Programming

How to: Update Records in a Database

You can use the TableAdapter.Update method to update (edit) records in a database. The TableAdapter.Update method provides several overloads that perform different operations depending on the parameters passed in. It is important to understand the results of calling these different method signatures.

Note:
If your application does not use TableAdapters, then you can use command objects to update records in your database (for example, ExecuteNonQuery). For more information on updating data with command objects, see "Update Records using Command Objects" below.

The following table describes the behavior of the various TableAdapter.Update methods:

Method	Description
TableAdapter.Update(DataTable)	Attempts to save all changes in the DataTable to the database. (This includes removing any rows deleted from the table, adding rows inserted to the table, and updating any rows in the table that have changed.)
TableAdapter.Update(DataSet)	Although the parameter takes a dataset, the TableAdapter attempts to save all changes in the TableAdapter's associated DataTable to the database. (This includes removing any rows deleted from the table, adding rows inserted in the table, and updating any rows in the table that have changed.) Note: A TableAdapter's associated DataTable is the DataTable created when the TableAdapter was originally configured.
TableAdapter.Update(DataRow)	Attempts to save changes in the indicated DataRow to the database.
TableAdapter.Update(DataRows())	Attempts to save changes in any row in the array of DataRows to the database.
TableAdapter.Update("new column values", "original column values")	Attempts to save changes in a single row that is identified by the original column values.

You typically use the TableAdapter.Update method that takes a DataSet, DataTable, or DataRow(s) when your application uses datasets exclusively to store data.

You typically use the TableAdapter.Update method that takes column values when your application uses objects to store data.

If your TableAdapter does not have an Update method that takes column values, then it means that either the TableAdapter is configured to use stored procedures or its GenerateDBDirectMethods property is set to false. Try setting the TableAdapter's GenerateDBDirectMethods property to true from within the Dataset Designer and then save the dataset to regenerate the TableAdapter. If the TableAdapter still does not have an Update method that takes column values, then the table probably does not provide enough schema information to distinguish between individual rows (for example, no primary key is set on the table).

Update Existing Records Using TableAdapters

TableAdapters provide different ways to update records in a database depending on the requirements of your application.

If your application uses datasets to store data, then you can simply update the records in the desired DataTable and then call the TableAdapter.Update method and pass in either the DataSet, DataTable, DataRow, or array of DataRows. The table above describes the different Update methods.

To update records in a database with the TableAdapter.Update method that takes DataSet, DataTable, DataRow, or DataRows()

Edit records in the desired DataTable by directly editing the DataRow in the DataTable. For more information, see How to: Edit Rows in a DataTable.

After the rows are edited in the DataTable, call the TableAdapter.Update method. You can control the amount of data to update by passing in either an entire DataSet, a DataTable, an array of DataRows, or a single DataRow.

The following code shows how to edit a record in a DataTable and then call the TableAdapter.Update method to save the changes to the database. (This example uses the Northwind database Region table.)

Visual Basic

' Locate the row you want to update.
Dim regionRow As NorthwindDataSet.RegionRow
regionRow = NorthwindDataSet._Region.FindByRegionID(1)

' Assign the new value to the desired column.
regionRow.RegionDescription = "East"

' Save the updated row to the database
Me.RegionTableAdapter.Update(Me.NorthwindDataSet._Region)

// Locate the row you want to update.
NorthwindDataSet.RegionRow regionRow;
regionRow = northwindDataSet.Region.FindByRegionID(1);

// Assign the new value to the desired column.
regionRow.RegionDescription = "East";

// Save the updated row to the database.
this.regionTableAdapter.Update(this.northwindDataSet.Region);

If your application uses objects to store the data in your application, you can use the TableAdapter's DBDirect methods to send data from your objects directly to the database. These methods allow you to pass individual values for each column as method parameters. Calling this method updates an existing record in the database with the column values passed into the method.

The following procedure uses the Northwind Region table as an example.

To update records in a database using the TableAdapter.Update method that takes column values

Call the TableAdapter's Update method, passing in the new and original values for each column as parameters.

Note:
If you do not have an instance available, instantiate the TableAdapter you want to use.

Visual Basic

Dim regionTableAdapter As New NorthwindDataSetTableAdapters.RegionTableAdapter

regionTableAdapter.Update(1, "East", 1, "Eastern")

NorthwindDataSetTableAdapters.RegionTableAdapter regionTableAdapter = 
    new NorthwindDataSetTableAdapters.RegionTableAdapter();

regionTableAdapter.Update(1, "East", 1, "Eastern");

Update Records Using Command Objects

The following example updates existing records directly in a database using command objects. For more information on using command objects to execute commands and stored procedures, see Fetching Data into Your Application.

The following procedure uses the Northwind Region table as an example.

To update existing records in a database using command objects

Create a new command object; set its Connection, CommandType, and CommandText properties; and then open a connection and execute the command.

Visual Basic

Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("YOUR CONNECTION STRING")

Dim cmd As New System.Data.SqlClient.SqlCommand
cmd.CommandType = System.Data.CommandType.Text
cmd.CommandText = "UPDATE Region SET RegionID = 1, RegionDescription = 'East' WHERE RegionID = 1"
cmd.Connection = sqlConnection1

sqlConnection1.Open()
cmd.ExecuteNonQuery()
sqlConnection1.Close()

System.Data.SqlClient.SqlConnection sqlConnection1 = 
    new System.Data.SqlClient.SqlConnection("YOUR CONNECTION STRING");

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "UPDATE Region SET RegionID = 1, RegionDescription = 'East' WHERE RegionID = 1";
cmd.Connection = sqlConnection1;

sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();

Security

You must have access to the database you are trying to connect to, as well as permission to update records in the desired table.

To update records in a database with the TableAdapter.Update method that takes DataSet, DataTable, DataRow, or DataRows()

To update records in a database using the TableAdapter.Update method that takes column values

To update existing records in a database using command objects

Tasks

Concepts

Other Resources