Problem with primary keys.

Hi.

Yes, it's the dangers of using an application while it's being
developed, but I've hit a snag with the system I'm developing and I'd
appreciate any help.

Users are working with a data-entry program written in C# using ADO.NET
to enter data into a SQL Server (2000) database.

The system works as follows:
1) Open a connection the the DB (sqlconnection)
2) Create a sqldataadapter with missingschemaaction= .addkey
3) Fill a dataset with a simple query (fetch all records in the current
task entered by the user).
4) The connection is closed.
5) A dataview is created for the dataset and a form has controls bound
to this dataview.
6) The user can edit previously created records or add new records.
7) Rows are added through DataView.Addnew()
8) At regular intervals a connection is re-established with the
database
9) A dataadapter with missingschemaaction=.addkey is created using a
simple 'SELECT * FROM...' query
10) A Sqlcommandbuilder is created
11) dataadapter.update(dataset, tablename) is executed.
12) connection is closed.

The system seems to work fine for the most part. The problem is rows
are "disappearing". I.e. I can see them in the dataset on the user's
machine but not in the database.

The table has a key:
[ID] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_tbl_1] PRIMARY KEY CLUSTERED
( [ID] ) WITH FILLFACTOR = 90 ON [PRIMARY]

My guess is that User A is creating a record and the dataset is
assigning ID x+1. Meanwhile User B is creating a record and also
getting an ID of x+1.
When they run the update the user who writes last will "get" the ID and
for all intents and purposes the other user will lose their data.

Is this likely the problem? How ought one to solve it? I'm currently
using a simply INT as the ID, I'd hate to have to switch to the larger
GUID. How can I make sure that the ID is assigned only when the table
updates, as opposed to the moment dataview.addnew() is executed?

Hope that's reasonably clear and thanks in advance for any help!

regards,
Dave

.

Relevant Pages

  • Re: ADO Connection Timeout
    ... so what happens when a connection failure forces one station to revert ... to a local database? ... Further, you *will* have contention issues, Jet does not support record ... to the central server, but you are willing to live with periods where it ...
    (microsoft.public.data.ado)
  • DataView vs DataReader
    ... Hi all, quick question, a DataView is ... effect the number of connections to the database. ... A DataReader on the other hand always maintains a connection to the database ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: ADO Connection Timeout
    ... When the first test is run, the results are stored in the central database. ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
    (microsoft.public.data.ado)
  • Re: ADO Connection Timeout
    ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
    (microsoft.public.data.ado)
  • Re: Communicating over the internet
    ... > That way you provide one manner in which to open the database, ... > can keep the connection open and then when someone wants to ... > server as the database and the soap dll connecting with a TCP socket. ... The name of the running program might help. ...
    (microsoft.public.vb.general.discussion)