TableAdapters and Transactions

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

Wed, 18 Jul 2007 02:27:20 GMT

PingBack from http://blogs.msdn.com/bethmassi/archive/2007/07/10/working-with-tableadapters-related-datatables-and-transactions.aspx

re: TableAdapters and Transactions

Graham Edwards — Fri, 20 Jul 2007 01:59:55 GMT

I am very new to all this data adapter stuff and I think your videos and blogs are great, but I am stuck on one thing to do with related tables and it is driving me crazy. Can you help me? Plllllease????

I need to simply update an invoice header and invoice item table, but I am getting an error giving me a foreign key violation (HEADERID in item table) even though I have followed your procedures.

Here is my code can you tell me where i am going wrong? As I said HEADERID in the header is related to HEADERID in the item table.

Dim o_InvoiceDataService As New DataService.Invoices

Dim lobjHeader As New DataService.InvoicesTableAdapters.GDax_AuditInvoiceHeaderTableAdapter

lobjHeader.SetConnectionString(My.Settings.cash_sheetConnectionString)

lobjHeader.Fill(o_InvoiceDataService.GDax_AuditInvoiceHeader)

Dim lobjItem As New DataService.InvoicesTableAdapters.GDax_AuditInvoiceItemTableAdapter

lobjItem.SetConnectionString(My.Settings.cash_sheetConnectionString)

lobjItem.Fill(o_InvoiceDataService.GDax_AuditInvoiceItem)

' Declare a variable named newInvoicesHeaderRow of type HeaderRow.

Dim newInvoicesHeaderRow As DataService.Invoices.GDax_AuditInvoiceHeaderRow

' Assign the new HeaderRow that is returned to the newInvoicesHeaderRow variable.

newInvoicesHeaderRow = o_InvoiceDataService.GDax_AuditInvoiceHeader.NewGDax_AuditInvoiceHeaderRow

' ***** Set the value of the columns in the new HeaderRow. ******

newInvoicesHeaderRow.ACCOUNT_REF = "PP"

' ***** Add the new HeaderRow to the HeaderDataTable ******

o_InvoiceDataService.GDax_AuditInvoiceHeader.AddGDax_AuditInvoiceHeaderRow(newInvoicesHeaderRow)

' Declare a variable named newInvoicesItemRow of type ItemRow.

Dim newInvoicesItemRow As DataService.Invoices.GDax_AuditInvoiceItemRow

' Assign the new ItemRow that is returned to the newInvoicesItemRow variable.

newInvoicesItemRow = o_InvoiceDataService.GDax_AuditInvoiceItem.NewGDax_AuditInvoiceItemRow

' ***** Set the value of the columns in the new ItemRow. ******

newInvoicesItemRow.NET_AMOUNT = 46

' ***** Add the new ItemRow to the ItemDataTable ******

o_InvoiceDataService.GDax_AuditInvoiceItem.AddGDax_AuditInvoiceItemRow(newInvoicesItemRow)

lobjHeader.Update(o_InvoiceDataService.GDax_AuditInvoiceHeader)

lobjItem.Update(o_InvoiceDataService.GDax_AuditInvoiceItem)

re: TableAdapters and Transactions

Beth Massi — Tue, 24 Jul 2007 22:43:40 GMT

Hi Graham,

You need to make sure that the relation is set up properly on your DataSet as I explain here: http://blogs.msdn.com/bethmassi/archive/2007/07/10/working-with-tableadapters-related-datatables-and-transactions.aspx

This is because when you insert a new parent, the database fills its primary key and that key needs to be filled in the child's foreign key field before the child row is inserted. ADO.NET will do this for you (between the update statements) if you set up your relation properly, indicating that it's a FK constraint and to Cascade updates and deletes.

HTH,

-B

re: TableAdapters and Transactions

Graham Edwards — Fri, 27 Jul 2007 23:05:43 GMT

Thankyou very much. I looked at your videos again and solved all my problems. I have been looking for weeks for a site like yours.

I think it is fantastic and feel confidence in using all these new features in the right way.

I just wish I had come across it sooner, would have saved me hours of wasted time trying to get things to work.

Forms Over Data Video Series - Two More Videos Released

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

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

