Concurrency while adding new rows

  • Thread starter Thread starter Nathan
  • Start date Start date
N

Nathan

Hi,

I've been looking all around for how to do this but can't find any
instructions. I'm sure one of you can help me easily. I have a VB.NET that
accesses an access database. I create a dataset and add new rows to one of
its tables, then update when I'm finished. The new row already comes with a
key value (Autoincrease numeric value), which is thrown into the table.
However, if I work this process on two computers at the same time, the last
one to do the update gets an error because their both submitting rows with
the same key values. How can I get it so that the values the dataset uses
are not the same values that are submitted to the database? Also, I need to
know how to create new child rows for these new rows. Until now I've been
retrieving the key value of the parent row.

Thanks a lot,
Nathan
 
Nathan said:
Hi,

I've been looking all around for how to do this but can't find any
instructions. I'm sure one of you can help me easily. I have a VB.NET that
accesses an access database. I create a dataset and add new rows to one of
its tables, then update when I'm finished. The new row already comes with a
key value (Autoincrease numeric value), which is thrown into the table.
However, if I work this process on two computers at the same time, the last
one to do the update gets an error because their both submitting rows with
the same key values. How can I get it so that the values the dataset uses
are not the same values that are submitted to the database? Also, I need to
know how to create new child rows for these new rows. Until now I've been
retrieving the key value of the parent row.

Thanks a lot,
Nathan
What, exactly, is an "Autoincrease numeric value"?
Is this a value calculated in your application, based on what it finds in
the table in your dataset?
From your description, you have two (or more) instances of your application
connected to one instance of the Access database. If each application
instance is calculating new key values based on the same database data,
you'll get the duplicates you cite.
Why not use the Autonumber feature of Access to generate primary key values?
That way, the database itself keeps track of issued keys.
Other things you might want to look at:
An article in Access help: "Set options for a shared Access database (MDB)
In Visual Studio help: OleDbConnection.BeginTransaction Method
If my assumptions about your situation, as stated above, are incorrect,
please clarify.
 
You can determine when there are concurrency errors by examining the DataSet,
which is a collection of a) original rows, b) changed rows and c) rows that
have problems (like concurrency errors). By examining the problem rows, you
can then query the db and see where the auto-increment is at. If these are
simple inserts, you can correct without having to inform the user that you
changed anything; just remember to re-query the data in the DataSet so the
user sees the corrected key values.

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Sorry I wasn't completely clear on that. The value assigned to the primary
key column is an AutoNumber value from Access. The program is not connected
to the database when the new rows are created. I initially fill the dataset
with the current Access data, then to add rows I do something like:

Dim NewRow as DataRow = tblMyTable.NewRow
NewRow("Column1") = "Value"
NewRow("Column2") = "Value"
tblMyTable.Rows.Add(NewRow)
'And later on:
DataAdapter.Update(tblMyTable)

I don't set the primary key; the dataset does that automatically--based on
(I assume) the data it initially got from Access with the DataAdapter.Fill
method. When two instances of the program try this at the same time, they
are trying to update rows with duplicate primary key values. There must be
some way (???) for the dataset to create "temporary" primary keys and create
child rows based on those primary keys. Hope I've explained myself a little
better.

Nathan
 
Back
Top