Solved

.NET How can I force a datarow to write (if it was not changed)

Posted on 2013-10-27
29
809 Views
Last Modified: 2013-11-10
I have a large VB.net application to a database shared amongst several users. The code uses datatables to hold items while the user reviews data or, sometimes, makes changes.

If 2 users make changes to the same datarow, a "Concurrency violation" occurs which I catch and then display an error to the user.

My Problem: there is a "Save" button on the form. If the user clicks this button, the data will be saved and potentially cause the Concurrency violation above if there is a conflict. BUT "Save" uses a data adapter.update() function. If the user has not made any changes, .Update will do nothing and even though some other user might have made changes, since .Update won't write this user's unchanged data, we never get the concurrency exception. I would like "Save" to force an update. Using datarow.SetModified() does NOT force the write if the datarow has actually not been changed.

How can I force .update to write the unchanged row?
0
Comment
Question by:bgraves
  • 12
  • 8
  • 4
  • +1
29 Comments
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
I'm puzzled.
You want to force an error when the user clicks the save button.  Why?  Is it to show that another user is viewing the data on that row?
0
 

Author Comment

by:bgraves
Comment Utility
I want to force a write. If the data has not been changed by another user, there is no error and really no problem in writing the same data. However, if another user has changed some piece of the data then the concurrency code will make an exception which will be caught.

So clicking "Save" would actually be a check that the data is still good.
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
Still puzzled - this seems to imply others aren't allowed to modify anything despite having the possibility to change and save data.

Anyway:
What if you overwrite a field with the same data then try to save.
eg, if an int has the value 42 then 'change' it to 42 and try to save it.
0
 
LVL 20

Expert Comment

by:ElrondCT
Comment Utility
I would suggest that you create a field in the table that automatically gets incremented each time you make an update. That will force the .Update to actually update the database, and you can do your concurrency verification.
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
One thing to bear in mind.
If I understand correctly then it is likely your users will always receive this warning when using the save button, even if nothing has changed.  (The DB might flag the record as being changed when the actual update happens, even if nothing has changed, so the second fictitious update will result in the exception).
0
 

Author Comment

by:bgraves
Comment Utility
>I would suggest that you create a field in the table that automatically gets incremented each time you make an update. That will force the .Update to actually update the database, and you can do your concurrency verification.

Yes, that could work but requires every table in the database to have the new field and then the coding for handling each table changes as well! I accept the idea but its a lot of work. Really just need to get the data-adapter to flush the data, it would be so easy and use all the existing mechanisms.

>it is likely your users will always receive this warning when using the save button, even if nothing has changed

No, flushing the data when nothing has changed causes no errors. The concurrency error is initiated by the data adapter when it does an SQL SELECT with all the fields instantiated with the expected old values. Normally one hit is expected. If there are no hits, it means that at least one of the fields has changed by another user => concurrency error.
0
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
Instead of relying on a ConcurrencyException, do the check yourself.

The easiest way to do that is to add a TimeStamp field in the database table. If you do not know about a TimeStamp field, it is changed automatically when there is a change in any field in the row.

If your database does not support that type of field, simply use a DateTime field that you update with the current date and time everytime a change is made in the row. That update could be done in an event in the form or grid used to modify the data, a trigger in the database, or in the RowUpdating event of the DataAdapter when the e.Row.RowState is Modified.

After an Update, use a stored procedure or a SQL command that compare the TimeStamp (or DateTime field) in your DataRow (or all your DataRows if you Update the whole Table) with the one in the database. If they do not match, then you have a conflict.
0
 
LVL 20

Expert Comment

by:ElrondCT
Comment Utility
What happens if you save a value from a field, change that field, then change it back? I'm pretty sure that will set the RowState to Modified, and I think that will cause a true Update to be done; seems like I ran into that as a problem for me a few months back. You'd still have to change the coding for each table, but you wouldn't have to change the database structure.
0
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
@ElrondCT

Your mechanism works, but can have unwanted side effects and one has to be very careful about using it.

It forces an Update even when none is needed.

If there are a lot of rows, it can bring a performance issue.

If the table is being used by other applications that locks records, the chances that you will get an error trying to update a locked row will be very high.

If there are any kind of code or trigger that reacts to an Update, such as something that records the name of the last user who did a modification or the date of the last modification, an history table, name it, you end up recording false information in the table.

And one always have to think of maintenance. Maybe your mechanism won't have any impact right now, but if changes are made later that require adding such a trigger, you will end up with problems that might be hard to pinpoint, because the programmer that adds the trigger might not remember or even be aware that useless updates will launch that new trigger.
0
 

Author Comment

