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:
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:
But, when I open the Product table in Visual Studio the original values are still there:
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.
See Question&Answers more detail:
os