How can I automate the column titled as ID NUmber in my database, in VS 2005?

I have a table with a primary key titled as 'ID NUmber' which needs to be created automatically, however every time i add a new record the ID is not added and i have to write it manually i.e. 1, 2, 3.., could you please advice me how i can format this; i know you can do this with microsoft Access but with VS 2005 + VB language this option is not available under data type

*i am using VS 2005 and VB language

Go into design view for the table

toward the bottom, you'll see Identity Specification - open that up and set IS IDentity to True

|||Thank you, i have found the 'IS identity' but mine is set to 'No' and is blocked (i can't click on it). should i be using an specific data type; currenly i am using 'Real' is that right? I had to use 'Real' as with other data types the option for 'primary key' was not available.|||

Generally (though it might be possible some other way that I'm not familiar with), the way to do this is with an INT datatype, so it can automatically increment.

Maybe someone else can chime in here, how to do it some other way

|||

To set a column as an Identity column the datatype has to be one of the INT types - tinyint, smallint, int, bigint etc.

|||

i have used the tinyint to set the IS identity as 'yes', however when i run the database and add a new row to the database the row id is for example 9 instead of 2, even though i have deleted all the previous records and doesn't realise that i have now only 1 row and that the next id should be 2. is there any way to correct this?

|||

You need to read up books online about Identity columns. Once a number is assigned to a row, its gone. Even if the row is deleted the number is gone. Any new rows will get the next number. If you were doing this as a test and want to empty the table and reset the identity you can TRUNCATE the table instead of delete. Again, before doing the truncate, read up books online about the command. You can also use DBCC CHEKCIDENT to reset the seed value.

|||Remember, also - tinyint is defined like this:

tinyint

0 to 255

So - I'd recommend at least using INT

One last thing also - you said that your column is titled "ID Number"

My (and others) recommend that you remove the space (IDNumber) and that you never put spaces in your field or table names. Believe me, this will cause you less grief in the future.