|
|
You're in the
sql server programming
group:TableAdapterManger with UpdateAll to cascade key updates
sql server programming:
relationships? In case you are not familiar with the TableAdapterManager, this question also applies to TableAdapter.Update(). We've been unable to get parent-child relationships to update when calling update on a tableadapter which persists them to a local database cache. I.E. when the primary key is set to autoIncrement, the primary key updates in the cache but the relationship does not cascade up through the table adapter to rows which have not yet been saved. When the table adapter attempts to save those child rows, they fight constraints. Do we have to stop the update, poll the data for the changed key ourselves, and update the dataset before allowing the table adapter to continue? P.S. I put this in another place, but apparently had not created a no-spam
Hello mudnug,
I'm not sure I have understood your issue very clearly. According to your description, your dataset has two tables (Parent-Child relationship). The parent key of the parent table is set to AutoIncrement in your underling database. Your .net application inserted a new row (for example: Primary key is 100) into the Parent table. Then, your application inserted some rows (Foreign key is 100) into the Child Table. When updating, because the primary key in your underling database is AutoIncrement, the Primary key has been changed to 10. But the Foreign key in the child rows still persists as "100". Thereby, you received a Foreign Key Constraint when updating the child row. You need a solution to update the foreign key of child rows when updating. If you misunderstood anything here, please don't hesitate to correct me. In order to understand the issue clearly, would you please also let me know if your application is developed by VS 2005? (or VS 2008 beta 2). As far as I know, TableAdapterManager is not a component in VS 2005. But, VS 2005 IDE generates a TableAdatper class for us after we created a Typed DataSet. I'm not sure what VS you are using. (VS 2005 or VS 2008 beta2?). Please clarify the above issues for me. I will follow up. It's my pleasure to assist you. Have a great day, Best regards, Wen Yuan Microsoft Online Community Support ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
Dear WenYuan Wang,
You understood the question perfectly. In the particular case, there are several child tables. We are using VS 2008 beta 2. Thanks for your response. I hope the clarification will allow someone to provide an additional response. [quoted text, click to view] "WenYuan Wang [MSFT]" wrote:
> Hello mudnug, > > I'm not sure I have understood your issue very clearly. > > According to your description, your dataset has two tables (Parent-Child > relationship). The parent key of the parent table is set to AutoIncrement > in your underling database. Your .net application inserted a new row (for > example: Primary key is 100) into the Parent table. Then, your application > inserted some rows (Foreign key is 100) into the Child Table. When > updating, because the primary key in your underling database is > AutoIncrement, the Primary key has been changed to 10. But the Foreign key > in the child rows still persists as "100". Thereby, you received a Foreign > Key Constraint when updating the child row. You need a solution to update > the foreign key of child rows when updating. If you misunderstood anything > here, please don't hesitate to correct me. > > In order to understand the issue clearly, would you please also let me know > if your application is developed by VS 2005? (or VS 2008 beta 2). As far as > I know, TableAdapterManager is not a component in VS 2005. But, VS 2005 IDE > generates a TableAdatper class for us after we created a Typed DataSet. I'm > not sure what VS you are using. (VS 2005 or VS 2008 beta2?). > > Please clarify the above issues for me. I will follow up. It's my pleasure > to assist you. > Have a great day, > Best regards, > > Wen Yuan > Microsoft Online Community Support > ================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. >
Hello Mudnug,
Thanks for your clarifying. In ADO.net 2.0, this is a common issue with DataAdapter. But I'm very sure about VS 2008 beta2. The final RTM version has not been released so far. Hope the below method also works fine in your scenario. As you may know, to successfully submit the new rows in Child Table, we need to retrieve the new auto-increment values for the new primary key (by SCOPE_IDENTITY()), apply those values to the appropriate child rows (by UpdateRule), and then submit the Child Table to the database. This process sounds complicated, but it's actually fairly simple. For typed dataset, what we have to do is to retrieve auto-increment value after insert operation. INSERT INTO Table_1 (c2, c4, c5, c6) VALUES (@c2,@c4,@c5,@c6) select @c1 = SCOPE_IDENTITY() Then, add the primary key as output parameter @c1. At last, we need to set the DataRelation in the DataSet designer to "Both Relation and Foreign Key Constraint", and then set the Update and Delete rules to Cascade. For detailed information, you may refer to the following article. http://blogs.msdn.com/bethmassi/archive/2007/07/10/working-with-tableadapter s-related-datatables-and-transactions.aspx [Working with TableAdapters and Related DataTables] I also suggest you may consider using transaction when want all rows updated consistently. http://blogs.msdn.com/bethmassi/archive/2007/07/11/tableadapters-and-transac tions.aspx [TableAdapters and Transactions] Hope this helps, please let me know if this is what you need. I will follow up. We are glad to assist you. Have a great day, Best regards, Wen Yuan Microsoft Online Community Support ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
Sorry, I should correct the following line.
[quoted text, click to view] >But I'm very sure about VS 2008 beta2. But I'm NOT very sure about VS 2008 beta2.Thanks. Wen Yuan Microsoft Online Community Support ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
The latest (beta 2) version of the software tools include a
TableAdapterManger with an UpdateAll method. Does it do none of the work of properly inserting foreign keys? I am not aware of any reason why a properly designed database would not have such keys. I understand that the UpdateAll method removed the need to insert, update, and delete each datatable in the proper order (it is now code generated in the UpdateAll method). If the implementation of TableAdapterManger.UpdateAll does not support keys, can anyone suggest a way of modifying the partial classes to include the needed functionality, or suggest how to construct a macro that would do this? Thanks for your guidance, WenYuan Wang. [quoted text, click to view] "WenYuan Wang [MSFT]" wrote:
> Hello Mudnug, > Thanks for your clarifying. > > In ADO.net 2.0, this is a common issue with DataAdapter. But I'm very sure > about VS 2008 beta2. The final RTM version has not been released so far. > Hope the below method also works fine in your scenario. > > As you may know, to successfully submit the new rows in Child Table, we > need to retrieve the new auto-increment values for the new primary key (by > SCOPE_IDENTITY()), apply those values to the appropriate child rows (by > UpdateRule), and then submit the Child Table to the database. This process > sounds complicated, but it's actually fairly simple. > > For typed dataset, what we have to do is to retrieve auto-increment value > after insert operation. > INSERT INTO Table_1 (c2, c4, c5, c6) VALUES (@c2,@c4,@c5,@c6) > select @c1 = SCOPE_IDENTITY() > > Then, add the primary key as output parameter @c1. > > At last, we need to set the DataRelation in the DataSet designer to "Both > Relation and Foreign Key Constraint", and then set the Update and Delete > rules to Cascade. > > For detailed information, you may refer to the following article. > http://blogs.msdn.com/bethmassi/archive/2007/07/10/working-with-tableadapter > s-related-datatables-and-transactions.aspx > [Working with TableAdapters and Related DataTables] > > I also suggest you may consider using transaction when want all rows > updated consistently. > http://blogs.msdn.com/bethmassi/archive/2007/07/11/tableadapters-and-transac > tions.aspx > [TableAdapters and Transactions] > > > Hope this helps, please let me know if this is what you need. I will follow > up. We are glad to assist you. > > Have a great day, > Best regards, > > Wen Yuan > Microsoft Online Community Support > ================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. >
Hello Mudnug,
Thanks for your reply. I have tried VS 2008 beta 2 on my test box. There are not too many changes in Typed DataSet of VS 2008. Would you please try the steps as below to check if this method works on your side? 1) Right-click the TableAdapter of the Parent Table in TypedDataSet.xsd file. 2) Select "Configure..." in context menu. 3) Click "Advanced Options..." button in the " TableAdapter Configuration Wizard". 4) Please make sure you have checked the checkbox before "Refresh the data table". (Thereby, TableAdatper will fetch the data after updates underling database.) 5) After that, please click "finish" button to close the Configuration Wizard. 6) Then, please double-click each relation in your TypedDataSet.xsd file. 7) Please set the DataRelation in the DataSet designer to "Both Relation and Foreign Key Constraint", and then set the "Update" and "Delete" rules to "Cascade".(as the screen-shooting. http://blogs.msdn.com/photos/bethmassi/images/3804145/500x480.aspx) Then, please use the TableAdapterManager to updateALL your typed dataset again. I think the error message should go away. Hope this helps. Let me know if you face any further issue or there is anything unclear. We are glad to assist you. Have a great day, Best regards, Wen Yuan Microsoft Online Community Support ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
The ideas you present are the ways to enable the much sought-after features.
In this case however, we are using Sql Server CE. "SQLCE does not support batch SQL, and so, multiple SELECT statements cannot be executed and their results cannot be automatically populated in multiple DataTable objects in a DataSet." It looks like no simple solution exists. [quoted text, click to view] "WenYuan Wang [MSFT]" wrote:
> Hello Mudnug, > Thanks for your reply. > > I have tried VS 2008 beta 2 on my test box. There are not too many changes > in Typed DataSet of VS 2008. > > Would you please try the steps as below to check if this method works on > your side? > > 1) Right-click the TableAdapter of the Parent Table in TypedDataSet.xsd > file. > 2) Select "Configure..." in context menu. > 3) Click "Advanced Options..." button in the " TableAdapter Configuration > Wizard". > 4) Please make sure you have checked the checkbox before "Refresh the data > table". (Thereby, TableAdatper will fetch the data after updates underling > database.) > 5) After that, please click "finish" button to close the Configuration > Wizard. > 6) Then, please double-click each relation in your TypedDataSet.xsd file. > 7) Please set the DataRelation in the DataSet designer to "Both Relation > and Foreign Key Constraint", and then set the "Update" and "Delete" rules > to "Cascade".(as the screen-shooting. > http://blogs.msdn.com/photos/bethmassi/images/3804145/500x480.aspx) > > Then, please use the TableAdapterManager to updateALL your typed dataset > again. I think the error message should go away. > > Hope this helps. Let me know if you face any further issue or there is > anything unclear. We are glad to assist you. > > Have a great day, > Best regards, > > Wen Yuan > Microsoft Online Community Support > ================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. >
Hello Mudnug,
Thanks for your feedback. Have a great day. Best regards, Wen Yuan Microsoft Online Community Support ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |