Progress bar - filling dataset

Hi,

Is there a way to use a progressbar while filling a dataset with a dataadapter?

In the application there is a dataadapter which fills a dataset. The problem is that there are sometimes >10000 records to be filled in the dataset. this takes some time. After the fillup of the dataset then a datagrid has to be filled with the dataset

Therefore I would like to use a progressbar to show the user that the application is processing the request.

The problem is that I don't know how to set the progressvalue of the progressbar, because the min and max values depend on the number of rows in the dataset and you can't know this before it is filled!!!!!!

Any ideas?

jinte

what i have done in situations such as this is to let the progress continually update and by that i mean:

Code:

---

progressbar.Min = 0
progressbar.Max = 500

if iX = 450 then iX = 0


progressbar.value = iX

---

in this manner the progress will begin again - at least this shows that something is going on to the user.

I've done that also when you either can't or don't want to figure out the actual percentage.

In your case, though, can't you just get the Count of the data rows in your data table?

I don't believe that the dataadapter provides any method of callback to show progress. You could fill it one row at a time but then you'd slow the whol process down. It may be easier to just show some form of animation like the copy dialog that runs while it is working but doesn't actually show the progress.

thanks,


@zebula8 : good idea to show something.

@Mike Hildner
I get the values of an excelsheet. The dataadapter selectcommand is based on the values the user inputs (eg hitword,...). So this isn't a case where I can count the rows in the datatable because it doesn't exist untill the user creates the select command. The number of rows depends on the selectcommand.

@edneeis : same idea as zebula8, and I agree that slowing down the application can't be a good choice.

Any other ideas?

Thanx

Haven't used Excel as a data source, but would it be possible to do something like a SELECT COUNT (*) FROM MYTABLE...?

Edneeis is right about slowing the app down, but depending on what you're doing, this can be OK sometimes - albeit maybe not in this case.

Perceived speed versus actual speed is something to consider. A progress bar of any sort will be slower than no progress bar, but to the user, the application will appear fast.

As suggested, just showing that something is going on can be good enough.

Yes mine and ZeBula8 are similar except I meant something more of an animation sytle then an actual progressbar. Here is an example.

Thanks Edneeis,

I appreciate your posting, but I think it's a pretty complicated for me because I consider myself still as a newbie. Delegates and asynchronous programming isn't my best. But I gave it a try and adopted your code to my application:

VB Code:

Public Class FrmSearchCorresp2
    Public Delegate Sub FillHandler(ByVal DSExcel As DataSet)
    Private distraction As FrmDistraction
    Private filled As Boolean
 
   
    Public Function GetSourcePath(ByVal number As String) As String
        Dim FileName As String
        'alle info bevindt zich in nummer.xls
        FileName = "\\Epsilon2\vol3\dok\" & number & "\corresp\" & number & "Nummer.xls"
        Return FileName
    End Function
 
