Negative Primary Key in local DB made with VS2010?

Question

I start a new project in MS Visual Studios 2010 Professional, and add a local database. I set up the DataSet and connect it to the form with a navigator, etc.

I open up the table and put a few test values in then test run it to try adding new values.
When I start the program and try to add new values it lets me, but the Primary Key populates as -1 (even though there are only 2 prior values). It will let me populate the rest of the fields as normal, but when I go to the next the primary key is assigned -2.

The primary key is an Int, no nulls allowed, Unique and Primary key set to true. Identity set to true, with increment 1, and seed as 1.

Any ideas?

Answer 1

It sounds like your insert statement is inserting the primary key from the values in the dataset. Datasets will store a negative number in such a column when you add new rows (They do this so that the column id doesn't conflict with an existing id).



If your insert statement explicitly lists the id column, then you will get those values, rather than the autoincrement value. So you don't want the id listed in the insert.



ETA: Did you make the id column the primary/autoincrement key after you added the table to your dataset? That might be the problem.



In the dataset designer, is there a yellow key icon next to your id column? If no, right click it and Set Primary Key.

Also, check the properties for that column. They should be:

AutoIncrement: True

AutoIncrementSeed: -1

AutoIncrementStep: -1

ReadOnly: True





Finally, in the dataset designer, right click the title for the table adapter and look at properties.

There you should see Insert Command. Click the triangle thingy, then click in CommandText and finally click the ... button. If the id column is checked for the query, uncheck it.



It takes a while to get the hang of the ins and outs of the dataset designer, but it can save you some time once you get to know how to use it.

Answer 2

Yeah stay away from the database controls. You have better control doing things old school.

Answer 3

Yeah... I don't trust any of that DataSet stuff: it always seems to work better for me if I write my own code to insert the values, rather than trusting Visual Studio to know, somehow, what I mean...