Auto Increment Problem with Dataset

  • Thread starter Thread starter Bruce Stockwell
  • Start date Start date
B

Bruce Stockwell

the setup:

Webservice/WinClient application/SQL server.

VS.Net (visual basic) winform wizard creates a simple form
with load cancel cancelall and datagrid bound to a simple
Dataset with one Datatable. No coding by programer, All
wizardry. User starts app, opens form, adds (4) records
and clicks update.

Problem:
(8) records are added to the dataset with id(key) of
0
1
2
3
24
25
26
27

it is obvious that the auto increment is out of sync. Only
records 24, 25, 26 and 27 are in SQL Server. 0,1,2 and 3
only exist in the dataset and are removed upon reload.

This situation exist with a form created with textboxes
vice the datagrid as well?

Help!

v/r
Bruce Stockwell
 
Hi Bruce,
If I understand your situation, the 0 to 3 values are the
autoincrement values assigned to new records in the
dataset that have not yet been updated to SQL Srvr. The
dataset autoincrements based on a seed you set (default
0). This is set by opening the XSD for the dataset, and
clicking on your identity column in the datatable. In the
properties window, you should see a place to set the
autoinc seed and the autoinc step.
JT
 
Hi Bruce,

You should set AutoIncrement negative numbers - so they can't interfere with
ones already existing.
Take note that this are only provisional numbers which should be replaced
after storing rows to database.
 
Thanks for the reply Miha

so where in the {add three rows} >> {update} >> {merge} >>
{end up with six rows} process does one replace the
provisional keys? It almost sounds like after the merge
you'd need to go clean up the dataset of originally added
rows. The deletes and updates would all be valid.

I'm I making this too complex?

v/r
Bruce
 
Hi Bruce,

Yes, the step between update and merge is a bit tricky (*not.after* merge).
Before update you'll have to store somewhere all Added rows pairs (one from
the original dataset and one in GetChanges() dataset).
After update and before Merge you'll have to update original rows you've
stored with actual identity values (don't forget to set
DataColumn.ReadOnly=false for this operation) you get after sucessful
update.
Only then Merge should work just fine.

Take also a look at this article:
HOW TO: Retrieve an Identity Value from a Newly Inserted Record from SQL
Server by Using Visual C# .NET
http://support.microsoft.com/default.aspx?scid=kb;en-us;320897&Product=vcSnet

or this:
HOW TO: Retrieve the Identity Value While Inserting Records into Access
Database By Using Visual C# .NET

http://support.microsoft.com/default.aspx?scid=kb;en-us;816112&Product=vcSnet
 
Bruce,
See the "Retreiving Identity or Autonumber Values" topic in the help
files. If you do all your inserts using stored procedures just return
SCOPE_IDENTITY() from the SP and add a parameter linked to the identity
field with a Direction of ReturnValue as in the example. Or you can handle
this in a OnRowUpdated event handler using a select of SCOPE_IDENTITY().

Ron Allen
 
Miha

Thanks! Conceptually that makes sense. Now I just have to
apply it programatically.

V/R
Bruce
 
How I would handle it:

1. Set autoinc seed and step both to -1. This way, you
will know the ident key is dataset generated and not from
sql server.
2. Use a rowversion/timestamp column in your database
table. Retrieve that value with your dataadapter's select
command.
3. Change rowversion property in datatable to ReadOnly =
FALSE. (In the xsd)

4. Do inserts with sproc, retrieving SCOPE_IDENTITY and
new rowversion value. ADO.net will automatically replace
your negative ident value with the new one retrieved from
the database.

/* Check if Insert was successful.
If not, rollback transaction and exit. */
IF @@ROWCOUNT = 1 AND @@ERROR = 0 -- SUCCESS
BEGIN
COMMIT TRAN
SELECT @RETCODE = 0
SELECT @TS = TS FROM myTable WHERE PkID =
SCOPE_IDENTITY()
SELECT @PkID = SCOPE_IDENTITY(),
@RetMsg = 'Row added to database.'
END
ELSE
BEGIN
ROLLBACK TRAN
SELECT @RetCode = 1,
@RetMsg = 'Row NOT added!'
END

5. To insure concurrency, do updates with logic similar
to the following in your update sproc:

DECLARE @CheckTS rowversion

/*Test for concurrency*/
SELECT @CheckTS = TS FROM myTable
WHERE PkID = @OriginalPkID

IF @CheckTS <> @OriginalTS
BEGIN
SELECT @TSnew = @CheckTS,
@RetCode = 0,
@RetMsg = 'Another user updated this
information ' +
'while you were editing it.'
RETURN
END
 
For anyone concerned. This is how I handled this one.

objProgramsDS = PurgeDataSet(objProgramsDS, "tblPrograms")

Public Function PurgeDataSet(ByVal myDataSet As DataSet,
ByVal myTable As String)
Dim Tbl As DataTable = myDataSet.Tables(myTable)
Dim Row As DataRow
For Each Row In Tbl.Select("", "",
dataViewRowState.Added)
Tbl.Rows.Remove(Row)
Next
Return myDataSet
End Function
 
Back
Top