by:bgraves
Comment Utility
> What happens if you save a value from a field, change that field, then change it back?

That indeed sets the rowstate to modified, but is not sufficient to do the job. I believe that MS has the old data as well as the new data and they must check because it definitely does not cause the data to be rewritten to the database. The datatable "stuff" is too d..n smart!

datarow.rowstate=rowstate.modified can also set the flag but, of course, MS must be comparing original field values with current or something equivalent because it will now write back to the database!
0
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
RowState is ReadOnly, so you cannot do datarow.rowstate=rowstate.modified. The only way to change the RowState to Modified is to actually change the data.

What could be done, is change a value and call AcceptChanges. This replaces the original values with the new value and sets the state to Unchanged. Then bring back the original value to set back the Modified state, and this time, the Update will work because the system does not see the "real" original value.

But as I told previously, this can have dangerous side effects, right now or in the future. You should never force an Update where none is needed.
0
 

Author Comment

by:bgraves
Comment Utility
> this can have dangerous side effects, right now or in the future

I understand your objection. But I still need to go ahead. I cannot have the user hit "SAVE" and the data actually be in a different state without giving a warning. I could write code to go around the datatable and recheck the database, but now I might as well throw out the whole datatable architecture.

> is change a value and call AcceptChanges

so datarow.item(x)="dummy"
datarow.acceptchanges()
datarow.Item(x)=<original value>
dataadapter.update(datatable)

The above does the trick.
AcceptChanges() throws out all changes, so I need to check that there are no changes (if there are changes, then just Update() will take care of looking for Concurrency issues anyway).
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
Just out of interest did you actually try my rather earlier suggestion which was (just not worder the way you have it):

datarow.Item(x)=<original value>
dataadapter.update(datatable)
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:bgraves
Comment Utility
> Just out of interest did you actually try my rather earlier suggestion which was (just not worder the way you have it):
>datarow.Item(x)=<original value>
>dataadapter.update(datatable)

Yes, actually I stuff a whole lot of "original" values back into the datarow just prior to update. They come from a user display that may or may not have modified them. What is interesting and frustrating is that you can copy stuff all day long into the table and it will not write to the database unless the new value is actually different then the old. The datarowstate will show as modified if you copy the same value back, but it will not write!!

In the end, the solution I accepted does not actually work because my datarowstate is always showing as modified. I cannot do the .acceptchanges because that will discard any real edits the user may have made. It took a little testing to reveal the flaw. So I am still looking for an answer but I think maybe there isn't a way to solve this.
0
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 250 total points
Comment Utility
Simply check if the row was modified before doing the trick, so that you wont lose the real edits:

If datarow.RowState = DataRowState.Unchanged Then
      datarow.Item(x) = "dummy"
      datarow.AcceptChanges()
      datarow.Item(x) = "<original value>"
      dataadapter.update(DataTable)
End If
0
 

Author Comment

by:bgraves
Comment Utility
Yes, please reopen. The currently marked solution looks like it will be part of the final solution but there are still big pieces missing.
0
 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 250 total points
Comment Utility
I think the very simplest is for you to check the current record in the database when the save button is clicked BUT before your current code (bring back that one record using the ID <I assume you do have an ID for the record> using a separate SQL statement).  Now check the contents of the fields in your current user display.  If they are not the same then someone else has changed that record in the underlying database table in the intervening time.

This requires no changes to tables.  There will be no problems caused by just forcing an update somehow with the same data.  

If I understand your requirements this will do everything you require and be pretty trivial to implement.
0
 

Author Comment

by:bgraves
Comment Utility
What I am doing is telling the underlying data mechanism that it should try to do an update. The first part of the update is going to be a SELECT against the existing data. If that SELECT fails, it can only be because someone else has changed the record. This generates an exception, then I retrieve the current record and compare it to what is in the datatable and get the user to make a disposition.

MY Problem has always been to get the underlying mechanism to perform a check for any concurrent change when the record in my hand has not changed, i.e. try the SELECT and see if it flies. Of course I could retrieve the record and do a compare and process the change etc. This would have to be done in maybe 50 locations, dealing with each and every kind of table and relation in the system. The system will have a very infrequent update cycle. I would be surprised if a concurrent change EVER takes place but I need to cover the bases.

Here is what does not work:
OrigValue=dr.Item(0)
dr.Item(0)="some filler
dr.AcceptChanges
dr.Item(0)=OrigValue
dataAdapter.Update(...)

