473,287 Members | 1,868 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Dynamic search text in datagrid

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
Oct 29 '08 #1
0 2696

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: JP011 | last post by:
Hello I have hit a major road block when it comes to building my dynamic datagrid. To make a long story short I need a dynamic datagrid because my connection string could change and I need the...
4
by: Brian Shannon | last post by:
I have 3 combo boxes and two date text boxes on a .aspx page. The user can fill in any of the 5 controls or none to filter a datagrid. I was hoping someone could explain how to efficiently build...
3
by: Leo J. Hart IV | last post by:
OK, here's another question for the experts: I am building a multi-step (3 steps actually) form using a panel for each step and hiding/displaying the appropriate panel/panels depending on which...
1
by: benoit | last post by:
Hi, I created a Dynamic Datagrid and i added an EditCommandColumn to it. Works fine, but my Editcommand eventhandler seems to have a problem with PostBack This is my code private DataGrid...
4
by: Gregory Gadow | last post by:
If there is a more appropriate forum, please let me know and I will post there. Our field reps can go on to our website and select from several sets of data to create the address we then provide...
0
by: Savvas | last post by:
Hi, I am using VB.Net 2005 for a windows application. The application was actually upgraded from vb.net 2003. I have a search field which is used as a parameter to search all datagrid...
0
by: | last post by:
I have a question about spawning and displaying subordinate list controls within a list control. I'm also interested in feedback about the design of my search application. Lots of code is at the...
0
by: MaryamSh | last post by:
Create Dynamic Dropdownlist Controls and related event -------------------------------------------------------------------------------- Hi, I am creating a Dynamic Search in my application. I...
1
by: chris3vic | last post by:
I have a dynamically created datagrid, populated from a dataset that is filled by a sql string triggered by a command button. The application itself allows a user to input a selection of criteria and...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.