Knowledge Base

HOW TO: Fill a DataSet from a Data Source and Update Another Data Source by Using Visual Basic .NET

Article ID: 310347

Article Last Modified on 9/4/2003

APPLIES TO

Microsoft .NET Framework 1.1 Service Pack 1
Microsoft ADO.NET 1.1
Microsoft Visual Basic .NET 2002 Standard Edition
Microsoft Visual Basic .NET 2003 Standard Edition

This article was previously published under Q310347

For a Microsoft Visual C# .NET version of this article, see 313028.

This article refers to the following Microsoft .NET Framework Class Library namespace:

System.Data.SqlClient

IN THIS TASK

SUMMARY Description of the Technique Create a New Authors Table in the Northwind Database and a New Visual Basic Project Update a Data Source Other Than the Original Data Source

Update All Records to an Empty Table Using the CommandBuilder
Update All Records to an Empty Table Using TSQL Commands
Update Only the Changed Records to a Different Table
Update Only the Changed Records to Both Tables

Troubleshooting REFERENCES

SUMMARY

ADO.NET provides new flexibility in how you update your data source. This article describes how you can update a data source that is different from the original data source that you use to create the DataSet object.

back to the top

Description of the Technique

Earlier versions of Microsoft ActiveX Data Objects (ADO) allow you to update records in a data source other than the original data source of those records, though this is difficult to do. Because ADO.NET is a truly disconnected model and because ADO.NET introduces the DataAdapter object, you can more easily update a data source with information from another data source.

You can use the InsertCommand, UpdateCommand, and DeleteCommand properties of DataAdapter to update the data source with modifications that are made to the DataSet object. These properties contain the INSERT, UPDATE, and DELETE SQL commands respectively. You can use these commands to post the modifications back to the target data source. You can use any of the following methods to create these commands:

Manually create the commands in code.
Use the CommandBuilder object to automatically generate the commands.
Use the DataAdapter Wizard to visually generate the commands.

This article includes samples for the first two methods.

This article describes four ways to update records in a data source other than the original data source of those records. To successfully run the sample code, you must first create the Visual Basic project. After you complete the steps in the Create a New Authors Table in the Northwind Database and a New Visual Basic Project section, you can proceed to any of the other sections.

back to the top

Create a New Authors Table in the Northwind Database and a New Visual Basic Project

Each of the samples in this article requires a Visual Basic project and a new table in the sample Northwind database.

NOTE: In the code to fill the DataSet with the records from the Authors table, you can also insert the data in a different data source. To insert the data in a different data source, you must set the AcceptChangesDuringFill property to False. When you set AcceptChangesDuringFill to False, the AcceptChanges method of the DataRow object is not called when a row is added to the data table. Therefore, for each row that is added to the data table, the RowState property value is Added instead of Unchanged.

As a result, when the DataSet is submitted to the Update method of a DataAdpater, DataSet invokes the INSERT command of the DataAdapter instead of the UPDATE command for each of the DataRow objects. For more information, refer to the REFERENCES section.

To create a Visual Basic project and a new table in the Northwind database, follow these steps:

Open Query Analyzer, and then select the Northwind database. Run the following SQL script to create the Authors table:

CREATE TABLE [dbo].[authors] (
    [au_id] [varchar] (11) primary key NOT NULL ,
    [au_lname] [varchar] (40) NOT NULL ,
    [au_fname] [varchar] (20) NOT NULL ,
    [phone] [char] (12) NULL ,
    [address] [varchar] (40) NULL ,
    [city] [varchar] (20) NULL ,
    [state] [char] (2) NULL ,
    [zip] [char] (5) NULL ,
    [contract] [bit] NULL 
) ON [PRIMARY]
GO

Start Microsoft Visual Studio .NET, and then create a new Windows Application project in Visual Basic .NET.
Add the following code to the top of the Code window before the "Public Class Form1" code. This code allows you to use the objects of these namespaces without fully qualifying them.
```
Imports System.Data.SqlClient
Imports System.Data
                    
```

At the top of Public Class Form1, just below the Inherits statement, add the following declaration statements:

Dim PubConn As New SqlConnection _
("Data source=server;integrated security=sspi;" & _
"initial catalog=pubs;")
Dim NWindConn As New SqlConnection _
("Data source=server;integrated security=sspi;" & _
"initial catalog=northwind;")
Dim pubCom As New SqlCommand("select * from authors", PubConn)
Dim dsPub As New DataSet()
Dim daPub As New SqlDataAdapter()

