Using TableAdapters to Insert Related Data into an MS Access Database

Using TableAdapters to Insert Related Data into an MS Access Database

Rate This
  • Comments 63

I’ve posted before about how to use TableAdapters to update parent-child (master-detail) relationships against SQL server. It’s pretty straightforward and Visual Studio generates all the code for you to properly insert, update and delete your data. However if you’re using MS Access then there’s one thing that Visual Studio doesn’t do because it’s not supported when using Access.

How Parent-Child Inserts Work

When Visual Studio generates the insert commands on a SQL-Server TableAdapter it looks to see if the table’s primary keys are auto-generated (identity columns) and if so, Visual Studio will write an additional statement to retrieve the key using the SCOPE_IDENTITY functionality of SQL Server. When in the DataSet designer, if you look at the insert statement in the properties window for the SQLTableAdapter you will see two statements separated by a semi-colon:

image

INSERT INTO [dbo].[Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued);
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products WHERE (ProductID = SCOPE_IDENTITY())

SQL Server supports batch statements through ADO.NET commands so this will populate the primary key back in the DataRow inside the DataSet as each row is inserted into the database. If you are enforcing foreign key constraints with a parent-child relation set up on two DataTables and you set the Update Rule to Cascade then any foreign key references will also be updated in the children. Because the TableAdapterManager will save the children after their parent records, when the child saves to the database it will contain the correct parent key which must already exist in the database before a child can be inserted in order to maintain referential integrity in the database.

Unfortunately Access doesn’t support batch statements. If you look at what is generated for Access you will only see one statement (also the OLEDB provider does not support named parameters hence the question mark placeholders):

INSERT INTO `Products` (`ProductName`, `SupplierID`, `CategoryID`, `QuantityPerUnit`, `UnitPrice`, `UnitsInStock`, `UnitsOnOrder`, `ReorderLevel`, `Discontinued`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)

So if you are doing inserts, especially for related parent-child data, you need a way to intercept the DataRow and set the primary key right after the row is inserted into the database and before any children are inserted. There’s an excellent article by Bill Vaughn (VB MVP) that explains this issue as well as a KB Article that shows how to solve it using the DataAdapter. These were written before Visual Studio had the concept of TableAdapters (which were added in VS 2008) so let’s see how we could use this technique to enhance our TableAdapters via partial classes.

Setting up the Parent-Child DataSet

The first step is to make sure you set up the tables in your Access database to use the AutoNumber feature for the primary keys on the rows. Here I’m using Access 2007 against the Northwind Access database. AutoNumber is used for both the primary keys on the Products and Categories tables:

image

Next you need to make sure you set up the relationship on the DataSet properly so that the primary key on the parent will cascade to the foreign key on the child. Set the relation in the DataSet designer to "Both Relation and Foreign Key Constraint" and then set the Update and Delete rules to Cascade. Just right-click on the relation and select "Edit Relation" in the DataSet designer:

image image

Loading and Editing the Parent-Child DataSet

You now are telling the DataSet to enforce the foreign key relationship which means that you must have a parent for every child. This means you have to load the data in parent then child order. You also have to be careful with your queries. You have to make sure that every row in the child DataTable will have a corresponding parent row in the parent DataTable. This also means that you have to make sure to call EndEdit on any new parent BindingSource before any children can be added.

For example, from the Data Sources window drag the Categories parent table as details and the related child Products table as a DataGridView on the form and Visual Studio will generate the code to load and save our data.

image

Head over to the code behind and make sure that the parent is filled first before the child. Also make sure that EndEdit is called on the CategoriesBindingSource before a new product can be inserted into the DataGridView. EndEdit will flush the data row being edited by the controls into the DataTable. In this example I just am calling EndEdit on the CategoriesBindingSource when the user selects the grid.

Public Class Form1

    Private Sub CategoriesBindingNavigatorSaveItem_Click() _
            Handles CategoriesBindingNavigatorSaveItem.Click
        Me.Validate()
        'Call EndEdit on all BindingSources! 
        Me.CategoriesBindingSource.EndEdit()
        Me.ProductsBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.ProductsDataSet)
    End Sub

    Private Sub Form1_Load() Handles MyBase.Load
        'Load parent before child!
        Me.CategoriesTableAdapter.Fill(Me.ProductsDataSet.Categories)
        Me.ProductsTableAdapter.Fill(Me.ProductsDataSet.Products)
    End Sub

    Private Sub ProductsDataGridView_Enter() Handles ProductsDataGridView.Enter
        'You must commit the parent row to the DataTable before adding child rows 
        Me.CategoriesBindingSource.EndEdit()
    End Sub

End Class

