Dynamic search text in datagrid

[JamesOo]

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

 

[cjard]

I didnt read the code; it's a bit of an unindented mess. I did see some indication on the last page of it, that you need to read the PQ link in my signature..

But as for the problem of providing a "query any column" facility, you have it pretty much correct; build an sql dynamically that has placeholders for the table, the column to be queried, a suitably named parameter (read PQ) and assigna value to the parameter based on your query requiremnt (read PQ)

 

[MattP]

How Do I: Create a Search Form?

You should be able to take this example and expand upon it.

 

[buzz_peppermint]

hye there,

i already saw the tutorial and i do it step by step just like it instructed.
But i using db = access(.mdb)

the query is:

LIKE @LastName + '%'

but when i did it .. it autogenarate to:

LIKE '@FirstName' + '%'

so, what is the prob? thank you

 

[cjard]

What tutorial?

 

[buzz_peppermint]

How Do I: Create a Search Form?

suggested at above.

 

[cjard]

Ugh. Silverlight.. I should install it

Anyways, this tutorial will basically ask you to write an SQL like this:


SELECT * FROM person WHER name LIKE @name

-

In access there is no support for named parameters:

SELECT * FROM person WHER name LIKE ?


This means:
The order you add parameters to the command object is the order they appear in the sql
You cannot reuse a parameter (in sqlserver you can: SELECT * FROM person WHERE name LIKE @name OR nickname LIKE @name)


-

So, change your @FirstName into question mark: ?

Do not append the % in the SQL, put it in the parameter value:


"SELECT * FROM person WHERE name LIKE ?"
...
command.Parameters.AddWithValue("nameParam", "Smith%")



-

This makes your searching flexible

-

Consider upgrading from access to sql server. it's a much better db

 

[buzz_peppermint]

thank you for your reply.

actually , i want to use db: sql.
but the connection to the server eg( it_server) cannot be establish.

error msg:

"when connecting to sql server 2005, this failure maybe caused by the fact that under the default settings SQL server does not allow remote connections.
(provider: named piped provider, error 40- could not open a connection to sql server)


i have already install sql management studio and configure the surface area configuration to allow remote connections.

i already google or msdn it, but still did not found the best solutions to problem.

maybe i have left a few step when installing the sql server or sqlexpress ???



can you help me to solve this problem.

 

[cjard]

enable password authentication, and use a user/password combination instead of Integrated Security

 

[buzz_peppermint]

thank you for your advise.i will try it again for the sql server setting.

but can you refer to me any notes/website how to use wildcard character ( db: access )? bassiclly i want to understand more.

i only find * and ?( wildcard char) for access

my code is like this:

where firstname like ? - right query
where firstname like * - wrong query

 

[MattP]

10 tips for using wildcard characters in Microsoft Access criteria expressions

 

[buzz_peppermint]

thank you for you url
but i already try the example for a few week but it seem doesn't work.
i have done a lot of research but the query itself

let say i want to search (field: firstname) start with alphabet:

like "*"

------it wont recognize *

but the query builder wont return any field?

what is my mistake

 

[cjard]

Matt, this advice is for working within the Access front end. It is not valid/relevant to ADO.NET queries because the wildcard character is %


Buzz. Either:

put the wildcard in the query:

WHERE [LastName] LIKE ? & '%'



Or put the wildcard in the value:

WHERE [LastName] LIKE ?

cmd.Parameters.AddWithValue("p1", "Smith%")


The second approach is MUCH more flexible

 

[buzz_peppermint]

thank you very much !!!!!!!

this really help me because i already stuck with the problem for a few week.

right now i have a new problem!

this is the error:

Concurrency violation: the update command affected 0 of the expected 1 records.

Include logic in your applications to handle concurrency violations

i also attach a pic to explaint the error

plz advice me.

 

[cjard]

Someone else changed the data in between you downloading it and you uploading it again. To simplistically solve the "problem", just turn off optimistic concurrency in the tableadapter wizard

If youre using a CommandBuilder in code, I'm given to understand that you cannot turn off the generating of optimistically concurrent queries

Lastly, I say "problem" in double quotes because it is not really a problem at all, it is merely a notification to you that youre about to overwrite changes that someone else made, and it allows you to decide how to go about merging the two user's work. If it's all too much for your brain right now, just turn it off and come back to it later, when users start complaining that their edits have been lost