'GetDataFromExcel creates a dataset from an excel file on the server in my company
    Public Function GetDataFromExcel(ByVal FileName As String, ByVal RangeName As String) As System.Data.DataSet
       
        Dim SQLString As String
        Dim hitword, firma, afzender, bestemmeling As Boolean
 
        If Me.TextBoxHitword.Text <> "" Then hitword = True Else hitword = False
        If Me.TextBoxFirma.Text <> "" Then firma = True Else firma = False
        If Me.TextBoxAfzender.Text <> "" Then afzender = True Else afzender = False
        If Me.TextBoxBestemmeling.Text <> "" Then bestemmeling = True Else bestemmeling = False
 
        Try
            Dim strConn As String = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & FileName & "; Extended Properties=Excel 8.0;"
 
            DataConnectionCorresp = New OleDbConnection(strConn)
 
            DataConnectionCorresp.Open()
            'maakt een selectcommand aan
            'alle combinaties dienen bekeken te worden : 4*4 combinaties
            SQLString = "SELECT * FROM " & RangeName
            'controle alle mogelijke variaties met hitword = true
            If (hitword = True And firma = False And afzender = False And bestemmeling = False) Then
                SQLString = SQLString & " WHERE [korte inhoud] like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " OR Firma like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " OR Afzender like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " OR Bestemmeling like '%" & Me.TextBoxHitword.Text & "%'"
                'SQLString = SQLString & " OR Doc#nr# like '%" & Me.TextBoxHitword.Text & "%'"
            ElseIf (hitword = True And firma = True And afzender = False And bestemmeling = False) Then
                SQLString = SQLString & " WHERE [korte inhoud] like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " AND (Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " OR Afzender like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " OR Bestemmeling like '%" & Me.TextBoxHitword.Text & "%')"
            ElseIf (hitword = True And firma = True And afzender = True And bestemmeling = False) Then
                SQLString = SQLString & " WHERE [korte inhoud] like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " AND Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " AND (Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
                SQLString = SQLString & " OR Bestemmeling like '%" & Me.TextBoxHitword.Text & "%')"
            ElseIf (hitword = True And firma = True And afzender = False And bestemmeling = True) Then
                SQLString = SQLString & " WHERE [korte inhoud] like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " AND Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " AND (Bestemmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
                SQLString = SQLString & " OR Afzender like '%" & Me.TextBoxHitword.Text & "%')"
            ElseIf (hitword = True And firma = True And afzender = True And bestemmeling = True) Then
                SQLString = SQLString & " WHERE [korte inhoud] like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " AND Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " AND Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
                SQLString = SQLString & " AND Bestemmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
                'controle van alle mogelijke variaties met hitword = false
            ElseIf (hitword = False And firma = True And afzender = False And bestemmeling = False) Then
                SQLString = SQLString & " WHERE Firma like '%" & Me.TextBoxFirma.Text & "%'"
            ElseIf (hitword = False And firma = False And afzender = True And bestemmeling = False) Then
                SQLString = SQLString & " WHERE Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
            ElseIf (hitword = False And firma = False And afzender = False And bestemmeling = True) Then
                SQLString = SQLString & " WHERE Bestemmmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
            ElseIf (hitword = False And firma = True And afzender = True And bestemmeling = False) Then
                SQLString = SQLString & " WHERE Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " AND Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
            ElseIf (hitword = False And firma = True And afzender = False And bestemmeling = True) Then
                SQLString = SQLString & " WHERE Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " AND Bestemmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
            ElseIf (hitword = False And firma = False And afzender = True And bestemmeling = False) Then
                SQLString = SQLString & " WHERE Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
            ElseIf (hitword = False And firma = False And afzender = False And bestemmeling = True) Then
                SQLString = SQLString & " WHERE Bestemmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
            ElseIf (hitword = False And firma = True And afzender = True And bestemmeling = True) Then
                SQLString = SQLString & " WHERE Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " AND Bestemmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
                SQLString = SQLString & " AND Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
            End If
 
            'MsgBox("bestemmeling=" & bestemmeling & "firma=" & firma)
            'MsgBox("afzender=" & afzender & "trefwoord=" & hitword)
 
            Dim SelectCommand As New OleDbCommand(SQLString, DataConnectionCorresp)
            Dim DAExcel As New OleDbDataAdapter
            DAExcel.SelectCommand = SelectCommand
            ' vul dataset
            DAExcel.Fill(DSExcel, "Excel")
            DataConnectionCorresp.Close()
 
            filled = True
 
             Return DSExcel
        Catch
            'eventuele error handling
            Return Nothing
        End Try
    End Function
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        DSExcel.Clear()
        'Here I create the delegate
 
        Dim del As New FillHandler(AddressOf FillDatasetThread)
        del.BeginInvoke(DSExcel, AddressOf CallBack, DSExcel)
        distraction = New FrmDistraction
        distraction.ShowDialog()
 
    End Sub
 
    Public Sub FillDatasetThread(ByVal DSExcel As DataSet)
        Dim filenameNummer As String = GetSourcePath(Me.TextBoxNummer.Text)
 
        If File.Exists(filenameNummer) = True Then
            GetDataFromExcel(GetSourcePath(Me.TextBoxNummer.Text), "[blad1$A3:F3000]") 
        Else
            MsgBox("Dossier " & Me.TextBoxNummer.Text & " bestaat niet.  Gelieve een ander dossiernr. in te geven", MsgBoxStyle.Critical, "")
        End If
    End Sub
 
    Public Sub CallBack(ByVal ar As IAsyncResult)
        Dim DS As DataSet = CType(ar.AsyncState, DataSet)
        If filled = True Then
            distraction.DialogResult = DialogResult.OK
            If DS.Tables(0).Rows.Count > 0 Then
                Me.createstyles()
                Me.dataGrid1.DataSource = DS
                Me.dataGrid1.DataMember = "Excel"
            Else
                MsgBox("No results", MsgBoxStyle.Critical, "")
            End If
        End If
    End Sub

The actual distraction animation is in the FrmDistraction and the code is the same as in your sample.

The problem is the following: when the user hits the button1, then the distractionanimation appears and starts working. After a few seconds (I suppose after the dataset DSExcel is created) the animation stops working and disappears but my datagrid1 isn't filled up with the results however it should because it's in my CallBack Sub.

I think there is a problem with the different threads (one for the distraction animation and one for filling up the dataset) and the fact that the dataset necessary for filling up the datagrid is created or passed to the wrong thread.

Am I correct? Or am I doing something wrong?

Any ideas?

Thanx in advance...

Jinte

You are right it is because of the way you are handling the dataset. Instead of passing one into the method on the other thread you are letting it grab one from the other thread and then trying to return it as a function result but not grabbing the result. You almost have it. I made a few slight changes give this a try:

