Question Concurrency Violation. Can't force an edit into the database.

Runescope

Well-known member
Joined
Jan 6, 2011
Messages
53
Programming Experience
Beginner
Greetings,

I have an unbound Access Database that I'm trying to fill with almost 20 years of data stored in text files. Each file is one day's prices of items, each file has around 10,000 different items. Sometimes the name of the item changes slightly, and I want to write that change into the database along with what date the change was made.

I should probably mention that I'm working in VB.Net (Visual Studio 2010 Ultimate)

VB.NET:
If Val(UpDate) > Val(FFoundRow(0)("UpDate")) Then ' FFoundRow is a Datarow for that specific item
    FFoundRow(0)("FName") = FName ' always making sure the name is updated
    FFoundRow(0)("UpDate") = UpDate ' the most recent date
    FDA.Update(FDT) : FDT.AcceptChanges() '  I have switched these two about, with no difference.
    'FDS.Clear() : FDA.Fill(FDS, "Fund Info") : FDT = FDS.Tables("Fund Info") '  Commented out, I'll explain below.
End If

Okay, so what's happening is that when I go to update, I get the error: "Concurrency Violation: The Updatecommand affected 0 of the expected 1 records force change"

I know that the problem is that the PREVIOUS change I made to the database for that item hasn't been saved, and now I'm writing ANOTHER change, and it's throwing an error. Which makes sense. I know one way to resolve this is to uncomment the line that clears and reloads the data into the dataset and datatable. I've tried it and it works just fine.

However, it REALLY slows down the process, and it's going to keep getting slower and slower as more and more data is stored in the database. There are over 3000 days of information to go through, so the faster I can make it the better.

So my question is, is there some way to force the database to accept the most recent changes without reloading the entire database?

Thank you for your time and attention to this question. I can't think of anything to add, but feel free to answer questions.
 
It revolves around the was the database query in the data adapter is structured. This article will give you some background info:

https://msdn.microsoft.com/en-us/library/aa0416cz(v=vs.110).aspx

Essentially, your update command looks like this:

UPDATE people SET name =@newname WHERE id = @id and name = @oldname

Your data set contains two rows like this:

1, sarah, john
1, sarah, tim

Your update find ID1 sarah the first time and updates to john, then fails to find ID 1 sarah (now called john) the second time for update to tim. Clearing and refilling after the update from sarah -> john "solves" it because the app will be trying to "update set name = tim where name = john and id = 1" rather than "update name = tim where name = sarah and id = 1"


How to get around it? Well, you say youre happy with "latest wins" strategy, so alter the update command generating strategy of the data adapter. A dataadapter has a commandbuilder (https://msdn.microsoft.com/en-us/library/tf579hcz(v=vs.110).aspx) that fills in the relevant updating commands if they are null. How the command builder generates sql can be varied using the conflictoption proeprty:
https://msdn.microsoft.com/en-us/library/system.data.conflictoption(v=vs.110).aspx

You want the conflictoption of "overwritechanges"


-

As an aside, a lot of this gets a lot easier if you use typed datasets:
1) open the data sources window and add a connection to your access db, the wizard asks you which tables you want in your dataset and what to call it
2) a visual design surface that looks a lot like an access database relationshops window will appear
3) datatables and tableadapters are "glued together" on the designer. to edit the queries that push and pull to a database table, right click the tableadapter and Configure it
4) there's an option in the advanced options of the wizard to turn off concurrency, you will see the generated queries change

Capture.PNG

IMPORTANT: the "tables" you see on the design surface are NOT your database tables. They are strongly typed DataTable objects that represent the tables in your database. Think of them as a collection of rows, and rows are custom Class objects that have a bunch of properties representing the database columnvalues. The tableadapter pushes data to and from the db table based on what the datatable in local memory looks like. Youre already familiar with the dirty version of this: dataadapter and datatable/datarow that are NOT strongly typed (i.e. you have to access them via peopleTable.Rows(0)("Firstname") = "sarah". Ugh)