re: TableAdapters and Transactions

Warren — Mon, 27 Aug 2007 07:38:03 GMT

Great series!! I really love the Forms Over Data videos. Very informative especially for a somelike like me which came from access and vb6.

I have been making a payroll project and the series has been my guide and im wondering if you can help me with one problem. I have a parent detail dataset which i have related properly. And i have created a update stored procedure for both the parent and detail table which has a "Where (PayrollID = @PID) And (EmployeeID = @EID) And (TableRowView = @Original_TableRowView)" clause on the timestamp column. I followed the above transaction. The problem is i always get a concurrency error when i save the detail table. If i comment out the timestamp Where clause it updates succesffuly. Any ideas?

Thanks

re: TableAdapters and Transactions

Bob Bergan — Thu, 30 Aug 2007 07:58:40 GMT

The TransactionScope example fails for me. I'm using Express. Is that the reason? Any way around it? I'll provide specifics, if you like, or is Express hopeless for Transaction processing?

re: TableAdapters and Transactions

Beth Massi — Sat, 08 Sep 2007 03:21:01 GMT

Hi Bob,

SQL- Express should work. Make absolutely sure that the connection reference is the same for all your TableAdapters.

-B

re: TableAdapters and Transactions

Beth Massi — Sat, 08 Sep 2007 03:35:06 GMT

Warren,

Make sure that your Update statement also contains a ;SELECT after it to reselect the updated timestamp back into the DataSet. This should happen automatically when you use the designer. i.e.

UPDATE [dbo].[Customer] SET [LastName] = @LastName, [FirstName] = @FirstName, [Address] = @Address, [City] = @City, [State] = @State, [ZIP] = @ZIP WHERE (([CustomerID] = @Original_CustomerID) AND ([Modified] = @Original_Modified));

SELECT CustomerID, LastName, FirstName, Address, City, State, ZIP, Modified FROM Customer WHERE (CustomerID = @CustomerID)

HTH,

-B

re: TableAdapters and Transactions

Warren — Tue, 11 Sep 2007 15:55:18 GMT

thanks for the response, unfortunately the detail part is still not updating. the Master record seems to be updating fine but the whole transaction rollback because the detail is not updating. I tried using the designer generated code but still get a exception saying a concurrency violation, the update command affeced 0 of the expected 1 record. If i leave out the timestamp field from the where clause on the detail records the updates succeeds. Any ideas?

Warren

re: TableAdapters and Transactions

Beth Massi — Wed, 12 Sep 2007 02:54:59 GMT

Warren,

Does it work if you don't use stored procs and instead just use the TableAdapter designer generated insert/update/delete statements?

re: TableAdapters and Transactions

Warren — Sat, 15 Sep 2007 08:37:18 GMT

Hi beth,

Using the table generated adapter designer statements also fails to update, - "concurrency violation, the update command affected 0 of the expected 1 record."

Warren

re: TableAdapters and Transactions

Beth Massi — Tue, 25 Sep 2007 20:52:04 GMT

Hi Warren,

Does the sample application attached to this post work for you? Are you using a timestamp field in SQL server or are you using a datetime?

I would open a trace in SQL profiler and watch the update statement. It sounds like the database row is being updated before your dataset's row is updated. Is it possible you have a trigger firing somewhere?

You also may want to ask your question on the forums because more people may be able to respond.

http://forums.microsoft.com/msdn/default.aspx?forumgroupid=10&siteid=1

re: TableAdapters and Transactions

Warren — Fri, 28 Sep 2007 13:50:29 GMT

Hi Beth,

Yes your example works perfectly but your example does not use store proc. to update the tables and yes i'm using timestamp fields. I haved looked at the SQL profiler and although the update fails the timestamps get updated. Anyway thank you so much for taking time to respond to my questions. I wil check out the forum...

More power,

Warren

re: TableAdapters and Transactions

Warren — Sun, 30 Sep 2007 06:26:56 GMT

Hi Beth,

Dang, I finnaly solved it!!! hahaha... stupid rookie mistake!!!. It was the Cascade update on the primary key that was breaking the transaction. I have a text based document number which i use as primary key and in my update sp updates this col which then cascade updates the details table and changes thier timestamp col hence the concurrency error. I rewrote the sp to not update the document no. which in the first place should never have been editable after inserting and the update transaction now works!! hehehe.

