Solved

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

Posted on 2006-07-12
15
235 Views
Last Modified: 2010-04-23
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
        Try
            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
            table.Clear()
            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!"
            Else
                Label4.Text = "record not found!"
            End If

        Catch exceptionObject As Exception
            Label4.Text = exceptionObject.Message

        End Try

        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
            table.Clear()
            OleDbDataAdapter1.Fill(table)

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

    End Sub

Any help or guidance would be appreciated.

-Reuben
0
Comment
Question by:redouard
  • 6
  • 6
  • 3
15 Comments
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 17096714
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)
and
>>OleDbDataAdapter1.Fill(table)
at the bottom

0
 

Author Comment

by:redouard
ID: 17130348
Sorry about, I was trying sometime and forgot to remove the piece of code
0
 

Author Comment

by:redouard
ID: 17130358
Do you have any idea how to approach this problem. I've been trying on my own and still no luck
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 17136182
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 ?
0
 

Author Comment

by:redouard
ID: 17137731
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

-Reuben
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 17143761
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)
        DA.Fill(DT)
        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

0
 

Author Comment

by:redouard
ID: 17149300
Hello,

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.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 19

Expert Comment

by:arif_eqbal
ID: 17151741
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
     DA.Fill(DT)
Remove
    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"

0
 

Author Comment

by:redouard
ID: 17155451
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.

-reubem
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 17165293
Thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17257346
>>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?
http://www.experts-exchange.com/help.jsp#hi68
0
 

Author Comment

by:redouard
ID: 17263047
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.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 125 total points
ID: 17264397
I suggest you close this question and start a new thread.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17265427
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?
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/help.jsp#hi17
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 17268946
:-)
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. ??
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now