Modify the Data source arguments in the following code to point to your Microsoft SQL Server, and then close the Code window:

Dim PubConn As New SqlConnection _
("Data source=server;integrated security=sspi;" & _
"initial catalog=pubs;")
Dim NWindConn As New SqlConnection _
("Data source=server;integrated security=sspi;" & _
"initial catalog=northwind;")

Double-click the form to display the Form_Load event in the Code window. Add the following code to this event to open the connection to the Authors table in the Pubs database and to fill the DataSet, dsPub, with the records from the Authors table:
```
      PubConn.Open()
      daPub.SelectCommand = pubCom
      daPub.AcceptChangesDuringFill = False
      daPub.Fill(dsPub, "Authors")
                    
```
Add two TextBox controls (TextBox1 and TextBox2) to Form1.
Add a Button control to Form1. Change the Name property to btnEdit, and then change the Text property to Edit.
Double-click the button to display the btnEdit_Click event in the Code window. Add the following code to this event:
```
      Console.Write(dsPub.Tables("Authors").Rows(1)(1))
      Console.Write(dsPub.Tables("Authors").Rows(1)(2)) 

      dsPub.Tables("Authors").Rows(1).BeginEdit()
      dsPub.Tables("Authors").Rows(1)(1) = TextBox2.Text
      dsPub.Tables("Authors").Rows(1)(2) = TextBox1.Text
      dsPub.Tables("Authors").Rows(1).EndEdit()

      Console.Write(dsPub.Tables("Authors").Rows(1)(1))
      Console.Write(dsPub.Tables("Authors").Rows(1)(2))
                        
```
This code writes the original value of the second author's first and last names in the DataSet to the Output window. The code edits the author's first and last name fields and then finally writes the new values to the Output window to confirm that they have been changed in the DataSet. Notice that the code does not update the information back to the data source. This code only edits the DataSet object.

back to the top

Update a Data Source Other Than the Original Data Source

This section describes four different ways to update records in a data source other than the original data source of those records.

back to the top

Update All Records to an Empty Table Using the CommandBuilder

This section demonstrates one way to update a data source with information from another data source. In this sample, you create a DataSet from the Authors table in the Pubs database, edit a record in that DataSet, and commit the changed records and all other records to an empty Authors table in the Northwind database. You do not update any changes back to the original Pubs database.

This sample uses two DataAdapter objects, one DataSet object, and the CommandBuilder object. You use the first DataAdapter to create a DataSet based on the Authors table in the Pubs database. You connect the second DataAdapter to the Authors table in the Northwind database, and you use this DataAdapter for updating. Finally, you use the CommandBuilder object to create the Transact SQL (TSQL) commands that are necessary to update and to insert records into the data source.

Complete the steps in the Create a New Authors Table in the Northwind Database and a New Visual Basic Project section to set up the Authors table in the Northwind database and to create the Visual Basic .NET project.
Make sure that no records exist in the Authors table of the Northwind database. This can occur when you set DataAdapter.AcceptChangesDuringFill to False. If records exist in the Authors table, you receive a primary key violation when you run this code because you insert duplicate values.
Add a Button control to Form1. Change the Name property to btnUpdate1, and then change the Text property to Update1.

Double-click btnUpdate1 to display the Code window and the btnUpdate1_click event. Add the following code to this event:

      Dim NwindDA As New SqlDataAdapter("select * from authors", NWindConn)
      Dim x As SqlCommandBuilder = New SqlCommandBuilder(NwindDA)
      NWindConn.Open()

      NwindDA.Update(dsPub, "Authors")
      Console.Write ("Done")

This code creates a new DataAdapter object that works with the second Authors table. This DataAdapter object then uses the CommandBuilder object to create the TSQL commands.

Run the application.
Type a new first name in TextBox1, type a new last name in TextBox2, and then click Edit. This edits the records in the dsPubDataSet. This also displays the original values of the first name and the last name fields in the Output window, along with the new values for these same fields.
Click Update1. After "Done" appears in the Output window, stop running the application.
Open the Authors table in the Northwind database. Notice that the Authors table contains all of the records from the original table. In addition, the second record includes the values that you typed in the text boxes.
Refer to the original Authors table in the Pubs database. Notice that the values for the second record are not changed.

