Working with TableAdapters and Related DataTables

TableAdapters and Transactions

Beth Massi - Sharing the goodness that is VB — Thu, 12 Jul 2007 03:34:15 GMT

In my last post I showed you how to save related parent-child DataTables in proper update, insert and

Link Listing - July 11, 2007

Christopher Steen — Thu, 12 Jul 2007 06:13:53 GMT

Announcing the LunchTimerCoder.com Initiative [Via: Chris Pietschmann ] Building a PC, Part I [Via:...

Forms Over Data Video Series - Two More Videos Released

Beth Massi - Sharing the goodness that is VB — Thu, 02 Aug 2007 02:47:10 GMT

I just released two more videos ( #15 , #16 ) in the Forms Over Data video series . One is on understanding

re: Working with TableAdapters and Related DataTables

katiebird — Tue, 07 Aug 2007 21:50:58 GMT

Hi Beth,

I've been enjoying your series and trying to follow using my own database.

I'm getting an error about trying to convert a 1-dimensional array of system.data.datarow to a system.data.datarow -- AND I can't get past it.

Do you have any idea where I might be goofing up?

In the Meantime, I'll redo the example using Northwind.

Thanks for the series, it's terrific!

re: Working with TableAdapters and Related DataTables

Beth Massi — Fri, 10 Aug 2007 20:55:23 GMT

Katiebird,

I can't tell what's wrong unless you post the code. Sounds like you have some erroneous parentheses after your DataRow variable. You might want to ask this question on the forums: http://forums.microsoft.com/msdn/showforum.aspx?forumid=32&siteid=1

Cheers,

-B

Using Data Across Multiple Windows Forms

Beth Massi - Sharing the goodness that is VB — Mon, 01 Oct 2007 22:44:12 GMT

Recently I've had more than a few questions about how to handle working with data across multiple forms.

re: Working with TableAdapters and Related DataTables

Larson — Thu, 29 Nov 2007 20:05:51 GMT

Hi Beth,

I'm working through your FormsOverData series as an intro to SQL Server. I love it - I wish something like this had been around when I was cutting my teeth with Access!

I thought I had my head around Parent-Child Relationships, then I ran into this problem with referential integrity which I can reproduce very easily using your OMS application.

If I take Form2 from your OrderManager3 project, and enable referential integrity, it still all works fine. However, if I comment out the Fill calls in Form2_Load, then add a Button which does this:-

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Me.CustomerTableAdapter.FillByGetOne(Me.CustomerOrdersDataSet.Customer)

Me.OrdersTableAdapter.Fill(Me.CustomerOrdersDataSet.Orders)

End Sub

[FillByGetOne just gets the Customer record where CustomerID = 1]

So, I'm just pulling one Cust record down, but the Fill for the OrdersTableAdapter fails with an integrity violation. I'm really puzzled as to why. I'm sure its something very simple but I can't figure it out, even from your excellent series of videos.

Thanks for reading this.

re: Working with TableAdapters and Related DataTables

Beth Massi — Thu, 29 Nov 2007 21:48:01 GMT

Hi Larson,

When you enable referential integrity on the dataset that means the for every child there MUST be a parent. You need to make sure to only pull the records down for that same one customer on the orders table as well. Take a look at this video on how to handle saving as well: http://msdn2.microsoft.com/en-us/vbasic/bb725826.aspx

HTH,

-B

re: Working with TableAdapters and Related DataTables

Larson — Fri, 30 Nov 2007 14:32:28 GMT

Of course, I see that now. My mistake was that just because I only *see* the Orders belonging to a single Customer, I'd assumed that's all that was being brought down. But of course it's not. All the Order records come down, and what I see is controlled by the BindingAdapter. Correct?

Thanks for the invaluable pointers as always.

re: Working with TableAdapters and Related DataTables

Beth Massi — Mon, 10 Dec 2007 20:46:39 GMT

Hi Larson,

You can control what records are returned by adding a paramertized query on the TableAdapter. I show how to do that here: http://blogs.msdn.com/bethmassi/archive/2007/05/25/creating-a-parameterized-query.aspx

Cheers,

-B

re: Working with TableAdapters and Related DataTables

George Gomez — Mon, 17 Dec 2007 22:05:11 GMT

Please I need A form with the code ro sendd emial anywhere

The New TableAdapterManager in Visual Studio 2008

Beth Massi - Sharing the goodness that is VB — Tue, 08 Jan 2008 06:53:16 GMT

In my previous post on TableAdapters and Transactions I showed a couple techniques on how to perform

The New TableAdapterManager in Visual Studio 2008

Noticias externas — Tue, 08 Jan 2008 07:15:03 GMT

In my previous post on TableAdapters and Transactions I showed a couple techniques on how to perform

re: Working with TableAdapters and Related DataTables

Don Tinsley — Mon, 11 Feb 2008 07:38:17 GMT

I am banging my head against the wall trying to correctly get VB 2008 to update a related table. My form/data are setup almost identical to how they were in both 'updating related tables' vidoes. My problem is that when I add a record, the primary key of the parent stays -1 and perpetuates to the child. So when the tableadaptermanager updates with the dataset, the parent is put in and assigned a new (autonumber) id, but the child table rows are put in with -1 as the id. I am using Access 2003 and don't know if that is the problem.

re: Working with TableAdapters and Related DataTables

Beth Massi — Mon, 11 Feb 2008 20:13:14 GMT

Hi Don,

I'm not an Access developer but I do know that it's a bit of work inserting rows since Access does not support batch SQL statements. What the TableAdapters do for you when you create your DataSets against SQL Server is they generate an additional statement after the INSERT command that is used to retrieve the SCOPE_IDENTITY().

In Access (using the Jet Provider 4.0) you will need to issue a separate command (SELECT @@IDENTITY) after each row is inserted in order to retrieve the identity. You can do this by adding a handler in each TableAdapter to the _adapter.RowUpdated.

Here's some information that may help explain: http://msdn2.microsoft.com/en-us/library/ms971502.aspx

HTH,

-B

re: Working with TableAdapters and Related DataTables

Ben — Sun, 06 Apr 2008 15:13:46 GMT

testing... last post didn't appear...

re: Working with TableAdapters and Related DataTables

Ben — Sun, 06 Apr 2008 15:23:30 GMT

Ok that's weird. I posted this message before and it didn't appear (I got redirected to your homepage), but my previous test post did work... Anyway, here goes again...

Firstly, Beth, great article. Exactly what I've been looking for, thanks!

I had not set relations in the dataset designer to cascade integrity, and was hence struggling with parent/child relationships being lost after executing tableadapter.update() on the parent table.

Now I'm past that, can you help with my next problem please? I'm guessing I'm doing something silly...

Basically, after inserting the parent row, I try to insert the child row (which appears to now be correctly referencing its parent).

However, the transaction commits ok, but the child record is nowhere to be seen in the SQL database (the parent is visible there). Do you know what I'm doing wrong?

On the last couple of attempts to post, I included a code summary... but left it out this time in the hope that my message gets accepted.

Thanks,

Ben.

re: Working with TableAdapters and Related DataTables

Ben — Sun, 06 Apr 2008 15:54:51 GMT

I've worked out why the second update - childTableTableAdapter.update(ds.childRow) - isn't working.

It looks as though the first update for the parent row - parentTableTableAdapter.update(ds.parentRow) - causes the rowstate on the child row to change from "added" to "unchanged".

I've no idea why that's happening though. Any thoughts?

re: Working with TableAdapters and Related DataTables

Ben — Sun, 06 Apr 2008 16:52:10 GMT

Hmmm. Rank amateur.

I had Accept/Reject rule on the relationship set to cascade as well...

Thanks again for getting me headed in the right direction!!

re: Working with TableAdapters and Related DataTables

Dermot Heslin — Mon, 21 Apr 2008 19:53:00 GMT

Hi Beth,

Thanks for this article - it was very helpful in setting up related DataTables in my current project.

One point that might be helpful for developers - if you're not using drag-and-drop databinding, you need to link the related records in code using <childrow>.SetParentRow(<parentrow>). Took me a while to figure that out!

Beth Massi - Sharing the goodness that is VB : Working with TableAdapters and Related DataTables

Dating — Sun, 01 Jun 2008 10:02:01 GMT

This past couple weeks I've been asked to help out a few people in the community work through a couple snags they were having with respect to the TableAdapters and how they should work in a multi-user application when updating related parent-child tables

Beth Massi - Sharing the goodness that is VB : Working with TableAdapters and Related DataTables

Weddings — Fri, 06 Jun 2008 03:11:08 GMT

re: Working with TableAdapters and Related DataTables

Asaf — Tue, 24 Jun 2008 23:40:59 GMT

Great article! I have not succueded to find a good explenation on this confusing subject until found your one.

thanks a lot

re: Working with TableAdapters and Related DataTables

Frans van Ek — Mon, 07 Jul 2008 17:55:59 GMT

Hi beth,

thanks for all your video's, I've learned a lot from them. I'm currently combining the video's to work with a middle tier and updating related tables.

(i've created a tool to generate the webservice from the datamanager so they are always in sink) i think it's cool.

one thing i'm a bit confused about, is when you say you dont want to load the whole table in the dataset when you don't need all of the data.

I can understand that. So in the query we make a filtering using a @parameter. But wat is the best way to get the related childs loaded in the related child table.

Do we have to loop through the parent table and load the corresponding childs? perhaps not with a loop but triggert by an event?

Or is the best way to create a fill method on the childtable wich is based on same filter as we've used to fill the parenttable?

thanks again

Frans

re: Working with TableAdapters and Related DataTables

Beth Massi — Mon, 21 Jul 2008 22:57:46 GMT

Hi Frans,

When developing a distributed application you always need to balance the number of calls you are making to your middle-tier with the amount of data you want to return. In general, it's better to bring down a larger set of data than to make multiple repeated calls to the middle-tier. This is because even the simple act of making the call can be expensive. So looping may not be the best way to go about it.

However, you definitely don't want to clog the wire with a lot of data especially if you don't really need it on the client.

So you need to look for the right balance by doing perf and load testing. Try to break down each entity into a manageable set of data and test the performance and scalability. For instance you may have a form that works with a single order and its order details. That would probably be a reasonable set of data to bring down in one call.

Also take a look at caching lookup tables and other read-only data on the client to help scalability. There's an easy way to add local caching to the client in VS2008. Here's a video on that technology: http://msdn.microsoft.com/en-us/vbasic/cc307991.aspx

HTH,

-Beth

re: Working with TableAdapters and Related DataTables

Nurlan — Sun, 25 Jan 2009 02:07:04 GMT

Hi Beth,

I am developing an app with Visual c#. But I am stuck at this point:

System.Data.SqlClient.SqlCommandBuilder cb;

cb = new System.Data.SqlClient.SqlCommandBuilder(daNI);

DataRow dRow = dsNI.Tables["TaskNI"].NewRow();

dRow[1] = taskTitle;

dRow[2] = taskText;

dRow[3] = taskDate;

dsNI.Tables["TaskNI"].Rows.Add(dRow);

daNI.Update(dsNI, "TaskNI");

So this is my code. My database table has 5 columns 1st and 2nd tables are primary and foreign keys respectively. 3rd and 4th columns are nullable

Error says: connection cannot be initiated. But when i tried to insert string where it should be datetime it recognizes that that column has to be datetime. So i think problem is arising from updating primary and foreign keys. How do we update foreign key?

thanx

re: Working with TableAdapters and Related DataTables

Beth Massi — Fri, 13 Feb 2009 18:44:26 GMT

Hi Nurlan,

If the error says that the connection cannot be initiated then it's something wrong with the database connection. Check your connection string and make sure you've assigned a connection to the data adapter.

HTH,

-B

re: Working with TableAdapters and Related DataTables

Rachel — Wed, 18 Feb 2009 21:24:47 GMT

Hi Beth,

I followed your tutorials fine for updating two related tables (many thanks!) but am running into problems going from two to three related tables – can you help? I’m new to VB2008 and it is probably something obvious! A simple example (using the Northwind database):

I drag three datagridviews onto one form: Customers (main node), Orders (related node) and Order Details (related node under Orders).

Orders is a child of Customers, and Order Details is a child of Orders.

I’ve created Relation and FK Constraints with Cascade Update and Delete rules for both relations. Hierarchical update is set to true.

I add the following code (below) to the form, but when I run the code attempt to save a new customer with a new order and order details I get the error: ‘You cannot add or change a record because a related record is required in table ‘Orders’.

Private Sub Form1_Load_(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Me.CustomersTableAdapter.Fill(Me.NwindDataSet.Customers)

Me.OrdersTableAdapter.Fill(Me.NwindDataSet.Orders)

Me.Order_DetailsTableAdapter.Fill(Me.NwindDataSet.Order_Details)

End Sub

Private Sub OrdersBindingSource_AddingNew(ByVal sender As Object, ByVal e As System.ComponentModel.AddingNewEventArgs) Handles OrdersBindingSource.AddingNew

Me.CustomersBindingSource.EndEdit()

End Sub

Private Sub Order_DetailsBindingSource_AddingNew(ByVal sender As Object, ByVal e As System.ComponentModel.AddingNewEventArgs) Handles Order_DetailsBindingSource.AddingNew

Me.OrdersBindingSource.EndEdit()

End Sub

Private Sub CustomersBindingSourceBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomersBindingSourceBindingNavigatorSaveItem.Click

Me.Validate()

Me.CustomersBindingSource.EndEdit()

Me.OrdersBindingSource.EndEdit()

Me.Order_DetailsBindingSource.EndEdit()

Me.TableAdapterManager.UpdateAll(Me.NwindDataSet)

End Sub

re: Working with TableAdapters and Related DataTables

james p — Fri, 20 Mar 2009 00:16:25 GMT

Wondered if you ever managed to resolve this problem? I found that when working with three or more related tables that i couldn't get the TableAdapterManager to work.

My only workaround was sending in the updates, changes and deletes in code as you had to in vb2005 (before the TableAdapterManager was created).

I used the code from Beth's video here

http://msdn.microsoft.com/en-us/vbasic/bb725826.aspx

i came into even more problems trying to do all this across different forms and keeping them all in sync!

re: Working with TableAdapters and Related DataTables

Kris — Sat, 09 May 2009 00:28:33 GMT

I too have had trouble using the tableadapter to update multiple related tables all at once! I'm really hoping there is some sort of easy "duhhh" thing going on here because i dont want to have to manually code everything. Althought at this point, after a month of chugging through possible issues...im about ready to do it. Has ANYONE figured out how to get the tableadaptermanager to work using three or more (nested) related tables?

re: Working with TableAdapters and Related DataTables

Waner Michaud — Fri, 15 May 2009 00:11:51 GMT

Hi Beth,

Can you guide me on how to get a child form to save along with the record that I parsed from a parent form..

the parent form is registration form, from the registration record to open a case form.

Please look at the codes below:

Private Sub OpenCaseFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OpenCaseFile.Click

Dim c5 As New Cases

c.Reg_IDLabel1.Text = Me.Reg_IDLabel1.Text

c.Complainant_IDLabel1.Text = Me.Complainant_IDComboBox.Text

c.Offender_IDLabel1.Text = Me.Offender_IDComboBox.Text

c.Commission_IDLabel1.Text = Me.Commission_IDLabel1.Text

c.ShowDialog()

End Sub

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

this is the save from function for the child form:

Private Function Save() As Boolean

Dim saved As Boolean = False

If Me.RegisCaseFileDataSet.HasChanges Then

Try

'Send Adds/Updates in Parent-Child order

Dim RegisUpdates() As DataRow = _

Me.RegisCaseFileDataSet.Registration.Select("", "", _

DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)

Dim CasesUpdates() As DataRow = _

Me.RegisCaseFileDataSet.Case_file.Select("", "", _

DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)

Me.RegistrationBindingSource.EndEdit()

Me.RegistrationTableAdapter.Update(RegisUpdates)

Me.ComplainantsBindingSource.EndEdit()

Me.ComplainantsTableAdapter.Update(CasesUpdates)

Me.OffendersBindingSource.EndEdit()

Me.OffendersTableAdapter.Update(CasesUpdates)

Me.CasefileBindingSource.EndEdit()

Me.Case_fileTableAdapter.Update(CasesUpdates)

'Send Deletes in Child-Parent order

Dim CasesDeletes() As DataRow = _

Me.RegisCaseFileDataSet.Case_file.Select("", "", _

DataViewRowState.Deleted)

Dim RegisDeletes() As DataRow = _

Me.RegisCaseFileDataSet.Registration.Select("", "", _

DataViewRowState.Deleted)

'Child

Me.Case_fileTableAdapter.Update(CasesDeletes)

Me.CasefileBindingSource.EndEdit()

'Parent

Me.RegistrationBindingSource.EndEdit()

Me.RegistrationTableAdapter.Update(RegisDeletes)

Me.ComplainantsBindingSource.EndEdit()

Me.ComplainantsTableAdapter.Update(CasesDeletes)

Me.OffendersBindingSource.EndEdit()

Me.OffendersTableAdapter.Update(CasesDeletes)

saved = True

Catch ex As Exception

MsgBox(ex.ToString)

End Try

End If

Return saved

End Function

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

I have set the relationship correctly, but I cannot get Reg_ID, Complainant_id, Offender_ID to be pushed into the case_file table..

thanks,

Best Regard

Using TableAdapters to Insert Related Data into an MS Access Database

Beth Massi - Sharing the goodness that is VB — Fri, 15 May 2009 03:48:02 GMT

I’ve posted before about how to use TableAdapters to update parent-child (master-detail) relationships

Using TableAdapters to Insert Related Data into an MS Access Database

Visual Studio Data — Sat, 16 May 2009 04:54:30 GMT

I’ve posted before about how to use TableAdapters to update parent-child (master-detail) relationships

re: Working with TableAdapters and Related DataTables

rpms2002 — Wed, 22 Jul 2009 09:20:32 GMT

I cannot figure out how to use the TableAdaptors to update Parent-child records when the Parent has been filtered i.e. with employees my Table Adaptor Selects employees that were not terminated.

When I have a child table say EmployeeAvailability, when setting its table adaptor I have to apply the parent filter to ensure I only select child records that have a parent record selected.

Now the child table adaptor will not automatically generate the Insert, Update or delete within the adaptor.

Can anyone point me to some examples of how this real world scenario is handled.

cheers

Kieran

re: Working with TableAdapters and Related DataTables

Beth Massi — Wed, 22 Jul 2009 23:32:32 GMT

Hi rpms,

The designer cannot generate commands automatically if you are using multiple tables in your select statement. In essence you are creating a "view" of your data and because there could be many different ways to update the backend tables, the designer wouldn't know what to generate.

That said, because you aren't selecting anything different in the results than columns from the child table there is a trick that you can do.

Use the dataset designer to generate all the commands as you normally would when you don't specify a filter on the child table. Then go into the Properties window and select the SelectCommand.CommandText property and write your join and filter condition to the parent table. As long as you do not modify the fields that are being selected this should work. When the designer asks if you want to re-generate the update commands answer NO.

HTH,

-B

re: Working with TableAdapters and Related DataTables

Beth Massi — Thu, 23 Jul 2009 03:05:47 GMT

Hi rpms,

I wrote up a blog post on how a couple different ways you can do this real world scenario:

http://blogs.msdn.com/bethmassi/archive/2009/07/22/filtering-child-datatables-using-tableadapters.aspx

I hope this answers your question.

Cheers,

-B

re: Working with TableAdapters and Related DataTables

Italian Cousin — Wed, 09 Sep 2009 22:59:38 GMT

I'm trying to make a master-detail insert in a local SQL CE database, updating through a TableAdapterManager.

But I'm getting huge problems.

Please help me looking here:

http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/e66a081c-f622-4fe7-bf56-2828f04f9f48/

Thanx

re: Working with TableAdapters and Related DataTables

Beth Massi — Wed, 09 Sep 2009 23:13:31 GMT

Hi Italian Cousin,

You're going to need to do something similar as Access in this case since SQL CE doesn't support batch statements. Take a look at this post for more info:

http://blogs.msdn.com/bethmassi/archive/2009/05/14/using-tableadapters-to-insert-related-data-into-an-ms-access-database.aspx

HTH,

-B