searchfrm.vb

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

'------------------------------------------------------------------------
'
' Author      : Anas S.
' Date        : 1 Jan 2004
' Version     : 1.0
' Description : This Form Filters and 
'               Searches the database the user either has
'               to right click a column header in the Data
'               grid and chose from the menu or type a text search
'              
'
'------------------------------------------------------------------------
Imports System
Imports System.Drawing
Imports System.Collections
Imports System.ComponentModel
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.OleDb


Namespace DataEasy
    _
    '/ <summary>
    '/ Summary description for Form1.
    '/ </summary>
    Public Class searchfrm
        Inherits System.Windows.Forms.Form
#Region "Private Declarations"
        Private activateMouse As Boolean = False 'a boolian to allow mouse click on the DGrid
        Private elements As Collection 'the filter menues' elements are stored here
        Private checkedMenu() As String 'the element you are looking for in the coulmn
        Private accessDataSet As New DataSet()
        Private accessConnection As New OleDbConnection()

        Private tableName As String = "" 'DataBase table name
        Private doUpdate As Boolean = True 'Update the filter menues or not 
        Private columnHit As Integer 'which column in the table is hit
        Private WithEvents dGrid As System.Windows.Forms.DataGrid
        Private FilterMenu() As System.Windows.Forms.ContextMenu 'the filter menues for all the columns
        Private cBoxParamets As System.Windows.Forms.ComboBox 'the combobox which holds the column names
        'to chose from in the search
        Private WithEvents searchTxt As System.Windows.Forms.TextBox 'the search element is put here
        'for the text based search
        Private WithEvents Findbtn As System.Windows.Forms.Button 'find row in data according to text based search
        Private WithEvents btnRestore As System.Windows.Forms.Button 'remove all filters button
        Private MFRM As Form
#End Region
        Private lblSelectString As System.Windows.Forms.TextBox

        '/ <summary>
        '/ Required designer variable.
        '/ </summary>
        Private components As System.ComponentModel.Container = Nothing

        'The search class constructor
        Public Sub New(ByVal motherFrm As Form, ByVal datasource As String, ByVal table_Name As String, ByVal SelectString As String)
            InitializeComponent()

            'This refers to the parent form
            MFRM = motherFrm
            'the tablename to do the search on
            tableName = table_Name

            'Initializing the connection here to the source mdb file
            CType(Me.accessDataSet, System.ComponentModel.ISupportInitialize).BeginInit()
            accessConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + datasource '
            accessConnection.Open()
            loadData(SelectString)
            lblSelectString.Text = "Select Command = " + SelectString
            activateMouse = True
            accessConnection.Close()

            Dim myDataTable As DataTable = accessDataSet.Tables(tableName)

            'Find all columns and put them in the combobox
            'cBoxParamets
            Dim i As Integer
            For i = 0 To myDataTable.Columns.Count - 1
                cBoxParamets.Items.Add(myDataTable.Columns(i).Caption)
                If i = 0 Then
                    cBoxParamets.Text = myDataTable.Columns(i).Caption
                End If
            Next i
            dGrid.Height = Me.Height - 135
        End Sub 'New


        '/ <summary>
        '/ Clean up any resources being used.
        '/ </summary>
        Protected Overloads Sub Dispose(ByVal disposing As Boolean)
            If disposing Then
                If Not (components Is Nothing) Then
                    components.Dispose()
                End If
            End If
            MyBase.Dispose(disposing)
        End Sub 'Dispose


