searchfrm.vb

This application uses OleDb as a backhand communicator with the file to allow the user to : select,

                End Try
                FilterMenu(i) = New ContextMenu()
                Console.WriteLine(elements(i + 1)(0))
                make_menues(elements(i + 1), FilterMenu(i))
            Next i
        End Sub 'loadData

#End Region

#Region "Make Menues"
        'This routine makes and updates filter menus
        'inaccordance with the displayed data in the grid
        Private Sub make_menues(ByVal array_elements() As Object, ByVal cMenu As System.Windows.Forms.ContextMenu)
            Dim Prev_Element As String = ""

            Dim mfirstItems As MenuItem = New MenuItem("None")
            AddHandler mfirstItems.Click, AddressOf Me.cMenuClick
            'mfirstItems.Click += New System.EventHandler(Me.cMenuClick)
            cMenu.MenuItems.Add(mfirstItems)

            Dim mItems() As System.Windows.Forms.MenuItem = New MenuItem(array_elements.Length) {}
            Dim i As Integer
            For i = 0 To array_elements.Length - 1
                If Prev_Element <> array_elements(i).ToString() Then
                    mItems(i) = New MenuItem(array_elements(i).ToString())
                    AddHandler mItems(i).Click, AddressOf Me.cMenuClick
                    cMenu.MenuItems.Add(mItems(i))
                    Prev_Element = array_elements(i).ToString()
                End If
            Next i
        End Sub 'make_menues

#End Region

