Using TableAdapters to Insert Related Data into an MS Access Database
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:
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:
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:
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.
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!
Comments
Anonymous
May 14, 2009
PingBack from http://www.anith.com/?p=37999Anonymous
May 15, 2009
I’ve posted before about how to use TableAdapters to update parent-child (master-detail) relationshipsAnonymous
June 04, 2009
Hi Beth, I'm confused, are the DataSet designer and Data Sources window available in Access?Anonymous
June 04, 2009
Hi grovelli,Sorry I wasn't clear. You can use Visual Studio to connect to Access databases throgh the ADO.NET OleDb client classes. Design your database in Access then use Visual Studio to design the application and use the DataSet designer and Data Sources window in VS to connect to the Access database file.A scenario where this is appropriate is if you are migrating to .NET from Access or VB6 and need to keep the data in Access or if you are creating small business applications. Access databases are multi-user file-based databases so they can't scale or give you the security like SQL Server but a lot of small businesses use them.However for new .NET development I'd suggest using SQL Compact instead (if your application is single-user) or using the free SQL Express version (if you need multi-user). You get more capibilities and SQL integrates better with Visual Studio.HTH,-BAnonymous
June 04, 2009
Try as they might, they will never be able to replace Access for its unrivalled flexibility, topflight reporting engine with pivot tables and pivot charts, tight interface with the rest of the Office suite... ;-)Anonymous
June 26, 2009
Do you think the lack of the code found in this blog is the reason why I keep getting the error "Cannot clear table tblPhone because ForeignKeyConstraint tblPhonetblConsignee enforces constraints and there are child rows in table tblConsignee" every time I delete a parent record (one that is not linked to a child) and then try to re-fill the table adapters? (I am using an Access Database that does not use cascading for deletion, but makes sure that a parent record cannot be deleted if it is linked to a child).Anonymous
June 26, 2009
Hi Andrew,It sounds like you still have abandoned child rows. Make sure when you refill, that you fill the parent table first and then the child table.I'd also try setting the cascade on Updates and Deletes on the DataSet relation (like I show above in the dataset designer).If that doesn't work you may also want to try setting EnforceConstraints on the dataset to False while you are refilling the tables and then set back to True again after the fill. If it fails when you try to re-enable the constraints, look at the data in the dataset and you should see the abandoned child rows. This would mean that you probably need to adjust your TableAdapter query.HTH,-BAnonymous
June 26, 2009
Worked perfectly. You are awesome! Thank you!Anonymous
June 26, 2009
Thanks, my mom thinks so too ;-) Glad you got it working!Anonymous
July 02, 2009
At the partEnhancing the TableAdapter Partial Classes you say: "Right-click on the TableAdapter and select View Code and the partial class file that you can edit will be created for you"I did this on the table icons which are at the bottom of the Form1.vb [Design] tab but it only shows me the existing main form code (without these partial classes).Do you have any idea why this is happening?Anonymous
July 02, 2009
Hi Kostas,You need to right-click on the TableAdapter class definition not the instance on the form. Open the DataSet designer (double-click on the dataset.xsd in the Solution Explorer).HTH,-BAnonymous
July 04, 2009
Thank you very much, for your answer Beth,by right-click on the dataset.xsd in the Solution Explorer and select View code the dataset.vb file shows but it's empty. I typped your code and now the primary key is updated from the database.of cource your way is simpler,Thanx again...Anonymous
July 27, 2009
Thank you Beth for explaining this.Do you happen to have a C# version of the code?MikeAnonymous
August 06, 2009
Hi Beth,I get here by google search. I'm working with MS Access Database 2003 and VS 2005.When I do: "Right-click on the TableAdapter class in the DataSet Designer and select View Code", the partial class file created does not looks like you post it. I even can't see its namespace.It shows the Dataset Partial class instead of TableAdapter Partial class, and the partial class for the tables too. That's what it shows.How can I get the TableAdapter partical class?Can I overwrite the partial class created by default, and paste what you post (namespace and tableadapter partial classes)?I would like VB do it by itself as you said.I have tried many times many ways with no resultsI appreciate yuor helpRegards,CesarAnonymous
August 06, 2009
Hi Cesar,Did you right-click on the TableAdapter or the DataSet? You can also double-click on the TableAdapter name (under the dataset). You can also just copy the code into the partial class file I have above, including the tableadapter namespace.HTH,-BAnonymous
August 06, 2009
Hi Beth!,Nice to have you response!In the DataSet Designer, I select the table then I right-click on the TableAdapter, Which is beneath the list of columns name of the table, and finally select View code.My main question is how can I get the TableAdapter Class code?I appreciate you help.Regards,Anonymous
August 06, 2009
Try double-clicking on the TableAdapter. I have a feeling you aren't selecting it first.Anonymous
August 06, 2009
Thanks Beth, I'll try it later and carefully bacuase I still have the same problem.I appreciate your time.Regards,Cesar.Anonymous
August 06, 2009
No problem, Cesar. You can also just copy the code (including the namespace) directly into the partial class file.Anonymous
August 07, 2009
Beth,I just realize that i don't have the "TableAdapterManager" that appears in your form.I also have the problem with "If pk IsNot Nothing AndAlso pk.Count = 1 Then" sentence. The propery count does not appear. And with "AccessIDHelper.SetPrimaryKey(Me.Transaction, e)". The Transaccion propery/method does not appear too.Do you think the TableAdapterManager would be the problem?How do i get it in my form?Thanks inadvance for your helpRegards,Anonymous
August 07, 2009
The comment has been removedAnonymous
August 10, 2009
The comment has been removedAnonymous
August 10, 2009
Hi Beth!Many thanks for your answers.I' try to move on the next version of VS. In the mid time 'I'll try to figure out how to do it with I have.Again, many thanks for you time,CesarAnonymous
September 28, 2009
Hi really nice explanation.I have the same question like Mike before. How we can do this in C#?thanksSenolAnonymous
September 28, 2009
The comment has been removedAnonymous
October 05, 2009
In C#, where can you hook up the event handler in the partial class? Don't you need to do that within a method?Thanks,-eAnonymous
October 05, 2009
Hi Eric,Because C# doesn't have declarative event handlers, you need to manually add an event handler in the EndInit method. See this walkthrough for more information:http://msdn.microsoft.com/en-us/library/ms171930.aspxHTH,-BAnonymous
October 05, 2009
OK, so I tried looking for a virtual or partial method in the generated TableAdapter class that I could use to do the event hookup. It doesn't look like there is one. (Were TableAdapters a product of the VB team? Certainly this blog is the best documentation that exists.)Anyway, I gave it up and just created new methods which must be explicitly called from elsewhere in the application. Here is the result in C# (which is still giving me problems... see below):namespace WpfTest.RfpsTableAdapters { public partial class RfpsTableAdapter { public void HookUpEvents() { this._adapter.RowUpdated += new OleDbRowUpdatedEventHandler(_adapter_RowUpdated); } private void _adapter_RowUpdated(object sender, OleDbRowUpdatedEventArgs e) { AccessIDHelper.SetPrimaryKey(this.Transaction, e); } } public partial class ChargesTableAdapter { public void HookUpEvents() { this._adapter.RowUpdated += new OleDbRowUpdatedEventHandler(_adapter_RowUpdated); } private void _adapter_RowUpdated(object sender, OleDbRowUpdatedEventArgs e) { AccessIDHelper.SetPrimaryKey(this.Transaction, e); } } public class AccessIDHelper { public static void SetPrimaryKey(OleDbTransaction trans, OleDbRowUpdatedEventArgs e) { if ((e.Status == System.Data.UpdateStatus.Continue) && (e.StatementType == System.Data.StatementType.Insert)) { System.Data.DataColumn[] pk = e.Row.Table.PrimaryKey; if ((pk != null) && (pk.Length == 1)) { OleDbCommand cmdGetIdentity = new OleDbCommand("SELECT @@IDENTITY", trans.Connection, trans); e.Row[pk[0]] = (int)(cmdGetIdentity.ExecuteScalar()); e.Row.AcceptChanges(); } } } }}It compiles, but now I'm getting a NullReferenceException because this.Transaction is null. Not sure why yet...Anonymous
October 05, 2009
Oops, it looks like we were typing simultaneously. Thanks for the fast reply.Thanks to that link, I see what I was missing (and will now share so that no one else has to slap their forehead with so much vigor)... there is no EndInit() method in the generated code, but it is present on System.Data.DataSet, so you can just override it.I'll repost a clean version of the code above once I make sure it works.Anonymous
October 05, 2009
The comment has been removedAnonymous
October 05, 2009
It looks like the TableAdapter already has a parameterless constructor, and it isn't marked as virtual or override. Can I still make a constructor? Or are you thinking that I could make a new constructor with a different definition, like: public TableAdapter(bool HookUpStuff) ?Thanks,EricAnonymous
October 05, 2009
Unfortunately, I'm still having some problems with the transaction being null. From looking at the designer code, I can't see where the Transation/_transaction value would be set... any ideas?Is the syntax OK? Is this.Transaction the C# equivalent of Me.Transaction in VB? I seem to recall reading something about "Me" having a bit of extra functionality.Thanks,EricAnonymous
October 08, 2009
It would be great if some one get this working in C#....Anonymous
October 09, 2009
Hi Eric,Remember that this is a partial class (I even forget sometimes) not a child class, there's no inheritance here. You just have two files for a single class definition in this case. So unfortunately a constructor approach won't work either. You need to have this in a init method or a method that runs first in order to hook up the events properly. That may be why your transaction is null, but that doesn't seem right.VB hooks up the handlers for you when the class is constructed with the Handles syntax so you need to find a way to do the same. Maybe the C# forums can help. I'll try to play with this and let you know if I can find a way to get it working in C#.Other option is to have a VB data access layer ;-)-BAnonymous
October 14, 2009
I've used Oracle for years and am pretty good with SQL but I am new to Visual Studio 2008 and VB in general so I am experimenting. I'm creating a contacts application with three tables, "Family", "Member", and "Communication". I have one Master-Detail form with with the Family as the Parent and Member as child data. I set it up as Family being basic information of the family and a DataGridView showing individual members of the family with other information like birthday, etc...I'm trying to conduct a Search routine for Family. I've got most of the code from one of your videos but because I have foreign key constraints it's not working. It gives me the following error:"Cannot clear table Family because ForeignKeyConstraint FK_Member_Family enforces constraints and there are child rows in Member."In another comment on this page you told someone to set EnforceConstraints to false, which I did and it works but when I set it back to True it gives me this error:"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."I perform my search by putting code on the Double-Click to Enter Search Mode, and then perform the Search on the KeyDown (enter key). One problem I am seeing is I think I need to clear out the DataGridView when I enter search mode (double-click). And then when I perform the search, have it automatically fill in the DataGridView.I hope I am clear on this.Thanks and your videos are great and very helpful, please add more.Anonymous
October 20, 2009
Wow, this is frustrating...It looks like the code I posted above works using a Winforms app built using VS2008. Unfortunately for me, I'm trying to play with VS2010 using WPF.I'm coming to the conclusion that data binding in WPF will be the bane of my existence. There are so many levels of poorly-documented indirection that dozens of trips through the generated code (after disabling Just-My-Code, of course) still leave me no closer to figuring out what is going on here.Anyway, thanks for posting a great article on how to deal with Access. Hopefully the C# version will be of use to someone.Anonymous
October 22, 2009
Hi Eric,I don't see why this code wouldn't work with WPF data binding. The code above is more about the DAL, not the UI data binding. You may want to check out these videos to see if they help:http://msdn.microsoft.com/en-us/vbasic/bb466226.aspx#wpfdataHTH,-BAnonymous
October 28, 2009
I have been troubled by this problem(parent child table update) for a very long time.Although I don't use sql ce db,I find this article particularly useful.And the key point are:Set the relation in the DataSet designer to "Both Relation and Foreign Key Constraint" and then set the Update and Delete rules to Cascade2.Make sure that EndEdit is called on the CategoriesBindingSource before a new product can be inserted into the DataGridView.Thanks a lot!Anonymous
November 04, 2009
Thank you! I solved my ODBCDataSet's "identity crisis" with your IDHelper method... Just had to change the OleDb* objects to Odbc*.Anonymous
November 24, 2009
Hi,am using VS 2005 and get this errors:Error 1 Handles clause requires a WithEvents variable defined in the containing type or one of its base types. D:Visual Studio 2005ProjectsPeLoggPeLoggPelletsdatabasUtvecklingDataSet.vb 7 53 PeLoggError 2 'Transaction' is not a member of 'PeLogg.peloggLeveranserRaderDataTable'. D:Visual Studio 2005ProjectsPeLoggPeLoggPelletsdatabasUtvecklingDataSet.vb 9 38 PeLoggfor each Partial Public ClassDo I have to do something diffrent in VS2005?Anonymous
November 28, 2009
Here some code to do the same thing for Microsoft SQL Server Compact EditionPartial Public Class JOBSTableAdapter Private Sub _adapter_RowUpdated(ByVal sender As Object, _ ByVal e As System.Data.SqlServerCe.SqlCeRowUpdatedEventArgs) _ Handles _adapter.RowUpdated SQLCEIDHelper.SetPrimaryKey(Me.Connection, e) End Sub End Class Public Class SQLCEIDHelper ''' <summary> ''' Retrieves the primary key autonumber values from SQL CE ''' </summary> ''' <remarks></remarks> Public Shared Sub SetPrimaryKey(ByVal conn As SqlCeConnection, _ ByVal e As SqlCeRowUpdatedEventArgs) 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 SqlCeCommand("SELECT @@IDENTITY", conn) ' Execute the post-update query to fetch new @@Identity Dim id As Integer id = CInt(cmdGetIdentity.ExecuteScalar) e.Row(pk(0)) = id e.Row.AcceptChanges() End If End If End Sub End ClassAnonymous
December 01, 2009
Thanks Mikhail Mozolin,I also wrote an article on how to do this with SQL CE here:http://blogs.msdn.com/bethmassi/archive/2009/09/15/inserting-master-detail-data-into-a-sql-server-compact-edition-database.aspxHTH,-BAnonymous
December 02, 2009
The comment has been removedAnonymous
February 03, 2010
The comment has been removedAnonymous
February 09, 2010
First of all thank you for this article Beth!Like Eric i tried to use your solution for Access in C#.But i also ran into the problem with transaction = null and even Merlin's way didn't work.Now, for all those with the same problem:Use the solution Eric provided but in SetPrimaryKey just the Connection and the Transaction from the OleDbRowUpdatedEventArgsThen you have to initialize the OleDbCommand like:OleDbCommand cmdGetIdentity = new OleDbCommand("SELECT @@IDENTITY", e.Command.Connection, e.Command.Transaction);Regards,BastianAnonymous
July 03, 2010
Hi, I am unable to insert,update or delete data.I am using Visual STudio Express EditionAnonymous
September 14, 2010
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 IdItemFK IdItem <---/ ItemName QtyWhen 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,SergeiAnonymous
April 21, 2011
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'Anonymous
November 20, 2011
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 SubAnd 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 SubI 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 ...Anonymous
November 20, 2011
@swampmonster316 What's the error & stacktrace?Anonymous
November 20, 2011
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.Anonymous
November 20, 2011
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.Anonymous
November 21, 2011
@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,-BAnonymous
November 22, 2011
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: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.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.ToStringin 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 !Anonymous
November 23, 2011
The comment has been removedAnonymous
November 24, 2011
Hi Beth,thank you so much for this great tutorial.I have been at the brink of desparation with this stupid DataSet Designer...Anonymous
November 28, 2011
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.aspxHTH,-BethAnonymous
November 28, 2011
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.Anonymous
May 23, 2012
hi,can you explain to me how to connect ms.access database to wapwinGIS file using visual studio?Anonymous
March 13, 2013
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.Anonymous
August 08, 2013
The comment has been removedAnonymous
February 03, 2014
I have a problem, I have coded a program over the last 5 years using VB2008 and MS Access 2003.I ran into problems with the "memo" field as I needed a large field to hold necessary information, sence a version of SQL Server came with the VS 2008 program I installed and then reallized that the Express version was more to my liking, I installed it and attempted to get data into it. I started changing the program where necessary using DTS for the data, shortly I realized that there were problems with data transfer, I then noticed that all the other programs I had written for Access 2003 were having problems and somehow in the SQLSERVER area, , I still do not no why?. I then purchased a copy of MS Office 2010 and converted the 2003 to the 2010. Now the VB program seems to have a problem with the Tableadapter as after I converted all the SQL Server requirements to Access 2010 I have over 100 (I assume that is the number of them in the program) Tableadapter errors (Not recognizeing them) I am not sure were to go from here. I used Import, as well as Dim statements and nothing seems to workAnonymous
May 07, 2014
Hi,For the life of me I cannot get the child table to update, this never gets triggered.Partial Public Class PropertyAmtsTableAdapter 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 ClassAny Ideas?Anonymous
November 20, 2014
Respected Sir,How to get this exact ID value to a textbox ?Yours faithfullyMurulimadhavAnonymous
February 08, 2015
Partial Public Class UniformsDataTable Private Sub _adapter_RowUpdated(ByVal sender As Object, _ ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles _adapter.RowUpdated AccessIDHelper.SetPrimaryKey(Me.transaction, e) End Subthis must be wrong ... i got an argument like this"transaction is not a member of blahhhhh"Anonymous
April 09, 2015
Terrific article, I searched all afternoon for the answer to this problem. REALLY glad I stumbled on your article. Greatly appreciate the time you took to write it,Anonymous
June 22, 2015
The comment has been removedAnonymous
June 22, 2015
Debugging more i have come to "trans" is "nothing"Anonymous
December 04, 2015
My datagrid view is not related with my parents details. Something is missing. I can't find the example. Please helpAnonymous
December 04, 2015
I can't find the example on CodeGallery