• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 705
  • Last Modified:

VSE VB.NET SQL SERVER CE how to reset identity column on tables.

For most all database design I have been using the DBE in Visual Basic Express 2010 with SQL Server Compact Edition 3.5.  Now I am ready to test deployment and I want to reset my database to its unused state.

This means I need to reset all the identity columns back to 0.

Is there a way to do this within VBE 2010?  If so, how?  

If not, what would you suggest as an alternative approach?

Thanks in advance.
0
codefinger
Asked:
codefinger
  • 9
  • 6
  • 3
  • +2
2 Solutions
 
Aaron TomoskyTechnology ConsultantCommented:
I know you can use SQL management studio to connect to the db and do this... Vb isn't my thing.
0
 
Alpesh PatelAssistant ConsultantCommented:
If there is no data in table then truncate it. It will automatically reset the counter.

If there is data in table then i is not possible to reset counter.

Thanks,

Alpesh
0
 
Kalyanum Deepak KumarCommented:
Hi Check this, might need to deal with this carefully and check there is no data loss for production environment.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Kalyanum Deepak KumarCommented:
oh sorry : here is the url to check http://msdn.microsoft.com/en-us/library/ms176057.aspx
0
 
Kalyanum Deepak KumarCommented:
basically, it describes DBCC commands.
0
 
codefingerAuthor Commented:
From another website:

The Truncate table command does not work in SQL SERVER CE. A great work around for this is to use “DELETE from [Table]” and then to reset the Identity column if you are using one you can use “ALTER TABLE [TableName] ALTER COLUMN ID IDENTITY (1,1)”

My problem is I do not see a SQL Worksheet in the VBE IDE in which to enter this command.
Is it not available in the IDE or am I just missing it?
0
 
Aaron TomoskyTechnology ConsultantCommented:
Get the SQL server management studio. That's what it's for.  
0
 
codefingerAuthor Commented:
aarontomosky:

I downloaded it.  Two questions:

Is 2008 the latest available Express (free) version?

How do I install the bulk of it to my E: drive, where I have more room?
(All the paths during the setup are greyed out so they cannot be changed.)

Thanks!
0
 
Aaron TomoskyTechnology ConsultantCommented:
The newest version is 2008 R2. You can choose the install folder but it's not a large install.
0
 
CodeCruiserCommented:
Execute the following command using a command object

ALTER TABLE <IdentityTable> ALTER COLUMN <Identity Column> <Data Type> (51, 1);

 
0
 
codefingerAuthor Commented:
Have not tried it with the command object yet, but I wonder why this did not work in the SQL Management Studio (there was an error parsing the query)::

ALTER TABLE CommandCodes ALTER COLUMN CommandNo <int> (51, 1);

Is the  syntax not quite right?  Do I need some single quotes somewhere maybe?  (and I wonder what (51,1) has to do with?)

0
 
Aaron TomoskyTechnology ConsultantCommented:
If you right click modify the table there is a line in the properties for increment value.
0
 
codefingerAuthor Commented:
aarontomosky:


Not seeing it. May not be there for SQL Server Compact Edition, or not having used this tool before, I just don't know precisely where to look and click.   Right clicking on the table name my only choices are  Edit Table and Properties.    Editing the table schema I can change the identity seed, but since the table was previously populated, I assume that no longer has any effect.  Difficult to say for sure, since from within this tool I do not see how to display the table data and add a row to it like I can with the DBE and the VBE IDE.

Please advise.
0
 
Aaron TomoskyTechnology ConsultantCommented:
Correct, you must clear the table to reset the identity seed or else it would assign the same Pk values all over again.
0
 
codefingerAuthor Commented:
The table is empty, but as yet I have been unable to truncate it or do anything to set it back to an "unused" state.    So right now, it likely still "remembers" the last identity it used and will most likely continue to increment from that point.   I suspect if I can get the syntax right, CodeCruiser's suggestion will work, but I would also like to understand at least this much about how to use this tool, since I took the trouble to find it, download it, and install it and it may prove useful in the future.



0
 
codefingerAuthor Commented:
Success!

ALTER TABLE CommandCodes ALTER COLUMN CommandNo IDENTITY (1,1);


BTW, aarontomosky, how do I clear the result panes?
0
 
Aaron TomoskyTechnology ConsultantCommented:
Just close it
0
 
codefingerAuthor Commented:
aarontomosky:

I see menu options to hide it or show it, none to clear it and it does not have a close X
0
 
codefingerAuthor Commented:
Ah, but the message window text is EDITABLE, which I did not expect.  I can select it all and delete it.
0
 
codefingerAuthor Commented:
CodeCruiser was on the right track, but the actual SQLthat worked for me is what I posted under the word "Success!"
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 6
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now