Using TableAdapters to Insert Related Data into an MS Access Database

re: Using TableAdapters to Insert Related Data into an MS Access Database

PatO — Wed, 13 Mar 2013 17:56:10 GMT

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.

re: Using TableAdapters to Insert Related Data into an MS Access Database

fefed — Wed, 23 May 2012 17:37:54 GMT

hi,

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

re: Using TableAdapters to Insert Related Data into an MS Access Database

Matt — Tue, 29 Nov 2011 14:35:01 GMT

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.

re: Using TableAdapters to Insert Related Data into an MS Access Database

Beth Massi — Tue, 29 Nov 2011 01:25:45 GMT

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

re: Using TableAdapters to Insert Related Data into an MS Access Database

Michael — Thu, 24 Nov 2011 17:16:23 GMT

Hi Beth,

thank you so much for this great tutorial.

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

re: Using TableAdapters to Insert Related Data into an MS Access Database

swampmonster316 — Thu, 24 Nov 2011 12:27:40 GMT

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!

re: Using TableAdapters to Insert Related Data into an MS Access Database

swampmonster316 — Wed, 23 Nov 2011 08:41:45 GMT

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 !

re: Using TableAdapters to Insert Related Data into an MS Access Database

Beth Massi — Tue, 22 Nov 2011 01:11:59 GMT

@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

re: Using TableAdapters to Insert Related Data into an MS Access Database

swampmonster316 — Mon, 21 Nov 2011 05:32:50 GMT

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.

re: Using TableAdapters to Insert Related Data into an MS Access Database

swampmonster316 — Mon, 21 Nov 2011 05:26:42 GMT

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.