VB Code:

Public Class FrmSearchCorresp2
    Public Delegate Sub FillHandler(ByVal ds As DataSet)
    Private distraction As FrmDistraction
    Private filled As Boolean
 
 
    Public Function GetSourcePath(ByVal number As String) As String
        Dim FileName As String
        'alle info bevindt zich in nummer.xls
        FileName = "\\Epsilon2\vol3\dok\" & number & "\corresp\" & number & "Nummer.xls"
        Return FileName
    End Function
 
    'GetDataFromExcel creates a dataset from an excel file on the server in my company
    Public Sub GetDataFromExcel(ByVal FileName As String, ByVal RangeName As String, ByVal ds As System.Data.DataSet)
 
        Dim SQLString As String
        Dim hitword, firma, afzender, bestemmeling As Boolean
 
        If Me.TextBoxHitword.Text <> "" Then hitword = True Else hitword = False
        If Me.TextBoxFirma.Text <> "" Then firma = True Else firma = False
        If Me.TextBoxAfzender.Text <> "" Then afzender = True Else afzender = False
        If Me.TextBoxBestemmeling.Text <> "" Then bestemmeling = True Else bestemmeling = False
 
        Try
            Dim strConn As String = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & FileName & "; Extended Properties=Excel 8.0;"
 
            DataConnectionCorresp = New OleDbConnection(strConn)
 
            DataConnectionCorresp.Open()
            'maakt een selectcommand aan
            'alle combinaties dienen bekeken te worden : 4*4 combinaties
            SQLString = "SELECT * FROM " & RangeName
            'controle alle mogelijke variaties met hitword = true
            If (hitword = True And firma = False And afzender = False And bestemmeling = False) Then
                SQLString = SQLString & " WHERE [korte inhoud] like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " OR Firma like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " OR Afzender like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " OR Bestemmeling like '%" & Me.TextBoxHitword.Text & "%'"
                'SQLString = SQLString & " OR Doc#nr# like '%" & Me.TextBoxHitword.Text & "%'"
            ElseIf (hitword = True And firma = True And afzender = False And bestemmeling = False) Then
                SQLString = SQLString & " WHERE [korte inhoud] like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " AND (Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " OR Afzender like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " OR Bestemmeling like '%" & Me.TextBoxHitword.Text & "%')"
            ElseIf (hitword = True And firma = True And afzender = True And bestemmeling = False) Then
                SQLString = SQLString & " WHERE [korte inhoud] like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " AND Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " AND (Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
                SQLString = SQLString & " OR Bestemmeling like '%" & Me.TextBoxHitword.Text & "%')"
            ElseIf (hitword = True And firma = True And afzender = False And bestemmeling = True) Then
                SQLString = SQLString & " WHERE [korte inhoud] like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " AND Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " AND (Bestemmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
                SQLString = SQLString & " OR Afzender like '%" & Me.TextBoxHitword.Text & "%')"
            ElseIf (hitword = True And firma = True And afzender = True And bestemmeling = True) Then
                SQLString = SQLString & " WHERE [korte inhoud] like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " AND Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " AND Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
                SQLString = SQLString & " AND Bestemmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
                'controle van alle mogelijke variaties met hitword = false
            ElseIf (hitword = False And firma = True And afzender = False And bestemmeling = False) Then
                SQLString = SQLString & " WHERE Firma like '%" & Me.TextBoxFirma.Text & "%'"
            ElseIf (hitword = False And firma = False And afzender = True And bestemmeling = False) Then
                SQLString = SQLString & " WHERE Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
            ElseIf (hitword = False And firma = False And afzender = False And bestemmeling = True) Then
                SQLString = SQLString & " WHERE Bestemmmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
            ElseIf (hitword = False And firma = True And afzender = True And bestemmeling = False) Then
                SQLString = SQLString & " WHERE Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " AND Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
            ElseIf (hitword = False And firma = True And afzender = False And bestemmeling = True) Then
                SQLString = SQLString & " WHERE Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " AND Bestemmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
            ElseIf (hitword = False And firma = False And afzender = True And bestemmeling = False) Then
                SQLString = SQLString & " WHERE Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
            ElseIf (hitword = False And firma = False And afzender = False And bestemmeling = True) Then
                SQLString = SQLString & " WHERE Bestemmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
            ElseIf (hitword = False And firma = True And afzender = True And bestemmeling = True) Then
                SQLString = SQLString & " WHERE Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " AND Bestemmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
                SQLString = SQLString & " AND Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
            End If
 
            'MsgBox("bestemmeling=" & bestemmeling & "firma=" & firma)
            'MsgBox("afzender=" & afzender & "trefwoord=" & hitword)
 
            Dim SelectCommand As New OleDbCommand(SQLString, DataConnectionCorresp)
            Dim DAExcel As New OleDbDataAdapter
            DAExcel.SelectCommand = SelectCommand
            ' vul dataset
            DAExcel.Fill(ds, "Excel")
            DataConnectionCorresp.Close()
 
            filled = True
 
            'Return DSExcel
        Catch
            'eventuele error handling
            'Return Nothing
        End Try
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'I assume dsexcel is already created somewhere
        'DSExcel.Clear()
        'for testing I just created a new dataset which shouldn't matter since it isn't a typed dataset
        Dim ds As New DataSet
        'Here I create the delegate
 
        Dim del As New FillHandler(AddressOf FillDatasetThread)
        'del.BeginInvoke(DSExcel, AddressOf CallBack, DSExcel)
        del.BeginInvoke(ds, AddressOf CallBack, ds)
        distraction = New FrmDistraction
        distraction.ShowDialog()
 
    End Sub
 
    Public Sub FillDatasetThread(ByVal ds As DataSet)
        Dim filenameNummer As String = GetSourcePath(Me.TextBoxNummer.Text)
 
        If File.Exists(filenameNummer) = True Then
            GetDataFromExcel(GetSourcePath(Me.TextBoxNummer.Text), "[blad1$A3:F3000]", ds)
        Else
            MsgBox("Dossier " & Me.TextBoxNummer.Text & " bestaat niet.  Gelieve een ander dossiernr. in te geven", MsgBoxStyle.Critical, "")
        End If
    End Sub
 
    Public Sub CallBack(ByVal ar As IAsyncResult)
        Dim DS As DataSet = CType(ar.AsyncState, DataSet)
        If filled = True Then
            distraction.DialogResult = DialogResult.OK
            If DS.Tables(0).Rows.Count > 0 Then
                Me.createstyles()
                Me.dataGrid1.DataSource = DS
                Me.dataGrid1.DataMember = "Excel"
            Else
                MsgBox("No results", MsgBoxStyle.Critical, "")
            End If
        End If
    End Sub
 
