By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,468 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Auto Increment Problem with Dataset

P: n/a
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

Nov 22 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
JT
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
-----Original Message-----
the setup:

Webservice/WinClient application/SQL server.

VS.Net (visual basic) winform wizard creates a simple formwith 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. Onlyrecords 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

.

Nov 22 '05 #2

P: n/a
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.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

"Bruce Stockwell" <bs********@titan.com> wrote in message
news:33****************************@phx.gbl...
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

Nov 22 '05 #3

P: n/a
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
-----Original Message-----
Hi Bruce,

You should set AutoIncrement negative numbers - so they can't interfere withones already existing.
Take note that this are only provisional numbers which should be replacedafter storing rows to database.

--
Miha Markic - RightHand .NET consulting & software developmentmiha at rthand com
www.rthand.com

"Bruce Stockwell" <bs********@titan.com> wrote in message
news:33****************************@phx.gbl...
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

.

Nov 22 '05 #4

P: n/a
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...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...Product=vcSnet

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

"Bruce Stockwell" <an*******@discussions.microsoft.com> wrote in message
news:0d****************************@phx.gbl...
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
-----Original Message-----
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.

--
Miha Markic - RightHand .NET consulting & software

development
miha at rthand com
www.rthand.com

"Bruce Stockwell" <bs********@titan.com> wrote in message
news:33****************************@phx.gbl...
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

.

Nov 22 '05 #5

P: n/a
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
"Bruce Stockwell" <an*******@discussions.microsoft.com> wrote in message
news:0d****************************@phx.gbl...
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
-----Original Message-----
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.

--
Miha Markic - RightHand .NET consulting & software

development
miha at rthand com
www.rthand.com

"Bruce Stockwell" <bs********@titan.com> wrote in message
news:33****************************@phx.gbl...
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

.

Nov 22 '05 #6

P: n/a
Miha

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

V/R
Bruce
-----Original Message-----
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 fromthe original dataset and one in GetChanges() dataset).
After update and before Merge you'll have to update original rows you'vestored with actual identity values (don't forget to set
DataColumn.ReadOnly=false for this operation) you get after sucessfulupdate.
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 SQLServer 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 AccessDatabase By Using Visual C# .NET

http://support.microsoft.com/default.aspx?scid=kb;en- us;816112&Product=vcSnet
--
Miha Markic - RightHand .NET consulting & software developmentmiha at rthand com
www.rthand.com

"Bruce Stockwell" <an*******@discussions.microsoft.com> wrote in messagenews:0d****************************@phx.gbl...
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
>-----Original Message-----
>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.
>
>--
>Miha Markic - RightHand .NET consulting & software

development
>miha at rthand com
>www.rthand.com
>
>"Bruce Stockwell" <bs********@titan.com> wrote in message >news:33****************************@phx.gbl...
>> 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
>>
>
>
>.
>

.

Nov 22 '05 #7

P: n/a
JT
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

-----Original Message-----
Thanks for the response JT.

You nailed the problem.

Now for the solution. I'm aware of the seed and step
properties. I don't see how that would apply to a multi
user app. The app has no way of knowing what the next
autoincrement id is in the database. I could retrieve it
and set the inc programatically but then two users could
have the same initial seed while a third writes to the
database making the initial seed for the first two users
invalid anyway.

My frustration is why doesn't Dataset.merge understand
that the returning (from sql server) newly added rows
match the existing rows in the dataset and just merge themvice ignore them leaving hanging loose

Please stay with me on this one. I really do need help.
Thanks again JT for the quick reply

v/r
Bruce Stockwell
-----Original Message-----
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
-----Original Message-----
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

simpleDataset 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 3only 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

.

.


.

Nov 22 '05 #8

P: n/a
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
-----Original Message-----
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 fromthe original dataset and one in GetChanges() dataset).
After update and before Merge you'll have to update original rows you'vestored with actual identity values (don't forget to set
DataColumn.ReadOnly=false for this operation) you get after sucessfulupdate.
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 SQLServer 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 AccessDatabase By Using Visual C# .NET

http://support.microsoft.com/default.aspx?scid=kb;en- us;816112&Product=vcSnet
--
Miha Markic - RightHand .NET consulting & software developmentmiha at rthand com
www.rthand.com

"Bruce Stockwell" <an*******@discussions.microsoft.com> wrote in messagenews:0d****************************@phx.gbl...
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
>-----Original Message-----
>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.
>
>--
>Miha Markic - RightHand .NET consulting & software

development
>miha at rthand com
>www.rthand.com
>
>"Bruce Stockwell" <bs********@titan.com> wrote in message >news:33****************************@phx.gbl...
>> 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
>>
>
>
>.
>

.

Nov 22 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.