Results 1 to 2 of 2

Thread: help!!

  1. #1
    stelios Guest

    Default help!!

    Im trying to implement a simple search engine..connecting the words inserted with an works when one word inserted...but when two or just wont put the final quote " &#039; " around the last like statement in SQL..something like..&#039;% serachword % ... cant get it where im going wrong..this is the code below..please someone help me!! thanx from advance <BR><BR><BR>SearchText = Request.Form("txtSearchText") <BR>SearchText = Trim(SearchText)<BR>Response.Write SearchText & "<BR>"<BR><BR>tmpSQL = "Like"<BR>Dim Pos<BR>Pos = 1<BR>While Pos &#062; 0<BR> Pos = InStr(1, SearchText, " ")<BR> If Pos = 0 Then<BR> &#039;We have hit the end of the string<BR> tmpSQL = tmpSQL & "&#039;%" & SearchText & "%&#039;"<BR> Else<BR> tmpSQL = tmpSQL & "&#039;%" & Mid(SearchText,1,Pos) & "%&#039; " & DefaultBoolean & " Like " <BR> SearchText = Mid(SearchText,Pos+1,len(SearchText))<BR> End If<BR>Wend

  2. #2
    Join Date
    Dec 1969

    Default Will never work...

    When all is said and done, even if you fixed your mistake with the &#039; mark, it would never work.<BR><BR>You&#039;d end up with a SQL query something on the order of<BR><BR>SELECT * FROM table WHERE field LIKE &#039;%bill%&#039; OR LIKE &#039;%bob%&#039;<BR><BR>Nope. That is *NOT* legal!<BR><BR>You *must* repeat the field name for each LIKE! Thus (note the added "field"):<BR><BR>SELECT * FROM table WHERE field LIKE &#039;%bill%&#039; OR field LIKE &#039;%bob%&#039;<BR><BR>You are also working way too hard on the way you split up the search string into words. Never heard of SPLIT?<BR><BR>You want something like this:<BR><BR>&#060;%<BR>SQL = "SELECT * FROM table "<BR>delimiter = "WHERE"<BR>words = Split( SearchText, " " )<BR>For w = 0 To UBound( words )<BR>&nbsp; &nbsp; SQL = SQL & delimiter & " theFieldName LIKE &#039;%" & words(w) & "%&#039;&nbsp; &nbsp;"<BR>&nbsp; &nbsp; delimiter = DefaultBoolean<BR>Next<BR>&#039; and this is THE most important step:<BR>Response.Write "SQL is " & SQL & "&#060;HR&#062;" & vbNewLine &#039; to debug the code!<BR>...<BR>%&#062;<BR><BR>Incidentally, your use of MID in <BR>&nbsp; &nbsp; SearchText = Mid(SearchText,Pos+1,len(SearchText))<BR>is wrong. Go look at the specs for MID to figure out why.<BR><BR>It *happens* to work in this situation, but I suspect you don&#039;t know why. (Are you perchance used to Java and the substring method on String?)<BR>

Posting Permissions

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