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();

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





"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.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();
>
>

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 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.
>
>
>
>
>
> "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.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();
> >
> >

"Alpha" <(E-Mail Removed)> wrote in message
news:A1CA1D6A-9C26-4A9E-9BA7-(E-Mail Removed)...
> 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).

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" <(E-Mail Removed)> wrote in message
> news:A1CA1D6A-9C26-4A9E-9BA7-(E-Mail Removed)...
> > 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).
>
>
>

"Alpha" <(E-Mail Removed)> wrote in message
news:ABCC64A8-6A88-4982-A8F5-(E-Mail Removed)...
> 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?

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.

"Scott Roberts" wrote:

>
> "Alpha" <(E-Mail Removed)> wrote in message
> news:ABCC64A8-6A88-4982-A8F5-(E-Mail Removed)...
> > 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?
>
>
>