multiple keyword search to display to data grid control


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: multiple keyword search to display to data grid control

  1. #1
    Will Storer Guest

    multiple keyword search to display to data grid control


    I am currently working on a project that involves using a multiple keyword
    search of an access database. The search result is supposed to display to
    a data grid. Right now, the code that i have will:

    1. Accept the mulitiple keywords from a text box
    2. Separate the keywords into an array
    3. search each keyword in with an sql statement through a loop

    When it comes to the output, the data grid only displays the result of the
    last word in the the keyword search text box.

    Here is my code:

    **************************************************************
    Private Sub cmdKeyWordSearch_Click()
    Dim strKeyWord As String
    Dim SplitKeyWord() As String
    Dim x As Integer
    x = 0

    strKeyWord = " " + txtKeyWord.Text
    SplitKeyWord = Split(strKeyWord)

    Do Until x = UBound(SplitKeyWord) + 1

    SplitKeyWord(x) = "%" & SplitKeyWord(x) & "%"
    adoClients.RecordSource = "SELECT * FROM ClientAddresses " & _
    "WHERE Company LIKE" & Chr(34) & SplitKeyWord(x) & Chr(34)
    adoClients.Refresh
    dgrdClients.Visible = True
    x = x + 1
    loop

    End Sub
    **************************************************************
    I need the data grid to display all the addresses where the company has any
    of the keywords in it.

    Thank you in advance for the response :-)



  2. #2
    Colin McGuigan Guest

    Re: multiple keyword search to display to data grid control

    Will Storer wrote in message <38fe0722@news.devx.com>...
    >
    >I am currently working on a project that involves using a multiple keyword
    >search of an access database. The search result is supposed to display to
    >a data grid. Right now, the code that i have will:
    >
    >1. Accept the mulitiple keywords from a text box
    >2. Separate the keywords into an array
    >3. search each keyword in with an sql statement through a loop
    >
    >When it comes to the output, the data grid only displays the result of the
    >last word in the the keyword search text box.
    >
    >Here is my code:
    >
    >**************************************************************
    >Private Sub cmdKeyWordSearch_Click()
    > Dim strKeyWord As String
    > Dim SplitKeyWord() As String
    > Dim x As Integer
    > x = 0
    >
    > strKeyWord = " " + txtKeyWord.Text
    > SplitKeyWord = Split(strKeyWord)
    >
    > Do Until x = UBound(SplitKeyWord) + 1
    >
    > SplitKeyWord(x) = "%" & SplitKeyWord(x) & "%"
    > adoClients.RecordSource = "SELECT * FROM ClientAddresses " & _
    > "WHERE Company LIKE" & Chr(34) & SplitKeyWord(x) & Chr(34)
    > adoClients.Refresh
    > dgrdClients.Visible = True
    > x = x + 1
    > loop
    >
    >End Sub
    >**************************************************************
    >I need the data grid to display all the addresses where the company has any
    >of the keywords in it.
    >
    >Thank you in advance for the response :-)
    >
    >


    What database are you using? If you're using SQL Server, you can do
    something like this by using the 'UNION' keyword:

    <warning: Vaporcode>

    Private Sub cmdKeyWordSearch_Click()
    Dim strKeyWord As String
    Dim strSQL As String
    Dim SplitKeyWord() As String
    Dim x As Integer

    strKeyWord = " " + txtKeyWord.Text
    SplitKeyWord = Split(strKeyWord)

    For x = LBound(SplitKeyWord) To UBound(SplitKeyWord)
    strSQL = strSQL & "SELECT * FROM ClientAddresses " & _
    "WHERE Company LIKE '%" & SplitKeyWord(x) & "%'"
    If x < UBound(SplitKeyWord) Then
    strSQL = strSQL & " UNION "
    End If
    Next x
    adoClients.RecordSource = strSQL
    adoClients.Refresh
    dgrdClients.Visible = True
    End Sub

    What this should do is produce a SQL statement like this:

    SELECT * FROM ClientAddresses WHERE Company LIKE '%foobar%'
    UNION
    SELECT * FROM ClientAddresses WHERE Company LIKE '%barfoo%'
    UNION
    SELECT * FROM ClientAddresses...etc

    However, a more efficient way to do this might be to append OR statements
    onto your SQL, so the command ends up looking like:

    SELECT * FROM ClientAddresses
    WHERE Company LIKE '%foobar%'
    OR Company LIKE '%barfoo%'
    OR Company LIKE...etc

    --
    Colin McGuigan




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center