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