Why can't I insert data into local database (SQL Compact Edition) with C#?

Question

I am doing a project on Visual Studio. I am using a local database (empty sql server compact edition). I chose Dataset and created my table (Images). It has a primary autoincrement id column, and an nvarchar ImagePath column. I want to insert data in it and here is my code.

SqlCeConnection con = new SqlCeConnection();
con.ConnectionString = yeniApplicationDatabase.Properties.Settings.Default.DatabaseEdaConnectionString;
con.Open();
using (SqlCeCommand com = new SqlCeCommand("INSERT INTO Images (ImagePath) VALUES ('book')", con))
{
  com.ExecuteNonQuery();
}

I don't know why but this one doesn't give any error, the syntax(SQL) is fine. However, when I check the table data, it is still null. Here is the thing;

In the same run,
I execute that code, then I execute another one which is select * from images...
It shows 'book'. But still, the table data is empty, and when I rerun it without inserting, only selecting from Images, it is gone again. I really don't understand what is going on. Why can't I put anything in my database?

I also added con.Close() but it still doesn't work.

I have updated the answer. If the query doesn't work on connection clode, check the link that I have mentioned in the answer which deals with commiting insert transactions — reggie
– reggie, Commented Mar 21, 2011 at 17:35

Community · Accepted Answer · 2017-05-23 11:44:44Z

8

SqlCeConnection con = new SqlCeConnection();
con.ConnectionString =  yeniApplicationDatabase.Properties.Settings.Default.DatabaseEdaConnectionString;
con.Open();
SqlCeCommand com = new SqlCeCommand("INSERT INTO Images (ImagePath) VALUES ('book')", con);

com.ExecuteNonQuery();


com.Dispose();
con.Close();

Closing the connection should complete the insert.

EDIT: update on the solution

Which database file(.sdf) are you viewing to check whether the data has been inserted. check the content of the test table in the .sdf in the bin\Debug folder. I believe that your data is inserted properly in the database file which exist in bin\Debug folder.

Just found a similar question on stack overflow: Why can't I insert a record into my SQL Compact 3.5 database? and I firmly believe that your problem is exactly the same. you are checking the wrong database file.

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered Mar 21, 2011 at 17:25

reggie

13.8k13 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

31 Comments

Ada Over a year ago

this com is out of the using {..} block, it is con or com?

Ada Over a year ago

I tried con.Dispose() and con.Close() as you wrote but still it didn't add it.

reggie Over a year ago

I have updated the answer. try putting the code in a try catch block. and see if it gives you an error or gets executed successfully

Ada Over a year ago

i wrote try{ SqlCommand ..... } catch(Exception e ) and printed out the e but it didn't give any error.

Ada Over a year ago

I accepted the answer and I want to accept it a million times more. Thank you so much!

|

Dour High Arch · Accepted Answer · 2011-03-21 17:43:51Z

2

I do not use Eclipse, but something you must watch out for in Visual Studio when debugging local databases is the Copy file property for the database file in Solution Explorer.

By default this property is "Copy", which means a copy of the database is put in the bin\debug folder of your project and the connection string is to the copy. If you change the copy, your original file is not changed and the changes are discarded when your debug session exits.

See if your IDE copies the database and inspect the connection string. Also how are you "checking the data table"? Are you using the same connection string? Are you doing this after the debug session ends?

answered Mar 21, 2011 at 17:43

Dour High Arch

21.8k31 gold badges78 silver badges93 bronze badges

2 Comments

Ada Over a year ago

I write an sql statement like (select * from images). this is how I check. Is it wrong? And how can I check whether it copies? Do I need to close and open the connection each time after I execute a query? And another question is that do I have to lose all the data after I close Visual Studio? I wrote Eclipse by mistake, sorry.

Dour High Arch Over a year ago

select * from images is fine so long as the connection string used for the select is the same as that used for the insert. Is it? Put a breakpoint on the SqlCeCommand object and check.

Diligent Key Presser · Accepted Answer · 2014-10-28 09:50:40Z

0

SqlCeConnection conn = new SqlCeConnection(@"Data Source = c:\FullPath")

How to get full path: in solution explorer Right click on .Sdf then get fullPath.

edited Oct 28, 2014 at 9:50

Diligent Key Presser

4,3044 gold badges28 silver badges34 bronze badges

answered Oct 28, 2014 at 9:19

user4189173

1

Collectives™ on Stack Overflow

Why can't I insert data into local database (SQL Compact Edition) with C#?

3 Answers 3

31 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

31 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related