#Region "Filter Menu Click Event"
        'This routine handles the filter menu click event
        Private Sub cMenuClick(ByVal sender As Object, ByVal e As System.EventArgs)
            doUpdate = False
            Dim tempItem As MenuItem = CType(sender, MenuItem)
            Dim accessDataTable As DataTable = accessDataSet.Tables(tableName)

            If accessDataTable.Columns(columnHit).DataType.ToString() = "System.Byte[]" Then
                MessageBox.Show("This DataType Cannot Be Filtered", "Unable To Do Filter", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                Return
            End If

            checkedMenu(columnHit) = tempItem.Text
            Try
                loadData(MakeSelectString(checkedMenu))
                lblSelectString.Text = "Select Command = " + MakeSelectString(checkedMenu)
            Catch
            End Try
        End Sub 'cMenuClick

#End Region

#Region "Make the Select Command"
        'This routine creates the seacrh command to be used
        'as a select command based on the options specified
        'by the user through text based search or menu based
        'filter
        Private Function MakeSelectString(ByVal MenuChecked() As String) As String
            Dim accessDataTable As DataTable = accessDataSet.Tables(tableName)
            Dim STselect As String = "Select * From " + tableName + " Where "
            Dim there_is_Change As Boolean = False
            Dim i As Integer
            For i = 0 To MenuChecked.Length - 1

                Dim colType As String = accessDataTable.Columns(i).DataType.ToString()


                If MenuChecked(i) <> "None" Then
                    If i <> 0 And there_is_Change = True Then

                        If colType = "System.String" Then
                            STselect += " And [" + accessDataSet.Tables(tableName).Columns(i).Caption + " ] = '" + MenuChecked(i) + "'"
                        Else
                            If colType = "System.DateTime" Then
                                STselect += " And [" + accessDataSet.Tables(tableName).Columns(i).Caption + " ] = #" + MenuChecked(i) + "#"
                            Else
                                STselect += " And [" + accessDataSet.Tables(tableName).Columns(i).Caption + " ] = " + MenuChecked(i)
                            End If
                        End If
                    Else
                        If colType = "System.String" Then
                            STselect += " [" + accessDataSet.Tables(tableName).Columns(i).Caption + " ] = '" + MenuChecked(i) + "'"
                        Else
                            If colType = "System.DateTime" Then
                                STselect += " [" + accessDataSet.Tables(tableName).Columns(i).Caption + " ] = #" + MenuChecked(i) + "#"
                            Else
                                STselect += " [" + accessDataSet.Tables(tableName).Columns(i).Caption + " ] = " + MenuChecked(i)
                            End If
                        End If
                    End If
                    there_is_Change = True
                End If
            Next i
            If there_is_Change = False Then
                STselect = "Select * From " + tableName
            End If
            lblSelectString.Text = "Select Command = " + STselect
            Return STselect
        End Function 'MakeSelectString

#End Region

#Region "Data Grid Mouse Down Event"
        'This routine creates and loads filter menues for 
        'the datagrid and then displays them if the user right
        'clicks the header of any column
        Private Sub dGrid_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dGrid.MouseDown
            If activateMouse = False Then
                Return
            End If
            If e.Button <> System.Windows.Forms.MouseButtons.Right Then
                Return
            End If
            Dim myGrid As DataGrid = CType(sender, DataGrid)
            Dim hti As System.Windows.Forms.DataGrid.HitTestInfo
            hti = myGrid.HitTest(e.X, e.Y)
            'Dim message As String = "You clicked "

            Select Case hti.Type
                Case System.Windows.Forms.DataGrid.HitTestType.ColumnHeader
                    'message += "the column header for column " + hti.Column
                    columnHit = hti.Column
                    FilterMenu(hti.Column).Show(dGrid, New Point(e.X, e.Y))
            End Select
        End Sub 'dGrid_MouseDown

#End Region

#Region "Text based search"
        'Button find is clicked
        Private Sub Findbtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Findbtn.Click
            find_the_data()
        End Sub 'Findbtn_Click


        'based on the element required in the search string
        Private Sub find_the_data()
            Dim index As Integer = 0
            If searchTxt.Text = "" Then
                Return
            End If
            Dim accessDataTable As DataTable = accessDataSet.Tables(tableName)
            Dim CommandText As String = "SELECT * FROM " + tableName + " Where [" + cBoxParamets.Text + "] = "
            Try
                Dim i As Integer
                For i = 0 To (accessDataSet.Tables(tableName).Columns.Count) - 1
                    If cBoxParamets.Text = cBoxParamets.Items(i).ToString() Then
                        index = i
                    End If
                    checkedMenu(i) = "None"
                Next i

                If accessDataTable.Columns(index).DataType.ToString() = "System.Byte[]" Then
                    MessageBox.Show("This DataType Cannot Be Filtered", "Unable To Do Filter", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                    Return
                End If

                If accessDataTable.Columns(index).DataType.ToString() = "System.String" Then
                    CommandText += " '" + searchTxt.Text + "'"
                Else
                    If accessDataTable.Columns(index).DataType.ToString() = "System.DateTime" Then
                        CommandText += " #" + searchTxt.Text + "#"
                    Else
                        CommandText += searchTxt.Text
                    End If
                End If
                loadData(CommandText)
                lblSelectString.Text = "Select Command = " + CommandText
            Catch
            End Try
        End Sub 'find_the_data


        'This routine removes all the filters
        'and displays all the data
        Private Sub btnRestore_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRestore.Click
            Dim i As Integer
            For i = 0 To (accessDataSet.Tables(tableName).Columns.Count) - 1
                checkedMenu(i) = "None"
            Next i
            loadData(("Select * From " + tableName))
            lblSelectString.Text = "Select Command = Select * From " + tableName
        End Sub 'btnRestore_Click


        'The user clicked enter instead of buttonFind
        'should give same affect
        Private Sub searchTxt_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles searchTxt.KeyDown
            If e.KeyCode = Keys.Enter Then
                find_the_data()
            End If
        End Sub 'searchTxt_KeyDown

        'Dynamic Search with text change
        Private Sub searchTxt_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles searchTxt.TextChanged
            Dim index As Integer = 0
            If searchTxt.Text = "" Then
                lblSelectString.Text = "Select Command = Select * From " + tableName
                loadData(("Select * From " + tableName))
                Return
            End If
            Dim accessDataTable As DataTable = accessDataSet.Tables(tableName)
            Dim CommandText As String = "SELECT * FROM " + tableName + " Where [" + cBoxParamets.Text + "] Like "
            Try
                Dim i As Integer
                For i = 0 To (accessDataSet.Tables(tableName).Columns.Count) - 1
                    If cBoxParamets.Text = cBoxParamets.Items(i).ToString() Then
                        index = i
                    End If
                    checkedMenu(i) = "None"
                Next i

                If accessDataTable.Columns(index).DataType.ToString() = "System.Byte[]" Then
                    MessageBox.Show("This DataType Cannot Be Filtered", "Unable To Do Filter", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                    Return
                End If

                CommandText += " '" + searchTxt.Text + "%'"
                loadData(CommandText)
                lblSelectString.Text = "Select Command = " + CommandText
            Catch
            End Try
        End Sub 'searchTxt_TextChanged


#End Region

#Region "search Form events"
        'From Resize routine
        Private Sub searchfrm_Resize(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Resize
            dGrid.Height = Me.Height - 135
        End Sub 'searchfrm_Resize


        'Form Closing Routine
        Private Sub searchfrm_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
            MFRM.Enabled = True
        End Sub 'searchfrm_Closing
#End Region
    End Class 'searchfrm 
End Namespace 'DataEasy