Member Avatar for rapirap

Hi!

Please help me with my search functionality...searching using textbox and retrieving the data into a listview. here is my code:

openrstJCcaseInfo "select * from JCcaseInfo"
        If Not rstJCcaseInfo.EOF Then
            caseinfo.ListItems.Clear
            Dim m
            With rstJCcaseInfo
                While .EOF = False
                    If LCase(txtsearch.Text) = LCase(rstJCcaseInfo.Fields("CaseTitle").Value) Then
                    Set ls = caseinfo.ListItems.Add(, , rstJCcaseInfo.Fields("CaseStatus").Value)
                        ls.SubItems(1) = rstJCcaseInfo.Fields("CaseVenue").Value
                        ls.SubItems(2) = rstJCcaseInfo.Fields("Place").Value
                        ls.SubItems(3) = rstJCcaseInfo.Fields("CaseTitle").Value
                        ls.SubItems(4) = rstJCcaseInfo.Fields("CaseNo").Value
                        ls.SubItems(5) = rstJCcaseInfo.Fields("Nature_of_the_Case").Value
                        ls.SubItems(6) = rstJCcaseInfo.Fields("HandlingLawyer").Value
                        ls.SubItems(7) = rstJCcaseInfo.Fields("Remark").Value
                        ls.SubItems(8) = rstJCcaseInfo.Fields("Aging").Value
                        ls.SubItems(9) = rstJCcaseInfo.Fields("DateOfLatest_order_decision").Value
                        ls.SubItems(10) = rstJCcaseInfo.Fields("Date_of_COF").Value
                        ls.SubItems(11) = rstJCcaseInfo.Fields("Filing_Cab_No").Value
                        ls.SubItems(12) = rstJCcaseInfo.Fields("Cab_Drawer_No").Value
                    m = 1
                    End If
                    .MoveNext
                Wend
                .Close
            End With
        End If

what i wanted is, all the data will show into the list view, by just typing the first letter of the data/info.
please help me!!!

  • 2 Contributors
  • 5 Replies
  • 304 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Jx_Man

Dani AI

Generated

Good that ’s LIKE suggestion fixed the immediate problem for . For anyone reading later: using LIKE is fine for simple letter/prefix matches, but it’s worth hardening the implementation so the search is safe, fast and predictable.

Use a parameterized command instead of concatenating user text into SQL. That prevents SQL injection and makes it easier to handle wildcards and trimming. Example pattern (VB6 / ADO, generic names):

Dim cmd As ADODB.Command
Dim rs  As ADODB.Recordset
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = conn
cmd.CommandText = "SELECT * FROM YourTable WHERE SearchColumn LIKE @q"
cmd.CommandType = adCmdText
cmd.Parameters.Append cmd.CreateParameter("@q", adVarChar, adParamInput, 200, Trim(txtQuery.Text) & "%")
Set rs = cmd.Execute

For performance: prefer prefix searches (term + "%") when you expect users to type the start of words — that can use an index. Leading wildcards ("%term" or "%term%") force scans and will be slow on larger tables. If you need true substring or language-aware searches, use SQL Server full-text indexing (CONTAINS/FREETEXT) or maintain searchable tokens in an indexed column.

Handle special characters and UX: escape user-supplied percent/underscore characters if you want literal matches (use ESCAPE and replace those chars in the parameter), avoid querying on every keystroke (debounce or require a minimum length), and only select the columns you need rather than SELECT *. Finally, build WHERE clauses dynamically but always via parameters (add conditions only when the related combo/text control actually contains a value). These small changes will make the search reliable, secure and scalable.

Recommended Answers

All 5 Replies

Member Avatar for Jx_Man

Manipulate your sql query to receive input from textbox and put the code in textbox_change() event..

Private Sub Text1_Change()
' your code here
End Sub
Member Avatar for rapirap 
Private Sub txtsearch_Change()
If Combo1 = "" Or Combo2 = "" Then
        MsgBox ("You need to specify Case Venue!"), vbInformation
    Else
    openrstJCcaseInfo "select * from JCcaseInfo"
    If Not rstJCcaseInfo.EOF Then
        caseinfo.ListItems.Clear
        Dim m
        With rstJCcaseInfo
            While .EOF = False
                If LCase(txtsearch.Text) = LCase(rstJCcaseInfo.Fields("CaseTitle").Value) Then
                Set ls = caseinfo.ListItems.Add(, , rstJCcaseInfo.Fields("CaseVenue").Value)
                    ls.SubItems(1) = rstJCcaseInfo.Fields("Place").Value
                    ls.SubItems(2) = rstJCcaseInfo.Fields("CaseTitle").Value
                    ls.SubItems(3) = rstJCcaseInfo.Fields("CaseNo").Value
                    ls.SubItems(4) = rstJCcaseInfo.Fields("Nature_of_the_Case").Value
                    ls.SubItems(5) = rstJCcaseInfo.Fields("HandlingLawyer").Value
                    ls.SubItems(6) = rstJCcaseInfo.Fields("Remark").Value
                    ls.SubItems(7) = rstJCcaseInfo.Fields("Aging").Value
                    ls.SubItems(8) = rstJCcaseInfo.Fields("DateOfLatest_order_decision").Value
                    ls.SubItems(9) = rstJCcaseInfo.Fields("Date_of_COF").Value
                    ls.SubItems(10) = rstJCcaseInfo.Fields("Filing_Cab_No").Value
                    ls.SubItems(11) = rstJCcaseInfo.Fields("Cab_Drawer_No").Value
                m = 1
                End If
                .MoveNext
            Wend
            .Close
        End With
    End If
End If
end sub

this is my full code of my search...i don't know what's lacking with this in which the result is still the same...:-(

Member Avatar for Jx_Man

Like i said before to manipulate your SQL Statment :

openrstJCcaseInfo "select * from JCcaseInfo like " & "'%" & txtsearch.Text & "%'"
commented: Good Point :D +4
commented: :D +2
Member Avatar for rapirap

Thanks Jx Man...it works!!!

you're the MAN!!!:-)

Member Avatar for Jx_Man

You're Welcome, Dude
Then please mark this thread solved

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.