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

Question

Hi guys.

I have this little question, thats been on my head for while now.

Here it goes:

Is it possible to temporary disable the Auto_Increment on the collum 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

Collum Name (atributes)

ID (Primary Key, Auto Increment, int, not null)

Name (nvarchar(100), not null)

Notice:

• The table name is: People.

• Lets 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 its 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());
        }

Answer 1

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());
}

Answer 2

Already answered here...