End Class

Thanks Edneeis,

I'll try your suggestion tomorrow on my work, I don't have the application on my laptop at home (I live in Europe=> time difference).

I'll keep you informed.

Jinte

@Edneeis,

Well I said I would keep you informed. Here I am.

The application still isn't working correct. The distraction is showing up and disappearing (after the filling up has completed I suppose) but the datagrid isn't filled up. So there is a problem with the passing of the dataset I think.

Here you can find the code I used, with some comments in it and some questions.

VB Code:

Public Class FrmSearchCorresp2
    Inherits BeheerCDs.Form1
    Private WithEvents dataGrid1 As DataGrid
    Public DSExcel As New System.Data.DataSet
    Public DataConnectionCorresp As OleDbConnection
    Public Delegate Sub FillHandler(ByVal ds As DataSet)
    Private distraction As FrmDistraction
    Private filled As Boolean = False
 
  
    Public Function GetSourcePath(ByVal number As String) As String
        Dim FileName As String
        'alle info bevindt zich in nummer.xls
        FileName = "\\Epsilon2\vol3\dok\" & number & "\corresp\" & number & "Nummer.xls"
        Return FileName
    End Function
 
    'GetDataFromExcel creates a dataset from an excel file on the server in my company
    Public Sub GetDataFromExcel(ByVal FileName As String, ByVal RangeName As String, ByVal ds As System.Data.DataSet)
 
        Dim SQLString As String
        Dim hitword, firma, afzender, bestemmeling As Boolean
 
        If Me.TextBoxHitword.Text <> "" Then hitword = True Else hitword = False
        If Me.TextBoxFirma.Text <> "" Then firma = True Else firma = False
        If Me.TextBoxAfzender.Text <> "" Then afzender = True Else afzender = False
        If Me.TextBoxBestemmeling.Text <> "" Then bestemmeling = True Else bestemmeling = False
 
        Try
            Dim strConn As String = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & FileName & "; Extended Properties=Excel 8.0;"
 
            DataConnectionCorresp = New OleDbConnection(strConn)
 
            DataConnectionCorresp.Open()
            
            SQLString = "SELECT * FROM " & RangeName
 
            If (hitword = True And firma = False And afzender = False And bestemmeling = False) Then
                SQLString = SQLString & " WHERE [korte inhoud] like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " OR Firma like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " OR Afzender like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " OR Bestemmeling like '%" & Me.TextBoxHitword.Text & "%'"
            ElseIf (hitword = True And firma = True And afzender = False And bestemmeling = False) Then
                SQLString = SQLString & " WHERE [korte inhoud] like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " AND (Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " OR Afzender like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " OR Bestemmeling like '%" & Me.TextBoxHitword.Text & "%')"
            ElseIf (hitword = True And firma = True And afzender = True And bestemmeling = False) Then
                SQLString = SQLString & " WHERE [korte inhoud] like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " AND Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " AND (Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
                SQLString = SQLString & " OR Bestemmeling like '%" & Me.TextBoxHitword.Text & "%')"
            ElseIf (hitword = True And firma = True And afzender = False And bestemmeling = True) Then
                SQLString = SQLString & " WHERE [korte inhoud] like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " AND Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " AND (Bestemmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
                SQLString = SQLString & " OR Afzender like '%" & Me.TextBoxHitword.Text & "%')"
            ElseIf (hitword = True And firma = True And afzender = True And bestemmeling = True) Then
                SQLString = SQLString & " WHERE [korte inhoud] like '%" & Me.TextBoxHitword.Text & "%'"
                SQLString = SQLString & " AND Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " AND Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
                SQLString = SQLString & " AND Bestemmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
            ElseIf (hitword = False And firma = True And afzender = False And bestemmeling = False) Then
                SQLString = SQLString & " WHERE Firma like '%" & Me.TextBoxFirma.Text & "%'"
            ElseIf (hitword = False And firma = False And afzender = True And bestemmeling = False) Then
                SQLString = SQLString & " WHERE Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
            ElseIf (hitword = False And firma = False And afzender = False And bestemmeling = True) Then
                SQLString = SQLString & " WHERE Bestemmmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
            ElseIf (hitword = False And firma = True And afzender = True And bestemmeling = False) Then
                SQLString = SQLString & " WHERE Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " AND Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
            ElseIf (hitword = False And firma = True And afzender = False And bestemmeling = True) Then
                SQLString = SQLString & " WHERE Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " AND Bestemmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
            ElseIf (hitword = False And firma = False And afzender = True And bestemmeling = False) Then
                SQLString = SQLString & " WHERE Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
            ElseIf (hitword = False And firma = False And afzender = False And bestemmeling = True) Then
                SQLString = SQLString & " WHERE Bestemmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
            ElseIf (hitword = False And firma = True And afzender = True And bestemmeling = True) Then
                SQLString = SQLString & " WHERE Firma like '%" & Me.TextBoxFirma.Text & "%'"
                SQLString = SQLString & " AND Bestemmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
                SQLString = SQLString & " AND Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
            End If
 
            Dim SelectCommand As New OleDbCommand(SQLString, DataConnectionCorresp)
            Dim DAExcel As New OleDbDataAdapter
            DAExcel.SelectCommand = SelectCommand
            DAExcel.Fill(ds, "Excel")
            DataConnectionCorresp.Close()
 
            'after the connection is closed we can state that the process has ended so, filled can be set true
            filled = True
 
        Catch
 
        End Try
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'DSExcel is a private variable declared
        DSExcel.Clear()
        'creation of an instance of the delegate
        Dim del As New FillHandler(AddressOf FillDatasetThread)
        'I think Asyncstate must be set to nothing, and not to DSExcel?? Or am i wrong?
        'Can I use the DSExcel to pass as a variable?
        del.BeginInvoke(DSExcel, AddressOf CallBack, Nothing)
        'show the distraction form
        'the showing of the form is thread 1 (the synchronous operation)
        distraction = New FrmDistraction
        distraction.ShowDialog()
 
    End Sub
 
    ' this method is the start of the second thread : the asynchronous operation.  Am I correct?
    Public Sub FillDatasetThread(ByVal ds As DataSet)
        Dim filenameNummer As String = GetSourcePath(Me.TextBoxNummer.Text)
 
        'here there is something wrong: suppose the user inputs a filenamenummer that doesn't exists,
        'then the msgbox appears after the distraction form opens.
        'When is the delegate method returning a value?
        If File.Exists(filenameNummer) = True Then
            GetDataFromExcel(GetSourcePath(Me.TextBoxNummer.Text), "[blad1$A3:F3000]", ds)
        Else
            MsgBox("Dossier " & Me.TextBoxNummer.Text & " bestaat niet.  Gelieve een ander dossiernr. in te geven", MsgBoxStyle.Critical, "")
        End If
    End Sub
 
    'creation of the callback method
    Public Sub CallBack(ByVal ar As IAsyncResult)
        Dim DS As DataSet = CType(ar.AsyncState, DataSet)
        If filled = True Then
            'why do I need this dialogresult method?  Can't I detect when the dataset filling operation has
            'finished with the filled boolean or with the Endinvoke method of the delegate?
            distraction.DialogResult = DialogResult.OK
            If DS.Tables(0).Rows.Count > 0 Then
                'the datagrid is never filled up with the results of the dataset DSExcel
                'but the frmDistraction disappears so the filling up of the dataset has ended
                'only the results aren't passed
                Me.createstyles()
                Me.dataGrid1.DataSource = DS
                Me.dataGrid1.DataMember = "Excel"
            Else
                MsgBox("No results", MsgBoxStyle.Critical, "")
            End If
        End If
    End Sub

