SQL search string - Xtreme Visual Basic Talk

03-06-2002, 11:38 AM

Cinnamon

Junior Contributor

Join Date: Feb 2002

Location: Indianapolis

Posts: 216

SQL search string

Ok... you knew I'd be back with another problem. Now that I'm in the testing phase. The error messages are working and ONE WORD searches are perfect. That brings me to a the problem.

I clicked the SEARCH command button and typed in "abandoned" and got the 10 records I expected. The ENTRY field of the records contains the text "abandoned iron mines of Chester Borough". However... when I click on the SEARCH command button and type in "abandoned mines" I get NO MATCHES. I was hoping to get the same 10 as before.

Scratching my head and looking confused.... what do I do to fix this?

Code:

Private Sub Command6_Click()
Dim sSQL As String
Dim INQUIRE As String
Dim Count As Integer

On Error GoTo ErrLabel:


  INQUIRE = InputBox("Enter search criteria (leave blank for all):")

  sSQL = "SELECT * FROM cards WHERE entry LIKE '%" & INQUIRE & "%'"
  Set rsDB = New ADODB.Recordset
  
  If INQUIRE = "" Then
       rsDB.Open "SELECT * FROM Cards", cnDB, adOpenKeyset, adLockOptimistic, adCmdText
       Else
       rsDB.Open sSQL, cnDB, adOpenKeyset, adLockOptimistic, adCmdText
  End If
  
  Set MSHFG1.DataSource = rsDB
  
  Count = rsDB.RecordCount
  
  rsDB.MoveFirst
       Image1.Picture = LoadPicture(rsDB.Fields("CardPath"))
       Form1.Text1.Text = rsDB.Fields("Unique#")
       Form1.Text2.Text = rsDB.Fields("CardNum")
       Form1.Text3.Text = rsDB.Fields("CardPath")
       Form1.Text4.Text = rsDB.Fields("Entry") & ""
       Form1.Text5.Text = Count
       
       Exit Sub
      
ErrLabel:
    MsgBox ("There are no matches to your inquiry.")

End Sub

Cinnamon

03-06-2002, 12:04 PM

Ales Zigon

Dead dog's ghost

Forum Leader
* Expert *

Join Date: Feb 2001

Location: Celje, Slovenia, Europe

Posts: 2,601

You should split your wildcard criteria in two.

Something like this:

Code:

'go for the original query
If RecordSet .EOF Then 'found no match
'set new criteria
SQL="SELECT...... FROM Cards WHERE Entry LIKE '%abandoned%' OR Entry LIKE '%mines%';"
Requery

__________________
Yes, MSDN comes with VB! Yes, you must have at least 25 post to have an avatar! No, you cant write your OS in VB! and NO, YOU CAN NOT DECOMPILE IT!

I'm sure there are things that are more important than me - I just can't thing of any...

03-06-2002, 12:05 PM

usetheforce2

Senior Contributor

Retired Moderator
* Expert *

Join Date: Jul 2000

Location: Toronto, Ontario, Canada

Posts: 1,410

ah, took me a bit to see it

"abandoned mines" <-- is the string you're searching for which doesn't exit in the:
"abandoned iron mines of Chester Borough" <-- the string you are searching.

you may have to parse the string (abandoned mines) to perform you search

regan

__________________
winsock siteHERE||EliteVBHERE||C++ & VB Markup UtilityHERE

03-06-2002, 12:12 PM

Cinnamon

Junior Contributor

Join Date: Feb 2002

Location: Indianapolis

Posts: 216

parse

Ok.... I'm off digging in my VB manuals to learn how to parse a string!

Cinnamon

03-06-2002, 12:15 PM

Thinker

Iron-Fisted Programmer

Retired Moderator
* Guru *

Join Date: Jul 2001

Location: Fayetteville Arkansas USA

Posts: 18,127

zigona almost has it, but I would change Or to And

Code:

SQL="SELECT...... FROM Cards WHERE Entry LIKE '%abandoned%' And Entry LIKE '%mines%';"

__________________
Posting Guidelines

03-06-2002, 12:15 PM

usetheforce2

Senior Contributor

Retired Moderator
* Expert *

Join Date: Jul 2000

Location: Toronto, Ontario, Canada

Posts: 1,410

hey,

a simple way is to use the Slit function.

dim parse() as string

searchstring = "abandoned mines"

parse = split(SearchString, " ")

output:

parse(0) <-- would be "abandoned"
parse(1) <-- would be "mines"

regan

__________________
winsock siteHERE||EliteVBHERE||C++ & VB Markup UtilityHERE

03-06-2002, 12:21 PM