Thank you so much beth, would never have done it without you. Your form over data video series have been really a great help for me coming from vb6.access. More power to you..

Warren

re: TableAdapters and Transactions

Everton — Fri, 09 Nov 2007 16:45:33 GMT

Hello,

if I insert (in a new order) more than one product the line below starts a infinite loop and raises an overflow exception.

"For Each detail As Order_DetailsRow In details"

(method Order_DetailsDataTable_ColumnChanged, file OrdersDataSet.vb)

What could be wrong?

I am using VS2005 and SqlServer2005 Express.

Regards

re: TableAdapters and Transactions

Sam — Sat, 01 Dec 2007 06:10:18 GMT

Hi Beth,

First of all, I would said that this article is excellent and thank god that I found this website.

I seems not to be able to solve the dbconcurrency or the delete and update - "update command affected 0 of the expected 1 record." problems when working with related Tables.

I followed your previous(first part) of this article and my problems seems to go away at least for now but that problem seems to come back occasionally. I will received that error occasionally when start working only with new inserted records.

I would like to implement this second part of tutorial but I'm working with MS Access Database. Maybe you could post an article similar to this but intended for MS Access Database.

Thanks

Sam

re: TableAdapters and Transactions

Ed — Thu, 03 Jan 2008 22:24:29 GMT

Hi Beth:

Wow, you certainly understand dataadapters! I too am having issues with "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records" and only occurs when I add a new row to my dataset and attempt to use the Update method on my dataadapter. I have read about 20 different resolutions to this problem but none of them seem to apply to my situation. I did add a partial class to trap the RowUpdated event on the data adapter so that I could insert the Primary Key value into my dataset (using a Stored Procedure on SQL Server 2005) but that didn't solve my problem either. I am running out of solutions on resolving this problem and it's probably something benign and not obvious.

re: TableAdapters and Transactions

Beth Massi — Mon, 07 Jan 2008 23:12:08 GMT

Hi Ed,

If the parent fails to insert correctly then the child will also fail. It's important to do this inside a transaction like I explain above so that the integrity of your data stays intact. To troubleshoot the exact problem use SQL Profiler to see if there are failing triggers or other messages being returned from your database.

HTH,

-B

The New TableAdapterManager in Visual Studio 2008

Beth Massi - Sharing the goodness that is VB — Tue, 08 Jan 2008 06:53:12 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:14:48 GMT

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

re: TableAdapters and Transactions

Terry Chng — Sat, 01 Mar 2008 18:49:04 GMT

May i know is that any way to do this in vb.net 2003, the concept and the purpose is totally what i want to do but i using the visual studio 2003. Please help up... Your help are highly appreciated.

re: TableAdapters and Transactions

somashekar — Wed, 26 Mar 2008 15:56:12 GMT

content is fantastic. it help me a lot , thank you Beth Missi

re: TableAdapters and Transactions

Ben Curnow — Tue, 23 Dec 2008 06:01:29 GMT

Thanks Beth,

I have been trying for ages to implement batch transactions but none of the MS examples use strongly typed datasets and I could never work out how to set the transaction object. I never would have thought of partial classes.

Now my users won't keep getting upset because the data is inconsistent.

Ben

re: TableAdapters and Transactions

Ian — Wed, 08 Apr 2009 17:48:26 GMT

Thanks for this piece of Code. All other options I have seen are written in C#. I had to modify it for oracle which was easy. I did run into one problem which had me stumped for a few hours. I am using custom insert commands and it kept giving me an obscure error. I finally adapted the code for the partial class to following:

Dim CC_CNT As Integer

Me.Connection = conn

If trans IsNot Nothing Then

Me.Adapter.InsertCommand.Transaction = trans

Me.Adapter.DeleteCommand.Transaction = trans

Me.Adapter.UpdateCommand.Transaction = trans

For CC_CNT = 0 To Me.CommandCollection.Length - 1

Me.CommandCollection(CC_CNT).Transaction = trans

End If

This put transaction on all the commands in the command collections as well as the generic update/insert and delete. Hope this helps others