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
Reply With Quote