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

T-Sql fill in gaps in autoincrement field

Hello

I am using Microsoft.Jet.OLEDB.4.0. and I need to use SET IDENTITY_INSERT table_name ON in order to fill in gaps in autoincrement field but every time when I start executing my query VB reports error:

"Invalid SQL statment; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

I have tried with UPDATE tablename SET IDENTITY_INSERT ON but nothing

How can I solve this problem with SET statment?

Thanks.
0
damirsel
Asked:
damirsel
  • 5
  • 3
1 Solution
 
Anthony PerkinsCommented:
>> fill in gaps in autoincrement field <<
This is a very bad idea.  However if you insist:
As the name indicates SET IDENTITY_INSERT is intended for Inserts not Updates.  Something like this:

SET IDENTITY_INSERT YourTableName ON
INSERT YourTableName (ID, Col1, Col2, ...) VALUES (4, @Value1, @Value2, ...)
SET IDENTITY_INSERT YourTableName OFF

0
 
damirselAuthor Commented:
Why do you think that this is very bad idea?
0
 
Anthony PerkinsCommented:
Because you will lock the table while the insert occurs and so block anyone else from adding a row until it is complete.  But you are right, if this is for maintaining your home recipes than it should not be a problem.  I am used to many users accessing the site simultaneously and I am not concerned about wasting a value, as there are another 2 billion to contend with :)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Anthony PerkinsCommented:
I should have mentioned that the other problem is that you first have to find the missing value, here is one way to do it:
Find gaps in sequence numbers.
http://www.nigelrivett.net/SQLTsql/FindGapsInSequence.html

This is an overhead that most shops cannot afford.
0
 
damirselAuthor Commented:
Yes my database is planed to be used only by one user so there is no conflicts, but thanks anyway for your response. Now I am heaving real trouble to SET IDENTITY i have tried like this but still the same error appears

Dim con As ADODB.Connection
Set con = New ADODB.Connection

con.ConnectionString = "Mode=12;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mybase.mdb;User ID=admin;Password=;Jet OLEDB:Database Password=mypass;"
con.Open
con.Execute "SET IDENTITY_INSERT my_table ON INSERT INTO my_table(id,name) VALUES(3,'Jack') SET IDENTITY_INSERT my_table OFF"

Where am I going wrong ?
0
 
Anthony PerkinsCommented:
>>I am using Microsoft.Jet.OLEDB.4.0. and I need to use SET IDENTITY_INSERT table_name ON<<
My apologies.  You mentioned SET IDENTITY _INSERT and I assumed you were using MS SQL Server, since it appears you are using MS Access you are out of luck as that command (SET IDENTITY _INSERT) is not available in MS Access.
0
 
damirselAuthor Commented:
Is there some other way using Jet.OLEDB.4.0 to do this? (not neccesserely autonumber field)
0
 
Anthony PerkinsCommented:
If it is not an autonumber field, that of course you can insert any row with any ID you like.  If it is an autonumber row, you may need to resort to rebuilding the table every time.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now