Sql Server CE Insert fail without errors

Question

i have a problem in inserting some data into a table.

I have done a program with a simple DB. I use

• c#
• framework 3.5
• sql server mobile
• The program run on a GPRS device. Every day it receive a zip file, extract it and insert the rows into some tables.

Some quotes

Open the connection :

SqlCeConnection conn = new SqlCeConnection("DataSource = " + file);
conn.Open();                

Write a row :

String sql = "INSERT INTO Mercati(id,descr) VALUES(643,'SHAMPOO')"
SqlCeCommand execute = conn.CreateCommand();
execute.CommandText = sql;
int result = execute.ExecuteNonQuery();
execute.Dispose();

The data table is like that :

CREATE TABLE Mercati(id int PRIMARY KEY,descr nvarchar(40));

This is inside a try / catch. And every time result is 1 ( i cutted away debugging code ). I repeat that on different tables, with different data.

But SOMETIMES (1 time avery 4/5 transmissions) with one table the data are not inserted, with result=1 and no exceptions.

• Every time I transmit i reset the device and my program it's restarted.
• The tables are emptied every time (DELETE FROM Mercati)
• The data are every times the same (those are the base for a work, but they could change sometimes a months)

I'm really out of ideas. I cant understand what's the problem. Or simply how can i resolve that issue. Any solution or advise ?

Thanks a lot Daniele

Answer 1

I have resolved.

After INSERTs i do some backups, then i reset the device. BUT i didn't close the DB.

The strange is that i did the advice of SeaDrive, and after my insertions the table contains the exact number of rows. I did 9 INSERT and my select return 9 Rows.After reset i have only 1 row.

I think there is some sort of cache, that is transparent during execution, but leave the fisical file unchanged, unless the DB it's closed.

I hope that will help other poor developper like me to close their DB :D

Answer 2

The only thing I can think of that would make inserted data disappear is transaction control. Do you have any COMMIT/ROLLBACK programming?

Is there any chance that the connection string gets changed to a different DB?

As a debugging strategy, my first approach would be to read data from table after the insert to be sure the data is there. Look at the specific row and the total number of rows. I might try to read it at different points in the program to see when the row disappears.