I am using a VS generated data adapter to retrieve data and populate a table. Under certain conditions, it returns an empty table. I fill a dataset which is bound to a grid. The grid therefore comes up blank. This is as expected.

Because I get no records back, I use the Rows.Add method to add records to the table in my bound dataset. This works fine and the Grid now shows the records. BUT - when I go to update the table with the data now in the grid, I get an error as soon as the SECOND record is added. It tells me that the Identity field must be unique. This makes sense, of course, except I expected the Insert generated from the dataset to figure that out for me and increment it automatically like any manually created Insert statement would. Instead, it appears as though every record I've added to the table in my dataset contains the VERY SAME VALUE for this Identity field.

What magic must I perform to get the *&^%$ identity field to increment properly?

Assuming your using SQL Server, I'd check and make sure that you set it not only as an identity, but autonumber- which is done seperately.

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

This is not entirely true. In SQL Server, the Identity setting *is* the AutoNumber. You may be confusing the Identity with the Primary Key setting for the ID field.....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Allegretto non troppo - Allegro molto vivace by Mendelssohn & Bruch (Track 3 from the album: Violinkonzerte - Hoelscher)

It's a SQL Server INT field with Identity set to YES and Increment set to 1. I was under the impression that AUTONUMBER is an Access field type, and not a SQL Server type. Where is this AUTONUMBER in Sql Server and how do I make it work?

Nono- I typed it wrong.. Imar corrected me .. The field with identity set to yes will autonumber. As usual when I try to answer a technical question, I do it wrong :).

This is the kind of thing that I like to avoid, and why I don't use the identity field.. Instead I use a GUID as my primary keys.

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

Identity fields work fine EXCEPT in this instance. It looks like it might have to do with having the Identity field returned by the Select statement used to populate the table. I've removed it and the problem appears to have gone away.

Go figure!

So Ron, How are you creating the Identity key then?

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

Hal,

I'm not creating the Identity field value, nor have I ever. In the past, when creating my own Insert statements, I have ignored that type of field with the assumption that SQL Server would populate it during Insert. Removing it from the Select for this bound table appears to produce the same result. Very strange, but I'm happy to report that that appears to have fixed the problem.

I am not sure removing the Identity field from the dataset solved this after all. I discovered I could not update records in the grid without the key field (which also hapens to be the udentity field) and thought for a while I was stuck in some typical .NET Catch-22. But after putting it back in the Select statement, I found I could still add new records as well as update existing ones.

Now I believe the problem was that the Connection and DataAdapters automatically generated by the Server Browser were somehow in error. In the midst of making these changes I had to recreate these objects because for some reason the adapter was not rebuilding the Insert and Update statements when I modified the Select command in the VS IDE Properties window. I could add and remove fields and the Insert and Update statements just would not change. But when I rebuilt the objects, this problem magically went away.

So, I'd say chalk it up to yet another bug in the VS IDE.