How do I get all Textboxes on a form to be searchable?

Hello Everyone, I have created a form that has a 'Textbox' , 8 'ComboBoxes' and 2 richtextboxes.  I would like to have all boxes searchable. What I mean by that is when a user enters data in any of the boxes will return the entire row matching what the user entered. Here is what I have so far. It only work for one box which is the "Caseid" box.

Private Sub Retreive_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Retreive.Click
            Dim table As New DataTable
            Dim command As String
            Dim recordCount As Integer

            'set up an SQL query
           command = "select * from tbl_CallAudit where " _
            & "Case_id = " & " '" & CASEID.Text & "'"

            OleDbDataAdapter1.SelectCommand.CommandText = command
            Label4.Text = command

            'do the query
            recordCount = OleDbDataAdapter1.Fill(table)

            'display the result(s)
            If recordCount <> 0 Then
                ComboBox1.Text = CStr(table.Rows(0)(9))
                RichTextBox1.Text = CStr(table.Rows(0)(11))
                RichTextBox3.Text = CStr(table.Rows(0)(12))
                Label4.Text = "record found!"
                Label4.Text = "record not found!"
            End If

        Catch exceptionObject As Exception
            Label4.Text = exceptionObject.Message

        End Try

            DataGrid1.DataSource = table

            'set up an SQL Query
            OleDbDataAdapter1.SelectCommand.CommandText = _
            "select * from tbl_CallAudit where " & "case_id = " & CASEID.Text

            'fill data table with results from query

        Catch exceptionObject As Exception
            Label4.Text = exceptionObject.Message
        End Try

    End Sub

Any help or guidance would be appreciated.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Is it sort of an advance Search page ?
where user enters two or three search condition and you show the result in a grid ?
or is it a typing help to the user?
why are you using the same query twice
>> recordCount = OleDbDataAdapter1.Fill(table)
at the bottom

redouardAuthor Commented:
Sorry about, I was trying sometime and forgot to remove the piece of code
redouardAuthor Commented:
Do you have any idea how to approach this problem. I've been trying on my own and still no luck
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Actually i could not get your problem clearly
You have a page with some texboxes say EmployeeName, EmployeeID, EmployeeDepartment etc.
Now you want that if User writes "Jack" in EmployeeName textbox and hits search you should show him all the jack's in database
and if he enters "Marketing" as well in the EmployeeDepartment textbox, now you should search with both the conditions and show him the Jack in marketing department ?
Is it the case ?
redouardAuthor Commented:
Hello Arif,

That's exactly what I would like to do. Is it possible or am I barking up the wrong tree?

Thanks a million

It is quite common in fact
here is a small sample.......

I have a database in Access where I have an Employee Table with the fields "EmpID", "EmpName", "EmpSalary", "EmpDept"
Now I would allow Users to search based on three fields "EmpID", "EmpName" and "EmpDept"

So I have a Form with Three TextBoxes, One Search Button, and a Grid on which I show the Results
All I need to do is Code the Click Event of the Search Button

    Private Sub CmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdSearch.Click
        Dim Qry As String = BuildQuery()  '<-- Our Function that builds Query for us
        Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\TestApps\DataBase\Employee.mdb"
        Dim DT As New DataTable
        Dim DA As New OleDbDataAdapter(Qry, ConStr)
        Me.DGEmp.DataSource = DT
    End Sub

'Now this is our function of interest
    Private Function BuildQuery() As String
        Dim Qry As String
        Dim ID As String = TxtID.Text & "%" 'As simple as Add wildcards to the text in the Textboxes and Query
        Dim Name As String = TxtName.Text & "%"
        Dim Dept As String = TxtDept.Text & "%"
        Qry = String.Format("Select * from Emp Where EmpID like '{0}' And EmpName Like '{1}' And DeptID Like '{2}'", ID, Name, Dept)
        Return Qry
    End Function

