damirsel
asked on
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.
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.
ASKER
Why do you think that this is very bad idea?
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 :)
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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=Microsof
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 ?
Dim con As ADODB.Connection
Set con = New ADODB.Connection
con.ConnectionString = "Mode=12;Provider=Microsof
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 ?
>>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.
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.
ASKER
Is there some other way using Jet.OLEDB.4.0 to do this? (not neccesserely autonumber field)
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.
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