Solved

How to create an automatic row numbering database in SQL Server Compact

Posted on 2009-06-28
10
605 Views
Last Modified: 2013-11-27
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.

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

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

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


0
Comment
Question by:technicaltrader
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 69

Assisted Solution

by:Éric Moreau
Éric Moreau earned 25 total points
ID: 24732287
>>Second:
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.

This is the normal behaviour and that cannot be changed. Very often, this identity becomes the key and other records relate to this one using this key. It would quickly become a nightmare if you start moving all identities!
0
 

Author Comment

by:technicaltrader
ID: 24732590
OK. Now that I think about what you have pointed out that makes complete logic. Understood!

I will simplify this question then.

When I click to "AddNewItem" I can click it many times and create many new rows in the database. How can I limit this click to "AddNewItem" to create a new row, and another new row can not be created until the current row cells are populated? Possibly throws up a message of something like: "Current data is not complete" or something. Just thinking aloud now.

I like how this program is written. It is my first development. I am using this for my market trading. I have plans for other tools as well so teaching myself programming in VB. I am now just taking care of all the nuances that I don't like within this program. (Small details that makes it better)

Hope that makes sense.

Thanks again...
0
 
LVL 41

Accepted Solution

by:
pcelba earned 125 total points
ID: 24732603
Negative numbers are generated for record(s) which are in buffer memory to avoid gaps in numbering. Real numbers are assigned just before saving (when no way back exists). You still have a chance to cancel new record before saving but you need some temporary values used e.g. for relations.

Identity columns are designed to ensure unique ascending values but not consecutive values. Could you imagine how long it takes to renumber millions of records after one row deletion? If you need consecutive numbers on output then you should use ROW_NUMBER() function in your selects. More info: http://msdn.microsoft.com/en-us/library/ms186734.aspx

To avoid multiple insertions of empty rows you should add some validations. Validations on SQL Server level are called triggers. If you define insert trigger then you may control this behaviour "at the last instance". I would guess you'll need validation routine in VB. More info about SQL triggers:  http://msdn.microsoft.com/en-us/library/ms189799.aspx

For better data management at SQL Server level I would recommend to download SQL Management studio express: http://www.microsoft.com/express/sql/download/
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 100 total points
ID: 24741393
ROW_NUMBER() function is not available in Compact , nor are triggers. It is a pain...

Your only option is to use the Identity column.

Identity numbers are allocated and used at time of insert. So, if your insert fails, that number is used regardless, and you already know about deletes.

You can re-seed those numbers, but in compact, it really isn't worth it.

Where are you clicking "Addnewitem" ? if in your application, then there should be some kind of commit or insert statement. At that point, before the SQL to do the insert, test the columns to see if they contain information and disallow the update. You could flag various columns as being "mandatory". It really depends on how you have written your application.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 100 total points
ID: 24741409
By mandatory, you can use "NOT NULL" if adding directly to the table. It will give you and error message. However that identity value is already used. That is when it is best to manage in your code.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 125 total points
ID: 24743061
Good point, Mark. I did not realize it is not Express but Compact (even when mentioned in question title :-).

So, thanks for the clarification and my mistake correction.

Question to the author: Do you really plan to use it on Pocket PC?
0
 

Author Closing Comment

by:technicaltrader
ID: 31597700
Well to answer the question; "Do you really plan to use it on Pocket PC? Answer is "No" and as many red flags that were in my face, my new discovery of using SQL and Visual Basic again teaches me a new lesson. Fortunately I look to lessons such as this as "Constructive".

I wanted to use Express, not Compact. I failed to recognize the word "Compact". Being a bit naive and very new it doesn't surprise me. Now on my new quest of adding a database as express. I have become very efficient at starting a new database. Trouble is, I only see "Local Database" in "Add New Item" and comes up as "Compact". Express is loaded on this computer. Apparently I have new discoveries to unfold. If I can't figure it out, it will be time to start a new question. LOL...

Thanks to each of you as all comments taught me something!
0
 

Author Comment

by:technicaltrader
ID: 24746432
...and as I dig around about compact 3.5, it seems that this will work fine for the application as it is to be used. Believe I am on the right path at least for now. Again, you all directed me in a way with this question that I have progressed because of all of you. Again. I thank you!...
0
 
LVL 41

Expert Comment

by:pcelba
ID: 24749029
You are welcome, technicaltrader. I would recommend to download SQL Server Management Studio Express. It will allow easy database creation and SQL queries testing. If you need more features then you should buy SQL Server Developer version - cheap (not sure but around $50) and complete for development.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 24749151
Sorry for duplicate recommendation of SQL MSE :-). But it is free...
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Write a function 5 25
Advice on encrpting some store produces on MS SQL  sql 5 16
Mssql SQL query 14 28
Runtime 3044 error 14 17
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now