To use your typed datasets, you might do something like this (pseudocode; I use c# mainly, not vb)

VB.NET:
Dim ds as New MyDataSet()
Dim ta as New PeopleTableAdapter()
ta.Fill(ds, "smith") 'fill all people with last name smith

'example to iterate all rows and alter things
ForEach ro as PeopelRow in ds.People
  ro.LastName = ro.LastName.ToUpper()
Next ro

'example to load data
ds.People.BeginLoadData()
ForEach line as String in myTextFileLines
  Dim ro as PeopleRow = ds.People.NewPeopleRow()
  ro.FirstName = line.Substring(1,3)
  ro.LastName = line.Substring(4, 6)
  ds.People.AddPeopleRow(ro)
Next ro

'example to save to db
ta.Update(ds.People) 'Update() method will do INSERT UPDATE or DELETE sql as necessary. tableadapter has the comamnds. you can inspect them in teh visual designer proeprties grid for teh tableadapter


Much nicer than all this myrow(0)("firstname") = "soemthing", no? Design time typing of data sets is great, dead easy to set up etc

Note, please read the DNU link in my signature if using Typed Datasets; you can easily fall into a trap of thinking they aren't working if you dont
 
Last edited:
That's quite a lengthy explanation! But your code example only shows adding new rows. I don't have a problem adding rows, just editing rows that are already there. But I think your suggestion of the conflict option is what I'm looking for. I'll dig more into it when I'm more awake ... and at work. lol
 
See the part above where I wrote:

VB.NET:
'example to iterate all rows and alter things
ForEach ro as PeopelRow in ds.People
  ro.LastName = ro.LastName.ToUpper()
Next ro

I disagree that you don't have a problem adding rows; your data access strategy and code can be improved measurably to be more readable, faster and easier to write. Give typed datasets a go; the following link lists a suite of tutorials; "creating a simple data application" would be an ideal intro:
http://msdn2.microsoft.com/en-us/library/fxsa23t6(vs.100).aspx
 
Last edited:
That's ... not ... editing the rows.

Lemme explain. :)

This table has 5 fields. (There are two other tables, but if I can get this one working, the other two will be easy to change)
FID - The unique Autonumber assigned by the database upon creation of a row, used as a Key.
FCode - The unique code identifying the product.
FName - The name of the product.
UpDate - The last day this information was updated.
CCode - A unique code signifying which company makes the product.

Each day there are 10,000+ products that update. There are 4,572 days to go through.

The FFoundRow does a search for the FCode and if the Update is newer then both the FName & the Update need to be updated.

So my dataset would contain rows like this, filled from the text file for the first day:

1 , Sarah1234 , Sarah Stuff , 36529 , SAR (All dates are stored in Julian format)
2 , Robert7654 , Robert Stuff , 36529 , ROB
3 , Garry5678 , Garry Stuff , 36529 , GAR
(etc etc etc so that there is 10,000+ added)

Now, when I start scanning the text file for the next day, there are rows like this:

Sarah1234 , Sarah Junk , 36530 , SAR
Robert7654 , Robert Stuff , 36530 , ROB
Garry5678 , Garry Stuff , 36530 , GAR
(etc etc etc so that there is 10,000+ updated)

When it does a search for "Sarah1234", it will ONLY EVER find one record, that's what in the FFoundRow(0). And then it sees that the date is newer (one number higher, so one day newer obviously) and update the UpDate and the Product Name ('Sarah Stuff' to 'Sarah Junk').

But that's where I get the Concurrency Violation. It's only ever going to try writing new information to the database when that information is newer, so I'd just like it to get on with it. :)

I can't really tell the difference between your code and mine actually. It's slightly different, but not significantly. Is it because my database is unbound? Did I mention that? I thought I mentioned that. I should probably mention that. I also may not be fully understanding what you mean by a 'typed' dataset. I use databases, but I'm not the best with them and when I found something that was simple to work with, I've been sticking with it. :)
 
Last edited:
That's ... not ... editing the rows.

I iterate the data table and set every row to have an uppercase surname; that's an edit to the row


