473,847 Members | 1,735 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

What to do with AUTO-ID column when I add a new record to the data

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 do with the 1st column ? (Below
I have a "1" in place for now). Also, Does the datase.AcceptCh anges();
updates the changes to the database? Which command do I use to update the
changes in dataset back to the Access database table? Thanks, Alpha

dtSI.Rows.Add(n ew Object[] {1, txtNewitem.Text });
dsServiceItems1 .AcceptChanges( );
Nov 17 '05 #1
6 5075
Usefula ADO.NET article:

http://msdn.microsoft.com/library/de...adonetbest.asp

Excerpt from above article:

Avoiding Auto-Increment Value Conflicts
Like most data sources, the DataSet enables you to identify columns that
automatically increment their value when new rows are added. When using
auto-increment columns in a DataSet, with auto-increment columns from a data
source, avoid conflicts between the local numbering of rows added to the
DataSet and rows added to the data source.

For example, consider a table with an auto-incrementing primary key column
of CustomerID. Two new rows of customer information are added to the table
and receive auto-incremented CustomerID values of 1 and 2. Then, only the
second customer row is passed to the Update method of the DataAdapter, the
newly added row receives an auto-incremented CustomerID value of 1 at the
data source, which does not match the value 2, in the DataSet. When the
DataAdapter fills the second row in the table with the returned value, a
constraint violation occurs because the first customer row already has a
CustomerID of 1.

To avoid this behavior, it is recommended that, when working with
auto-incrementing columns at a data source and auto-incrementing columns in a
DataSet, you create the column in the DataSet with an AutoIncrementSt ep of -1
and an AutoIncrementSe ed of 0, as well as ensuring that your data source
generates auto-incrementing identity values starting from 1 and incrementing
with a positive step value. As a result, the DataSet generates negative
numbers for auto-incremented values that do not conflict with the positive
auto-increment values generated by the data source. Another option is to use
columns of type Guid instead of auto-incrementing columns. The algorithm that
generates Guid values should never generate the same Guid in the DataSet as
is generated by the data source.

If your auto-incremented column is used simply as a unique value, and does
not have any meaning, consider using Guids instead of auto-incrementing
columns. They are unique and avoid the extra work necessary to work with
auto-incremented columns.

"Alpha" wrote:
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 do with the 1st column ? (Below
I have a "1" in place for now). Also, Does the datase.AcceptCh anges();
updates the changes to the database? Which command do I use to update the
changes in dataset back to the Access database table? Thanks, Alpha

dtSI.Rows.Add(n ew Object[] {1, txtNewitem.Text });
dsServiceItems1 .AcceptChanges( );

Nov 17 '05 #2
I need to do this for both listbox controls and datagrid control. In the DB
there is a primary key, auto-genrated, for each of these tables. I bring
that key along with other columns into dataset. The key has to be part of
the dataset so I know which record the users have selected or udpated. I
also want to allow users to add additonal records(rows) to the controls. Is
there a easier way to do this? It sees like a lot of work just to add some
records. Doesn't the dataset automatically know to increment the primary key
column or should I increment that myself?

"Richard" wrote:
Usefula ADO.NET article:

http://msdn.microsoft.com/library/de...adonetbest.asp

Excerpt from above article:

Avoiding Auto-Increment Value Conflicts
Like most data sources, the DataSet enables you to identify columns that
automatically increment their value when new rows are added. When using
auto-increment columns in a DataSet, with auto-increment columns from a data
source, avoid conflicts between the local numbering of rows added to the
DataSet and rows added to the data source.

For example, consider a table with an auto-incrementing primary key column
of CustomerID. Two new rows of customer information are added to the table
and receive auto-incremented CustomerID values of 1 and 2. Then, only the
second customer row is passed to the Update method of the DataAdapter, the
newly added row receives an auto-incremented CustomerID value of 1 at the
data source, which does not match the value 2, in the DataSet. When the
DataAdapter fills the second row in the table with the returned value, a
constraint violation occurs because the first customer row already has a
CustomerID of 1.