Note that anytime you call EndEdit and flush the data to the DataTable, the row must not fail any constraints either (i.e. if NULLs aren’t being allowed then you have to set those values). One way to handle this is to add code to set default values in the TableNewRow handler on the DataTable.

Enhancing the TableAdapter Partial Classes

 

 

 

Now for the good stuff. Like I mentioned in the beginning, you need a way to set the primary key on the parent right after the row is inserted into the database and before any children are inserted. Now that we have keys cascading we just need to write code to handle the RowUpdated event on the DataAdapter inside the TableAdapter partial class. TableAdapters are generated classes that Visual Studio creates for us from the DataSet designer. These classes are declared as Partial Classes so that means we can add code to the same class even if it’s in a separate file. Right-click on the TableAdapter class in the DataSet Designer and select View Code and the partial class file that you can edit will be created for you.

Namespace ProductsDataSetTableAdapters

    Partial Public Class CategoriesTableAdapter

    End Class

    Partial Public Class ProductsTableAdapter

    End Class
End Namespace

In these classes we can handle the RowUpdated event on the private variable _adapter which gives us access to the ADO.NET DataAdapter that is executing the updates to our rows. The way we retrieve the primary key is by executing the statement  SELECT @@IDENTITY which tells Access to send back the last primary key it used on the connection. Because you have to add this handler to all your TableAdapters that are working against MS Access, to make things more manageable you can create a class with a Shared (static) method to handle setting the key and then call that from the handlers.

Imports System.Data.OleDb

Public Class AccessIDHelper
    ''' <summary>
    ''' Retrieves the primary key autonumber values from Access
    ''' </summary>
    ''' <remarks></remarks>
    Public Shared Sub SetPrimaryKey(ByVal trans As OleDbTransaction, _
                                    ByVal e As OleDbRowUpdatedEventArgs)
        If e.Status = UpdateStatus.Continue AndAlso _
           e.StatementType = StatementType.Insert Then
            ' If this is an INSERT operation...
            Dim pk = e.Row.Table.PrimaryKey
            ' and a primary key column exists...
            If pk IsNot Nothing AndAlso pk.Count = 1 Then
                Dim cmdGetIdentity As New OleDbCommand("SELECT @@IDENTITY", trans.Connection, trans)
                ' Execute the post-update query to fetch new @@Identity
                e.Row(pk(0)) = CInt(cmdGetIdentity.ExecuteScalar)
                e.Row.AcceptChanges()
            End If
        End If
    End Sub
End Class

Namespace ProductsDataSetTableAdapters

    Partial Public Class CategoriesTableAdapter

        Private Sub _adapter_RowUpdated(ByVal sender As Object, _
                                        ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) _
                                        Handles _adapter.RowUpdated

            AccessIDHelper.SetPrimaryKey(Me.Transaction, e)
        End Sub
    End Class

    Partial Public Class ProductsTableAdapter

        Private Sub _adapter_RowUpdated(ByVal sender As Object, _
                                        ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) _
                                        Handles _adapter.RowUpdated

            AccessIDHelper.SetPrimaryKey(Me.Transaction, e)
        End Sub
    End Class
End Namespace

So that’s how you can get the primary keys into the data rows and have them properly cascaded to the child rows. So now when the children are updated they will have the correct foreign key and the parent will exist in the database. I hope this helps clear up how to work with Access and Visual Studio. 

I’ve posted this example on CodeGallery so have a look.

Enjoy!

