CODEWITHSUNDEEP
c#visual-studio-2010sql-server-ce
Paradox
Paradox

Reputation: 91

C# SQL Server CE not inserting

SqlCeConnection sqlCnn = 
    new SqlCeConnection(Properties.Settings.Default.mainDBConnectionString);

SqlCeCommand sqlCmd = new SqlCeCommand(
    "INSERT INTO desktopItems (Location,Label) VALUES (@Location, @Label)", 
    sqlCnn);

sqlCnn.Open();
sqlCmd.Parameters.Add("@Location", openExe.FileName.ToString());
sqlCmd.Parameters.Add("@Label", openExe.SafeFileName.ToString());
sqlCmd.ExecuteNonQuery();

sqlCnn.Close();

I have this code but when I run the program, the database is not updating ...

Upvotes: 0

Views: 252

Answers (1)

Steve
Steve

Reputation: 216343

Usually this scenario is caused by a simple error in visualizing the database.
Your INSERT works as expected, but you check if the insert succeded looking at a database in the wrong directory.

Using the DATADIRECTORY substitution string with a WinForms application means that, at debug time, your database is expected to be located in the directory BIN\DEBUG from your base project folder.
Visual Studio make sure that this is the case because in your project, the database file, is marked with the property Copy To The Output Directory set to Copy Always or Copy If Newer.

And it is here that the insert happens when you run your code inside a debug session of Visual Studio.

Then you check the result of the execution using the SERVER EXPLORER connection. But this connection points to the original database in the Project Folder and, of course, the new record is not present.

Usually the database in the project folder is kept up to date for the deployement, with the correct schema and initial data, but without any records that are inserted just for debug purpose.

So you could simply add a new connection to the SERVER EXPLORER pointing to the database in the BIN\DEBUG, rename it (like 'DEBUG-DB') and keep your original connection in case you need to change something in the schema of the database before releasing your application.

Upvotes: 2

Related Questions