|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Problems call DataAdapter.Update and ConcurrencyI wonder if anyone can explain what is happening here? I have a dataadapter that I am using to correlate the changes in an SQL Table to a DataTable. I create a new DataRow then call .Update and acceptchanges, all works fine. The problem I have is when I try to amend some of the data and the call the Update again. This time I get a Concurrency error, which is understandable as the data has changed since I initially created the table but is not the desired outcome and I was expecting that after accepting changes can calling the .Fill the concurrency conflict would be resolved. Is there a way to get the adpater to update the changed rows. I have tried calling .Fill after the update but this does not seem to help. Ideally I would like to work on the datatable and not have to repopulate from the database every time I update a cell/row. Thanks for any help you can give. Steve Here is the code it use: Try _tradeAdapter.Update(_detailsTable) _detailsTable.AcceptChanges() _tradeAdapter.Fill(_detailsTable) Catch exTran As DBConcurrencyException MsgBox("There has been a conflict error on this trade, please refresh your details and check", MsgBoxStyle.Critical, "Update Conflict") s.Logging.ApplicationLog.LoggingLevels.ApplicationError) End Try Sorry, I did not call the .Fill correctly, it works if i do this, however,
the question still remains of whether this can be done without calling the ..fill and avoiding another trip to the database. Thanks again Steve Show quote "Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in message news:eWIId59MHHA.992@TK2MSFTNGP04.phx.gbl... > Hi all, > > I wonder if anyone can explain what is happening here? > I have a dataadapter that I am using to correlate the changes in an SQL > Table to a DataTable. I create a new DataRow then call .Update and > acceptchanges, all works fine. The problem I have is when I try to amend > some of the data and the call the Update again. This time I get a > Concurrency error, which is understandable as the data has changed since I > initially created the table but is not the desired outcome and I was > expecting that after accepting changes can calling the .Fill the > concurrency conflict would be resolved. > > Is there a way to get the adpater to update the changed rows. I have > tried calling .Fill after the update but this does not seem to help. > Ideally I would like to work on the datatable and not have to repopulate > from the database every time I update a cell/row. > > Thanks for any help you can give. > > Steve > > Here is the code it use: > Try > _tradeAdapter.Update(_detailsTable) > _detailsTable.AcceptChanges() > _tradeAdapter.Fill(_detailsTable) > Catch exTran As DBConcurrencyException > MsgBox("There has been a conflict error on this trade, please refresh your > details and check", MsgBoxStyle.Critical, "Update Conflict") > s.Logging.ApplicationLog.LoggingLevels.ApplicationError) > End Try > > I also had Concurrency violation when updating an existing record. Check
the OleDbDataAdapter's UpdateCommand and the OleDbParameters used in that command. See my thread http://groups.google.co.uk/group/microsoft.public.dotnet.framework.adonet/browse_frm/thread/4cf3fd3eabb5b85c/6102ad1bb75554ad for how I solved it. Show quote "Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in message news:OQt6RD%23MHHA.5064@TK2MSFTNGP04.phx.gbl... > Sorry, I did not call the .Fill correctly, it works if i do this, however, > the question still remains of whether this can be done without calling the > .fill and avoiding another trip to the database. > > Thanks again > > Steve > > "Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in message > news:eWIId59MHHA.992@TK2MSFTNGP04.phx.gbl... >> Hi all, >> >> I wonder if anyone can explain what is happening here? >> I have a dataadapter that I am using to correlate the changes in an SQL >> Table to a DataTable. I create a new DataRow then call .Update and >> acceptchanges, all works fine. The problem I have is when I try to amend >> some of the data and the call the Update again. This time I get a >> Concurrency error, which is understandable as the data has changed since >> I initially created the table but is not the desired outcome and I was >> expecting that after accepting changes can calling the .Fill the >> concurrency conflict would be resolved. >> >> Is there a way to get the adpater to update the changed rows. I have >> tried calling .Fill after the update but this does not seem to help. >> Ideally I would like to work on the datatable and not have to repopulate >> from the database every time I update a cell/row. >> >> Thanks for any help you can give. >> >> Steve >> >> Here is the code it use: >> Try >> _tradeAdapter.Update(_detailsTable) >> _detailsTable.AcceptChanges() >> _tradeAdapter.Fill(_detailsTable) >> Catch exTran As DBConcurrencyException >> MsgBox("There has been a conflict error on this trade, please refresh >> your details and check", MsgBoxStyle.Critical, "Update Conflict") >> s.Logging.ApplicationLog.LoggingLevels.ApplicationError) >> End Try >> >> > > JoWilliam,
In glancing at your link, it looks like you "solved" the problem by removing the code that checks for concurrency. What happens if a user updates a row while another user is looking at the row. If the user that is looking at the row updates it, then the first user's updates are overwritten, right? Kerry Moorman Show quote "JoWilliam" wrote: > I also had Concurrency violation when updating an existing record. Check > the OleDbDataAdapter's UpdateCommand and the OleDbParameters used in that > command. > > See my thread > http://groups.google.co.uk/group/microsoft.public.dotnet.framework.adonet/browse_frm/thread/4cf3fd3eabb5b85c/6102ad1bb75554ad > for how I solved it. > > "Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in message > news:OQt6RD%23MHHA.5064@TK2MSFTNGP04.phx.gbl... > > Sorry, I did not call the .Fill correctly, it works if i do this, however, > > the question still remains of whether this can be done without calling the > > .fill and avoiding another trip to the database. > > > > Thanks again > > > > Steve > > > > "Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in message > > news:eWIId59MHHA.992@TK2MSFTNGP04.phx.gbl... > >> Hi all, > >> > >> I wonder if anyone can explain what is happening here? > >> I have a dataadapter that I am using to correlate the changes in an SQL > >> Table to a DataTable. I create a new DataRow then call .Update and > >> acceptchanges, all works fine. The problem I have is when I try to amend > >> some of the data and the call the Update again. This time I get a > >> Concurrency error, which is understandable as the data has changed since > >> I initially created the table but is not the desired outcome and I was > >> expecting that after accepting changes can calling the .Fill the > >> concurrency conflict would be resolved. > >> > >> Is there a way to get the adpater to update the changed rows. I have > >> tried calling .Fill after the update but this does not seem to help. > >> Ideally I would like to work on the datatable and not have to repopulate > >> from the database every time I update a cell/row. > >> > >> Thanks for any help you can give. > >> > >> Steve > >> > >> Here is the code it use: > >> Try > >> _tradeAdapter.Update(_detailsTable) > >> _detailsTable.AcceptChanges() > >> _tradeAdapter.Fill(_detailsTable) > >> Catch exTran As DBConcurrencyException > >> MsgBox("There has been a conflict error on this trade, please refresh > >> your details and check", MsgBoxStyle.Critical, "Update Conflict") > >> s.Logging.ApplicationLog.LoggingLevels.ApplicationError) > >> End Try > >> > >> > > > > > > > Hi,
In my case only one user accesses the database. Your comment has puzzled me a bit though: if the original code generated by the OleDbDataAdapter and DataSet designer has the extra benefit of allowing concurrent access to a multi-user database, could you explain why updating an existing record didn't work in my single user environment? Show quote "Kerry Moorman" <KerryMoor***@discussions.microsoft.com> wrote in message news:22C3913B-D024-4FA4-B305-C2662B5284E4@microsoft.com... > JoWilliam, > > In glancing at your link, it looks like you "solved" the problem by > removing > the code that checks for concurrency. > > What happens if a user updates a row while another user is looking at the > row. If the user that is looking at the row updates it, then the first > user's > updates are overwritten, right? > > Kerry Moorman > > > "JoWilliam" wrote: > >> I also had Concurrency violation when updating an existing record. Check >> the OleDbDataAdapter's UpdateCommand and the OleDbParameters used in that >> command. >> >> See my thread >> http://groups.google.co.uk/group/microsoft.public.dotnet.framework.adonet/browse_frm/thread/4cf3fd3eabb5b85c/6102ad1bb75554ad >> for how I solved it. >> >> "Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in message >> news:OQt6RD%23MHHA.5064@TK2MSFTNGP04.phx.gbl... >> > Sorry, I did not call the .Fill correctly, it works if i do this, >> > however, >> > the question still remains of whether this can be done without calling >> > the >> > .fill and avoiding another trip to the database. >> > >> > Thanks again >> > >> > Steve >> > >> > "Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in >> > message >> > news:eWIId59MHHA.992@TK2MSFTNGP04.phx.gbl... >> >> Hi all, >> >> >> >> I wonder if anyone can explain what is happening here? >> >> I have a dataadapter that I am using to correlate the changes in an >> >> SQL >> >> Table to a DataTable. I create a new DataRow then call .Update and >> >> acceptchanges, all works fine. The problem I have is when I try to >> >> amend >> >> some of the data and the call the Update again. This time I get a >> >> Concurrency error, which is understandable as the data has changed >> >> since >> >> I initially created the table but is not the desired outcome and I was >> >> expecting that after accepting changes can calling the .Fill the >> >> concurrency conflict would be resolved. >> >> >> >> Is there a way to get the adpater to update the changed rows. I have >> >> tried calling .Fill after the update but this does not seem to help. >> >> Ideally I would like to work on the datatable and not have to >> >> repopulate >> >> from the database every time I update a cell/row. >> >> >> >> Thanks for any help you can give. >> >> >> >> Steve >> >> >> >> Here is the code it use: >> >> Try >> >> _tradeAdapter.Update(_detailsTable) >> >> _detailsTable.AcceptChanges() >> >> _tradeAdapter.Fill(_detailsTable) >> >> Catch exTran As DBConcurrencyException >> >> MsgBox("There has been a conflict error on this trade, please refresh >> >> your details and check", MsgBoxStyle.Critical, "Update Conflict") >> >> s.Logging.ApplicationLog.LoggingLevels.ApplicationError) >> >> End Try >> >> >> >> >> > >> > >> >> >> Hi,
"Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in message By default the DataAdapter will call AcceptChanges on each succesfull news:OQt6RD%23MHHA.5064@TK2MSFTNGP04.phx.gbl... > Sorry, I did not call the .Fill correctly, it works if i do this, however, > the question still remains of whether this can be done without calling the > .fill and avoiding another trip to the database. updated row, so you should not call it. Although not necesairly the case here, calling AcceptChanges at the wrong time may even cause a false "Concurrency Violation". If i understand it correctly, you only have this problem when you do : add record, update, modify same record, update and the problem is helped with calling Fill in between. Do you have an autonumber PK column in the DB table, if so , which DB are you using and how are you currently building the insert command (DataAdapter) ? HTH, Greetings Show quote > > Thanks again > > Steve > > "Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in message > news:eWIId59MHHA.992@TK2MSFTNGP04.phx.gbl... >> Hi all, >> >> I wonder if anyone can explain what is happening here? >> I have a dataadapter that I am using to correlate the changes in an SQL >> Table to a DataTable. I create a new DataRow then call .Update and >> acceptchanges, all works fine. The problem I have is when I try to amend >> some of the data and the call the Update again. This time I get a >> Concurrency error, which is understandable as the data has changed since >> I initially created the table but is not the desired outcome and I was >> expecting that after accepting changes can calling the .Fill the >> concurrency conflict would be resolved. >> >> Is there a way to get the adpater to update the changed rows. I have >> tried calling .Fill after the update but this does not seem to help. >> Ideally I would like to work on the datatable and not have to repopulate >> from the database every time I update a cell/row. >> >> Thanks for any help you can give. >> >> Steve >> >> Here is the code it use: >> Try >> _tradeAdapter.Update(_detailsTable) >> _detailsTable.AcceptChanges() >> _tradeAdapter.Fill(_detailsTable) >> Catch exTran As DBConcurrencyException >> MsgBox("There has been a conflict error on this trade, please refresh >> your details and check", MsgBoxStyle.Critical, "Update Conflict") >> s.Logging.ApplicationLog.LoggingLevels.ApplicationError) >> End Try >> >> > > Hi Bart,
Yes there is a PK column that I am returning by using the Rowupdated event for any .Inserted row. I use a command builder for update command as I want to make use of the concurrency exception. I dont know if it is by design for if I have got something wrong, but I can call .update many times as long I don't change the data (pretty pointless) as soon as the data gets changed it causes a concurrency violation. In some ways this is as desired but I wasn't expecting the violation while using the same Connection as the first update. The only solution I can find is to repopulate the datatable using the .fill after each update. This works fine but is costly as the application uses a heavily lagged WAN. Any thoughts? Thanks again, Steve. Show quote "Bart Mermuys" <bmermuys.nospam@hotmail.com> wrote in message news:I_Qoh.281969$WX6.5836931@phobos.telenet-ops.be... > Hi, > > "Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in message > news:OQt6RD%23MHHA.5064@TK2MSFTNGP04.phx.gbl... >> Sorry, I did not call the .Fill correctly, it works if i do this, >> however, the question still remains of whether this can be done without >> calling the .fill and avoiding another trip to the database. > > By default the DataAdapter will call AcceptChanges on each succesfull > updated row, so you should not call it. Although not necesairly the case > here, calling AcceptChanges at the wrong time may even cause a false > "Concurrency Violation". > > If i understand it correctly, you only have this problem when you do : add > record, update, modify same record, update and the problem is helped with > calling Fill in between. Do you have an autonumber PK column in the DB > table, if so , which DB are you using and how are you currently building > the insert command (DataAdapter) ? > > HTH, > Greetings > > >> >> Thanks again >> >> Steve >> >> "Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in message >> news:eWIId59MHHA.992@TK2MSFTNGP04.phx.gbl... >>> Hi all, >>> >>> I wonder if anyone can explain what is happening here? >>> I have a dataadapter that I am using to correlate the changes in an SQL >>> Table to a DataTable. I create a new DataRow then call .Update and >>> acceptchanges, all works fine. The problem I have is when I try to >>> amend some of the data and the call the Update again. This time I get a >>> Concurrency error, which is understandable as the data has changed since >>> I initially created the table but is not the desired outcome and I was >>> expecting that after accepting changes can calling the .Fill the >>> concurrency conflict would be resolved. >>> >>> Is there a way to get the adpater to update the changed rows. I have >>> tried calling .Fill after the update but this does not seem to help. >>> Ideally I would like to work on the datatable and not have to repopulate >>> from the database every time I update a cell/row. >>> >>> Thanks for any help you can give. >>> >>> Steve >>> >>> Here is the code it use: >>> Try >>> _tradeAdapter.Update(_detailsTable) >>> _detailsTable.AcceptChanges() >>> _tradeAdapter.Fill(_detailsTable) >>> Catch exTran As DBConcurrencyException >>> MsgBox("There has been a conflict error on this trade, please refresh >>> your details and check", MsgBoxStyle.Critical, "Update Conflict") >>> s.Logging.ApplicationLog.LoggingLevels.ApplicationError) >>> End Try >>> >>> >> >> > > Hi,
"Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in message Sounds ok, you already verified you're getting back the right key (DB news:O6DORDDNHHA.1912@TK2MSFTNGP02.phx.gbl... > Hi Bart, > > Yes there is a PK column that I am returning by using the Rowupdated event > for any .Inserted row. > I use a command builder for update command as I want to make use of the > concurrency exception. generated key) after update for new records...? > I dont know if it is by design for if I have got something wrong, but I I'm a bit confused here, in your OP you did mention a certain sequence > can call .update many times as long I don't change the data (pretty > pointless) as soon as the data gets changed it causes a concurrency > violation. (add,update,ammend,update) but here you are only talking about changing data. When exactly do and don't you have this concurrency violation exception. > In some ways this is as desired but I wasn't expecting the violation while I don't get what you mean with "same connection" and not expecting a > using the same Connection as the first update. The only solution I can > find is to repopulate the datatable using the .fill after each update. > This works fine but is costly as the application uses a heavily lagged > WAN. concurrency violation. To be clear, the optimistic concurrency control will cause a concurrency violation exception when the record has been changed in the DB by something else/other between the last fill and current update. You could extend your code a bit: Try _tradeAdapter.Update(_detailsTable) Catch ex As DBConcurrencyException Console.WriteLine( ex.Message ) If ( ex.Row.RowState = DataRowState.Modified ) Then For Each col As DataColumn In ex.Row.Table.Columns Console.WriteLine( "{0} oldvalue={1} newvalue={2}", _ col.ColumnName, _ ex.Row( col, DataRowVersion.Original ), _ ex.Row( col, DataRowVersion.Current ) ) Next End If End Try When you get a violation exception, you can compare the old values with the current values in the DB and if they are different then it's normal you get a concurrency violation, wrong keys may also cause this exception. HTH, Greetings Show quote > > Any thoughts? > > Thanks again, > > Steve. > > > > "Bart Mermuys" <bmermuys.nospam@hotmail.com> wrote in message > news:I_Qoh.281969$WX6.5836931@phobos.telenet-ops.be... >> Hi, >> >> "Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in message >> news:OQt6RD%23MHHA.5064@TK2MSFTNGP04.phx.gbl... >>> Sorry, I did not call the .Fill correctly, it works if i do this, >>> however, the question still remains of whether this can be done without >>> calling the .fill and avoiding another trip to the database. >> >> By default the DataAdapter will call AcceptChanges on each succesfull >> updated row, so you should not call it. Although not necesairly the case >> here, calling AcceptChanges at the wrong time may even cause a false >> "Concurrency Violation". >> >> If i understand it correctly, you only have this problem when you do : >> add record, update, modify same record, update and the problem is helped >> with calling Fill in between. Do you have an autonumber PK column in the >> DB table, if so , which DB are you using and how are you currently >> building the insert command (DataAdapter) ? >> >> HTH, >> Greetings >> >> >>> >>> Thanks again >>> >>> Steve >>> >>> "Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in message >>> news:eWIId59MHHA.992@TK2MSFTNGP04.phx.gbl... >>>> Hi all, >>>> >>>> I wonder if anyone can explain what is happening here? >>>> I have a dataadapter that I am using to correlate the changes in an SQL >>>> Table to a DataTable. I create a new DataRow then call .Update and >>>> acceptchanges, all works fine. The problem I have is when I try to >>>> amend some of the data and the call the Update again. This time I get >>>> a Concurrency error, which is understandable as the data has changed >>>> since I initially created the table but is not the desired outcome and >>>> I was expecting that after accepting changes can calling the .Fill the >>>> concurrency conflict would be resolved. >>>> >>>> Is there a way to get the adpater to update the changed rows. I have >>>> tried calling .Fill after the update but this does not seem to help. >>>> Ideally I would like to work on the datatable and not have to >>>> repopulate from the database every time I update a cell/row. >>>> >>>> Thanks for any help you can give. >>>> >>>> Steve >>>> >>>> Here is the code it use: >>>> Try >>>> _tradeAdapter.Update(_detailsTable) >>>> _detailsTable.AcceptChanges() >>>> _tradeAdapter.Fill(_detailsTable) >>>> Catch exTran As DBConcurrencyException >>>> MsgBox("There has been a conflict error on this trade, please refresh >>>> your details and check", MsgBoxStyle.Critical, "Update Conflict") >>>> s.Logging.ApplicationLog.LoggingLevels.ApplicationError) >>>> End Try >>>> >>>> >>> >>> >> >> > > Hi Guys,
Thanks for all the info, I think my understanding of the DataAdapter is lacking somewhat but is now getting there thanks to you. I am trying to do too many updates with out re-syncing the data which is causing the violation exception. Thanks for all your help. Show quote "Bart Mermuys" <bmermuys.nospam@hotmail.com> wrote in message news:4dVoh.282335$wC.5800047@phobos.telenet-ops.be... > Hi, > > "Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in message > news:O6DORDDNHHA.1912@TK2MSFTNGP02.phx.gbl... >> Hi Bart, >> >> Yes there is a PK column that I am returning by using the Rowupdated >> event for any .Inserted row. >> I use a command builder for update command as I want to make use of the >> concurrency exception. > > Sounds ok, you already verified you're getting back the right key (DB > generated key) after update for new records...? > >> I dont know if it is by design for if I have got something wrong, but I >> can call .update many times as long I don't change the data (pretty >> pointless) as soon as the data gets changed it causes a concurrency >> violation. > > I'm a bit confused here, in your OP you did mention a certain sequence > (add,update,ammend,update) but here you are only talking about changing > data. When exactly do and don't you have this concurrency violation > exception. > >> In some ways this is as desired but I wasn't expecting the violation >> while using the same Connection as the first update. The only solution I >> can find is to repopulate the datatable using the .fill after each >> update. This works fine but is costly as the application uses a heavily >> lagged WAN. > > I don't get what you mean with "same connection" and not expecting a > concurrency violation. To be clear, the optimistic concurrency control > will cause a concurrency violation exception when the record has been > changed in the DB by something else/other between the last fill and > current update. > > You could extend your code a bit: > > Try > _tradeAdapter.Update(_detailsTable) > Catch ex As DBConcurrencyException > Console.WriteLine( ex.Message ) > > If ( ex.Row.RowState = DataRowState.Modified ) Then > For Each col As DataColumn In ex.Row.Table.Columns > Console.WriteLine( "{0} oldvalue={1} newvalue={2}", _ > col.ColumnName, _ > ex.Row( col, DataRowVersion.Original ), _ > ex.Row( col, DataRowVersion.Current ) ) > Next > End If > End Try > > When you get a violation exception, you can compare the old values with > the current values in the DB and if they are different then it's normal > you get a concurrency violation, wrong keys may also cause this exception. > > > HTH, > Greetings > > >> >> Any thoughts? >> >> Thanks again, >> >> Steve. >> >> >> >> "Bart Mermuys" <bmermuys.nospam@hotmail.com> wrote in message >> news:I_Qoh.281969$WX6.5836931@phobos.telenet-ops.be... >>> Hi, >>> >>> "Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in message >>> news:OQt6RD%23MHHA.5064@TK2MSFTNGP04.phx.gbl... >>>> Sorry, I did not call the .Fill correctly, it works if i do this, >>>> however, the question still remains of whether this can be done without >>>> calling the .fill and avoiding another trip to the database. >>> >>> By default the DataAdapter will call AcceptChanges on each succesfull >>> updated row, so you should not call it. Although not necesairly the >>> case here, calling AcceptChanges at the wrong time may even cause a >>> false "Concurrency Violation". >>> >>> If i understand it correctly, you only have this problem when you do : >>> add record, update, modify same record, update and the problem is helped >>> with calling Fill in between. Do you have an autonumber PK column in >>> the DB table, if so , which DB are you using and how are you currently >>> building the insert command (DataAdapter) ? >>> >>> HTH, >>> Greetings >>> >>> >>>> >>>> Thanks again >>>> >>>> Steve >>>> >>>> "Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in >>>> message news:eWIId59MHHA.992@TK2MSFTNGP04.phx.gbl... >>>>> Hi all, >>>>> >>>>> I wonder if anyone can explain what is happening here? >>>>> I have a dataadapter that I am using to correlate the changes in an >>>>> SQL Table to a DataTable. I create a new DataRow then call .Update >>>>> and acceptchanges, all works fine. The problem I have is when I try >>>>> to amend some of the data and the call the Update again. This time I >>>>> get a Concurrency error, which is understandable as the data has >>>>> changed since I initially created the table but is not the desired >>>>> outcome and I was expecting that after accepting changes can calling >>>>> the .Fill the concurrency conflict would be resolved. >>>>> >>>>> Is there a way to get the adpater to update the changed rows. I have >>>>> tried calling .Fill after the update but this does not seem to help. >>>>> Ideally I would like to work on the datatable and not have to >>>>> repopulate from the database every time I update a cell/row. >>>>> >>>>> Thanks for any help you can give. >>>>> >>>>> Steve >>>>> >>>>> Here is the code it use: >>>>> Try >>>>> _tradeAdapter.Update(_detailsTable) >>>>> _detailsTable.AcceptChanges() >>>>> _tradeAdapter.Fill(_detailsTable) >>>>> Catch exTran As DBConcurrencyException >>>>> MsgBox("There has been a conflict error on this trade, please refresh >>>>> your details and check", MsgBoxStyle.Critical, "Update Conflict") >>>>> s.Logging.ApplicationLog.LoggingLevels.ApplicationError) >>>>> End Try >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > Here's your problem:
> I use a command builder for update command as I want to make use of The commands built by this do a match based on all of the fields in the> the concurrency exception. record when doing updates, so if any of your fields change and you don't retrieve them back, like a date_updated or something like that, it can't find the record when it does the second update because it's looking for matches for all of the fields. I usually use a stored procedure that performs the update and then returns the fields back with a SELECT statement so I can refresh my object accordingly. Or you can re-query. Or write your own logic and just use the primary key if you feel comfortable with that. I think there's a setting in the table adapter properties to change this to only use the PK, but I don't remember what it is offhand. As Bill pointed out, it's best to write your own logic to do updates. the CommandBuilder is dangerous. Robin S. ------------------------------ Show quote "Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in message news:O6DORDDNHHA.1912@TK2MSFTNGP02.phx.gbl... > Hi Bart, > > Yes there is a PK column that I am returning by using the Rowupdated > event for any .Inserted row. > I use a command builder for update command as I want to make use of > the concurrency exception. > I dont know if it is by design for if I have got something wrong, but > I can call .update many times as long I don't change the data (pretty > pointless) as soon as the data gets changed it causes a concurrency > violation. In some ways this is as desired but I wasn't expecting the > violation while using the same Connection as the first update. The > only solution I can find is to repopulate the datatable using the > .fill after each update. This works fine but is costly as the > application uses a heavily lagged WAN. > > Any thoughts? > > Thanks again, > > Steve. > > > > "Bart Mermuys" <bmermuys.nospam@hotmail.com> wrote in message > news:I_Qoh.281969$WX6.5836931@phobos.telenet-ops.be... >> Hi, >> >> "Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in >> message news:OQt6RD%23MHHA.5064@TK2MSFTNGP04.phx.gbl... >>> Sorry, I did not call the .Fill correctly, it works if i do this, >>> however, the question still remains of whether this can be done >>> without calling the .fill and avoiding another trip to the database. >> >> By default the DataAdapter will call AcceptChanges on each succesfull >> updated row, so you should not call it. Although not necesairly the >> case here, calling AcceptChanges at the wrong time may even cause a >> false "Concurrency Violation". >> >> If i understand it correctly, you only have this problem when you do >> : add record, update, modify same record, update and the problem is >> helped with calling Fill in between. Do you have an autonumber PK >> column in the DB table, if so , which DB are you using and how are >> you currently building the insert command (DataAdapter) ? >> >> HTH, >> Greetings >> >> >>> >>> Thanks again >>> >>> Steve >>> >>> "Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in >>> message news:eWIId59MHHA.992@TK2MSFTNGP04.phx.gbl... >>>> Hi all, >>>> >>>> I wonder if anyone can explain what is happening here? >>>> I have a dataadapter that I am using to correlate the changes in an >>>> SQL Table to a DataTable. I create a new DataRow then call .Update >>>> and acceptchanges, all works fine. The problem I have is when I >>>> try to amend some of the data and the call the Update again. This >>>> time I get a Concurrency error, which is understandable as the data >>>> has changed since I initially created the table but is not the >>>> desired outcome and I was expecting that after accepting changes >>>> can calling the .Fill the concurrency conflict would be resolved. >>>> >>>> Is there a way to get the adpater to update the changed rows. I >>>> have tried calling .Fill after the update but this does not seem to >>>> help. Ideally I would like to work on the datatable and not have to >>>> repopulate from the database every time I update a cell/row. >>>> >>>> Thanks for any help you can give. >>>> >>>> Steve >>>> >>>> Here is the code it use: >>>> Try >>>> _tradeAdapter.Update(_detailsTable) >>>> _detailsTable.AcceptChanges() >>>> _tradeAdapter.Fill(_detailsTable) >>>> Catch exTran As DBConcurrencyException >>>> MsgBox("There has been a conflict error on this trade, please >>>> refresh your details and check", MsgBoxStyle.Critical, "Update >>>> Conflict") >>>> s.Logging.ApplicationLog.LoggingLevels.ApplicationError) >>>> End Try >>>> >>>> >>> >>> >> >> > > Ah, you should not have to call AcceptChanges unless you're executing your
own code to post the changes to the database and not using the action Commands associated with the DataAdapter. -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- "Steve Lloyd" <steve.rem***@livenowpaylater.this.co.uk> wrote in message news:eWIId59MHHA.992@TK2MSFTNGP04.phx.gbl... > Hi all, > > I wonder if anyone can explain what is happening here? > I have a dataadapter that I am using to correlate the changes in an SQL > Table to a DataTable. I create a new DataRow then call .Update and > acceptchanges, all works fine. The problem I have is when I try to amend > some of the data and the call the Update again. This time I get a > Concurrency error, which is understandable as the data has changed since I > initially created the table but is not the desired outcome and I was > expecting that after accepting changes can calling the .Fill the > concurrency conflict would be resolved. > > Is there a way to get the adpater to update the changed rows. I have > tried calling .Fill after the update but this does not seem to help. > Ideally I would like to work on the datatable and not have to repopulate > from the database every time I update a cell/row. > > Thanks for any help you can give. > > Steve > > Here is the code it use: > Try > _tradeAdapter.Update(_detailsTable) > _detailsTable.AcceptChanges() > _tradeAdapter.Fill(_detailsTable) > Catch exTran As DBConcurrencyException > MsgBox("There has been a conflict error on this trade, please refresh your > details and check", MsgBoxStyle.Critical, "Update Conflict") > s.Logging.ApplicationLog.LoggingLevels.ApplicationError) > End Try > >  |
|||||||||||||||||||||||
Other interesting topics