back to the top

Update All Records to an Empty Table Using TSQL Commands

This sample allows you to move all of the records from the Authors table in the Pubs database to an empty Authors table in the Northwind database without changing the original table. To perform these updates, you manually create TSQL commands and then use these TSQL commands on the DataAdapter object. This method provides better performance than the CommandBuilder object method.

Complete the steps in the Create a New Authors Table in the Northwind Database and a New Visual Basic Project section to set up the Authors table in the Northwind database and to create the Visual Basic .NET project.
Make sure that no records exist in the Authors table of the Northwind database. This can occur when you set DataAdapter.AcceptChangesDuringFill to False. If records exist in the Authors table, you receive a primary key violation when you run this code because you insert duplicate values.

Even though a row is edited, you only need an InsertCommand property because all of the rows are flagged as Added due to the setting of AcceptChangesDuringFill.
Add a Button control to Form1. Change the Name property to btnUpdate2, and then change the Text property to Update2.

Double-click btnUpdate2 to display the Code window and the btnUpdate2_click event. Add the following code to this event:

Dim NwindDA As New SqlDataAdapter _
("select * from authors", NWindConn)

NwindDA.UpdateCommand = New SqlCommand _
("UPDATE authors SET au_lname = @lastName " & _
"WHERE au_ID = @auID", NWindConn)

Dim workParm As SqlParameter = _
NwindDA.UpdateCommand.Parameters.Add _
("@auID", SqlDbType.VarChar, 11)
workParm.SourceColumn = "au_ID"
workParm.SourceVersion = DataRowVersion.Original

Dim workparm4 As SqlParameter = _
NwindDA.UpdateCommand.Parameters.Add _
("@lastName", SqlDbType.NVarChar, 15)
workparm4.SourceColumn = "au_lname"
workparm4.SourceVersion = DataRowVersion.Current

NwindDA.InsertCommand = New SqlCommand _
("INSERT into authors (au_id, au_lname, au_fname) " & _ 
"VALUES (@au_id, @au_lname, @au_fname)", NWindConn)
Dim workParm1 As SqlParameter = _
NwindDA.InsertCommand.Parameters.Add _
("@au_ID", SqlDbType.VarChar, 11)
workParm1.SourceColumn = "au_ID"

Dim workParm2 As SqlParameter = _
NwindDA.InsertCommand.Parameters.Add _
("@au_lname", SqlDbType.VarChar, 40)
workParm2.SourceColumn = "au_lname"

Dim workParm3 As SqlParameter = _
NwindDA.InsertCommand.Parameters.Add _
("@au_fname", SqlDbType.VarChar, 20)
workParm3.SourceColumn = "au_fname"

NWindConn.Open()

NwindDA.Update(dsPub, "Authors")

Console.Write("Done")

This code creates a new DataAdapter object that works with the Authors table in the Northwind database. This DataAdapter then uses TSQL commands that you manually create.

Run the application.
Type a first name in TextBox1, type a last name in TextBox2, and then click Edit. This edits the records in the dsPubDataSet. This also displays the original values of the first name and the last name fields in the Output window, along with the new values for these same fields.
Click Update2.
After "Done" appears in the Output window, open the Authors table in the Northwind database. Notice that the Authors table contains all of the records from the original Authors table. In addition, the second record includes the new values.
Refer to the original Authors table in the Pubs database. Notice that the values for the second record are not updated because you called the Update method of the DataAdapter that is configured for the Northwind database.

back to the top

Update Only the Changed Records to a Different Table

In this sample, you send only the updated records to the Northwind database. This sample updates the data source by using the GetChanges method to pull the modified row or rows out of the original DataSet. The sample inserts the row or rows into a temporary DataSet named ChangedDS. The sample then calls the Update method of the DataAdapter object to pass this temporary DataSet to the Northwind database. Finally, the sample calls the AcceptChanges method of the working DataSet, dsPub, to bring dsPub to a consistent state to reflect the data source changes.

Complete the steps in the Create a New Authors Table in the Northwind Database and a New Visual Basic Project section to set up the Authors table in the Northwind database and to create the Visual Basic .NET project.
Make sure that the Authors table in the Northwind database contains at least the second row from the Authors table in the Pubs database. This is the row that you will edit and use for the update.