I'm not quite sure why you'd add a primary key that is an auto number, and have another column that is guaranteed unique. Make your produce code the primary key of your database table. This will make life easier later

You think you've found something simple, but it's turning out not so simple, right? All these concurrency exceptions are making life much harder.. I promise you that the typed dataset way of doing things will be easier. In my life I've seen countless people bring me their broken solution and ask for help fixing it. Often the answer has been "throw it away and do it like this" - I don't recommend this for some personal satisfaction in making people ditch what they think has been hours of hard work, I recommend it to try and set them on a way of doing data access that Microsoft actively recommends. Your way and my recommendation have similarities for sure, but my(-crosoft's) way is considerably easier and less error prone

If you're looking for fastest update (you have a lot of data) then you don't want to be downloading anything out of the database at all.. follow these steps:

Add a new Dataset type item to your project
When asked for the source of data, connect it to your database (the wizard will help with this)
Select all the tables you're interested in
The dataset design surface will populate with representative data tables for each of your database tables
Find the tableadapter that relates to your Products table
Right click it and choose add query
Add a statement that inserts rows
Add the following sql:

Update products set fname = ?, update = ?, ccode = ? Where fcode = ? And update < ?

Finish the wizard, a new query will be added (I'll assume you called it updateIfNewer). You'd use it like this:

VB.NET:
Dim ta as New XYZDatasetTableAdapters.ProductsTableAdapter 'you chose the name of the dataset when you added it

ForEach line as String in updateFileLines 'your job to populate this array with file lines. Try File.ReadAllLines()

Dim bits as String() = line.Split(",")

ta.UpdateIfLater( bits(1), Convert.ToInt32(bits(2)), bits(3), bits(0), Convert.ToInt32(bits(2)) )


We don't try to download tens of thousands of rows to the client, only to have e client find the row, change the data, and then send it back to the database. Data lives in databases, and databases are for keeping an manipulating data. We don't need to show this data to the user, we just want a fast, forward only iteration of each product file.

Why does this work? The supplied prod file contains all the info needed to find a product, and update it. We only want to update it if the date of the product in the DB is older that the incoming data. His is the purpose of the where clause

Be sure to:
Action my recommendation to make the fcode the primary key in the database. We don't need an int as well and it actively hinders rather than helps
Read the dnu link in my signature to avoid falling into a trap that will make it look like this code doesn't work
 
Hey!

Sorry for the delay in getting back to this, I had to take my annual vacation. :)

To be completely honest, your way (and microsoft's) seems hopelessly and needlessly complicated. So many things that have to be added and linked. It's definitely NOT simple, even though I'm sure that it is more stable and faster. Your point of having two unique columns is a good one though. It's a force of habit that whenever I make a new table, the first thing I do is add an autonumber and make it the key. I hardly ever end up with a second unique column.

Now, as to your 'editing'. That's just looping through the database and changing EVERYTHING indiscriminately. I HAVE to search to find ONE thing, and edit PART of it. It's more complex than what you were showing.

I agree that not having to load the database in would greatly speed things up, but how else am I supposed to find out if that record needs to be updated? I can't just throw more information into the database, possibly redundant and incorrect information. I need to check if it exists first and update it as necessary.

So much of your explanations seems like double-talk and gibberish to me though, it's hard to follow. I like SIMPLE solutions, and what you're suggesting? It's not simple. Oh well.
 
While I haven't read quite everything in this thread, I'm noticing there's a single concept, when new data comes into the Database (presumably into a different table than where the main data is) and the records "new" corresponding records that match the main table needs to take precedence in that main table, no?
If so, then I'm wondering why use VB at all?
Why not use an Update query in the database to update (your first post indicates you have a Date field to work with) the data in that main table and an Insert for the new records?

I know this is MS Access which means things like Stored Procedures can't be used here, but you could make an Update query in Access (and an Insert query) then just have your VB (or SSIS) program run the two queries.

Edit:
Your Update query would probably be something like this:
VB.NET:
Update m
Set m.FName = n.FName, m.UpDate = n.UpDate
From MainTable As m
  Inner Join NewDataTable As n
    On n.FCode = m.FCode
      And n.CCode = m.CCode
Where n.Update > m.Update;
What that query does is joins the two tables (main data and the new data tables) on Fcode and CCode to ensure a 1-to-1 product match but also checks the dates (julian dates are really nice to work with in this case) and applies the FName and Update data from the new data table to the main table, but only where the date in the new data table is newer since that's all you're interested in here.
The Insert query would be a Insert Select Left join, basically the same as above:
VB.NET:
Insert Into MainTable (FCode, FName, UpDate, CCode)
Select n.FCode
      ,n.FName
      ,n.UpDate
      ,n.CCode
From NewDataTable As n
  Left Join Main As m
    On m.FCode = n.FCode
      And m.CCode = n.CCode
Where m.FCode Is Null;
What this does is pulls everything from the new data table matching is (where possible) to the main table, anything where the FCode from the main table is null (meaning there isn't a match) is "new" data to be inserted, hence the where m.FCode Is Null, there's no match.
 
Hey!


To be completely honest, your way (and microsoft's) seems hopelessly and needlessly complicated.

I posted a 4 step process to adding a typed dataset to your project. You actually only need to do step 1, the other 3 were introducing you to how to work with it. Step 1 is a 4, maybe 5 page wizard that takes me in the region of 15 seconds to complete. The only way o make it less complicated would be to come round your house and do it for you..



Now, as to your 'editing'. That's just looping through the database and changing EVERYTHING indiscriminately. I HAVE to search to find ONE thing, and edit PART of it. It's more complex than what you were showing.

That's not looping through the database. The code never goes anywhere near a database. A datatable is not a database. It was also an example that you can take away and expand on (how to edit a row); i'm here to educate you, not do your work for you. Any real life situation is more complex than a contrived example, but at its crudest it only really wanted an If statement. I edited a part of the row (the last name), I just didn't do anything that was selective to rows. You wouldnt be so crude as to download an entire database into a datatable and then loop if using an If to decide whether to edit this one row though (and hopefully that would be obvious) - you'd ask the database to supply only the data you were interested in, and then indiscriminately edit that, because the discrimination was done elsewhere, saving millions of rows of network traffic

So why do I try to educate people rather than just solving their problems for them? Because if I give a man a fish, I'll just have to give him a fish every day to stop him dying of starvation. If I teach him how to fish, however...


I agree that not having to load the database in would greatly speed things up, but how else am I supposed to find out if that record needs to be updated?

You don't! You seem to regard a database as some sort of retarded data storage bucket where, if you want to change something in the bucket, you have to get everything out and look through it yourself and make some simple programmatic decision on it because the database is incapable, then put your thing back in the bucket. The point of a database is it holds info and is really good at finding/maintaining it for you. If you want to find Sarah and update her age to 44 but only if it's 43, you dont say "database, give me sarah's record... is her age 43, ok, it is, now here is an edited sarah whose age is 44", you just say "database, edit person's age to be 44 where age is 43 and name is sarah"

I can't just throw more information into the database, possibly redundant and incorrect information. I need to check if it exists first and update it as necessary.
I don't recall suggesting you send redundant info to a database. Ultimately you have an algorithm for deciding whether to update a record, give the database the algorithm and let it work out whether to update the record. It will tell you if it does so. You don't need to check it exists first; databases don't care if you tell them to update something that doesn't exist, they just tell you they updated nothing



It's not simple. Oh well.

For me this is a doddle, because I do it all day every day. For you, as it's new, you might need to plod through a tutorial or the odd wizard. It takes time to get mentally adjusted to these things, but ultimately this "simple" solution you've found of lashing a load of hard to read/maintain/debug database update code into a button click handler is just the crappy equivalent of the structured way I'm advocating it done. If you were to look under the hood of what the visual designer generates in terms of database access code, it's not a million miles different to what you've written. This isn't a justification for doing it the way you're proposing though, any more than it's justifiable to have a single file with all your code for your app in; things are broken up into modules for reasons, chiefly security, speed, re-use, ease of understanding and quality/error free-ness
 
Last edited:
Back
Top