UPDATE Articles SET artTitle = ? , artDate = ? , artDescription = ? , artVB6 = ? , artVBNET = ? , artURL = ? WHERE ( (artKey = ?) AND ((? IS NULL AND artTitle IS NULL) OR (artTitle = ?)) AND ((? IS NULL AND artDate IS NULL) OR (artDate = ?)) AND ((? IS NULL AND artDescription IS NULL) OR (artDescription = ?)) AND ((? IS NULL AND artVB6 IS NULL) OR (artVB6 = ?)) AND ((? IS NULL AND artVBNET IS NULL) OR (artVBNET = ?)) )

If you're using a different provider, the SQL command text will not be the same for your program. You can view your own SQL string with a line of code like this in your program:

MsgBox(myCommand.GetUpdateCommand.CommandText.ToString)

Notice that the command simply updates EVERY field (except the key) if a change is present. This VERY GENERIC and, in some ways, brute force approach does have limitations. An excellent article about limitations and alternatives to CommandBuilder was written by William Vaughn for MSDN Technical Articles and can be viewed here.

When the button is clicked the first time, an If statement causes this block of code to be executed. Any changes to the initial values in the TextBox and CheckBox components are simply copied to the DataTable for updating.

myOleDbDataAdapter.Fill(myDataSet, "Articles")
myDataTable = myDataSet.Tables("Articles")
Dim rowArticle As DataRow = myDataTable.NewRow()
rowArticle("artTitle") = txtTitle.Text
Try
    rowArticle("artDate") = txtDate.Text
Catch
    rowArticle("artDate") = Now()
End Try
rowArticle("artVB6") = chkVB6.CheckState
rowArticle("artVBNET") = chkVBNET.CheckState
rowArticle("artURL") = txtURL.Text
rowArticle("artDescription") = txtDescription.Text
myDataTable.Rows.Add(rowArticle)
myOleDbDataAdapter.Update(myDataSet, "Articles")
myDataSet.Tables("Articles").AcceptChanges()
cmdUpdate.Text = "Then, Select And Change An Article"
RefreshListBox()

Notice the use of a Try-Catch block to make sure that the contents of the date field are valid for the Access database. Some work on helping other fields - the URL field comes to mind - could be done too but this example is being kept simple.

Since a new row was added to myDataTable, the DataAdapter Update method inserts this row into the Access database.

The RefreshListBox subroutine that is called at the end is new too! I suggested at the beginning that you will want to think long and hard about the way you manage the transfer of data between your local DataSet and the actual database. In this case, I chose to refresh the entire thing each time to ensure independence. This isn't the only way to accomplish this but it helps to make the points clear in this tutorial. The RefreshListBox code is as follows:

myDataSet = New DataSet()
myDataTable = New DataTable()
myOleDbDataAdapter.Fill(myDataTable)
lstAboutVBData.DataSource = myDataTable
lstAboutVBData.DisplayMember = "artTitle"
lstAboutVBData.ValueMember = "artKey"

One of the last statements in this clause of the If block is a change to the Button caption which is also used to control flow in the program. The next time the Button is clicked, the second clause is executed and data in the database is changed instead:

myOleDbDataAdapter.Fill(myDataSet, "Articles")
myDataTable = myDataSet.Tables("Articles")
Dim myChangeRow As DataRow = myDataTable.Rows(lstAboutVBData.SelectedIndex)
myChangeRow("artKey") = artKeySave
myChangeRow("artTitle") = txtTitle.Text
Try
    myChangeRow("artDate") = txtDate.Text
Catch
    myChangeRow("artDate") = Now()
End Try
myChangeRow("artVB6") = chkVB6.CheckState
myChangeRow("artVBNET") = chkVBNET.CheckState
myChangeRow("artURL") = txtURL.Text
myChangeRow("artDescription") = txtDescription.Text
Dim newDataSet As DataSet
newDataSet = myDataSet.GetChanges()
myOleDbDataAdapter.Update(newDataSet, "Articles")
myDataSet.Tables("Articles").AcceptChanges()
cmdUpdate.Text = "Finally, Select And Delete An Article"
RefreshListBox()

ADO .NET conveniently provides a method to create a DataSet with only the changes necessary to be updated.

newDataSet = myDataSet.GetChanges()

And only these changes are actually sent to the database.

myOleDbDataAdapter.Update(newDataSet, "Articles")

Next page > Autonumber Complications > Page 1, 2, 3, 4, 5, 6