Search code examples
vb.netwinformsvisual-studio-2010ado.netsql-server-ce

Having trouble with Sqlcompact Database in my Windows Form Application

I have searched a lot regarding my problem but no solution so i am posting here to get advise from the experts.

I am making a windows form application , and using Sql compact database.

My Problem :

When i am inserting records in the database using code (run time) they are inserted deleted , update and i can see them updated,inserted,deleted in my application , but when i close the application and start again only a few times they are changed, once a million :). Else i again see blank database ,or if i directly put values in the database from the solution explorer Visual studio 2010 , they stay there forever. I am using simple update,insert ,delete statement but not sure why the changes made to database disappear when i close the application.

First this For all :

   Dim con As New SqlCeConnection
   Dim command As New SqlCeCommand

   connectionString = "Data Source=SqlCompactDatabase/RestaurantDatabase.sdf"
    con.ConnectionString = connectionString

    command.Connection = con
    con.Open()

My Insert Code :

            command.CommandText = "Insert into Inventory(itemcode,itemmin,itemmax,itemdesc) Values('" + itmcod + "','" + min + "','" + max + "','" + desc + "')"
            command.ExecuteScalar()

My Update Code:

    command.CommandText = "update Inventory set itemMin='" + min + "',itemMax='" + max + "',instock='" + stock + "',itemDesc='" + desc + "' where itemcode='" + TextBox1.Text + "'"
    command.ExecuteScalar()

My Delete Code:

      command.CommandText = "delete from inventory where itemcode='" + TextBox1.Text + "'"
    command.ExecuteScalar()

I open close connection correctly there is no error, there is something i can't figure out :)

Solution

  • If you added you SQLCE database to your project in Visual Studio, then look at its properties (select the file in the Solution Explorer and look to the Properties panel). Normally a database file has Build Action set to "Content" and Copy To Output option set to "Copy Always". This means, that every time you build your solution your database from Visual Studio project (most likely the empty one) is copied anew to your build folder ($projectname$\bin\Debug or \Release) and overwrites your existing database with the data added at runtime.

    To solve this problem just set Copy To Output to "If newer" and it will overwrite your data as long as your design-database file changes.