The above will force an update, for sure BUT we only want to force when we have not changed our data. .RowState will show as modified even though my flush of the data from the form to the table has the same data. So the problem is to know when my data has changed from the original. I simply do not understand why .RowState can show as .modified, and then refuse to flush the data (when all the data was overwritten with the same values). Obviously the data system does a compare of old and new field before flushing. So if the compare rules for writing, why doesn't it rule for showing modified???

The answer that works is:
Dim blnChanged As Boolean = False
For i = 0 To dr.Table.Columns.Count - 1
If dr(i, DataRowVersion.Original) <> dr(i, DataRowVersion.Current) Then
      blnChanged = True
      Exit For
End If
Next

If Not blnChanged Then
      Dim save As Object = dr.Item(1)
      dr.Item(1) = "asd"
      dr.AcceptChanges()
      dr.Item(1) = save
End If
      
dataAdapter.Update(...)

Thank you all for your help, many of the comments, warnings and ideas were very useful and helped reach this solution.
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
Any reason why you don't want to requery the source and compare the data currently in the table with your data in the PC.  It is simple, requires no writing of unchanged data AND still shows the user if someone else has modified things.


>>The first part of the update is going to be a SELECT against the existing data.  ...

Is that an assumption of how things work or is this something you (or someone else) has coded?  Because if it is something you do not know with certainty then you are building your whole strategy on a guess.
0
 

Author Comment

by:bgraves
Comment Utility
>>The first part of the update is going to be a SELECT against the existing data.  ..

>Is that an assumption of how things work

It's what appears in the commandBuilder.Update command - so its not an assumption.

> Any reason why you don't want to requery the source and compare the data
The requery and compare are doable, I would have to write a new and separate handler for when the match fails - why have two similar recoveries when one would do the job?
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
Because what you are doing is not particularly efficient and could lead to problems.

There is nothing to stop this check throwing the same (concurrency) exception when it fails so the actual handling of the 'error' is your current code - that would not require anything extra.

Your choice:
Extra code to try to force an update (which has proved difficult so far / may lead to other problems as yet unforeseen) or extra code to check the data in the table directly.
0
 

Accepted Solution

by:
bgraves earned 0 total points
Comment Utility
OK, I think I have a better answer!
I will go get the record and check it against the datarow(*,DataRowVersion.Original)
Now if they should not match, this is the case I have been trying to fix!
I cannot just continue and update, because no update will take place, CommandBuilder.UpdateCommand.CommandText does a SELECT against every single item in the datarow, then it applies the UPDATE from DataRowVersion.Current. In this case we know that the SELECT will fail, no exception will take place but the user's changes have all been ignored.

So here is a long winded but acceptable solution:
Read in the Current Record
Compare each item against DataRowVersion.Original
    if they all match, we have no problem just go Update()
    else
      for each non matching value
         save the value         ' needs to be an array
         and an index to the item that was different
         set DataRowVersion(index)=value just read from the database
      end for
      datarow.AcceptChanges       ' this makes DataRowVersion.Original=DataRow.Item()
      for each saved item
         use the index and set DataRow.Item(index)=the appropriate saved value()
      end for
      DataRow.Update
'
      Tell the user "we were forced to make these changes to your data", list each field name and value that was changed.

This works.
It can be placed centrally in the Update routine, which can determine the right table etc.
It avoids causing an exception in ALMOST every case and
the user gets told what happened.
There remains the concurrency problem that can occur in the few fractions of a second between the time I read in the values from the database and the time I call UPDATE. This can go to an exception and a similar process to tell the user what the problem fields were.
0
 

Author Comment

by:bgraves
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 0 points for bgraves's comment #a39615499

for the following reason:

Many contributed great ideas, but the final solution required a deeper understanding of the queries built by the CommandBuilder and then integrating the various approaches into something that the end user would accept as reasonable when he punches the SAVE button!
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
To be honest I do not see what is the difference between your 'check with the original and warn if different' and my earlier suggestion to do that.
0
 

Author Comment

by:bgraves
Comment Utility
I have no objection to your getting some of the credit - it doesn't mean anything to me!

But the real key was to set DataRowVersion.Current to the values from the database then call .AcceptChanges then update a second time with the user's changes. THIS WAS THE KEY, otherwise the SELECT generated by the command builder will fail and, as we all discovered, we have no way to force this when we want OR to bypass it when we have other changes that should not be lost.

Moderator,
I would give both James Burger and AndyAiscow partial credit because of their suggestions involving the .AcceptChanges use. I do not really need any credit.
thanks

BUT any other person seeking an answer to this question should be directed to my last post as it is the only one that shows the full solution.
0
 

Author Closing Comment

by:bgraves
Comment Utility
I picked 2 of the suggestions that led to the final solution but I do want people to find my post as it has the only complete solution.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now