'Now the above function would Serach for "Jackson" if user enters "Jac" in text box but not if he enters "son"
in case you wnat that as well change the first Three lines to this

        Dim ID As String = "%" & TxtID.Text & "%"
        Dim Name As String = "%" & TxtName.Text & "%"
        Dim Dept As String = "%" & TxtDept.Text & "%"

The above function would search for EmpID in the same way as Name, for eg if an Employee has ID "A001", if user enters "A00" it will find "A001"
But with IDs(and some special fields) that's not desirable suppose User enters "A001" fully and searches he would expect only one Employee in search but he would get "A0011" also, so in such special cases we do not want wild card search for that modify the function like this

For ID field remove the "%"
so instead of >> Dim ID As String = "%" & TxtID.Text & "%"
Have  >> Dim ID As String = TxtID.Text
and just after this line Add one more line
 >> If ID="" Then ID="%"

Also you can have all these logics (and more) bundled together within IF ELSE in the same function and based on the User input whether he wants exact ID serach or just Trailing match search etc. build an advanced search page

redouardAuthor Commented:

the rows are only being displayed in the Grid box not the related textboxes. How can I fix that. I'm not too sure what the buttom command do. It would work when I hard coded the values which is not right. What am I missing?

Qry = String.Format("Select * from tbl_CallAudit Where case_id Like '{0}' or urgency Like '{9}' or summary Like '{11}' or Resolution Like '{12}'", Urgency, Summary, Resolution)

Thanks for your help.
If you want to show the values in TextBoxes it means you expect only one Row to be returned by the Query
Or may be you want "Next" "Previous" buttons to move between selected records
If that is the case then
After the line
    Me.DGEmp.DataSource = DT

And have
    TextBox1.Text = DT.Rows(0).Item(0)
    TextBox2.Text = DT.Rows(0).Item(1)
    TextBox3.Text = DT.Rows(0).Item(2)
And so on... for the number of columns you are getting

As for Hard Coding, I did not get what is your concern...
Qry = String.Format("Select * from tbl_CallAudit Where case_id Like '{0}' or urgency Like '{9}' or summary Like '{11}' or Resolution Like '{12}'", Urgency, Summary, Resolution)

This line is fine, except for the {9}, {11} & {12}
You should have the numbers in sequence so the line would be
Qry = String.Format("Select * from tbl_CallAudit Where case_id Like '{0}' or urgency Like '{1}' or summary Like '{2}' or Resolution Like '{3}'",ID, Urgency, Summary, Resolution)

After the Double Quotes finishes ( at ...Like '{3}' ") then you should provide as many variables as the number of placeholders, here you have {0}, {1}, {2} & {3} so totally 4 variables should be provided in sequence, So I think you have missed ID and I have added that in the above modified query.

For eg. if you say
Str=String.Format("Hello {0}, I am {1}", YourName, MyName)
Where variable YourName contains "redouard" and variable MyName contains "arif_eqbal"
it would put the value of YourName at {0} and value of MyName at {1} and the string would be "hello redouard I am arif_eqbal"

redouardAuthor Commented:
Hello Arif_eqbal,

Thank you very much. Your recommendations were perfect! My application is now functional. I just now have to worry about the cosmetics. Look forward to working with you again.

Anthony PerkinsCommented:
>>Your recommendations were perfect! My application is now functional.<<
Than please close the question.  See here from EE Help:

How do I accept a comment?
redouardAuthor Commented:
Hello everyone,

suppose that the datagrid returns multiple hits data and I want to select one row from the datagrid to be display in the textboxes and dropdowns. Is that possible? Basically, I want to work in reverse.
Anthony PerkinsCommented:
I suggest you close this question and start a new thread.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
While I appreciate the points, they were not deseved as I did not contribute anything to this thread.  Please see here:

I accepted the wrong answer. Now what?
Well if you are asking this question on a new thread kindly be a little more elaborative, or may be clarify things here
like what do you mean by >>> returns multiple hits data , is it that the user selects more than one row, and you want to show the last selection in the combo box etc. ??
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.