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

[Guest]

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.AcceptChanges();
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(new Object[] {1, txtNewitem.Text});
dsServiceItems1.AcceptChanges();

 

[Guest]

Usefula ADO.NET article:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/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 AutoIncrementStep of -1
and an AutoIncrementSeed 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.

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.AcceptChanges();
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(new Object[] {1, txtNewitem.Text});
dsServiceItems1.AcceptChanges();

 

[Guest]

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?

Usefula ADO.NET article:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/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 AutoIncrementStep of -1
and an AutoIncrementSeed 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.

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.AcceptChanges();
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(new Object[] {1, txtNewitem.Text});
dsServiceItems1.AcceptChanges();

 

[Scott Roberts]

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).

 

[Guest]

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]

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?

 

[Guest]

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.update(dataset, tablename) when the application closed to save the
changes I made in the datagrid. Did I forget to do somehting else?
Thanks.