#Region "Windows Form Designer generated code"
        '/ <summary>
        '/ Required method for Designer support - do not modify
        '/ the contents of this method with the code editor.
        '/ </summary>
        Private Sub InitializeComponent()
            Dim resources As New System.Resources.ResourceManager(GetType(searchfrm))
            Me.dGrid = New System.Windows.Forms.DataGrid()
            Me.cBoxParamets = New System.Windows.Forms.ComboBox()
            Me.searchTxt = New System.Windows.Forms.TextBox()
            Me.Findbtn = New System.Windows.Forms.Button()
            Me.btnRestore = New System.Windows.Forms.Button()
            Me.lblSelectString = New System.Windows.Forms.TextBox()
            CType(Me.dGrid, System.ComponentModel.ISupportInitialize).BeginInit()
            Me.SuspendLayout()
            ' 
            ' dGrid
            ' 
            Me.dGrid.AccessibleRole = System.Windows.Forms.AccessibleRole.ColumnHeader
            Me.dGrid.AllowSorting = False
            Me.dGrid.DataMember = ""
            Me.dGrid.Dock = System.Windows.Forms.DockStyle.Bottom
            Me.dGrid.HeaderForeColor = System.Drawing.SystemColors.ControlText
            Me.dGrid.Location = New System.Drawing.Point(0, 115)
            Me.dGrid.Name = "dGrid"
            Me.dGrid.ReadOnly = True
            Me.dGrid.Size = New System.Drawing.Size(528, 248)
            Me.dGrid.TabIndex = 1
            ' 
            ' cBoxParamets
            ' 
            Me.cBoxParamets.Location = New System.Drawing.Point(16, 8)
            Me.cBoxParamets.Name = "cBoxParamets"
            Me.cBoxParamets.Size = New System.Drawing.Size(121, 21)
            Me.cBoxParamets.TabIndex = 2
            Me.cBoxParamets.Text = "Tables"
            ' 
            ' searchTxt
            ' 
            Me.searchTxt.Location = New System.Drawing.Point(16, 48)
            Me.searchTxt.Name = "searchTxt"
            Me.searchTxt.Size = New System.Drawing.Size(240, 20)
            Me.searchTxt.TabIndex = 3
            Me.searchTxt.Text = ""
            ' 
            ' Findbtn
            ' 
            Me.Findbtn.Location = New System.Drawing.Point(144, 8)
            Me.Findbtn.Name = "Findbtn"
            Me.Findbtn.Size = New System.Drawing.Size(112, 32)
            Me.Findbtn.TabIndex = 4
            Me.Findbtn.Text = "Find"
            ' 
            ' btnRestore
            ' 
            Me.btnRestore.Location = New System.Drawing.Point(264, 8)
            Me.btnRestore.Name = "btnRestore"
            Me.btnRestore.Size = New System.Drawing.Size(64, 60)
            Me.btnRestore.TabIndex = 5
            Me.btnRestore.Text = "Restore"
            ' 
            ' lblSelectString
            ' 
            Me.lblSelectString.BackColor = System.Drawing.Color.Black
            Me.lblSelectString.ForeColor = System.Drawing.Color.LimeGreen
            Me.lblSelectString.Location = New System.Drawing.Point(16, 80)
            Me.lblSelectString.Name = "lblSelectString"
            Me.lblSelectString.ReadOnly = True
            Me.lblSelectString.Size = New System.Drawing.Size(312, 20)
            Me.lblSelectString.TabIndex = 6
            Me.lblSelectString.Text = "Select Command"
            ' 
            ' searchfrm
            ' 
            Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
            Me.ClientSize = New System.Drawing.Size(528, 363)
            Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.lblSelectString, Me.btnRestore, Me.Findbtn, Me.searchTxt, Me.cBoxParamets, Me.dGrid})
            Me.Icon = CType(resources.GetObject("$this.Icon"), System.Drawing.Icon)
            Me.MinimizeBox = False
            Me.MinimumSize = New System.Drawing.Size(352, 360)
            Me.Name = "searchfrm"
            Me.Text = "DataEasy: Search DataBase"
            CType(Me.dGrid, System.ComponentModel.ISupportInitialize).EndInit()
            Me.ResumeLayout(False)
        End Sub 'InitializeComponent

#End Region

#Region "load Data"
        'This routine loads data from a mdb file
        'into the DGrid
        Private Sub loadData(ByVal SelectString As String)
            accessDataSet.RejectChanges()
            accessDataSet.Clear()

            Dim accessSelectCommand As New OleDbCommand()
            Dim accessInsertCommand As New OleDbCommand()
            Dim accessDataAdapter As New OleDbDataAdapter()

            accessSelectCommand.CommandText = SelectString
            accessSelectCommand.Connection = accessConnection
            accessDataAdapter.SelectCommand = accessSelectCommand

            ' Attempt to fill the dataset through the OleDbDataAdapter1.
            accessDataAdapter.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", tableName)})
            accessDataAdapter.Fill(accessDataSet)

            dGrid.SetDataBinding(accessDataSet, tableName)

            Dim col As Integer = accessDataSet.Tables(tableName).Columns.Count
            Dim row As Integer = accessDataSet.Tables(tableName).Rows.Count

            If doUpdate = True Then
                checkedMenu = New [String](col - 1) {}
            End If

            elements = New Collection()

            'elements = New Object()(col) {}
            FilterMenu = New ContextMenu(col - 1) {}

            Dim i As Integer
            For i = 0 To col - 1
                elements.Add(New Object(row - 1) {})
                If doUpdate = True Then
                    checkedMenu(i) = "None"
                End If
            Next i

            For i = 0 To col - 1
                Dim j As Integer
                For j = 0 To row - 1
                    If dGrid(j, i).GetType().Name = "Int32" Or dGrid(j, i).GetType().Name = "DateTime" Or dGrid(j, i).GetType().Name = "Decimal" Then
                        elements.Item(i + 1)(j) = dGrid(j, i)
                    Else
                        elements.Item(i + 1)(j) = dGrid(j, i).ToString()
                    End If
                Next j
            Next i

            For i = 0 To col - 1
                Try
                    Array.Sort(elements.Item(i + 1))
                Catch