I have a situation that does not fit into the tableadapter mold that I'm looking for some opinions on. I have two datatables. One is a parent (CheckInOutDetail) and one is the child to that parent (CheckInOutUnit)on one form. The parent lists products to be shipped.the child lists the available serial numbers to be shipped (if that particular product is serialized). Initially,both tables are loadedwith the list of items to ship and available serial numbers. The user then fills in an empty column in the CheckInOutDetailDataTable with the quantities of each item listed to be shipped and they check off an empty checkboxcolumn in the checkInOutunitDataTable to indicate which of the serial numbers are to be shipped. when both are saved I want a new record for each row in these tables to be inserted to the database with the information. i've been able to achieve the first part of this (without the serial numbers) by assigning an "insert" SPROC to the update method of the first table. however, the problem with this is that the second table needs to know which record from the first table each record is associated with and since they have not yeat been created, their identity number does not yet exist. typically, using the standard "insert" method for the datatable, the tableadapter would hande this by waiting to get the returned identity on the first table before inserting rows from the second table. however, in my scenario, since the table is already losded, none of the rows are recignized as being "new" because they already exist. however, they are recognized as needing to be updated when I type a quantity in the empty column.

Any idea's how best to work around this? Should I continue using the "update" method? If so, how do I get the identities from the first table to use in the second table. Or, is there a way to manually mark certain rows as new then use the "insert" method and will that solve my problem?

here is an example: (the NULL values are what the use will fill in.
CHECKINOUTDETAIL
OrderHeaderIDQuantityOrderQuantityProductNumber
8NULL3 SP1
8NULL3 SP2
8NULL3SP3
8NULL2SP2
8NULL1SP1

CHECKINOUTUNIT
OrderHeaderIDQuantityOrderQuantityProductNumber ProductID
8NULL1SP1 1
8NULL2SP1 1
8NULL3SP1 1
8NULL1SP2 2
8NULL2SP2 2
8NULL3SP2 2
8NULL1SP3 3
8NULL2SP3 3
8NULL3SP3 3
8NULL1SP2 2
8NULL2SP2 2
8NULL3SP2 2
8NULL1SP1 1
8NULL2SP1 1
8NULL3SP1 1

There may be a better answer to your question ... but I find that using straight SQL or stored procedure calls provides much more flexibility than using TableAdapters.

I have an example of using straight SQL here:

http://msmvps.com/blogs/deborahk/archive/2009/08/21/dal-save-data-using-a-sql-statement.aspx

I have an example of using straight stored procedures here:

http://msmvps.com/blogs/deborahk/archive/2009/08/14/dal-save-data-using-a-stored-procedure.aspx

Hope this helps.

---

www.insteptech.com ; msmvps.com/blogs/deborahk
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

If you look closely at this link:

http://msmvps.com/blogs/deborahk/archive/2009/08/21/dal-save-data-using-a-sql-statement.aspx

You can see that it returns the primary key id generated by the insert operation.

You can then use that Id as a parameter when you perform the insert on the child records.

Hope this helps.

---

www.insteptech.com ; msmvps.com/blogs/deborahk
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

There may be a better answer to your question ... but I find that using straight SQL or stored procedure calls provides much more flexibility than using TableAdapters.

I have an example of using straight SQL here:

http://msmvps.com/blogs/deborahk/archive/2009/08/21/dal-save-data-using-a-sql-statement.aspx

I have an example of using straight stored procedures here:

http://msmvps.com/blogs/deborahk/archive/2009/08/14/dal-save-data-using-a-stored-procedure.aspx

Hope this helps.

---

www.insteptech.com ; msmvps.com/blogs/deborahk
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

I have considered that option and had experimented with that method initially. However, the problem still stands of trying to determine the CheckInOutDetailID values to insert into the CheckInOutUnit table to relate the two tables as these values are not yet defined at the time the user enters the data. I've gotten around this before by using

(

IDENT_CURRENT('schema.tablename')

However this only works when there is one parent row and several child rows. In my current scenario, I have several parent rows and several child rows. Perhaps this is more of a sql question. However, I think the best solution is to run the insert statement on the checkinoutdetail table and be sure the stored procedure for the insert contains a select statement at the end to refresh the data. At this point we will now have the identity values in question. The problem then becomes how to get theses vales passed to the correct rows of the checkinoutunit table.

If you look closely at this link:

http://msmvps.com/blogs/deborahk/archive/2009/08/21/dal-save-data-using-a-sql-statement.aspx

You can see that it returns the primary key id generated by the insert operation.

You can then use that Id as a parameter when you perform the insert on the child records.

Hope this helps.

---

www.insteptech.com ; msmvps.com/blogs/deborahk
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!