Insert Into local database C#

Question

I can't Insert and select from Local database data in C#.

I've read these articles

All the code samples are the same, here's my sample.

SqlCeConnection conn = new SqlCeConnection(@"Data Source=|DataDirectory|\PacjenciDB.sdf");
conn.Open();
SqlCeCommand cmd = conn.CreateCommand();

cmd.CommandText="INSERT INTO pacjenci (nazwiskoimie,adres,skierowany,opis) values (@nazwiskoimie,@adres,@skierowany,@opis)";
cmd.Parameters.AddWithValue("@nazwiskoimie", txtnazwiskoimie.Text);
cmd.Parameters.AddWithValue("@adres", txtadres.Text);
cmd.Parameters.AddWithValue("@skierowany", txtskierowany.Text);
cmd.Parameters.AddWithValue("@opis", txtopis.Text);

cmd.ExecuteNonQuery();

conn.Close();

Can someone tell me what I'm doing wrong?

I've tried tons of samples about insert data, but it doesn't work.

I can manage .MDF, but .SDF seems quite problematic.

Hope you help me

Thanks for your reply. No, after execute code I clicked Show Table Data and select * from, and DB is empty (NULL,NULL...). I've tried in try/catch, but there aren't any errors — Sheep
– Sheep, Commented Jun 8, 2014 at 10:00
Personally, I wouldn't access a DB like that; why not use Linq-To-SQL or Linq-To-Entities? — frenchie
– frenchie, Commented Jun 8, 2014 at 10:00
I want to create an application with Local Database, LocalDB has .sdf extension — Sheep
– Sheep, Commented Jun 8, 2014 at 10:02
Localdb is not meant for application deployment, only development. FIrst page of the documentation pretty much sentence one tells you about that. On top, LOCALDB HAS NOT THE SDF EXTESION - that is SQL EMBEDDED (SqlCE) which is also the classe you use. Please get your basics straight. — TomTom
– TomTom, Commented Jun 8, 2014 at 10:06

martin · Accepted Answer · 2014-06-08 12:17:46Z

1

Ok, I'm going to take a guess here. Is the PacjenciDB.sdf included into Visual Studio project by any chance? Do you have the property "Copy to output folder" set to "Always" or something similar? It seems that every time you do a build you could be overwriting your output folder database file. Try putting the database in a folder that is not inside VS project.

BTW, your code is OK.

answered Jun 8, 2014 at 12:17

martin

2991 silver badge6 bronze badges

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

1 Comment

Sheep Over a year ago

Thank you for your reply, the problem was with the path of my .sdf file, I had .sdf in bin/debug and in files project second .sdf with the same name, in VS I set query DB in project folder, but String Connection was set to |DataDirectory| Thank you for Your help! Best Regards

ErikEJ · Accepted Answer · 2014-06-08 14:08:30Z

1

Look for a copy of the database with data in your bin/debug folder.

Solution is to not use |DataDirectory|, but use full path instead.

answered Jun 8, 2014 at 14:08

ErikEJ

42.1k5 gold badges82 silver badges120 bronze badges

1 Comment

Sheep Over a year ago

Thank you for your reply, the problem was with the path of my .sdf file, I had .sdf in bin/debug and in files project second .sdf with the same name, in VS I set query DB in project folder, but String Connection was set to |DataDirectory| Thank you for Your help! Best Regards

user2316116 · Accepted Answer · 2014-06-08 12:09:19Z

0

The above code is correct and most likely the problem is somewhere else, where you call that code from. If there is a problem with db connection or data is wrong - you should get an exception. Since there is no error occurred and no new records added - the code is not executed at all.

P.S.

.sdf is a Sql Server Compact Local Database, so using System.Data.SqlServerCe is correct http://msdn.microsoft.com/en-us/library/system.data.sqlserverce(v=vs.100).aspx

edited Jun 8, 2014 at 12:09

answered Jun 8, 2014 at 11:59

user2316116

6,8221 gold badge24 silver badges36 bronze badges

1 Comment

Sheep Over a year ago

Thank you for your reply, the problem was with the path of my .sdf file, I had .sdf in bin/debug and in files project second .sdf with the same name, in VS I set query DB in project folder, but String Connection was set to |DataDirectory| Thank you for Your help! Best Regards

Jon Hunter · Accepted Answer · 2014-06-08 10:30:51Z

-1

Try using double backslash when setting the path to your database file:

string dbPath + "Data Source=C:\\DataDirectory\\PacjenciDB.sdf";
SqlCeConnection conn = new SqlCeConnection(dbPath);
conn.Open();
SqlCeCommand cmd = conn.CreateCommand();

cmd.CommandText="INSERT INTO pacjenci (nazwiskoimie,adres,skierowany,opis) values (@nazwiskoimie,@adres,@skierowany,@opis)";
cmd.Parameters.AddWithValue("@nazwiskoimie", txtnazwiskoimie.Text);
cmd.Parameters.AddWithValue("@adres", txtadres.Text);
cmd.Parameters.AddWithValue("@skierowany", txtskierowany.Text);
cmd.Parameters.AddWithValue("@opis", txtopis.Text);

cmd.ExecuteNonQuery();

conn.Close();

edited Jun 8, 2014 at 10:30

answered Jun 8, 2014 at 10:21

Jon Hunter

8902 gold badges8 silver badges19 bronze badges

2 Comments

Sheep Over a year ago

The database file cannot be found. Check the path to the database. [ Data Source = \PacjenciDB.sdf ]

Ali Over a year ago

OP has a @ before Data source which is the same when you have double backslash

Collectives™ on Stack Overflow

Insert Into local database C#

4 Answers 4

1 Comment

1 Comment

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related