Could you please take a look? These delegates aren't simple to use. Are any good tutorials on delegates and asynchronous operations?

Many thanks, I really appreciate it.

What actually gets passed back is the asyncState parameter which is the last parameter in this line:

del.BeginInvoke(DSExcel, AddressOf CallBack, Nothing)

Try changing that to:

del.BeginInvoke(DSExcel, AddressOf CallBack, DSExcel)

The first item is just a parameter required by the method, the 2nd is the callback address, the third is the state object which can be retrieved at any point in the async call and is retrieved to return the result in the end call (Callback method):

Dim DS As DataSet = CType(ar.AsyncState, DataSet)

That is probably why it wasn't returning anything. If by chance its not that it must be something very simple that has been overlooked by both of us.

The signature of a BeginInvoke call essentially goes like this:
BeginInvoke([Any Parameters for the Sub that the delegate represents],[Where to call when the Invoked method is done],[AsyncState object which is just a generic object that we can optionally include that can be retrieved at any point in the async call])

@Edneeis:

it still isn't working but we are close. I inserted :

MsgBox(DS.Tables(0).Rows.Count)

in the callback method before me.createstyles and if the application runs it gives the correct number of rows in the dataset. So this means the dataset is filled but the datagrid still isn't filled.

I included the code in the zip-file.

