473,320 Members | 2,098 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Auto Increment Problem with Dataset

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
8 6210
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Bruce Stockwell | last post by:
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...
2
by: David P. Donahue | last post by:
I'm using the following code to add a row to a table (for holding images) in my database (obtained from http://www.codeproject.com/aspnet/image_asp.asp?df=100&forumid=38225&select=1038401): ...
2
by: VJ | last post by:
I have a dataset with a AutoIncrement column.. I write this to a XML file and read it back..to a Dataset.. The auto increment works perfectly every time. It picks up were it left...were does it...
2
by: Tom | last post by:
I am trying to store information into a table that has an auto increment field. There is currently no data in the table. Using the code below I cannot insert data into the table. I get an error...
2
by: frazer | last post by:
hi i have created a dataset manually. is ther any way to have auto increment in it?
6
by: Alpha | last post by:
I retrieve a table with only 2 columns. One is a auto-generated primary key column and the 2nd is a string. When I add a new row to the dataset to be updated back to the database. What should I...
5
by: vul | last post by:
In VB6 there is Auto Increment check box in Project Properties, which allow you have a new version every time you compile the project. Is there any easy way to have this feature in VB 2005? Some...
2
by: john | last post by:
Is it true that if I split my access database in backend and frontend and I implement custom auto increment for the ID fields, that my database is ready to be used in a multi-user environment? I...
13
by: S.Dickson | last post by:
I had an access database that i use as an ordering system. I have a form for entering customer details. When i add a new customer on the form the customer number is an auto number that appears when...
0
by: TDC | last post by:
Hello, I'm sure this is simple to resolve but I can't seem to see the answer. I am using Visual Studio 2005 (VB). I created a local SQL database (using Visual Studio 2005) with a few related...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.