How to create an automatic row numbering database in SQL Server Compact
Posted on 2009-06-28
Couple parts to this question so I will list them all as I believe they would be related in the problem to be solved. If not all related I would like help with the first question to start.
Trying to create a automatic row numbered database in SQL Server Compact. Now I can create the numbering, but is really odd how it operates. When you add a new row it numbers it in negatives (IE: -1 -2 -3 ...etc). When you save your data and re-open the database it is numbered properly and in positives.
When I delete rows, the numbering of the rows doesn't update. So if I have row 1 2 3 4 and 5 and delete row 2 and 4, save my work and re-open the database I now have 1 3 and 5. Would like it to update the numbering structure when rows are deleted.
I can click on "Add new row" several times and it does, Would like to only add a new row and no other rows can be added until the current "New Row" is populated.
I am using Visual Basic 2008 Express to build this database in SQL Server Compact. The database works very nice for me as it is but would like to figure out how to number this database in an efficient manner.
My process for creating and numbering this database:
1. "File" and then click "New Project"
2. Under "Project" I click "Add New Item"
3. I select "Local Database" in the "Add New Item" window
4. In the "Database Explorer" I right click "Tables" and click on "Create Table"
5. I name my "Column" and in "Data Type" I select either "bigint" or "int" and change the Default Value of "Identity" to True and "IdentityIncrement" and "IdentitySeed" to "1" .
6. Add the rest of my data columns
That is how I create my automatically numbering database but operates in a quirky manner. Hope this isn't overly detailed. Will provide additional details if needed. Thanks in advance.