dcsimg
Results 1 to 19 of 19

Thread: Search All Columns in an SQL database quickly?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2005
    Posts
    327

    Search All Columns in an SQL database quickly?

    Hey guys. I am moving my data from a local MDB to a server on the internet.

    I want to find a text string but I don't know what column it is in. So maybe I'm searching an address or a phone number or a name. I don't know that in advance.

    When searching a local DB the search was incredibly fast but on the web it's incredibly slow.

    This works but it's stinkin slow. Any better approach?

    Code:
       Sch$ = "555-1212" ' set this value to what we want to find
         
       MyCustomersSQL.Open "Select TOP 500 * from dbo.Cust ", SQLcon, adOpenKeyset, adLockBatchOptimistic, adCmdText
       
       Match = False
    
       While Not MyCustomersSQL.EOF
          CustID = MyCustomersSQL("CustID")
          If InStr(UCase(MyCustomersSQL("CustID")), Sch$) > 0 Then
             Match = True: GoTo MyMatch
          End If
          If InStr(UCase(MyCustomersSQL("Name")), Sch$) > 0 Then
             Match = True: GoTo MyMatch
          End If
          If InStr(UCase(MyCustomersSQL("RENEWAL")), Sch$) > 0 Then
             Match = True: GoTo MyMatch
          End If
          If InStr(UCase(MyCustomersSQL("ADDRESS1")), Sch$) > 0 Then
             Match = True: GoTo MyMatch
          End If
          If InStr(UCase(MyCustomersSQL("ADDRESS2")), Sch$) > 0 Then
             Match = True: GoTo MyMatch
          End If
          If InStr(UCase(MyCustomersSQL("CITY")), Sch$) > 0 Then
             Match = True: GoTo MyMatch
          End If
          If InStr(UCase(MyCustomersSQL("POSTAL")), Sch$) > 0 Then
             Match = True: GoTo MyMatch
          End If
          If InStr(UCase(MyCustomersSQL("PHONE1")), Sch$) > 0 Then
             Match = True: GoTo MyMatch
          End If
          If InStr(UCase(MyCustomersSQL("PHONE2")), Sch$) > 0 Then
             Match = True: GoTo MyMatch
          End If
          If InStr(UCase(MyCustomersSQL("NOTE1")), Sch$) > 0 Then
             Match = True: GoTo MyMatch
          End If
          If InStr(UCase(MyCustomersSQL("NOTE2")), Sch$) > 0 Then
             Match = True: GoTo MyMatch
          End If
          If InStr(UCase(MyCustomersSQL("EMAIL")), Sch$) > 0 Then
             Match = True : Goto MyMatch
          End If
          If InStr(UCase(MyCustomersSQL("LastInvoice")), Sch$) > 0 Then
             Match = True: GoTo MyMatch
          End If
          
    MyMatch:
          If Match Then
              MsgBox "We have a match"
          End If
    
          MyCustomersSQL.MoveNext
       Wend
    
       MsgBox "Sorry no match"
    Last edited by Darkbob; Feb 12th, 2019 at 11:53 AM.

  2. #2
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Search All Columns in an SQL database quickly?

    standard practice is to let the server do the heavy lifting using SQL.

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,293

    Re: Search All Columns in an SQL database quickly?

    I don't get it ... this isn't the first time I've seen this kind of request. How do you NOT know what you are searching for? Even wen you're doing keyword searches that could be spread out over multiple fields, there's some limits to it... But based on what you have listed in post 1, that's sheer madness.
    Also, I'd do this in SQL and let the database handle it. Getting everything back like that, looping through it, doing a field by field comparison like that isn't very efficient.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2005
    Posts
    327

    Re: Search All Columns in an SQL database quickly?

    Quote Originally Posted by techgnome View Post
    I don't get it ... this isn't the first time I've seen this kind of request. How do you NOT know what you are searching for? Even wen you're doing keyword searches that could be spread out over multiple fields, there's some limits to it... But based on what you have listed in post 1, that's sheer madness.
    Also, I'd do this in SQL and let the database handle it. Getting everything back like that, looping through it, doing a field by field comparison like that isn't very efficient.

    -tg

    First off, thanks for taking the time to reply.

    I realize the code above is not efficient. If it was efficient I wouldn't have asked for a better method.

    But as for the Why... I want to be able to search the entire table for data. I don't know in advance which field the data is located in or what I'm searching for.

    So for example, a customer calls in and I see his phone number on caller ID and I want to pull up my notes before I call him back. Or I have the customer ID from an invoice on a fax but I can't make out the name. Or I know the name of the customer but not any other info.

    So I need to be able to search all fields for a match without knowing which field I'm searching or what I'm searching it for.

    And as to the HOW... that's again back to my post. It's fast and simple with a local DB but how do I do it with an on-line SQL server?

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2005
    Posts
    327

    Re: Search All Columns in an SQL database quickly?

    Quote Originally Posted by DEXWERX View Post
    standard practice is to let the server do the heavy lifting using SQL.
    I had assumed the server would have an option to search the entire table for a string. But the question is how?

  6. #6
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    18,218

    Re: Search All Columns in an SQL database quickly?

    Quote Originally Posted by Darkbob View Post
    I had assumed the server would have an option to search the entire table for a string. But the question is how?
    Misunderstanding I think. I believe what they are saying is that you create a WHERE clause in the SQL statement you are passing to your database/server. Then, only the records matching are returned vs. returning 100% of the records and looping through them one at a time.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  7. #7
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,894

    Re: Search All Columns in an SQL database quickly?

    Looks like you would need to use multiple like statements in a where clause.

    Code:
    "select fieldlist from dbo.cust where CustID=" & thevalue &" or Name like '%" & thevalue &"%' or PHONE1 like '%" & thevalue  &"%'"
    and so on

    This assumes that CustID is a numeric field and the others are all text fields. I do not think you would want a partial match on customerid. If the user entered 1 then you may get 1 10-19 21 31 and so on. Like only works on text fields, not numeric fields so not an option on those.

  8. #8
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,082

    Re: Search All Columns in an SQL database quickly?

    You'd be better off concatentating all the fields you want to check, I'd imagine:

    SELECT * FROM MyTable WHERE ColA + ' ' + ColB + ' ' + ColC LIKE '%my search term%'
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2005
    Posts
    327

    Re: Search All Columns in an SQL database quickly?

    Quote Originally Posted by DataMiser View Post
    Looks like you would need to use multiple like statements in a where clause.

    Code:
    "select fieldlist from dbo.cust where CustID=" & thevalue &" or Name like '%" & thevalue &"%' or PHONE1 like '%" & thevalue  &"%'"
    and so on

    This assumes that CustID is a numeric field and the others are all text fields. I do not think you would want a partial match on customerid. If the user entered 1 then you may get 1 10-19 21 31 and so on. Like only works on text fields, not numeric fields so not an option on those.
    Thanks very much! I think I can narrow down the number of fields to common ones like name, address, customer ID and phone numbers.

    So just to be clear I would replace fieldlist with * if I want to return all the fields in each matching record? I would end up with a more or less standard recordset that might include 2 or 3 rows, right?
    Last edited by Darkbob; Feb 12th, 2019 at 01:27 PM.

  10. #10
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,293

    Re: Search All Columns in an SQL database quickly?

    Quote Originally Posted by Darkbob View Post
    First off, thanks for taking the time to reply.

    I realize the code above is not efficient. If it was efficient I wouldn't have asked for a better method.

    But as for the Why... I want to be able to search the entire table for data. I don't know in advance which field the data is located in or what I'm searching for.

    So for example, a customer calls in and I see his phone number on caller ID and I want to pull up my notes before I call him back. Or I have the customer ID from an invoice on a fax but I can't make out the name. Or I know the name of the customer but not any other info.

    So I need to be able to search all fields for a match without knowing which field I'm searching or what I'm searching it for.

    And as to the HOW... that's again back to my post. It's fast and simple with a local DB but how do I do it with an on-line SQL server?
    That's kind of what I thought... you're trying to build a one-size fits all sledge hammer to find your data... but think about it for just a moment... if you were doing this by hand... if let's say instead of a table of data you had a box of papers that had all this info written down, and you had an invoice number... would you go through each piece of paper looking at names, dates, phone numbers, comments? No... you'd look straight at the invoice number, because that's what you have. You KNOW you have that. And if you had a phone number, you don't bother looking at the invoice number field, or the name fields, right? No, you immediately look at the phone field on the paper.... that's kind of what I'm talking about... Now... yes, there's some times when something could be in more than one spot, I'll grant you that - a phone number could also be in comments... but it's not going to be in the name field (at least I HOPE NOT!) ... My guess is that you've got a search field that's got a single text box where you can enter *** you want and you're looking for a sledgehammer approach to find what ever was typed in. Personally I'd suggest something a bit more robust... let's say you want to search on Name, Phone number, or Invoice ID ... create a form with three textboxes... give them appropriate labels... one for Name, Phone, and one for Invoice.

    your SQL would look something like this
    Code:
    Select TOP 500 * from dbo.Cust where (Name like ('%' + ? + '%') or (? = '')) or (Phone like ('%' + ? + '%') or (? = '')) or (InvoiceID like ('%' + ? + '%') or (? = ''))
    Bleh... I wish Access used named parameters, it would make it so much easier to work with. -- because Access uses ordinal positioning of parameters, you'd need to add each parameter twice to get it into each spot where it belongs... first to get it into the LIKE clause, and a second time to get it into the empty string check.


    In short it's a parameterized query that will return where the name is a like match or the parameter is empty.

    Now, when you have a phone number, you enter the phone number and it will look in the phone field for it. This is just one way... there are other ways... but this is in a nutshell how it should be done. 1) let the database do the work 2) apply some logic to it so that you're not scanning every field of every row when you know there's some limits on the data you're searching for. A phone number isn't going to be in the name field... it should be in the Phone field... or in the Mobile, Fax, Home, Work fields... or in Comments... but it still isn't going to be PostCode field... if it is, then you have bigger problems than being able to search your data.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  11. #11
    PowerPoster
    Join Date
    Feb 2006
    Posts
    20,230

    Re: Search All Columns in an SQL database quickly?

    Quote Originally Posted by techgnome View Post
    Bleh... I wish Access used named parameters, it would make it so much easier to work with. -- because Access uses ordinal positioning of parameters, ...
    "Access" (you really mean Jet/ACE, MS Access doesn't even come into the picture) has no problem using named parameters since Jet 4.0 came on the scene and probably even earlier. The shortcut "?" syntax is just for simpler queries.

    Example from the docs:

    Code:
    PARAMETERS [Low price] Currency, [Beginning date] DateTime;
    SELECT OrderID, OrderAmount FROM Orders
    WHERE OrderAmount > [Low price] AND OrderDate >= [Beginning date]

  12. #12
    Fanatic Member
    Join Date
    Nov 2017
    Posts
    748

    Re: Search All Columns in an SQL database quickly?

    Quote Originally Posted by Darkbob View Post
    But as for the Why... I want to be able to search the entire table for data. I don't know in advance which field the data is located in or what I'm searching for.

    So for example, a customer calls in and I see his phone number on caller ID and I want to pull up my notes before I call him back. Or I have the customer ID from an invoice on a fax but I can't make out the name. Or I know the name of the customer but not any other info.

    So I need to be able to search all fields for a match without knowing which field I'm searching or what I'm searching it for.
    Your justification for doing it in this way contains the proof that you don't need to be doing it in this way.

    If you see the phone number in caller ID, then you obviously know you are looking up the phone number and not their name or their invoice number. If you have the Customer ID then you obviously know you are looking up the Customer ID and not their phone number or their name. If you know the name of the customer, then you obviously know you are looking up the customer's name, and not their phone number or their Customer ID.

    So it sounds like all you need is something in the UI like a ListBox or a ComboBox that lets you choose what field you are searching against and then use that selection to build the query.

    You could still include an option to search all fields in the case (the circumstances of which it would be necessary I can't imagine, TBH) where you need to do so. But that should be few and far between, and limiting the search to just the field that you know the data comes from should help performance.
    Last edited by OptionBase1; Feb 12th, 2019 at 06:13 PM.

  13. #13
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    18,218

    Re: Search All Columns in an SQL database quickly?

    Quote Originally Posted by Darkbob View Post
    So just to be clear I would replace fieldlist with * if I want to return all the fields in each matching record? I would end up with a more or less standard recordset that might include 2 or 3 rows, right?
    You shouldn't return fields you won't be using, but yes, fieldlist is a placeholder for the fields you want returned in the query.
    Your other question is also yes

    Now, we all would like some magic, easy, search where someone types characters in a textbox and clicks "Find" to search the entire table. Your initial attempt I think is a common one-size-fits-all attempt. But typically those types of attempts are not optimal as you're finding out, especially when every field in the table is not text-based. As others pointed out, it is often better to let the user decide which fields they want to search. They are the ones that ultimately populate the table, so they should be informed enough to know/guesstimate which fields to search.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  14. #14
    Frenzied Member
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    1,279

    Re: Search All Columns in an SQL database quickly?

    Quote Originally Posted by LaVolpe View Post
    Now, we all would like some magic, easy, search where someone types characters in a textbox and clicks "Find" to search the entire table.
    Kinda like Google, right?

    Thing is, you might not do anything as "magic" as Google does, but you can get close enough with some massaging of the input. In my app I have a "magic" search box where you can type a word (or words/numbers/combination thereof) and the program then tries to figure out the best results.

    For example, if you type something that is only numbers (or only numbers and special characters like spaces, dashes, dots, etc...) then I do something like this:

    • Search all the most common fields for exact matches. If any are found they get the highest result score.
    • Strip all the special characters and search all the common numeric-only fields for an exact match. If any are found they get a high result-1 score.
    • Search all the most common fields for an internal/wildcard exact match. If any are found they get a high result-2 score.
    • I might have other tests depending on the criteria, but they will get lower result score.
    • Return a recordset of results.

    I do something similar with text searches, but throw in a metaphone calculation to find similar sounding English words and score the results according to what I think the user is looking for based on the search criteria.

    With a bit of smart interpretation of a few entered characters you can provide surprisingly "magic" results....not Google level magic, but often good enough.

    That said, I agree with Dex that it is ideal to have a server do this heavy lifting - you pass a single string from the client and then cast some spells on the server to generate a single call result set which is then simply displayed to the user on the receiving end.
    Last edited by jpbro; Feb 12th, 2019 at 10:24 PM.

  15. #15
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,921

    Re: Search All Columns in an SQL database quickly?

    Hi,

    see if this can Help, I highlighted the relevant parts in blue for searching multiple Fields

    Code:
    Option Explicit
    Private cSQL As clsAccessSQL     'Class für SQL
    
    Private mcolDisplay As Collection
    
    Private Sub Class_Initialize()
      Set mcolDisplay = New Collection
    End Sub
    
    Public Function Count() As Long
      Count = mcolDisplay.Count
    End Function
    
    Public Function NewEnum() As IUnknown
      Set NewEnum = mcolDisplay.[_NewEnum]
    End Function
    
    Public Function item(ByVal Index As Variant) As clsKundeDisplay
      Set item = mcolDisplay(Index)
    End Function
    
    Public Sub Load(Optional KD_MANR As String, _
                        Optional KD_Firma As String, _
                        Optional KD_PLZ As String, _
                        Optional KD_ORT As String)
      Fetch KD_MANR, KD_Firma, KD_PLZ, KD_ORT
    End Sub
    
    Private Sub Fetch(KD_MANR As String, _
                        KD_Firma As String, _
                        KD_PLZ As String, KD_ORT As String)
      
      Dim rsKunde As Recordset
      Dim strSQL As String
      Dim strwhere As String
      Dim strWHERE2 As String
      Dim strOrderby As String
      Dim objDisplay As clsKundeDisplay
      
       Set cSQL = New clsAccessSQL
    
    
    'nach KD_MANR filtern
          If Len(KD_MANR) > 0 Then
             If Right(KD_MANR, 1) <> "%" Then
                KD_MANR = KD_MANR & "%"
                strwhere = " And (KD_MANR Like " & cSQL.strStr(KD_MANR) & ") "
                strOrderby = " KD_MANR, KD_Firma, KD_PLZ, KD_ORT"
             End If
          End If
    
     'nach PLZ filtern
          If Len(KD_PLZ) > 0 Then
             If Right(KD_PLZ, 1) <> "%" Then
                KD_PLZ = KD_PLZ & "%"
                strwhere = strwhere & " And (KD_PLZ Like " & cSQL.strStr(KD_PLZ) & ") "
                If Len(strOrderby) = 0 Then
                   strOrderby = " KD_PLZ, KD_MANR, KD_Firma, KD_ORT "
                End If
             End If
          End If
    'nach Firma filtern
     If Len(KD_Firma) > 0 Then
             If Right(KD_Firma, 1) <> "%" Then
                KD_Firma = KD_Firma & "%"
                strwhere = strwhere & " And (KD_Firma Like " & cSQL.strStr(KD_Firma) & ") "
                If Len(strOrderby) = 0 Then
                   strOrderby = " KD_Firma, KD_MANR,KD_PLZ , KD_ORT "
                End If
             End If
          End If
    'nach Ort filtern
     If Len(KD_ORT) > 0 Then
             If Right(KD_ORT, 1) <> "%" Then
                KD_ORT = KD_ORT & "%"
                strwhere = strwhere & " And (KD_ORT Like " & cSQL.strStr(KD_ORT) & ") "
                If Len(strOrderby) = 0 Then
                   strOrderby = " KD_ORT, KD_MANR, KD_PLZ, KD_Firma "
                End If
             End If
          End If
    
    If Len(strwhere) > 0 Then
             strwhere = Replace(strwhere, "And", "Where", 1, 1)
    Else
            strOrderby = "KD_MANR, KD_Firma, KD_PLZ, KD_ORT"
          End If
         
      strSQL = "SELECT KD_LfdNr, KD_MANR, KD_Firma, KD_PLZ, KD_ORT, " & _
        " KD_Zusatz1,KD_Land FROM tbl_Kunden " & strwhere & " Order By " & strOrderby
    
      Set rsKunde = New Recordset
      rsKunde.Open strSQL, Cn
      Do While Not rsKunde.EOF
        Set objDisplay = New clsKundeDisplay
        With objDisplay
          .KD_LfdNr = rsKunde("KD_LfdNr")
          .KD_MANR = rsKunde("KD_MANR") & vbNullString
          .KD_Firma = rsKunde("KD_Firma") & vbNullString
          .KD_Zusatz1 = rsKunde("KD_Zusatz1") & vbNullString
          .KD_ORT = rsKunde("KD_ORT") & vbNullString
          .KD_PLZ = rsKunde("KD_PLZ") & vbNullString
          .KD_Land = rsKunde("KD_Land") & vbNullString
          
          mcolDisplay.Add objDisplay
          Set objDisplay = Nothing
          rsKunde.MoveNext
        End With
      Loop
      rsKunde.Close
      Set rsKunde = Nothing
      Set cSQL = Nothing
    End Sub
    HTH
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  16. #16
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,279

    Re: Search All Columns in an SQL database quickly?

    Quote Originally Posted by jpbro View Post
    That said, I agree with Dex that it is ideal to have a server do this heavy lifting...
    Yep - and since the OP is using SQLServer apparently, the tool of choice is (usually), its built-in Full-Text-Search.
    which allows:
    - the definition of a "bunch of source-fields which can have different types" (from one or more of your original DB-tables)
    - under a "named umbrella" (a fulltext-index, which itself is hosted in a "fulltext-catalog")
    - which then (after population) - allow high-performance, google-like search-requests.

    It requires some studying, to set it up correctly...
    https://docs.microsoft.com/en-us/sql...ql-server-2017
    https://docs.microsoft.com/en-us/sql...ql-server-2017
    https://docs.microsoft.com/en-us/sql...ql-server-2017

    But after that hurdle is taken, the performance should be *much* better, than what's achievable with a "homegrown-approach".

    To not be misunderstood -
    there's nothing wrong with "concatenating Columns" and using the Like-Operator on that concat, as long as the performance is bearable...

    Olaf

  17. #17
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,306

    Re: Search All Columns in an SQL database quickly?

    Using a leading wildcard search (Like '%' + something + '%') is never going to be performant on a system of any size. You will always do a table scan (every row of the table start to finish). If you has a table with 10Million rows is that what you want to do?
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  18. #18

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2005
    Posts
    327

    Re: Search All Columns in an SQL database quickly?

    Hey guys... finally got around to finishing this. I'm sure this is all super basic easy stuff for you but I'm brand new to SLQ. Maybe this will help another Noob.

    Here's my multi-column search. Nice and fast. Better yet, I've broken out each component and then added them back together so you can see what's happening easier. I could have done it all one one line but for me that gets a bit confusing. The placement of the % symbol and the & symbol and the " and the ' symbol are all critical.

    Code:
       Dim MyCustomersSQL As New ADODB.Recordset
       Dim MySearch$,Sch$, S1$,S2$,S3$,S4$, S5$, S6$,S7$,S8$,S9$,S10$
       Sch$ = "GREEN"
    
       S1$ = "Select * from dbo.Cust WHERE " ' You will need to customize this for your own table name
    
       S2$ = "CustID LIKE '%" & Sch$ & "%' "     
       S3$ = "Name LIKE '%" & Sch$ & "%' "       
       S4$ = "RENEWAL LIKE '%" & Sch$ & "%' "
       S5$ = "ADDRESS1 LIKE '%" & Sch$ & "%' "
       S6$ = "ADDRESS2 LIKE '%" & Sch$ & "%' "
       S7$ = "CITY LIKE '%" & Sch$ & "%' "
       S8$ = "PHONE1 LIKE '%" & Sch$ & "%' "
       S9$ = "PHONE2 LIKE '%" & Sch$ & "%' "
       S10$ = "LastInvoice LIKE '%" & Sch$ & "%' "
       
       MySearch$ = S1$ & S2$ & " or " & S3$ & " or " & S4$ & " or " & S5$ & " or " & S6$ & " or " & S7$ & " or " & S8$ & " or " & S9$ & " or " & S10$
          
    
       ' This sets up the recordset MyCustomersSQL.  From here you can treat it like any normal recordset.
    
       MyCustomersSQL.Open MySearch$, SQLcon, adOpenKeyset, adLockBatchOptimistic, adCmdText

    Obviously you will want an input box or something like that to fill in the value of Sch$ but this is just for demo purposes. You will also have to figure out your own SQLcon connection string with your database name and password and table and whatnot.

    The value of MySearch$ becomes:

    Select * from dbo.Cust WHERE CustID LIKE '%GREEN%' or Name LIKE '%GREEN%' or RENEWAL LIKE '%GREEN%' or ADDRESS1 LIKE '%GREEN%' or ADDRESS2 LIKE '%GREEN%' or CITY LIKE '%GREEN%' or PHONE1 LIKE '%GREEN%' or PHONE2 LIKE '%GREEN%' or LastInvoice LIKE '%GREEN%'
    Last edited by Darkbob; Feb 23rd, 2019 at 11:55 AM.

  19. #19
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    18,218

    Re: Search All Columns in an SQL database quickly?

    Do note that if sch$ contains a tick (apostrophe), your code will blow up

    Ensure you trap for ticks & convert them to double ticks (two apostrophes), i.e., O'Hare becomes O''Hare

    Something like this simple example?
    Code:
        sch$ = InputBox("Criteria")
        If sch$ <> vbNullString Then
            S1$ = "Select * from dbo.Cust WHERE (" & _
                        "CustID % Or Name % Or RENEWAL % Or " & _
                        "ADDRESS1 % Or ADDRESS2 % Or CITY % Or " & _
                        "PHONE1 % Or PHONE2 % Or LastInvoice %)"
    
            If InStr(sch$, "'") Then sch$ = Replace(sch$, "'", "''")
            mySearch$ = Replace(S1$, "%", "Like '%" & sch$ & "%'")
        End If
    Edited: Though using a Command object for parameterized query negates the need for manually dealing with apostrophes. Dilletante and techgnome touched on parameterized queries earlier in the thread
    Last edited by LaVolpe; Feb 23rd, 2019 at 01:55 PM.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width