Thanks for your help.

I guess I'm getting old, but here is the fixed code:

VB Code:

Public Class FrmSearchCorresp2
        Inherits BeheerCDs.Form1
        Private WithEvents dataGrid1 As DataGrid
        Public DSExcel As New System.Data.DataSet
        Public DataConnectionCorresp As OleDbConnection
        Public Delegate Sub FillHandler(ByVal ds As DataSet)
        Private distraction As FrmDistraction
 
 
        Public Function GetSourcePath(ByVal number As String) As String
            Dim FileName As String
            'alle info bevindt zich in nummer.xls
            FileName = "\\Epsilon2\vol3\dok\" & number & "\corresp\" & number & "Nummer.xls"
            Return FileName
        End Function
 
        'GetDataFromExcel creates a dataset from an excel file on the server in my company
        Public Sub GetDataFromExcel(ByVal FileName As String, ByVal RangeName As String, ByVal ds As System.Data.DataSet)
 
            Dim SQLString As String
            Dim hitword, firma, afzender, bestemmeling As Boolean
 
            If Me.TextBoxHitword.Text <> "" Then hitword = True Else hitword = False
            If Me.TextBoxFirma.Text <> "" Then firma = True Else firma = False
            If Me.TextBoxAfzender.Text <> "" Then afzender = True Else afzender = False
            If Me.TextBoxBestemmeling.Text <> "" Then bestemmeling = True Else bestemmeling = False
 
            Try
                Dim strConn As String = _
                "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & FileName & "; Extended Properties=Excel 8.0;"
 
                DataConnectionCorresp = New OleDbConnection(strConn)
 
                DataConnectionCorresp.Open()
 
                SQLString = "SELECT * FROM " & RangeName
 
                If (hitword = True And firma = False And afzender = False And bestemmeling = False) Then
                    SQLString = SQLString & " WHERE [korte inhoud] like '%" & Me.TextBoxHitword.Text & "%'"
                    SQLString = SQLString & " OR Firma like '%" & Me.TextBoxHitword.Text & "%'"
                    SQLString = SQLString & " OR Afzender like '%" & Me.TextBoxHitword.Text & "%'"
                    SQLString = SQLString & " OR Bestemmeling like '%" & Me.TextBoxHitword.Text & "%'"
                ElseIf (hitword = True And firma = True And afzender = False And bestemmeling = False) Then
                    SQLString = SQLString & " WHERE [korte inhoud] like '%" & Me.TextBoxHitword.Text & "%'"
                    SQLString = SQLString & " AND (Firma like '%" & Me.TextBoxFirma.Text & "%'"
                    SQLString = SQLString & " OR Afzender like '%" & Me.TextBoxHitword.Text & "%'"
                    SQLString = SQLString & " OR Bestemmeling like '%" & Me.TextBoxHitword.Text & "%')"
                ElseIf (hitword = True And firma = True And afzender = True And bestemmeling = False) Then
                    SQLString = SQLString & " WHERE [korte inhoud] like '%" & Me.TextBoxHitword.Text & "%'"
                    SQLString = SQLString & " AND Firma like '%" & Me.TextBoxFirma.Text & "%'"
                    SQLString = SQLString & " AND (Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
                    SQLString = SQLString & " OR Bestemmeling like '%" & Me.TextBoxHitword.Text & "%')"
                ElseIf (hitword = True And firma = True And afzender = False And bestemmeling = True) Then
                    SQLString = SQLString & " WHERE [korte inhoud] like '%" & Me.TextBoxHitword.Text & "%'"
                    SQLString = SQLString & " AND Firma like '%" & Me.TextBoxFirma.Text & "%'"
                    SQLString = SQLString & " AND (Bestemmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
                    SQLString = SQLString & " OR Afzender like '%" & Me.TextBoxHitword.Text & "%')"
                ElseIf (hitword = True And firma = True And afzender = True And bestemmeling = True) Then
                    SQLString = SQLString & " WHERE [korte inhoud] like '%" & Me.TextBoxHitword.Text & "%'"
                    SQLString = SQLString & " AND Firma like '%" & Me.TextBoxFirma.Text & "%'"
                    SQLString = SQLString & " AND Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
                    SQLString = SQLString & " AND Bestemmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
                ElseIf (hitword = False And firma = True And afzender = False And bestemmeling = False) Then
                    SQLString = SQLString & " WHERE Firma like '%" & Me.TextBoxFirma.Text & "%'"
                ElseIf (hitword = False And firma = False And afzender = True And bestemmeling = False) Then
                    SQLString = SQLString & " WHERE Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
                ElseIf (hitword = False And firma = False And afzender = False And bestemmeling = True) Then
                    SQLString = SQLString & " WHERE Bestemmmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
                ElseIf (hitword = False And firma = True And afzender = True And bestemmeling = False) Then
                    SQLString = SQLString & " WHERE Firma like '%" & Me.TextBoxFirma.Text & "%'"
                    SQLString = SQLString & " AND Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
                ElseIf (hitword = False And firma = True And afzender = False And bestemmeling = True) Then
                    SQLString = SQLString & " WHERE Firma like '%" & Me.TextBoxFirma.Text & "%'"
                    SQLString = SQLString & " AND Bestemmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
                ElseIf (hitword = False And firma = False And afzender = True And bestemmeling = False) Then
                    SQLString = SQLString & " WHERE Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
                ElseIf (hitword = False And firma = False And afzender = False And bestemmeling = True) Then
                    SQLString = SQLString & " WHERE Bestemmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
                ElseIf (hitword = False And firma = True And afzender = True And bestemmeling = True) Then
                    SQLString = SQLString & " WHERE Firma like '%" & Me.TextBoxFirma.Text & "%'"
                    SQLString = SQLString & " AND Bestemmeling like '%" & Me.TextBoxBestemmeling.Text & "%'"
                    SQLString = SQLString & " AND Afzender like '%" & Me.TextBoxAfzender.Text & "%'"
                End If
 
                Dim SelectCommand As New OleDbCommand(SQLString, DataConnectionCorresp)
                Dim DAExcel As New OleDbDataAdapter
                DAExcel.SelectCommand = SelectCommand
                DAExcel.Fill(ds, "Excel")
                DataConnectionCorresp.Close()
            Catch
 
            End Try
        End Sub
 
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            'DSExcel is a private variable declared
            DSExcel.Clear()
            'creation of an instance of the delegate
            Dim del As New FillHandler(AddressOf FillDatasetThread)
            'Can I use the DSExcel to pass as a variable?
            del.BeginInvoke(DSExcel, AddressOf CallBack, DSExcel)
            'show the distraction form
            'the showing of the form is thread 1 (the synchronous operation)
            distraction = New FrmDistraction
            distraction.ShowDialog()
 
            'execution continues here when the dataset is filled and displayed
        End Sub
 
        ' this method is the start of the second thread : the asynchronous operation.  Am I correct?
        Public Sub BeginFill(ByVal ds As DataSet)
            Dim filenameNummer As String = GetSourcePath(Me.TextBoxNummer.Text)
 
            'here there is something wrong: suppose the user inputs a filenamenummer that doesn't exists,
            'then the msgbox appears after the distraction form opens.
            'When is the delegate method returning a value?
            If File.Exists(filenameNummer) = True Then
                GetDataFromExcel(GetSourcePath(Me.TextBoxNummer.Text), "[blad1$A3:F3000]", ds)
            Else
                MsgBox("Dossier " & Me.TextBoxNummer.Text & " bestaat niet.  Gelieve een ander dossiernr. in te geven", MsgBoxStyle.Critical, "")
            End If
        End Sub
 
        ' this method is the end of the second thread : the asynchronous operation.  Am I correct?
        Public Sub EndFill(ByVal ds As DataSet)
            'close the distraction dialog
            distraction.DialogResult = DialogResult.OK
            'handle results
            If ds.Tables(0).Rows.Count > 0 Then
                Me.createstyles()
                Me.dataGrid1.DataSource = ds
                Me.dataGrid1.DataMember = "Excel"
            Else
                MsgBox("No results", MsgBoxStyle.Critical, "")
            End If
        End Sub
 
        'creation of the callback method
        Public Sub CallBack(ByVal ar As IAsyncResult)
            Dim DS As DataSet = CType(ar.AsyncState, DataSet)
            Dim del As New FillHandler(AddressOf EndFill)
            'marshal back to the UI thread with the results
            Me.Invoke(del, New Object() {DS})
        End Sub