If the Authors table in the Northwind database does not contain this row, you can run the code from the Update All Records to an Empty Table Using the CommandBuilder section to add all of the records to this table. You can also add the records manually.
Comment out the following line of code in the Form_Load event:
```
'daPub.AcceptChangesDuringFill = False
                        
```
This inserts the rows into the DataSet as existing rows.
Add a new Button control to Form1. Change the Name property to btnUpdate3, and then change the Text property to Update3.

Double-click Update3 to display the btnUpdate3_click event. Add the following code to this event:

Dim NwindDA As New SqlDataAdapter _
("select * from authors", NWindConn)

NwindDA.UpdateCommand = New SqlCommand _
("UPDATE authors SET au_lname = @lastName, " & _
"au_fname = @au_fname " & _
"WHERE au_ID = @auID", NWindConn)

Dim workParm As SqlParameter = _
NwindDA.UpdateCommand.Parameters.Add _
("@auID", SqlDbType.VarChar, 11)
workParm.SourceColumn = "au_ID"
workParm.SourceVersion = DataRowVersion.Original

Dim workparm4 As SqlParameter = _
NwindDA.UpdateCommand.Parameters.Add _
("@lastName", SqlDbType.NVarChar, 15)
workparm4.SourceColumn = "au_lname"
workparm4.SourceVersion = DataRowVersion.Current

Dim workParm1 As SqlParameter = _
NwindDA.UpdateCommand.Parameters.Add _
("@au_fname", SqlDbType.VarChar, 11)
workParm1.SourceColumn = "au_fname"
workParm1.SourceVersion = DataRowVersion.Current

NWindConn.Open()

Dim changedDS As New DataSet()
changedDS = dsPub.GetChanges(DataRowState.Modified)

NwindDA.Update(changedDS, "Authors")

Console.Write("Done")

dsPub.AcceptChanges()

Run the application.
Type a first name in TextBox1, type a last name in TextBox2, and then click Edit. This edits the records in the dsPubDataSet. This also displays the original values of the first name and the last name fields in the Output window, along with the new values for these same fields.
Click Update3.
After "Done" appears in the Output window, open the Authors table in the Northwind database. Notice that the Authors table contains the changes that you made to the DataRow.
Refer to the original Authors table in the Pubs database. Notice that the values for the second record are not updated.

back to the top

Update Only the Changed Records to Both Tables

To update the records in both data sources, you can use two DataAdapter objects and the GetChanges method of the DataSet object. In this sample, you create a DataSet object based on the Authors table in the Pubs database. You edit this DataSet and then copy the changed records into a new DataSet object. You use this new DataSet to update the tables of the data source. After you update the table of the first data source, you call the GetChanges method again and re-create the temporary DataSet. You can then update the table of the second data source.

In this sample, you use the temporary DataSet and call GetChanges twice because the Update method of the DataAdapter implicitly calls AcceptChanges on the DataSet that you pass to it. The implicit AcceptChanges method resynchronizes the original and the current values for each cell that is changed. In addition, the implicit AcceptChanges method flags the DataRow as UnModified. This eliminates the changes and prevents you from sending the changes to another data source.

In this particular instance, you can skip the second GetChanges method and pass the original DataSet to the Update method of the Pubs DataAdapter. This eliminates the dsPubs.AcceptChanges call. However, the combination of the temporary DataSet and the GetChanges method allows you to roll back to the original modifications. In earlier versions of Microsoft ActiveX Data Objects (ADO), you persist the recordset to a file to achieve this functionality.

Complete the steps in the Create a New Authors Table in the Northwind Database and a New Visual Basic Project section to set up the Authors table in the Northwind database and to create the Visual Basic .NET project.
Make sure that the Northwind database contains an Authors table, and make sure that the Authors table in the Northwind database contains at least the record that you will modify from the Authors table in the Pubs database.

Comment out the following line of code in the Form_Load event:

'daPub.AcceptChangesDuringFill = False

Add a Button control to Form1. Change the Name property to btnUpdate4, and then change the Text property to Update4.

Double-click btnUpdate4 to display the btnUpdate4_click event. Add the following code to this event:

Dim NwindDA As New SqlDataAdapter _
("select * from authors", NWindConn)

NwindDA.UpdateCommand = New SqlCommand _
("UPDATE authors SET au_lname = @lastName, au_fname = @au_fname " & _
"WHERE au_ID = @auID", NWindConn)

