Update/Refresh Form Status View

Hi all

I have been working on an application to automate excel charts. The automation and data retrieval work fine.

One problem is that the data retrieval can take upto one minute. This causes a problem as the user does not know if the app is active or dead.

So I have created a pogress form which quite simply refreshes a label on the form as  below.
Calculating
Calculating .
Calculating . .
Calculating . . . etc

I have used the progress bar which is not suitable as i do not know how long the query will take.
I have used the form timer which just doesn't raise any events.
I have used the system timer which is the same as the form timer.
I am at present using thread timer which does really well for 3 seconds and then freezes as the others.

The problem I am having is that once the program reaches the call to access the database the processe freezes and the progress form is no longer updated until the datareader has finished its query.

The code is below, the first class calls the progress form, then gets Excel ready, then querys the database and finally outputs what it has retrieved finally closing the progress form.

Excel class call
***************************************************************
Imports System
Imports System.IO
Imports System.Collections
Public Class CToExcel

    Public Function ToExcel(ByVal sql As String, ByRef cn As System.Data.OleDb.OleDbConnection, ByRef oRetObjectApp As Object, ByRef oRetObjectWSht As Object, ByRef strLog As String) As Boolean
        Dim oProgress As Object = Nothing
        Try
            ToExcel = False

            oProgress = CreateObject("CSLJo_XL.frmProgress")
            oProgress.Show()
            System.Windows.Forms.Application.DoEvents()

            ''' This class is responsible for starting Excel up,
            ''' creating a new sheet, then running the stored proc
            ''' whose name was passed in, piping the results into Excel.

            Dim excelApp As Object
            Dim workBook As Object
            Dim workSheet As Object

            '''assigning excel application to object and opening application with a new worksheet to work on
            excelApp = CreateObject("Excel.Application")
            excelApp.Visible = True
            workSheet = excelApp.workBooks.Add.ActiveSheet
            excelApp.Sheets("Sheet3").Select()
            excelApp.ActiveWindow.SelectedSheets.Delete()
            excelApp.Sheets("Sheet2").Select()
            excelApp.ActiveWindow.SelectedSheets.Delete()
            'workBook = excelApp.workbooks.Add

            Dim dr As System.Data.OleDb.OleDbDataReader
            Dim diExcel As DirectoryInfo
            Dim iRow As Integer
            Dim iCol As Integer

            Dim fldCount As Integer
            Dim strField As String

            '''making connection to database
            Dim cmd As New System.Data.OleDb.OleDbCommand
            '''initialize values

            fldCount = 0
            iRow = 1
            iCol = 1

            cmd.CommandTimeout = 200
            cmd.Connection = cn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = sql
            dr = cmd.ExecuteReader()

            excelApp.Range("A1").Select()
            While dr.Read() = True
                While fldCount < dr.FieldCount
                    If Not IsDBNull(dr.Item(fldCount)) Then
                        strField = (dr.Item(fldCount))
                    Else
                        strField = ""
                    End If
                    workSheet.Cells(iRow, iCol).Value = strField
                    fldCount = fldCount + 1
                    iCol = iCol + 1
                End While
                fldCount = 0
                iCol = 1
                iRow = iRow + 1
            End While
            dr.Close()

            '''the chart will be modified back in the instantiating class
            '''so as to allow unique graph/chart automation
            oRetObjectApp = excelApp
            oRetObjectWSht = workSheet

            If Not oProgress Is Nothing Then oProgress.Close()
            Return True
        Catch ex As Exception
            strLog = strLog & "  failed...  " & ex.Message
            If Not oProgress Is Nothing Then oProgress.Close()
            Exit Function
        End Try

    End Function

End Class
****************************************************************

Progress form call
****************************************************************
Imports System.IO
Public Class frmProgress
    Inherits System.Windows.Forms.Form
    Dim intCnt As Integer = 1
    Dim tmrProgress As System.Threading.Timer

****WINDOWS DESIGNER GENERATED CODE*****

Private Sub frmProgress_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        tmrProgress = New System.Threading.Timer(New System.Threading.TimerCallback(AddressOf tmrProgress_Status), Nothing, 0, 1000)
    End Sub

    Private Sub tmrProgress_Status(ByVal state As Object)
        If intCnt < 24 Then
            lblCalculating.Text = lblCalculating.Text & " ."
            intCnt = +1
        Else
            lblCalculating.Text = "Calculating"
            intCnt = 1
        End If

        lblCalculating.Refresh()
        lblCalculating.Update()
        Me.Refresh()
        Me.Update()
        System.Windows.Forms.Application.DoEvents()
    End Sub

End Class

I appreciate any hints or help

Thanks

name43Start Free Trial