The problem was that the callback method wasn't on the UI thread so it couldn't display the dataset. To solve it you just use Me.Invoke to marshal back to the UI thread.

To answer some of your questions:

Quote:

---

I think Asyncstate must be set to nothing, and not to DSExcel?? Or am i wrong? Can I use the DSExcel to pass as a variable?

---

AsyncState is our return value, similiar to passing a variable to a sub ByRef to change the value. You can use DSExcel although there is no need to use a variable with full form level scope, but if you want to then that is fine too.

Quote:

---

Why do I need this dialogresult method? Can't I detect when the dataset filling operation has finished with the filled boolean or with the Endinvoke method of the delegate?

---

Setting the dialogresult to ok is what closes the distraction form. Using Close should work too, but setting the value to a dialogresult will allow you to catch that result if its needed. There is no need for the filled variable either since the callback method will not be called until the operation is complete. If you want to double check the operation you can check the ar.IsComplete property. Also we never call EndInvoke we just let it fall out of scope. EndInvoke blocks a thread until the operation is complete which is not what we want.

Also note: End Fill only needs the code to display the UI for the results. Execution will continue back at the line following distraction.ShowDialog() in the button.click event after endfill is done.

thanks for taking a look at my code.

I have only one remark:

VB Code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            'DSExcel is a private variable declared
            DSExcel.Clear()
            'creation of an instance of the delegate
            Dim del As New FillHandler(AddressOf FillDatasetThread)
            'Can I use the DSExcel to pass as a variable?
            del.BeginInvoke(DSExcel, AddressOf CallBack, DSExcel)
            'show the distraction form
            'the showing of the form is thread 1 (the synchronous operation)
            distraction = New FrmDistraction
            distraction.ShowDialog()
 
            'execution continues here when the dataset is filled and displayed
        End Sub

