Is it possible that even thoush c# says that a record is updated in the database that it actually is not for a few milliseconds or something? System.Data.OleDb.
I have a C# winforms application that manages appointments.
The problem that I am experiencing is that the application appears to be updating appointments in the database and then retrieiving the data so fast that the old data is being returned instead of the new updated data (is that possible??)
I normally use sql server but this app is writing records to Access 2003 and I don't know if this is an Access issue or what.
Heres the quick overview:
When a user edits an existing appointment it is opened in a modal window and when the user presses save I update the record in the Database and then fire a Save() event so that the main appointment screen refreshes with the updated appointment information.
(very, very basic stuff...nothing fancy)
This is basically what the save button code looks like on the modal form.
//step 1 save updated appointment to database
//fire save event so event_handler will refresh main screen
//here is the saveAppointmentToDB method ..which is saving data without issue.
System.Data.OleDb.OleDbConnection cn =DataHelper.GetConnection();
System.Data.OleDb.OleDbCommand cmd = cn.CreateCommand();
int numberOfRowsAffected = 0;
cmd.CommandText = sql;
numberOfRowsAffected = cmd.ExecuteNonQuery();
numberOfRowsAffected always returns 1 indicating the record was updated successfully every time.
Now after the code above executes, my Save event is fired and in the eventhandler I am attempting to retrieve the updated data from the database so that I can display the updated appointment information on screen for the user.
private void appointment_Save(object sender, EventArgs e)
sql ="SELECT * from Appointment where starttime > #" + from + "# and startTime < #" + to + "#"; //create an open connection
System.Data.OleDb.OleDbConnection cn = DataHelper.GetConnection(); //create the command object
System.Data.OleDb.OleDbCommand cmd = DataHelper.GetCommand(cn,sql, CommandType.Text); //use the command to return a datatable DataTable dt = DataHelper.GetDataTable(cmd);
//close the open connection
cn.Close();//return the datatable return dt;
When the event handler fires it retrieves the old appointment data..not the new appointment data like it should. It is as if the data in the database is not yet updated?
Even though the ExecuteNonQuery() in the Update function says the record was updated it appears not to be.
The reason I know this is because If I put a breakpoint in code immediately after the update and immediately before I retrieve the data from the database, the code works fine because I am slowing the app down by a few seconds using the breakpoint.
Without the break point slowing the app down, the old appointment data is retrieved every time.
Does anyone have any idea why it would say my data is udpated yet it appears not to be, when it is retrieved and if I slow the
app down by a few milliseconds or seconds the code works fine?
My console.writeLine results are listed below for you showing that the data is being updated and then retrieved and the old data is being retrieved rather than the new data.
Both the update and read occur in the same millisecond.
Update Appoints new Subject= matt's teeth whitening
Update Appoints Time: 01:13:06:136
Number of rows affected =1
//here the old subject is being returned..not the updated subject
Load Appoints Subject: matt's teeth whitening 14
Load Appoints Time: 01:13:06:136
Any help is greatly appreciated.