What's New
Q & A
Tip Jar
C# Helper...
Follow VBHelper on Twitter
MSDN Visual Basic Community
TitleUpdate a DataSet that doesn't have primary keys in VB .NET
DescriptionThis example shows how to update a DataSet that doesn't have primary keys in VB .NET.
KeywordsDataGrid, DataTable, bind, ADO.NET, data, database, primary key
CategoriesDatabase, Controls
Normally data adapter objects use a table's primary key to find records that must be updated or deleted. If a table doesn't have a primary key, you cannot use a command builder object to automatically build the commands needed to update and delete records.

When this program starts, it creates an OleDbDataAdapter to select data from a database. It creates a DataSet and uses the adapter's Fill method to load it with data. It then sets the DataGrid control's DataSource property to the Addresses DataTable in the DataSet.

Private Const TABLE_NAME As String = "Addresses"

Private m_ConnectString As String

Private m_DataAdapter As OleDbDataAdapter
Private m_DataSet As DataSet

Private Sub Form1_Load(ByVal sender As System.Object, ByVal _
    e As System.EventArgs) Handles MyBase.Load
    Const SELECT_STRING As String = "SELECT * FROM " & _
        TABLE_NAME & " ORDER BY FirstName, LastName"

    ' Get the database file name.
    Dim db_name As String = Application.StartupPath
    db_name = db_name.Substring(0, db_name.LastIndexOf("\"))
    db_name &= "\Contacts.mdb"

    ' Compose the connection string.
    m_ConnectString = _
          "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & db_name & ";" & _
          "Persist Security Info=False"

    ' Create a DataAdapter to load the Addresses table.
    m_DataAdapter = New OleDbDataAdapter(SELECT_STRING, _

    ' Create and fill the DataSet.
    m_DataSet = New DataSet
    m_DataAdapter.Fill(m_DataSet, TABLE_NAME)

    ' Bind the DataGrid to the DataTable.
    dgContacts.DataSource = m_DataSet.Tables("Addresses")
End Sub
When the program is ending, it makes an OleDbCommandBuilder object for the adapter. The adapter will use the command builder to make SQL insert commands as necessary to create new records. Because the table doesn't have a primary key, the command builder cannot make update or delete commands.

The program then makes the delete command itself. It creates a new OleDbCommand object, passing the constructor the SQL statement that it should use to update records. The WHERE clause uses the record's FirstName and LastName fields to identify the record that should be modified. The program replaces values in the statement with question marks. It then adds parameters to the command object to correspond to the question marks. The important part here is the final argument passed to the parameters' constructors, which gives the name of the database field for each parameter.

Note that the program sets the SourceVersion property for the WHERE clause values to Original. That tells the command to look at the original values for those fields and not the current values as modified by the user.

Similarly the program makes a command to delete records.

Finally the program uses the adapter's Update method to save any changes to the data.

' Save changes to the data.
Private Sub Form1_Closing(ByVal sender As Object, ByVal e _
    As System.ComponentModel.CancelEventArgs) Handles _
    ' Use a CommandBuilder to make the INSERT command.
    Dim command_builder As New _

    ' Make a database connection.
    Dim conn As New OleDbConnection(m_ConnectString)

    ' Make the UPDATE command "by hand."
    Dim update_command As New OleDbCommand( _
        "UPDATE " & TABLE_NAME & " SET " & _
        "FirstName=?, LastName=?, Street=?, City=?, " & _
            "State=?, ZIP=? " & _
        "WHERE FirstName=? AND LastName=?", _

    ' Define output parameters.
    update_command.Parameters.Add("@FirstName", _
        OleDbType.VarWChar, 50, "FirstName")
    update_command.Parameters.Add("@LastName", _
        OleDbType.VarWChar, 50, "LastName")
    update_command.Parameters.Add("@Street", _
        OleDbType.VarWChar, 50, "Street")
    update_command.Parameters.Add("@City", _
        OleDbType.VarWChar, 50, "City")
    update_command.Parameters.Add("@State", _
        OleDbType.VarWChar, 2, "State")
    update_command.Parameters.Add("@ZIP", _
        OleDbType.VarWChar, 10, "ZIP")

    ' Define intput (WHERE) parameters.
    Dim param As OleDbParameter
    param = update_command.Parameters.Add("@FirstName", _
        OleDbType.VarWChar, 50, "FirstName")
    param.SourceVersion = DataRowVersion.Original
    param = update_command.Parameters.Add("@LastName", _
        OleDbType.VarWChar, 50, "LastName")
    param.SourceVersion = DataRowVersion.Original

    ' Attach the update command to the DataAdapter.
    m_DataAdapter.UpdateCommand = update_command

    ' Make the DELETE command "by hand."
    Dim delete_command As New OleDbCommand( _
        "DELETE FROM " & TABLE_NAME & " " & _
        "WHERE FirstName=? AND LastName=?", _

    ' Define intput (WHERE) parameters.
    param = delete_command.Parameters.Add("@FirstName", _
        OleDbType.VarWChar, 50, "FirstName")
    param.SourceVersion = DataRowVersion.Original
    param = delete_command.Parameters.Add("@LastName", _
        OleDbType.VarWChar, 50, "LastName")
    param.SourceVersion = DataRowVersion.Original

    ' Attach the delete command to the DataAdapter.
    m_DataAdapter.DeleteCommand = delete_command

    ' Update the database.
        m_DataAdapter.Update(m_DataSet, TABLE_NAME)
    Catch ex As Exception
    End Try
End Sub
For information on database programming in VB .NET, see my book Visual Basic .NET Database Programming.
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.