Sql Server CE - Temporary disable auto increment on a specific column

Question

I have this little question that's been on my mind for a while now.

Here it goes:

Is it possible to temporary disable the Auto_Increment on the column ID.

So that I can add a new row to the table and being able specify the ID value when inserting the row.

And then in the end enable the Auto_Increment again, and let do its work as usual?

And if its possible how can I do it?

The table structure is very simple

Column name (attributes)

ID (Primary Key, Auto Increment, int, not null)
Name (nvarchar(100), not null)

Note:

The table name is: People.
Let's also consider that the table already has data and cannot be changed.
The database server is SQL Server CE.
The SQL commands will be executed in a C# program, if it's of any help.

I really hope its possible, it would come very handy.

Thanks

EDIT

SqlActions SqlActions = new SqlActions();

SqlCeCommand SqlCmd = new SqlCeCommand("SET IDENTITY_INSERT People ON", SqlActions.Connection());

try
{
    SqlCmd.ExecuteNonQuery();
}
catch (SqlCeException Error)
{
    Console.WriteLine(Error.ToString());
}

string query = "INSERT INTO People SET (ID, Nome) VALUES (@ID, @Nome)";

SqlCeCommand SqlInsert = new SqlCeCommand(query, SqlActions.Connection());

SqlInsert.Parameters.AddWithValue("@ID", 15);
SqlInsert.Parameters.AddWithValue("@Nome", "Maria");

try
{
    SqlInsert.ExecuteNonQuery();
}
catch (SqlCeException Error)
{
    Console.WriteLine(Error.ToString());
}

The connection string is working, I have tried it.

He reports:

There was an error parsing the query. [ Token line number = 1,Token line offset = 20,Token in error = SET ]

SOLUTION thanks to OrbMan

        SqlActions SqlActions = new SqlActions();

        SqlCeCommand SqlCmd = new SqlCeCommand("SET IDENTITY_INSERT People ON", SqlActions.Connection());
        try
        {
            SqlCmd.ExecuteNonQuery();

            string query = "INSERT INTO People (ID, Nome) VALUES (@ID, @Nome)";
            SqlCmd.CommandText = query;
            SqlCmd.Parameters.AddWithValue("@ID", 15);
            SqlCmd.Parameters.AddWithValue("@Nome", "Vania");
            SqlCmd.ExecuteNonQuery();
        }
        catch (SqlCeException Error)
        {
            Console.WriteLine(Error.ToString());
        }

Already answered [here](http://stackoverflow.com/questions/682654/sql-server-ce-3-5-identity-insert)... — Dean Kuga, Apr 21 '10 at 15:40

D'Arcy Rittich · Accepted Answer · 2010-04-21T21:30:06.427

8

I believe you can use SET IDENTITY_INSERT. I am not sure if this works in all versions.

Update 2:

Try this version:

SqlActions SqlActions = new SqlActions();
SqlCeCommand SqlCmd = new SqlCeCommand("SET IDENTITY_INSERT People ON", SqlActions.Connection());
try
{
    SqlCmd.ExecuteNonQuery();
    string query = "INSERT INTO People (ID, Nome) VALUES (@ID, @Nome)";
    SqlCmd.CommandText = query;
    SqlCmd.Parameters.AddWithValue("@ID", 15);
    SqlCmd.Parameters.AddWithValue("@Nome", "Maria");
    SqlCmd.ExecuteNonQuery();
}
catch (SqlCeException Error)
{
    Console.WriteLine(Error.ToString());
}

edited Apr 21 '10 at 21:30

answered Apr 21 '10 at 15:36

D'Arcy Rittich

167,292
40
290
283

I've tried "SET IDENTITY_INSERT People ON" and then i tried to insert some rows specifying the ID, and the program crashes. And VB is only able to say this: "The column cannot be modified. [Column name = id]" – Fábio Antunes Apr 21 '10 at 16:15
The same happens if i try add a row and specifying the ID and not running "SET IDENTITY_INSERT". For what i can see i think "SET IDENTITY_INSERT" doesn't as any affect. – Fábio Antunes Apr 21 '10 at 16:21
1

Are you executing it as one semi-colon separated batch statement? – D'Arcy Rittich Apr 21 '10 at 19:29
Separated statement.. According to Sql Server Docs, set indentity_insert works with SQL Server CE 3.5 e newer. Mine is 3.5, do i don't see whats the problem. – Fábio Antunes Apr 21 '10 at 19:30
Its in my new edit. PS: Its C# code, i made a mistake before and typed VB in my comment, sorry. – Fábio Antunes Apr 21 '10 at 20:39
1

@Fabio: you are doing it as two queries, you need to do it in one, e.g., `string query = "SET IDENTITY_INSERT People ON;INSERT INTO People SET (ID, Nome) VALUES (@ID, @Nome)";` – D'Arcy Rittich Apr 21 '10 at 21:05
Still not working. "There was an error parsing the query. [ Token line number = 1,Token line offset = 31,Token in error = INSERT ]" – Fábio Antunes Apr 21 '10 at 21:10
Hmm, ok, try re-using the same command object in your code. I'll post an updated example. – D'Arcy Rittich Apr 21 '10 at 21:17
No luck :( "There was an error parsing the query. [ Token line number = 1,Token line offset = 20,Token in error = SET ]" I really don't have idea wants wrong :S Many examples on the web do the same as you told me. – Fábio Antunes Apr 21 '10 at 21:27
1

@Fabio: No that's good! It is complaining about your `INSERT` query syntax now. Remove the word `SET`, it should not be there for an `INSERT`, that is for `UPDATE` queries. I updated my code. – D'Arcy Rittich Apr 21 '10 at 21:29
Bingo. This will save me a lot of trouble in future cases when restoring the database from a xml file. My greatest thanks. – Fábio Antunes Apr 21 '10 at 21:38

Sql Server CE - Temporary disable auto increment on a specific column

1 Answers1