Tech Off Thread

4 posts

Data being retrieved too fast after update?

Back to Forum: Tech Off
  • User profile image
    ferguslogic

    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?

    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.

    private button_save()
    {
       //step 1 save updated appointment to database
       SaveAppointmentToDatabase();

       //step 2
       //fire save event so event_handler will refresh main screen

       Save()
    }

    //here is the saveAppointmentToDB method ..which is saving data without issue.

    SaveAppointmentToDatabase()
    {

    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.

    //event handler
    private void appointment_Save(object sender, EventArgs e)
    {
    string sql;

    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.



  • User profile image
    nlondon

    I didn't try to repro and resolve this in a traditional sense, but it did stick out to me that you didn't close your connection in the SaveAppointmentToDatabase() method.  Try cn.Close() and see if that commits your changes.

  • User profile image
    ferguslogic

    Yes that was definitely it.

    thank you so much. I really appreciate it.

     

  • User profile image
    zorci

    Sorry to disturb you but I have a quistion.

    Last year you wrote a topic with the name:

    Transaction Management between layers

    Did you ever find a way to handle that?

    I'm building a webapplication and want to start a transaction when aq request first arrives OnBeginRequest and close it when OnRequestEnd occurs.

    My problem is that I recieve a lot of request (like 4-5) some of them hitting my application asking for an image. They sometimes executes faster than the real work and closes my transaction before all Request threads are done. You get it?

    Do you know about a best practise or can you help me in any way.

    Thanks in advance.


Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.