1
I have the code below, but I need to make it searchable in query table,
below code only allowed seach the table which in show mdb only. (i.e. have 3 table, but only can search either one only, cannot serch by combine 3 table)
Example I have the query table below, how do I make the code to seach based on the query from this:
SELECT Product.ID, Product.Description, Quantity.Quantity, Quantity.SeialNo, Quantity.SupplierID, Supplier.SupplierName, Supplier.Address, Supplier.Phone
FROM (Product INNER JOIN Quantity ON Product.ID = Quantity.LED) INNER JOIN Supplier ON Quantity.SupplierID = Supplier.SupplierName;
'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).BeginIni t()
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).Capt ion)
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()
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.lblSelectString = New System.Windows.Forms.TextBox
CType(Me.dGrid, System.ComponentModel.ISupportInitialize).BeginIni t()
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
'
'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"
'
'
'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.Add(Me.lblSelectString)
Me.Controls.Add(Me.btnRestore)
Me.Controls.Add(Me.Findbtn)
Me.Controls.Add(Me.searchTxt)
Me.Controls.Add(Me.cBoxParamets)
Me.Controls.Add(Me.dGrid)
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)
Me.PerformLayout()
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
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.ToStri ng() = "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.ColumnHe ader
'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
'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
End Class 'searchfrm
