Click here to Skip to main content
15,908,841 members
Please Sign up or sign in to vote.
4.50/5 (2 votes)
See more:
Hello,

When I try to insert something into my Local Database (SQL Compact) it does add. But it doesn't save. I have tried serveral things. Here is my code:

Connection String
C#
public static SqlCeConnection Connect()
{
    SqlCeConnection connection = new SqlCeConnection();
    connection.ConnectionString = @"Data Source=|DataDirectory|\Database\Database.sdf";
    connection.Open();

    return connection;
}


The "Add" Class.
C#
public static void AddTodo(string title, string description, string content)
{
    SqlCeCommand command = new SqlCeCommand("insert into items (title, description, content) values (@Title, @Description, @Content)", Database.Connect());
    command.Parameters.Add(new SqlCeParameter("Title", title));
    command.Parameters.Add(new SqlCeParameter("Description", description));
    command.Parameters.Add(new SqlCeParameter("Content", content));
    command.ExecuteNonQuery();
    Database.Disconnect();
}


Last piece of code
C#
private void addButton_Click(object sender, RoutedEventArgs e)
{
    TodoAdder.AddTodo(titleBox.Text, descriptionBox.Text, contentBox.Text);
}


When I rebuild database it deletes it. Or it never is in there.

Thanks in advance.

Updated question:
When I execute the insert query it does show up in the ListBox. But when I check my Database it is empty. Also, when I restart the program. It is empty again.
Posted
Updated 8-Jun-18 2:19am
v3

Mark the sdf file as copy only if different or remove it from your solution and keep it only in 1 location e.g.: bin of your application
 
Share this answer
 
Comments
kZR 15-Jul-11 20:21pm    
It still doesn't add any data to the database. It just stays empty. But it does show in the ListBox. It just doesn't add to the database.
fcronin 15-Jul-11 20:35pm    
... did you check \bin\debug or \bin\release for another db file to view to see if your record(s) is in there...?
Philippe Mori 16-Jul-11 8:45am    
This is probably your problem. I think that by default a copy of the database is made when you build the application and the copy is used at run-time. This is usually what we want as the database used at design time should normally stay "empty".

See also DataDirectory search on Google
Philippe Mori 16-Jul-11 8:52am    
By the way copying only when modified might be a good alternative if you seldom change the table and it does not matters to restart from scratch in that case. Otherwise, you would typically have to make some code to update your existing database when its format changes.

If you don't like how |DataDirectory| works, you can always create your connection string with a filename that you hava manually build.
Try matching your parameter names when you add the values. So where it says this:
C#
command.Parameters.Add(new SqlCeParameter("Title", title));

should be:
C#
command.Parameters.Add(new SqlCeParameter("@Title", title));
 
Share this answer
 
Comments
kZR 15-Jul-11 20:35pm    
This doesn't work, thanks anyway.
I'm not positive, but I think I've seen before that some database configs have a flag for auto-commit or no... you may want to make sure auto-commit is turned on in your case... or send a commit after you add the record.
 
Share this answer
 
Hello,

Closing the connection should complete the insert.

connection.Dispose();
connection.Close();


good luck.
 
Share this answer
 
v2
Comments
kZR 15-Jul-11 20:35pm    
This doesn't work, thanks anyway.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900