To avoid this behavior, it is recommended that, when working with
auto-incrementing columns at a data source and auto-incrementing columns in a
DataSet, you create the column in the DataSet with an AutoIncrementSt ep of -1
and an AutoIncrementSe ed of 0, as well as ensuring that your data source
generates auto-incrementing identity values starting from 1 and incrementing
with a positive step value. As a result, the DataSet generates negative
numbers for auto-incremented values that do not conflict with the positive
auto-increment values generated by the data source. Another option is to use
columns of type Guid instead of auto-incrementing columns. The algorithm that
generates Guid values should never generate the same Guid in the DataSet as
is generated by the data source.

If your auto-incremented column is used simply as a unique value, and does
not have any meaning, consider using Guids instead of auto-incrementing
columns. They are unique and avoid the extra work necessary to work with
auto-incremented columns.

"Alpha" wrote:
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 do with the 1st column ? (Below
I have a "1" in place for now). Also, Does the datase.AcceptCh anges();
updates the changes to the database? Which command do I use to update the
changes in dataset back to the Access database table? Thanks, Alpha

dtSI.Rows.Add(n ew Object[] {1, txtNewitem.Text });
dsServiceItems1 .AcceptChanges( );

Nov 17 '05 #3

"Alpha" <Al***@discussi ons.microsoft.c om> wrote in message
news:A1******** *************** ***********@mic rosoft.com...
I need to do this for both listbox controls and datagrid control. In the DB there is a primary key, auto-genrated, for each of these tables. I bring
that key along with other columns into dataset. The key has to be part of
the dataset so I know which record the users have selected or udpated. I
also want to allow users to add additonal records(rows) to the controls. Is there a easier way to do this? It sees like a lot of work just to add some records. Doesn't the dataset automatically know to increment the primary key column or should I increment that myself?


The problem is that the database is generating the key, not the DataSet. So
when you insert a record the DataSet would need to re-read the record to get
the key. This is messy, messy, messy - and it's not DB independant. If you
have any control over the database (and specifically key generation) then I
would recommend you change it (either use a stored procedure in the DB to
generate the key or use GUIDs).
Nov 17 '05 #4
Ok, what if I don't bring in the primary key into the dataset because I have
another column that is unique contrained. Now can I just use the update from
the dbadapter to insert the records back to the database? Will that work? I
mean, shouldn't the database generate the key for the new record but then
it's not passed back to the dataset which has it's own indexing, right?

Thanks, Alpha

"Scott Roberts" wrote:

"Alpha" <Al***@discussi ons.microsoft.c om> wrote in message
news:A1******** *************** ***********@mic rosoft.com...
I need to do this for both listbox controls and datagrid control. In the

DB
there is a primary key, auto-genrated, for each of these tables. I bring
that key along with other columns into dataset. The key has to be part of
the dataset so I know which record the users have selected or udpated. I
also want to allow users to add additonal records(rows) to the controls.

Is
there a easier way to do this? It sees like a lot of work just to add

some
records. Doesn't the dataset automatically know to increment the primary

key
column or should I increment that myself?


The problem is that the database is generating the key, not the DataSet. So
when you insert a record the DataSet would need to re-read the record to get
the key. This is messy, messy, messy - and it's not DB independant. If you
have any control over the database (and specifically key generation) then I
would recommend you change it (either use a stored procedure in the DB to
generate the key or use GUIDs).

Nov 17 '05 #5

"Alpha" <Al***@discussi ons.microsoft.c om> wrote in message
news:AB******** *************** ***********@mic rosoft.com...
Ok, what if I don't bring in the primary key into the dataset because I have another column that is unique contrained. Now can I just use the update from the dbadapter to insert the records back to the database? Will that work?


Presumably, yes. But then why have an auto-generated primary key?
Nov 17 '05 #6
I was going to use the auto-generated key for uniquely identifying each
record in database. I did run into another column where it also needs to be
unique but it's entered by user so I put a unique constrain on it too. I'm
more comfortable using the auto-key for query and identifying records but for
this case I think this is just what I have to do to accomodat the ability for
users to add records. Thanks for your help. I'll give it a try.