shouldn't this be:

VB Code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            'DSExcel is a private variable declared
            DSExcel.Clear()
            'creation of an instance of the delegate
'replace Filldatasetthread by beginfill
            Dim del As New FillHandler(AddressOf BeginFill)
            'Can I use the DSExcel to pass as a variable?
            del.BeginInvoke(DSExcel, AddressOf CallBack, DSExcel)
            'show the distraction form
            'the showing of the form is thread 1 (the synchronous operation)
            distraction = New FrmDistraction
            distraction.ShowDialog()
 
            'execution continues here when the dataset is filled and displayed
        End Sub

tomorrow i'll test the code and i'll keep you informed.

kind regards

inte

Yes it should, good catch!

@ Edneeis,

As promised I tested the code you suggested and it works fine. However there is still something strange going on. If the user clicks button1 the first time, then the dataset is filled while the distraction form is appearing. After the fillup of the dataset, the distraction form disappears and the datagrid is filled up with the correct data. So far so good.

The problem arises when the user clicks the button a second time (after the datagrid is filled). The distraction form appears and the dataset is filled up. Also the datagrid is filled up, again with the correct results, but the distraction form doesn't disappear (even after the filling up of the datagrid). Because of this the program crashes (you can't do anything anymore).

Any idea why this is so. I think it has something to do with me.invoke(..) but I can't figure out what.

Can you help me?

Thanks

Jinte (a desperate newby)

I'm not sure it works fine for me. I tried several times but could not reproduce the problem. I was able to successfully run the fill bit several times. Here is my test example (see attached). I had it exactly like yours, using a dataadapter and connection created at designtime but switched to an xml file to make it more portable.

Me.Invoke simply invokes the delegate passed in on the same thread as the form (ME).

Here is a better animation if you don't want to use the clock. Just replace the clock image with this one on your distraction form.

http://www.vbforums.com/attachment.p...postid=1614500

Hey I was thinking, this example and the code exposed came about to make it appear to show progress on a lengthy process, but I wanted to point out that the same code (minus the distraction form) will allow the user to continue using your app and complete the process in the background. If you want the user to be able to interact and work in your application while the dataset is being filled then just don't show the distraction form. The only thing in the current code stopping the user from interaction is the fact that the distraction form is shown modally (ShowDialog). So I figured I'd let you know that you have that option if you prefer it.

@Edneeis

I compiled your solution and it worked like it should. I'll try to find the difference with my solution, so hopefully it will work then.

Thanks for your time,

Jinte