0

I'm writing a small application for college, a video rental application. I have no problems reading from the database, but I cannot update the data in the tables.

In particular I'm looking to update the cell showing the quantity of a film in stock after someone rents a movie.

So far I've been trying this:

string updateDVDs = "UPDATE Products SET dvd_quantity = " + product.Quantity + " WHERE title = '"+ product.Name +"';";
cmdUpdateDVDs = new SqlCeCommand(updateDVDs, dBConnection);
dBConnection.Open();
cmdUpdateDVDs.ExecuteNonQuery();
dBConnection.Close();

I don't get any errors, but the cell doesn't update in the table. Any help would be greatly appreciated, please let me know if you require further information.

UPDATE: OK, I have some interesting developments, I have updated the code to this:

string updateDVDs = "UPDATE Products SET dvd_quantity = " + product.Quantity + " WHERE title = '" + product.Name + "'";
dBConnection = new SqlCeConnection(connectionString);
cmdUpdateDVDs = new SqlCeCommand(updateDVDs, dBConnection);
cmdUpdateDVDs.Connection.Open();
int rows = cmdUpdateDVDs.ExecuteNonQuery();
dBConnection.Close();

From what I can tell looking on MSDN both methods are valid, but this second one seems to be working. Now the weird bit, I rent the movie 'Die Hard', it all goes swimmingly, I get these results:

Database updated

All good, There is no way back from this point, so I quit the application and start again. I go to rent 'Die Hard' a second time to confirm, and, success!! the new reading on the DVD quantity is 0 as expected: DVD quantity successfully updated!

But, when I open the Product table in Visual Studio the original values are still there: In Visual Studio the table hasn't updated

Not only that, but when I run the application again after opening the table in Visual Studio the DVD quantities are reset to the original values and the updated values are gone.

Am I missing something simple here? I've tried refreshing the table, it doesn't make any difference. As long as I don't open the table in Visual Studio the application behaves as expected, no matter how many times I run it, the values update as expected, until I open the table itself.

mal
  • 3,022
  • 5
  • 32
  • 62
  • None of my string queries have the ; at the end. For instance: `const string query = "SELECT * FROM dailyreport";` is my query. – PiousVenom Nov 05 '12 at 15:49
  • 1
    I should suggest using a ORM like EF or Linq2SQL to talk against a database instead of using queries in your code. check out this [link](http://researchaholic.com/2012/02/10/how-to-get-entity-framework-to-talk-to-sql-ce-4/) – Jordy van Eijk Nov 05 '12 at 15:53
  • Thanks Prayos, I changed my statement, it didn't make a difference. – mal Nov 05 '12 at 16:35
  • @JordyvanEijk will this cause problems for my lecturer who might not have that SQL compact toolbox? when he's looking at my work? – mal Nov 05 '12 at 16:36
  • What is your connection string? By default VS sets it to a temporary file, not the original source file. Read http://stackoverflow.com/questions/12093962/ and http://stackoverflow.com/questions/11801352/. – Dour High Arch Nov 05 '12 at 17:07
  • @mal If you make sure that you add the references from a specific place and not from the GAC (Global Assembly Cache) and give your lecturer the complete solutionfolder including the references, it would not be a problem. I always use this format: ProjectDir --> References, SolutionDir --> .... Or add an NuGet package and take a look how they do it. That is also a nice way. – Jordy van Eijk Nov 06 '12 at 08:02
  • @DourHighArch my connection string is string connectionString = @"Data Source=|DataDirectory|\HKRMoviesDB.sdf"; I've no problems reading from any part of the database. – mal Nov 06 '12 at 08:32
  • What is the value of the "Copy to Output Directory" property of your .sdf file? – Dour High Arch Nov 06 '12 at 17:08
  • So... is the .sdf file in your project newer than the one in |DataDirectory|\HKRMoviesDB.sdf? If it is, your HKRMoviesDB.sdf will be overwritten. Also, you are using different connection strings in your original question and in your update, so it is not surprising they have different behaviors. – Dour High Arch Nov 07 '12 at 18:47
  • Hi all, thanks for your help, As Dour High Arch it was the connection strings that where the problem. I changed the connection string in the server explorer from the original one to the one stored in the bin folder. – mal Nov 09 '12 at 09:36
  • @Dour High Arch do you want to write an answer I can accept?? – mal Nov 09 '12 at 09:44

2 Answers2

2

You have to tell us exactly what connection string you are using, saying things like "I updated the connection to point to the database in the bin folder" is not helpful. Are you using a connection string like c:\Users\Me\Visual Studio 2010\Projects\MyProject\bin\debug\HKRMoviesDB.sdf? That is not right, that is hard-coded to your VS2010 debug test folder on your machine, and will not work for anyone else, will only work when you run from the debugger, and will not work when you deploy your app.

ADO.NET looks for vertical bars in connection strings and expands the enclosed string using the AppDomain property. For example, if you use Data Source=|DataDirectory|\HKRMoviesDB.sdf from the VS debugger, DataDirectory expands to your project's bin folder, where VS copies the debug DLLs. If you deploy your app using an MSI installer, DataDirectory expands to your app's install folder chosen by the user. Further explanation is at this answer.

Be aware that DataDirectory may not be a directory writable by users; it is a good directory for read-only data, but if you want users to write to the file you should copy it to Environment.SpecialFolder.ApplicationData or somewhere in your app, as explained in this answer.

There is one more thing to keep in mind. When you are debugging you typically want to copy your original source data to the debug folder, test it, then discard your test data because you typically do not want your test data included in the final deployment. This is what the "Copy to Output Directory" property in your project does. If it is set to "copy if newer", which it probably should be, then any changes you make to your test data is temporary; the next time you update your original source data in your project, your test data will be discarded. Remember, your application's deployment directory is typically not writable by users so you should not be writing user data in DataDirectory anyway.

Community
  • 1
  • 1
Dour High Arch
  • 21,513
  • 29
  • 75
  • 90
0

A fellow student pointed this out to me:

"From what I understand, the database that your code updates, unless you hard coded the path to the database is the file in your /bin/debug/ folder. The file you view in Visual studio is the one in your project root."

I updated the connection to point to the database in the bin folder.

mal
  • 3,022
  • 5
  • 32
  • 62