Dim workParm As SqlParameter = _
NwindDA.UpdateCommand.Parameters.Add _
("@auID", SqlDbType.VarChar, 11)
workParm.SourceColumn = "au_ID"
workParm.SourceVersion = DataRowVersion.Original

Dim workparm4 As SqlParameter = _
NwindDA.UpdateCommand.Parameters.Add _
("@lastName", SqlDbType.NVarChar, 15)
workparm4.SourceColumn = "au_lname"
workparm4.SourceVersion = DataRowVersion.Current

Dim workParm3 As SqlParameter = _
NwindDA.UpdateCommand.Parameters.Add _
("@au_fname", SqlDbType.VarChar, 20)
workParm3.SourceColumn = "au_fname"
workParm3.SourceVersion = DataRowVersion.Current

NWindConn.Open()

daPub.UpdateCommand = New SqlCommand _
("UPDATE authors SET au_lname = @lastName, au_fname = @au_fname " & _
"WHERE au_ID = @auID", PubConn)

Dim workParm7 As SqlParameter = _
daPub.UpdateCommand.Parameters.Add _
("@auID", SqlDbType.VarChar, 11)
workParm7.SourceColumn = "au_ID"
workParm7.SourceVersion = DataRowVersion.Original

Dim workparm8 As SqlParameter = _
daPub.UpdateCommand.Parameters.Add _
("@lastName", SqlDbType.NVarChar, 15)
workparm8.SourceColumn = "au_lname"
workparm8.SourceVersion = DataRowVersion.Current

Dim workParm11 As SqlParameter = _
daPub.UpdateCommand.Parameters.Add _
("@au_fname", SqlDbType.VarChar, 20)
workParm11.SourceColumn = "au_fname"
workParm11.SourceVersion = DataRowVersion.Current

Dim changedDS As New DataSet()
changedDS = dsPub.GetChanges(DataRowState.Modified)

NwindDA.Update(changedDS, "Authors")
changedDS = dsPub.GetChanges(DataRowState.Modified)

daPub.Update(changedDS, "Authors")
dsPub.AcceptChanges()

Console.Write("Done")

Run the application.
Type a new first name in TextBox1, type a new last name in TextBox2, and then click Edit. This edits the records in the dsPubDataSet. This also displays the original values of the first name and the last name fields in the Output window, along with the new values for these same fields.
Click Update4.
After "Done" appears in the Output window, open the Authors table in the Pubs database and the Authors table in the Northwind database. Notice that both tables include the new values for the second record.

back to the top

Troubleshooting

When you work with the code in the Update Only the Changed Records to a Different Table and Update Only the Changed Records to Both Tables sections, you may receive a System.ArgumentNullException error. If this occurs, verify that you comment out the following line of code:

'daPub.AcceptChangesDuringFill = False

This line of code tells the DataAdapter to call the AcceptChanges method when the Fill method of the DataAdapter is called.

back to the top

REFERENCES

For additional information about how to update back to the original data source, click the article number below to view the article in the Microsoft Knowledge Base:

308051 PRB: Output Parameters Are Not Returned When You Run an ADO.NET Command in Visual Basic .NET

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:

308055 HOW TO: Update a SQL Server Database by Using the SqlDataAdapter Object in Visual Basic .NET

310373 PRB: DataAdapter.Fill Does Not Set All of the Properties

301216 HOW TO: Populate a DataSet Object from a Database by Using Visual Basic .NET

For more information, refer to the following Microsoft Web sites:

DataRow.AcceptChanges Method
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataDataRowClassAcceptChangesTopic.asp

DataAdapter.AcceptChangesDuringFill Property
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataCommonDataAdapterClassAcceptChangesDuringFillTopic.asp

back to the top

Additional query words: data store

Keywords: kbhowtomaster kbsqlclient kbsystemdata KB310347

Microsoft KB Archive/310347

Contents

HOW TO: Fill a DataSet from a Data Source and Update Another Data Source by Using Visual Basic .NET

IN THIS TASK

SUMMARY

Description of the Technique

Create a New Authors Table in the Northwind Database and a New Visual Basic Project

Update a Data Source Other Than the Original Data Source

Update All Records to an Empty Table Using the CommandBuilder

Update All Records to an Empty Table Using TSQL Commands

Update Only the Changed Records to a Different Table

Update Only the Changed Records to Both Tables

Troubleshooting

REFERENCES