Searching any record

[mzeewashooo]

iam using vb 2010 and i want to search for any record when i enter a text in the search text box. i mean, if my records are as follows:
dan,daniel,edn,steven,mary.
and i just inpunt 'd' in the search box, the datagrid lists: dan,daniel,edn because they have a 'd' in them.

my code is as follows.

Sub search()

Dim objConn As New MySqlConnection
Dim objCmd As New MySqlCommand
Dim strConnString,strSQL As String

strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false"
strSQL = "SELECT * FROM donationtype WHERE firstName like '%" & strKeyWord &"'"

objConn.ConnectionString = strConnString
With objCmd
.Connection = objConn
.CommandText = strSQL
.CommandType = CommandType.Text
End With

End Sub

[hericles]

Change your SQL to have the % sign in front and behind the strKeyWord. This will make the search include anything before or after a 'D'(in your example), not just before like it is now.

[mzeewashooo]

i have done that, but it gives me an error that the strKeyWord has being uesed before it is assigned a value, help please.

[kRod]

Try this

Click Here

[hericles]

So where are you assigning strKeyWord to the string you want to search for? It isn't in the method you have posted up. To avoid that error simply set strKeyword to seomthing before you create the SQL statement.

[jezguitarist30]

Try to use the following query.

strSQL = "SELECT * FROM donationtype WHERE firstName like '%" & strKeyWord & "%' "

[M.Waqas Aslam]

hello !
use this code at the search textbox textchange event.

Sub search()
dim con as new sqlconnection("your connection string")
con.open()
dim dt as new datatable
dim da as new sqldataadapter("select * from table1 where firstname like '%" & txtSearch.text & "%'",con)
da.fill(dt)
datagridview1.datasource= dt
con.close()
End Sub

simple change this code ,hope this will work fine.

Regards

[mzeewashooo]

Private Sub FirstNameToolStripTextBox_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles FirstNameToolStripTextBox.TextChanged
        Dim objConn As New MySqlConnection
        Dim objCmd As New MySqlCommand
        Dim strConnString, strSQL As String
        strConnString = "Server=localhost;Database=kscf;Uid=root;Pwd=;Pooling=false"
        strSQL = "SELECT * FROM donationtype WHERE FirstName like '%" & strKeyWord & "'% "
        objConn.ConnectionString = strConnString
        With objCmd
            .Connection = objConn
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With

[mzeewashooo]

the above is my code for the textbox in which te search criteria should be inputed

[hericles]

That code is OK except for 2 things: where you are defining the strKeyWord? It definitely isn't within the method you posted.
And the second % is outside the single quote: strKeyWord & "'% " should be
strKeyWord & "%'"
Also, of course, you need to open the connection, execute the command and close the connection.

[mzeewashooo]

the above is my code for the textbox in which te search criteria should be inputed

[TnTinMN]

If you are not limiting the allowable characters in the textbox to exclude characters that the "LIKE" statement uses, you should preprocess the textbox's text to bracket those characters similar to like what I have shown in my example. I believe the SQL "LIKE" command treats these as special characters: "[", "]", "%", and "_".

I would suggest that you do not query the database for each change to the textbox. Just fill the underlying datatable with all the records and use a dataview as the datasource for the datagridview. You change the dataview's rowfilter based on the textbox. This example is just to demo the filter logic and does not require a database to fill the table. So just ignore the filling of the datatable part of it. It is the dataview logic that is important.

Public Class Form1
   Private dt As New DataTable("fred")
   Private filter As New DataView(dt)
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

      Dim r As DataRow
      With dt
         .Columns.Add(New DataColumn("name", GetType(String)))
         r = .NewRow : r(0) = "dan" : .Rows.Add(r)
         r = .NewRow : r(0) = "danny" : .Rows.Add(r)
         r = .NewRow : r(0) = "fred" : .Rows.Add(r)
         r = .NewRow : r(0) = "andrew" : .Rows.Add(r)
         r = .NewRow : r(0) = "mary" : .Rows.Add(r)
         r = .NewRow : r(0) = "sue" : .Rows.Add(r)
         r = .NewRow : r(0) = "jill" : .Rows.Add(r)
         r = .NewRow : r(0) = "jack" : .Rows.Add(r)
         r = .NewRow : r(0) = "r[on" : .Rows.Add(r) 'special character
         r = .NewRow : r(0) = "r*ch" : .Rows.Add(r) 'special character
         .AcceptChanges()

      End With
      DataGridView1.DataSource = filter
   End Sub

   Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
      Dim filtertext As New System.Text.StringBuilder(TextBox1.Text.Length)

      If TextBox1.Text = "" Then
         filter.RowFilter = ""
      Else
         'process string for special characters 
         Dim specialchars() As String = {"[", "]", "*", "%"}
         For Each c As Char In TextBox1.Text
            If specialchars.Contains(c) Then
               filtertext.Append("[" & c & "]")
            Else
               filtertext.Append(c)
            End If
         Next
      End If
      filter.RowFilter = "[name] like '*" & filtertext.ToString & "*'"

   End Sub
End Class

[mzeewashooo]

ok let me try it out then get back to you.

[mzeewashooo]

it has worked but it wont search every record on every column or row.