title: Sql Server CE - Temporary disable auto increment on a specific column
body: <p>I have this little question that's been on my mind for a while now.</p>
  
  <p>Here it goes:</p>
  
  <p>Is it possible to temporary disable the Auto_Increment on the column ID.</p>
  
  <p>So that I can add a new row to the table and being able specify the ID value when inserting the row.</p>
  
  <p>And then in the end enable the Auto_Increment again, and let do its work as usual?</p>
  
  <p>And if its possible how can I do it?</p>
  
  <p>The table structure is very simple</p>
  
  <p>Column name (attributes)</p>
  
  <pre><code>ID (Primary Key, Auto Increment, int, not null)
  Name (nvarchar(100), not null)
  </code></pre>
  
  <p>Note:</p>
  
  <ul>
  <li>The table name is: <code>People</code>.</li>
  <li>Let's also consider that the table already has data and cannot be changed.</li>
  <li>The database server is SQL Server CE.</li>
  <li>The SQL commands will be executed in a C# program, if it's of any help.</li>
  </ul>
  
  <p>I really hope its possible, it would come very handy.</p>
  
  <p>Thanks</p>
  
  <p><strong>EDIT</strong></p>
  
  <pre><code>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());
  }
  </code></pre>
  
  <p>The connection string is working, I have tried it.</p>
  
  <p>He reports:</p>
  
  <blockquote>
    <p>There was an error parsing the query.
    [ Token line number = 1,Token line
    offset
    = 20,Token in error = SET ]</p>
  </blockquote>
  
  <p><strong>SOLUTION</strong> <em>thanks to <a href="https://stackoverflow.com/users/39430/orbman">OrbMan</a></em></p>
  
  <pre><code>        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());
          }
  </code></pre>