But why the database wasn't updated when I use the
dbadapater.upda te(dataset, tablename) when the application closed to save the
changes I made in the datagrid. Did I forget to do somehting else?
Thanks.

"Scott Roberts" wrote:

"Alpha" <Al***@discussi ons.microsoft.c om> wrote in message
news:AB******** *************** ***********@mic rosoft.com...
Ok, what if I don't bring in the primary key into the dataset because I

have
another column that is unique contrained. Now can I just use the update

from
the dbadapter to insert the records back to the database? Will that work?


Presumably, yes. But then why have an auto-generated primary key?

Nov 17 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
6134
by: Robert Downes | last post by:
I'm using the following in a page that I'm testing in Mozilla: p.actionLinkBlock {border: 1px #000000 dashed; padding: 0.2cm; width: auto} But the dashed border is extending to the right-edge of the screen. I want it to only extend as far as it needs to to nicely contain the content within (a couple of links). Is width: auto the wrong property to do this? Is Mozilla rendering the style wrong?
1
6465
by: Theodore A. Jencks | last post by:
Hi all, I posted a question about a side navigation bar earlier where I was encountering this problem. I thought I'd repost the question in a more general format because I'm not sure people quite understood what I was asking in my last post. Here is what I'd like to do: 1. Have a div tag that's soul purpose is to display a repeating background image in the y-axis. 2. The div tag should also have a fixed width.
7
6561
by: Ugrasena via DBMonster.com | last post by:
Hi team i am using DB2 7.2 on Windows2000. We have lot many SQL Procedure Calls, is there any way i can Turn off the Auto Commit on that Database, the Problem i have is many of our Application Dont Use Auto Commit = false, and if we have any Error in the SQL procedure say after Executing 10 Update and Insert Statements the DB2 is commiting all the Previous Statements, even though there was an Error, how can i prevent this from the Database...
20
2886
by: Vijay Kumar R. Zanvar | last post by:
Hello, Unlike register, auto keyword can not be used to declare formal parameter(s). Is there any specific reason for this? Kind regards, Vijay Kumar R. Zanvar
5
3287
by: maya | last post by:
at work they decided to center divs thus: body {text-align:center} #content {width: 612px; text-align:left; margin: 0 auto 0 auto; } this works fine in IE & FF, EXCEPT in FF it doesn't work if I change 'auto' to 0 for left and right margin values; I have to leave those at 'auto'.. so I would like to know what exactly means 'auto' -- what value it represents exactly (and does it apply for all elements/values you might apply 'auto' to?)
2
3129
by: Ehud Banai | last post by:
Hi All, Is there any script or dll that allows me to do auto correction to digital pictures from PHP? Thanks in advance, Ehud
4
8837
by: =?Utf-8?B?TWFyaw==?= | last post by:
Hi... Following the samples online, we implemented a windows service in .Net. Like most services, there's a worker thread actually doing stuff in the background. The problem we've run into is how to get the service to exit when the worker thread has a fatal error *and* get the SCM to invoke the auto-restart configuration? The worker thread can error out while the parent thread in the server process chugs along. We can set the exit...
22
3101
by: nospam_news | last post by:
I currently get asked about my usage of "auto". What is it for? The keyword is clearly superflous here. In contrast to the huge majority of C/C++ developers I write definitions very explicitly like that: int main(char argc, char *argv, char *env) { try { auto Exception mainException(1); mainException.setErrNo(42);
13
1793
by: LC's No-Spam Newsreading account | last post by:
I have happily lived for 15 years (our first web server started in 1993) without CSS, but I now decided to learn and experiment with it. My personal preference has always been that the width of items in my pages are USUALLY not set in fixed values, but as percentages (e.g. for frames in frameset, or tables w.r.t the display area), since a priori I cannot know what screen or window size users will use, and the browser window can be...
21
6371
by: JOYCE | last post by:
Look the subject,that's my problem! I hope someone can help me, thanks
0
10982
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10645
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10706
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10335
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7053
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5909
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4524
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4116
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3160
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.