Member Avatar for matoo

I am trying to write program in vb6.0, in which i am using ms access database. i want to search in a table called "customer". the Field is called "Name". in the feild i have saved full name e.g Asmat Ullah Khan, Asmat Khan, Asmat Ullah. now what i want to do is search only Asmat and it should go through whole table and display all the names in listview i.e Asmat Ullah Khan, Asmat Ullah, Asmat Khan. how can i search to get this result.Thank you

  • 2 Contributors
  • 3 Replies
  • 361 Views
  • 15 Hours Discussion Span
  • Latest Post Latest Post by matoo

Dani AI

Generated

Nice catch by — the missing wildcard was the real issue. Below are succinct, practical points and a couple of safe examples to avoid the same pitfall and make the search robust.

Access wildcard rules and why % sometimes works (or not)

  • Access has two wildcard syntaxes: the traditional Access/JET set (ANSI‑89) uses * and ?, while ANSI‑92 (used by some providers or when the database is set to SQL Server–compatible syntax) uses % and _. Mixing them can make your pattern be treated literally. See Microsoft’s notes on Access wildcards and the ANSI modes: Use wildcards in queries and comparison of Access SQL and ANSI SQL.

A safer ADO pattern (parameterized, avoids concatenation)

  • Use an ADODB.Command and a parameter so you can append the wildcard to the parameter value (prevents injection and quoting headaches). Example pattern (adjust wildcard char to your ANSI mode):
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = gobjConn
cmd.CommandText = "SELECT ID,CName FROM Customer WHERE CName LIKE ?"
cmd.Parameters.Append cmd.CreateParameter(, adVarWChar, adParamInput, 255, SearchText & "%")
Set rs = cmd.Execute

(See Microsoft’s example of CreateParameter and Append for VB: Append and CreateParameter methods example (VB).)

Whole‑word matching and quick tips

  • To match a whole word (so "Asmat" matches but "Asmatullah" does not), search against a space-padded field: e.g. WHERE ' ' & CName & ' ' LIKE '* ' & ? & ' *' (use * or % per your ANSI mode).
  • Avoid a leading wildcard on large tables (LIKE '%foo') because it prevents index seeks and forces scans — this will slow queries; see SQL Server seek predicate guidance: .

Troubleshooting checklist

  • Verify gobjConn is open before executing.
  • Trim the input and handle Nulls (use Nz/COALESCE or test for Null).
  • Run the SQL in Access query designer to confirm behavior and check the database’s ANSI‑92 setting if % behaves oddly. (See Access wildcard docs above.)

If more detail is wanted (exact parameter code for your VB6 environment or a whole-word regex filter after fetching rows), include the connection/provider string and ANSI setting and a short sample of how you open gobjConn.

Recommended Answers

All 3 Replies

Member Avatar for jhai_salvador

This question has been asked and answered several times already. if you try to use the search bar on this forum, you find the answer to your question. you can also use GOOGLE to find answer to your question.

here are some links that could help you.

https://www.daniweb.com/software-development/visual-basic-4-5-6/threads/29709/search-code-in-a-database

https://www.daniweb.com/software-development/visual-basic-4-5-6/threads/334183/how-to-create-a-search-command-in-vb6

https://www.daniweb.com/software-development/visual-basic-4-5-6/threads/445719/how-to-search-database-and-display-matching-record-in-vb6-

Member Avatar for matoo

i tried the links you send but my problem is still there let me tell little more what i want and what i am getting
there is table called "Customer" fiels called "CName"
now CName is Full name
eg
Asmat
Asmat Ullah
Asmat Ullah Khan
Asmat Khan
asmat
asad
ali

now when i load the code which is

Private Sub CmdCSearch_Click()
    If TxtCSearch.Text = "" Then
        Dim info As String
        If Option1.Value = True Then
            info = "Customer ID"
        Else
            info = "Customer Name"
        End If
        MsgBox "First Enter " & info & "."
        TxtCSearch.SetFocus
    Else
        '###############################################
        Dim rsCSearch As ADODB.Recordset
        Set rsCSearch = New ADODB.Recordset
        'rsCSearch.Open "Customer", gobjConn, adOpenDynamic, adLockPessimistic
        If Option1.Value = True Then
            rsCSearch.Filter = "ID='" & TxtCSearch.Text & "'"
        Else
            'rsCSearch.Filter = "CName='" & Trim(TxtCSearch.Text) & "%'"
            'SQL = "Select * FROM Customer WHERE CName like '" & Trim(TxtCSearch.Text) & "%'"
            rsCSearch.Open "SELECT * FROM Customer WHERE CName LIKE " & "'" & TxtCSearch.Text & "'", gobjConn, adOpenStatic, adLockOptimistic
        End If
        ListView2.ListItems.Clear
        Do Until rsCSearch.EOF = True
            Dim CustomerListObj As ListItem
            Set CustomerListObj = ListView2.ListItems.add(, , rsCSearch!ID)
            CustomerListObj.SubItems(1) = rsCSearch!CName
            rsCSearch.MoveNext
        Loop
    End If
End Sub

my DB connection is connected when my main page loads
and DB info is in modules
gobjConn is the connection name

now when i enter asmat in TxtCSearch (it is the textbox) i only get two results Asmat and asmat

i want to search like if i put "a" in textbox i should get all starting starting with "a"
if i put "as" then all result starting with "as" and if i put "asmat"
then i should get all asmat, asmat ullah, asmat ullah khan, asmat khan, and asmat
but i only get
asmat, Asmat 2 results

rsCSearch.Filter = "CName='" & Trim(TxtCSearch.Text) & "%'" 
'no result is shown
rsCSearch.Filter = "CName='" & Trim(TxtCSearch.Text) & "'"
only asmat, Asmat are shown
Edited by matoo
Member Avatar for matoo 
rsCSearch.Open "SELECT * FROM Customer WHERE CName LIKE " & "'" & TxtCSearch.Text & "%'", gobjConn, adOpenStatic, adLockOptimistic

got it working % was missing thanks for your time

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.