Update a Table using transactions

[João Santa Bárbara]

Hi all,
i have a problem with a transaction.i will tray to explain my problem.
i have a Dataset that i have fill using 2 DataAdapter with a simple query
( all tables with an autonumber).

i have made code for the dataadapter rowupdate to get the @@identity of my
table.
everything works fine.

now is the problem,
i have made a transaction to made this works,
when i do the endcurrent edit of my CurrencyManager, i want to submit the
row to the DataBase.
i have submited the first table and works fine
when i try to submit the second table it throws an exception.
after that every thing is ok

but when i press again the save button
when i´m going to submit the first table again it throws an cocurrency
violation, and the row state
of the row that i´m try to submit have changed the rowstate from Added to
Modified. anyone knows why ??

thsk
JSB

plsea see the code below


so i have made some thing like this

'update Function
dim cmd as SQlClient.CommandBulider ( DataAdapter)
dim cmd1 as SQlClient.CommandBulider ( DataAdapter2)
try

BD.Open
trans = bd.BeginTransaction

DataAdapter.update ( Ds,Table)
' some code that mades a error
' bla bla bla
' in this peace of code ocurs an Error that throws an Exception
DataAdapter2.update ( Ds,Table2)

trans.comit
catch
trans.rollback
finally
bd.close
end try

 

[William Ryan eMVP]

Joao:

Something seems very weird here. If the first update worked, then the
acceptchanges is called and the rowstates of all the rows in the first table
shouldn't inidcate any modifications. So, if you call update successfully,
you can call it 10,00000000 times immediately afterward and nothign will
happen (ok your finger would get tired but you get my point). So, if no new
editing is done and now your rowstate is modified, I can't imagine the
update is working.

So, you call update once on the first table, everything is good, call update
on the second table, it blows up. Now, you don't do any new editing, press
save, which I assume fires update1 then update2, update 1 blows up with a
concurrency exception and the rowstates are changed? I noticed the
exception block rolls back the transaction...have you stepped through and
seen what's happening there b/c there's no visual cue that an exception is
thrown As force of habit, I throw in Debug.Assrt(false,
exceptionVariable.ToString()); so that I'll get visual notification if i
raise an exception in debug mode, but when I put in it release it won't
happen.

Anyway, get back to me on these b/c something definitely seems weird.

Also, just as a matter or preference, may I suggest moving these to stored
procs and implementing your transactions on the back end. It's a much more
robust mechanism.. the whole notion of client side transactions leaves me a
bit skittish and maybe it's just b/c of misuse, but I've heard more horrow
stories about client side transactions failing , or the app blowing up
leaving locks on the table and all sorts of other ghastly stuff. Just a
suggestion.

Cheers,

Bill

 

[João Santa Bárbara]

thks, but i have 2 tables with lots of fields, and doing a Store procedure
is not one of my options.
but i think that microsoft should review this in future versions of ADO.NET

but thks any way,


JSB

 

[William Ryan eMVP]

Ok, the stored proc was only a suggestion (although just out of curiousity,
why aren't you allowed to use stored procs? they are vastly superior in just
about every noteworthy way).

Anyway though, the issue about HasChanges, did you try that? We can
defintely get to the bottom of this but we've got to reduce the number of
unknowns.