Timing problem using Access database

[David Mac]

Hi All,

I think this is my first question here.

My web application uses an Access database and I'm getting some strange behaviour which might be related to that. Here's the issue:

At the end of processing a users request (during which a lot of calculations are done), a record is updated with a status value indicating the success of the calculations. At the same time an error message and date/time are updated.

The web page uses a FormView to display the results of the calculations but I sometimes don't see the correct value for status and date.
So for example, the calculations could fail but the web page shows the values in the database before they were just updated.
This doesn't always happen, it seems to be an intermitent problem.

I have tried putting a delay in after the record is updated and that seemed to help but it didn't eliminate it.

sql = "UPDATE QuoteDetail SET"
                sql &= " Status = " & status & ", ErrMsg='" & Utilities.fixSingleQuotes(errMsg) & "', CalcDate = NOW()"
                sql &= " WHERE RevisionID=" & revisionID
                cmd.CommandText = sql
                cmd.ExecuteNonQuery()

                System.Threading.Thread.Sleep(0)

Any help/ideas greatly accepted as this is causing my customer a major headache!
Oh, yes we will move to SQL Server one day but not right yet

David

[David Mac]

I'm trying it with ...Sleep(1000) (1 sec) now and it seems to be working... still testing...
I think the (0) argument causes the current thread to wait til outstanding threads are complete but presumably the cmd.execute doesn't work like that. It will execute in it's own time? If that's the case then there are bound to be timimg issues!
That can't be right... does anyone know?
Does the .Net code wait til the database command has been executed?

[kvprajapati]

Invoke FormView1.DataBind() or rebind FormView after updating/deleting/inserting.

[David Mac]

Yes I'm doing that already.

update db
databind all controls
wrong values shown!

the delay of 1 second seems to have solved it but i'm still testing...

[kvprajapati]

Please show us your code. Please don't forget to wrap source code with code-tags.

[David Mac]

I think the real issue here is the relationship between the database update and the rebind. I have modified my approach from the code above, I now do the delay just before the rebind as this can occur under different circumstances (different actions that cause the db to be updated)

Here's my code:

In the aspx page when a button is clicked the following procedure is called:

Private Function calcQuote() As Boolean
    
        clearMessages()
        
        ' Get the quote to calc
        Dim q As New XMS.QuoteDetail(Session("revisionID"))
        With q
            If .revisionID = 0 Then
                Master.displayMsg("failed to create an instance of QuoteDetail?!")
                Return False
            End If
            If Not .validToCalc Then
                Master.displayMsg(.errMsg)
                Return False
            End If

            Dim calcsOk As Boolean = .doCalcs
            rebind()
                
            ' Initialise footer totals (otherwise they are doubled!)
            totalCostColumns = 0
            totalLengthColumns = 0
            totalWeightColumns = 0
            totalQtyColumns = 0
            
            If Not calcsOk Then
                ' Display error msg
                Master.displayMsg("an error occured during calcs: " & .errMsg)
            End If

            ' Display any warnings (may or may not have failed)
            If .warningMsg = String.Empty Then
                ' No warnings
            Else
                lblWarningMsg.Text = .warningMsg
            End If
        End With   

        Return True
    End Function

    Private Sub rebind()
        ' Rebind all the databound controls
        ' This is called after calculating or sending a quote

        System.Threading.Thread.Sleep(1000) '## Force a delay to ensure the correct values are picked up from the db

        gvQuoteRevisions.DataBind()
        fvQuote.DataBind()
        fvExtras.DataBind()
        For Each control As Control In fvExtras.Controls
            If TypeOf control Is GridView Then
                control.DataBind()
            End If
        Next
    End Sub

    Private Sub clearMessages()
        Master.displayMsg("")
        lblWarningMsg.Text = String.Empty
    End Sub

Within the calculations ( q.doCalcs() ), the following procedure is called to update the status

Private Function saveStatus() As Boolean
            Dim sql As String
            Dim cmd As New OleDbCommand '##
            Try
                cmd = cnn.CreateCommand
                sql = "UPDATE QuoteDetail SET"
                sql &= " Status = " & status & ", ErrMsg='" & Utilities.fixSingleQuotes(errMsg) & "', CalcDate = NOW()"
                sql &= " WHERE RevisionID=" & revisionID
                cmd.CommandText = sql
                cmd.ExecuteNonQuery()
                Return True

            Catch ex As Exception
                ' Error
                errMsg = "error saving the quote's status: " & ex.Message
                Return False
            End Try
        End Function

Please note that in this last procedure I am using an existing Connection that was opened at the start of processing doCalcs
( cmd = cnn.CreateCommand )
I'm not sure if this is a good or bad idea but it saves opening and closing connections all the way through the code

[kvprajapati]

You must have to close db connection immediately after the successfully execution of insert/update/delete actions.

[David Mac]

Is that a guess or do you say that from experience?
The code above works successfully but without the delay just before rebinding I get occassional errors.
I will try opening and closing the connection in the saveStatus procedure

[David Mac]

your right, I should be closing the connection!
this explains a number of issues I've been experiencing with the system!
many thanks for your help