Help: SQL CE insert fails until I view the table

Question

I am noticing something strange happening. I'm porting an application to work with Sql Server CE 3.5, and finding something strange happening.

What i'm seeing is that an insert will fail (violating the primary key constraint), and will keep failing until I use Visual Studios Server Explorer and open the table with the Show Data function.

Whether its parameterized sql filling in all columns (except primary key) in my code, or even running "INSERT INTO SecurityUser (UserName) VALUES ('ch00k')" from server explorer, its always a primary key constraint (in this case the primary key is SecurityUserID, and I have double checked that it is an auto increment field), and it always works (and keeps working) once i've opened the table in visual studio.

Has anyone come across anything like this before? Or is there some extra bit of sql I should be running when I create the table, to get the primary key to work? The create table code has just been ported straight from Sql Server 2005 code, and runs without error.

Answer 1

Do you by any chance start a transaction? If so, are you sure you called Commit on it? If not, you should.

Answer 2

I am using a transaction, but the insert fails with an exception, which it shouldn't do. There is nothing else connected to the database, i'm not setting a primary key explicitly, but it still fails. Its very strange.