reboot

Keeper of foo

Retired Moderator
* Guru *

Join Date: Nov 2001

Location: Graceland

Posts: 15,612

Just guessing here but, is the Slit function similar to the Split function?

03-06-2002, 12:25 PM

usetheforce2

Senior Contributor

Retired Moderator
* Expert *

Join Date: Jul 2000

Location: Toronto, Ontario, Canada

Posts: 1,410

LOL

Thanks reboot

__________________
winsock siteHERE||EliteVBHERE||C++ & VB Markup UtilityHERE

03-06-2002, 12:26 PM

Cinnamon

Junior Contributor

Join Date: Feb 2002

Location: Indianapolis

Posts: 216

Hmmm,... thinking

Correct me if I'm wrong....
but my search string is a variable called INQUIRE.

this variable could contain "abandoned mines" or "abandoned chester" or "iron borough" or even "abandoned mines borough"

Would'nt I need to break (parse) these out and then let the search check for any instance of each of them.

Just a thought... I could be way off. Please let me know.

Cinnamon

#10

03-06-2002, 12:35 PM

usetheforce2

Senior Contributor

Retired Moderator
* Expert *

Join Date: Jul 2000

Location: Toronto, Ontario, Canada

Posts: 1,410

hey,

thats no prob if you where looking for:

if INQUIER = "abandoned mines"

"abandoned mines in sudbury"

it would work. But you are searching

"abandoned iron mines of Chester Borough" <-- you would have to search for "abandoned iron minds"

for every additional word you will have to add a Like field %value%

thinkers:

Quote:

SQL="SELECT...... FROM Cards WHERE Entry LIKE '%abandoned%' And Entry LIKE '%mines%';"

will find any record that contains both word (abandoned, mines)

if you wanted to search for three words you'd need:

Code:

SQL="SELECT...... FROM Cards WHERE Entry LIKE '%abandoned%' And Entry LIKE '%mines% And Entry LIKE '%borough%';"

hope that makes sense

Regan

__________________
winsock siteHERE||EliteVBHERE||C++ & VB Markup UtilityHERE

#11

03-06-2002, 02:24 PM

Ales Zigon

Dead dog's ghost

Forum Leader
* Expert *

Join Date: Feb 2001

Location: Celje, Slovenia, Europe

Posts: 2,601

Just to wrap this up. You can use this procedure to parse a string into the SQL statement

Code:

Dim INQUIRE As String, AddInquire As String, Parse() As String, iCount As Integer
INQUIRE = "SELECT * FROM Cards WHERE Entry LIKE '%"
If InStr(1, Text1.Text, Chr$(32)) <> 0 Then
    Parse = Split(Text1.Text, Chr$(32))
        For iCount = 0 To UBound(Parse)
            If iCount < UBound(Parse) Then
                AddInquire = AddInquire & Parse(iCount) & "%' And Entry LIKE '%"
            Else
                AddInquire = AddInquire & Parse(iCount)
            End If
        Next iCount
        INQUIRE = INQUIRE & AddInquire & "%';"
Else
    INQUIRE = INQUIRE & Text1.Text & "%';"
End If

#12

03-06-2002, 02:33 PM

mhsueh001

Guest

Posts: n/a

String length question.

I broght this up in another thread, but didn't get a response.
I had code similar to what zigona has.

What happens when AddInquire exceeds a 255 character string length?

If I'm doing a long search and you build your where string to a point where it exceeds 255 characters it will generate a syntax error.

I ended up using multiple arrays of strings which was not pretty.

Are arrays of strings the only way to get around the 255 character string limitation?

#13

03-06-2002, 02:37 PM

Cinnamon

Junior Contributor

Join Date: Feb 2002

Location: Indianapolis

Posts: 216

thanks guys...

I'll give all this a try first thing tomorrow morning!

Cinnamon

#14

03-06-2002, 02:58 PM

Ales Zigon

Dead dog's ghost

Forum Leader
* Expert *

Join Date: Feb 2001

Location: Celje, Slovenia, Europe

Posts: 2,601

Re: String length question.

Quote:

Originally posted by mhsueh001
What happens when AddInquire exceeds a 255 character string length?

If I'm doing a long search and you build your where string to a point where it exceeds 255 characters it will generate a syntax error.

Nothing. There will be no error.
You can have string as long as 2 billion characters [2^31] (var lenght string).

#15

03-07-2002, 08:46 AM

Cinnamon

Junior Contributor

Join Date: Feb 2002

Location: Indianapolis

Posts: 216

Thanks!

Zigona the code works great. I modified it a bit to allow user input . Thanks again!

Cinnamon