Leave a Comment
  • Please add 8 and 4 and type the answer here:
  • Post
  • Hi,

         I am unable to insert,update or delete data.I am using Visual STudio Express Edition

  • Hi all,

    Thanks for great advices, especially you Beth!

    I'm having problem with connecting ItemName from one table to another table via IdItem as FK. Tables look like this:

    PK IdState      --- PK IdItem

    FK IdItem  <---/       ItemName

      Qty

    When I select IdItem via comboBox and enter it's Qty, I would like to make new DataSet that will fill in ItemName instead of IdItem, but corresponding one.

    How to achieve this as SQL statement or some other way.

    Thanks ahead!

    BR,

    Sergei

  • The statement "Dim cmdGetIdentity As New OleDbCommand("SELECT @@IDENTITY", trans.Connection, trans)" is giving System Exception telling 'Object reference not set to an instance of an object'

  • Hi Beth!

    I am trying to insert related data into an MS Access database EXACTLY like you have shown above, yet I am getting an error.

    I use this code like yours:

    Namespace MyDataSetTableAdapters

       Public Class AccessIDHelper

           '<summary>

           'Retrieves the primary key autonumber values from Access

           '</summary>

           '<remarks></remarks>

           Public Shared Sub SetPrimaryKey(ByVal trans As OleDbTransaction, ByVal e As OleDbRowUpdatedEventArgs)

               If e.Status = UpdateStatus.Continue AndAlso e.StatementType = StatementType.Insert Then

                   ' If this is an INSERT operation...

                   Dim CustomerID = e.Row.Table.PrimaryKey

                   ' and a primary key column exists...

                   If CustomerID IsNot Nothing AndAlso CustomerID.Count = 1 Then

                       Dim cmdGetIdentity As New OleDbCommand("SELECT @@IDENTITY", trans.Connection, trans)

                       ' Execute the post-update query to fetch new @@Identity

                       e.Row(CustomerID(0)) = CInt(cmdGetIdentity.ExecuteScalar)

                       e.Row.AcceptChanges()

                   End If

               End If

           End Sub

       End Class

       Partial Public Class CustomerTableAdapter

           Private Sub _adapter_RowUpdated(ByVal sender As Object, ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles _adapter.RowUpdated

               AccessIDHelper.SetPrimaryKey(Me.Transaction, e)

           End Sub

    And I use this code to insert the record:

    Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click

           If MsgBox("Yes or No", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then

               Dim newOrderDetailsRow As MyDataSet.OrderDetailsRow

               newOrderDetailsRow = MyDataSet.OrderDetailsTable.NewOrderDetailsRow()

               newOrderDetailsRow.OrderCategory = txtOrderCategory.Text

               'newOrderDetailsRow.Discounts = Val(ComboDiscounts.SelectedValue)

               newOrderDetailsRow.Region = txtRegion.Text

               -----------------------------

               ---------------------------

                ----------------- etc etc

               Me.MyDataSet.OrderDetailsTable.Rows.Add(newOrderDetailsRow)

               ' Save the new row to the database

               Me.OrderDetailsTableAdapter.Update(newOrderDetailsRow) <<<<<<<<<<<<<<<<< Error Here

               Me.Close()

               Dim f As Form = New AddNewOrderDetailsForm

               f.Show()

           End If

       End Sub

    I am not able to insert and move on as I am getting the error marked above.

    What could be possibly wrong with the above code and why is this giving an error ??

    Please help Beth !

    Thanks a lot !!

    Waiting for your reply ...

  • @swampmonster316 What's the error & stacktrace?

  • Hi Beth!

    The specific error I'm getting when trying to insert a record is, "You must enter a value in the 'OrderDetails.CustomerID' field."

    What could be causing this error and what should I do to remove it ?

    Thanks for replying.

  • The CustomerID field is itself an AutoNumber field, so I should not have to fill that in myself, this should be auto incremented in the TextBox when it retrieves the CustomerID. So the user should just be entering the record and not worry about the CustomerID field which should be displayed as the OrderDetails form loads.

    Please help me understand is I am wrong with this.

  • @swampmonster316 - the CustomerID field will only auto-increment on the parent table (Customer). It needs to be set on the child rows as the foreign key either by the binding source or in code. If you are using the binding source method then you just need to make sure that you set up the relation on the dataset properly (like shown above) AND you need to make sure you drag the RELATED data source onto the form -- in your case you would expand the customer and see Order under that. You don't need to set any fields manually if you use a binding source and it will take care of setting the foreign keys for you.

    HTH,

    -B

  • Hi Beth!

    I really appreciate the time you take to reply to everyone you can!

    I am facing two nagging, frustrating issues at the moment:

    1. As shown above, I did exactly like you show, I setup the relation in the dataset properly; then dragged the RELATED Order as DataGridView control. Then I coded the Save button as:

           Me.Validate()

           'Call EndEdit on all BindingSources!

           Me.ParentTableBindingSource.EndEdit()

           Me.ChildTableBindingSource.EndEdit()

           Me.TableAdapterManager.UpdateAll(Me.MyDataSet1)

    On clicking the Save button; no child rows are being added for the customer. No exceptions and errors are thrown. Copy to Output Directory is set to 'copy if newer'. I can also add a new customer and see it physically present in the database when I open it. So no issues with 'Copy to Output Directory'.

    Also, as the Save button is clicked, the control passes back to the Main form and the newly added child row or rows should be visible in the Lixtbox on the Main form. What am I doing wrong here ?? Please help.

    2. Every time I select a new Customer from the ListBox on the main form, sometimes I get two different errors: 'Cannot clear table Customer because ForeignKeyConstraint CustomerTableOrderTable enforces constraints and there are child rows in OrderTable.' at --->>>Table.Clear()

    in the code below:

               If ClearBeforeFill Then

                   Table.Clear()

               End If

               Return Adapter.Fill(Table)

    While at other times, it gives me a completely different error: 'Object reference not set to an instance of an object.' at --->>>Dim CustomerID As String = CustomerListBox.SelectedValue.ToString

    in the code below:

           Dim CustomerID As String = CustomerListBox.SelectedValue.ToString

           CustomerTableAdapter.FillByCustomerID(MyDataSet1.CustomerTable, CustomerID)

           OrderTableAdapter.FillByCustomerID(MyDataSet1.OrderTable, CustomerID)

    Can you please help me understand these two errors and how I will correct them ?

    Very grateful to you !

  • Hi Beth!

    I am having a totally different behavior now. I set up the updates to the database like you show in this video: msdn.microsoft.com/.../bb725826 (Updating Related Tables).

    Then I tried to save the new rows created with the DataGridView control with this code:

    Private Function Save() As Boolean

           Dim saved As Boolean = False

           If Me.MyDataSet1.HasChanges Then

               Try

                   Dim customerUpdates() As DataRow = Me.MyDataSet1.Customer.Select("", "", DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)

                   Dim orderUpdates() As DataRow = Me.MyDataSet1.Order.Select("", "", DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)

                   Me.CustomerTableAdapter1.Update(customerUpdates)

                   Me.OrderTableAdapter1.Update(orderUpdates)

                   Dim customerDeletes() As DataRow = Me.MyDataSet1.Customer.Select("", "", DataViewRowState.Deleted)

                   Dim orderDeletes() As DataRow = Me.MyDataSet1.Order.Select("", "", DataViewRowState.Deleted)

                   Me.OrderTableAdapter1.Update(orderDeletes)

                   Me.customerTableAdapter1.Update(customerDeletes)

                   saved = True

               Catch ex As Exception

                   MsgBox(ex.ToString)

               End Try

           End If

           Return saved

       End Function

    and pressing the Save button:

    Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click

           Try

               Me.Validate()

               'Call EndEdit on all BindingSources!

               Me.CustomerBindingSource.EndEdit()

               Me.OrderBindingSource.EndEdit()

               Me.TableAdapterManager.UpdateAll(Me.MyDataSet1)

               If Me.Save Then

                   MsgBox("Your records were saved.")

               End If

           Catch ex As Exception

               MsgBox(ex.ToString)

           End Try

    End Sub

    I noticed this strange behavior: When I try to add rows for the first customer in the ListBox and click the Save button, the rows are being saved back to the database where I can open and verify it. But when I try to pick another customer from the ListBox, I get a strange error which says:

    'Cannot update OrderID; field not updatabale at System.Data.CommonDbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent BatchCommandInfo[]batchCommands,Int32CommandCount.....etc etc...'

    This is a very long error and I have no idea what this is or how to deal with it. I've searched many places but could not find how to deal with this error.

    The second problem still remains when I try to choose another customer from the ListBox and it continues to give me an error:

    'Cannot clear table Customer because ForeignKeyConstraint CustomerTableOrderTable enforces constraints and there are child rows in OrderTable.'  on this line --->>>Table.Clear()

    in the code below:

              If ClearBeforeFill Then

                  Table.Clear()

              End If

              Return Adapter.Fill(Table)

    While at other times, it gives me a completely different error: 'Object reference not set to an instance of an object.'

    on this line --->>> Dim CustomerID As String = CustomerListBox.SelectedValue.ToString

    in the code below:

          Dim CustomerID As String = CustomerListBox.SelectedValue.ToString

          CustomerTableAdapter.FillByCustomerID(MyDataSet1.CustomerTable, CustomerID)

          OrderTableAdapter.FillByCustomerID(MyDataSet1.OrderTable, CustomerID)

    I even tried to fix this by turning off the Constraints to false on the DataSet designer. But nothing seems to be working.

    Beth, please help me resolve these two errors as I am nearing the completion of my application and I'm really stuck in the middle.

    Appreciate all your help, thanks a lot!

  • Hi Beth,

    thank you so much for this great tutorial.

    I have been at the brink of desparation with this stupid DataSet Designer...

  • Hi Swampmonster316,

    It doesn't look like you are using the TableAdapterManager. Take a look at this video which explains how to use this object, it's much easier to deal with than managing the update/insert/deletes yourself:

    msdn.microsoft.com/.../cc138241.aspx

    HTH,

    -Beth

  • Hi, nice tutorial. I am trying to make a simple DB application using SQLCE. When I insert a new row into my dataset and update the dataadapter, it works OK. When I delete the same row, it throws DBConcurrencyException after updating the dataadapter.

    This happens only if I assign primary key to the identity column. Without the primary key it works OK, but I am not able to use the DataRow.Find function, which is vital for my application.

  • hi,

    can you explain to me how to connect ms.access database to wapwinGIS file using visual studio?

  • Thank You Beth - this is still 5 star information and was exactly the guidelines I needed for my project to proceed. I modified it for ODBC and it works the same.

Page 4 of 5 (63 items) 12345