search fields in a table


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 12 of 12

Thread: search fields in a table

  1. #1
    Join Date
    Nov 2006
    Posts
    9

    Unhappy search fields in a table

    hello!i got a problem with my codes.iwant to serach and display some fields from my table. but i got an error ,i cant fix it. if someone can help!!!
    here is the codes:
    Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
    
    <!--#include file="connection/connect.asp" -->
    
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <title>search by keyword</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>
    <body>
    
    <% 
    If request.querystring<>""  then 
    If len(request.querystring("q"))>=5 then 
    
    ' This variables are needed to work with the database
    
    DIM recordset
    DIM StrOpen
    DIM StrOpenInContruction ' This variable is used to create variable "StrOpen"
    
    
    ' The name of the table in our sql database. In our case this name is "followup" 
    DIM NameOfTableInDB            
    NameOfTableInDB="followup"
    
    ' The name of each colunm in the table is contained in the array "ColunmNameIntable". 
    ' The table in my database provided in this codes has up to 7 colunms 
    ' As in this script we will not use all of them, only the ones we will use are included in this array
    
    
    DIM ColunmNameIntable(5) 
    
     ColunmNameIntable(0)="patient_result"
     ColunmNameIntable(1)="patient_med"
     ColunmNameIntable(2)="patient_status"
     ColunmNameIntable(3)="patient_id"
     ColunmNameIntable(4)="vct_id"
    
    ' When displaying data we will use this values 
    DIM DataName(5) 
    
     DataName(0)="patient result"
     DataName(1)="patient medication"
     DataName(2)="patient actual status"
     DataName(3)="patient_id"
     DataName(3)="vct_id"
    
    
    
    ' Keywords to search will be saved to variable "TheWords"
    DIM TheWords    
    TheWords = request.querystring("q")
    
    ' Mode will be saved to this variable. Value will be "OR" or "AND"  
    DIM TheMode        
    TheMode = request.querystring("mode")
    
    
    ' When TheWords contains more than one keyword, 
    ' keywords will be save to array "TheWordsArray"
    DIM TheWordsArray 
    
    ' If more than one keywords are introduced, then we will set up MorethanOneKeywords value to "yes"
    DIM MorethanOneKeywords 
    
    ' Will be used to calculate maximum number of keywords
    DIM Wmax 
    
     ' Just counters
    DIM i,j                               
    
    If instr(1,request.querystring("q")," ",1) >0 then
       MorethanOneKeywords="yes"
       TheWordsArray=Split(TheWords," ")
       Wmax=ubound(TheWordsArray)
    	For i=0 to Wmax
    		if len(TheWordsArray(i))<3 then
          Response.write ("Search Term <b>" & TheWordsArray(i) & "</b> is very sort, so it has not been used<HR>")
    			TheWordsArray(i)=""
        end if
      next
    end if 
    
    
    ' First we will get "StrOpen", wich is later use to filter the database
    
    If MorethanOneKeywords="yes" then
    
       if TheMode="OR" then
          For i=0 to Wmax
    		     if TheWordsArray(i)<>"" then
    		       For j=1 to 5
                  StrOpen= StrOpen & " OR " & ColunmNameIntable(j) & " LIKE '%" & TheWordsArray(i) & "%'"
              next
            end if
          next
       end if 
    
       if TheMode="AND" then
          For j=1 to 5
            StrOpenInContruction=""
            For i=0 to Wmax
               if TheWordsArray(i)<>"" then
                  StrOpenInContruction=  StrOpenInContruction & " AND " & ColunmNameIntable(j) & " LIKE '%" & TheWordsArray(i) & "%'"
              end if
           next
           StrOpen=StrOpen & " OR (" & Right (StrOpenInContruction,Len(StrOpenInContruction)-5) & ")"
          next
       end if 
    
    else
      
       For j=1 to 5
           StrOpen= StrOpen & " OR " & ColunmNameIntable(j) & " LIKE '%" & TheWords & "%'"
      next
    
    end if
    
    StrOpen= "SELECT * FROM followup" & followup & " WHERE " & Right (StrOpen,Len(StrOpen)-5)
    'response.Write(StrOpen)
    'response.End
    
    ' Now, we will open the data base and perform the search
    
    Set recordset = Server.CreateObject("ADODB.Recordset")
    recordset.Open StrOpen, ConnLot
    
    ' Finally we will show the maching records
    
    If recordset.EOF And recordset.BOF Then
      'If we get here it means we have selected no rows from the table in our database
      Response.write ("There are 0 records.")
    else
      'If we get here it means we have selected one or more rows from the table in our database
    	recordset.MoveFirst
    	While Not recordset.EOF
        'Next 3 lines will display search results. We may change the way we are displaying the results
        For j=1 to 5
    		   Response.write (DataName(j) & ": " & recordset.Fields (ColunmNameIntable(j)) & "<BR>")
        next
    		Response.write ("<HR>")
    		recordset.MoveNext
    	Wend
    	End If
    
    else
      Response.write ("Very sort search name. Please try again.")
    end if
    
    end if 
    
    %>
    
    </body>
    </html>
    here is the displaying error:
    "Incorrect syntax near the keyword 'LIKE'.
    /dolla&cam_project/councelor/followup/search_part.asp, line 128"

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    When you Response.Write(StrOpen), what does it say? (Please copy and paste.)
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Nov 2006
    Posts
    9

    search

    its displaying this:

    "SELECT * FROM WHERE atient_med LIKE '%patient_med%' OR patient_status LIKE '%patient_med%' OR patient_id LIKE '%patient_med%' OR vct_id LIKE '%patient_med%' OR LIKE '%patient_med%'"
    Last edited by Phil Weber; 11-16-2006 at 09:16 PM.

  4. #4
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    OK, that is not a valid SQL statement. There should be a table name between FROM and WHERE. Also, it looks like the first character of patient_med ("atient_med") is missing just after the WHERE.
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  5. #5
    Join Date
    Nov 2006
    Posts
    9

    search fields in tables

    i tried to put the table name in the statement but it still give me the same error!if you have any other suggestion!!!
    Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
    
    <!--#include file="connection/connect.asp" -->
    
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <title>search by keyword</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>
    <body>
    
    <% 
    If request.querystring<>""  then 
    If len(request.querystring("q"))>=5 then 
    
    ' This variables are needed to work with the database
    
    DIM recordset
    DIM StrOpen
    DIM StrOpenInContruction ' This variable is used to create variable "StrOpen"
    
    
    ' The name of the table in our sql database. In our case this name is "followup" 
    DIM NameOfTableInDB            
    NameOfTableInDB="followup"
    
    ' The name of each colunm in the table is contained in the array "ColunmNameIntable". 
    ' The table in my database provided in this codes has up to 7 colunms 
    ' As in this script we will not use all of them, only the ones we will use are included in this array
    
    
    DIM ColunmNameIntable(5) 
    
     ColunmNameIntable(0)="patient_result"
     ColunmNameIntable(1)="patient_med"
     ColunmNameIntable(2)="patient_status"
     ColunmNameIntable(3)="patient_id"
     ColunmNameIntable(4)="vct_id"
    
    ' When displaying data we will use this values 
    DIM DataName(5) 
    
     DataName(0)="patient result"
     DataName(1)="patient medication"
     DataName(2)="patient actual status"
     DataName(3)="patient_id"
     DataName(3)="vct_id"
    
    
    
    ' Keywords to search will be saved to variable "TheWords"
    DIM TheWords    
    TheWords = request.querystring("q")
    
    ' Mode will be saved to this variable. Value will be "OR" or "AND"  
    DIM TheMode        
    TheMode = request.querystring("mode")
    
    
    ' When TheWords contains more than one keyword, 
    ' keywords will be save to array "TheWordsArray"
    DIM TheWordsArray 
    
    ' If more than one keywords are introduced, then we will set up MorethanOneKeywords value to "yes"
    DIM MorethanOneKeywords 
    
    ' Will be used to calculate maximum number of keywords
    DIM Wmax 
    
     ' Just counters
    DIM i,j                               
    
    If instr(1,request.querystring("q")," ",1) >0 then
       MorethanOneKeywords="yes"
       TheWordsArray=Split(TheWords," ")
       Wmax=ubound(TheWordsArray)
    	For i=0 to Wmax
    		if len(TheWordsArray(i))<5 then
          Response.write ("Search Term <b>" & TheWordsArray(i) & "</b> is very sort, so it has not been used<HR>")
    			TheWordsArray(i)=""
        end if
      next
    end if 
    
    
    ' First we will get "StrOpen", wich is later use to filter the database
    
    If MorethanOneKeywords="yes" then
    
       if TheMode="OR" then
          For i=0 to Wmax
    		     if TheWordsArray(i)<>"" then
    		       For j=0 to 5
                  StrOpen= StrOpen & " OR " & ColunmNameIntable(j) & " LIKE '%" & TheWordsArray(i) & "%'"
              next
            end if
          next
       end if 
    
       if TheMode="AND" then
          For j=0 to 5
            StrOpenInContruction=""
            For i=0 to Wmax
               if TheWordsArray(i)<>"" then
                  StrOpenInContruction=  StrOpenInContruction & " AND " & ColunmNameIntable(j) & " LIKE '%" & TheWordsArray(i) & "%'"
              end if
           next
           StrOpen=StrOpen & " OR (" & Right (StrOpenInContruction,Len(StrOpenInContruction)-5) & ")"
          next
       end if 
    
    else
      
       For j=1 to 5
           StrOpen= StrOpen & " OR " & ColunmNameIntable(j) & " LIKE '%" & TheWords & "%'"
      next
    
    end if
    
    StrOpen= "SELECT * FROM followup" & followup & " WHERE " & Right (StrOpen,Len(StrOpen)-5)
    'response.Write(StrOpen)
    'response.End
    
    ' Now, we will open the data base and perform the search
    
    Set recordset = Server.CreateObject("ADODB.Recordset")
    recordset.Open StrOpen, ConnLot
    
    ' Finally we will show the maching records
    
    If recordset.EOF And recordset.BOF Then
      'If we get here it means we have selected no rows from the table in our database
      Response.write ("There are 0 records.")
    else
      'If we get here it means we have selected one or more rows from the table in our database
    	recordset.MoveFirst
    	While Not recordset.EOF
        'Next 3 lines will display search results. We may change the way we are displaying the results
        For j=1 to 5
    		   Response.write (DataName(j) & ": " & recordset.Fields (ColunmNameIntable(j)) & "<BR>")
        next
    		Response.write ("<HR>")
    		recordset.MoveNext
    	Wend
    	End If
    
    else
      Response.write ("Very sort search name. Please try again.")
    end if
    
    end if 
    
    %>
    
    </body>
    </html>

  6. #6
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    We need to see the value of strOpen now. That's all that matters; we don't need your entire code, just the SQL query it generates.
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  7. #7
    Join Date
    Nov 2006
    Posts
    9

    search the table

    Code:
    StrOpen= "SELECT * FROM followup" & followup & " WHERE " & Right (StrOpen,Len(StrOpen)-5)
    response.Write(StrOpen)
    its generating the same sql query as i mntionned before!

    SELECT * FROM WHERE atient_med LIKE '%patient_med%' OR patient_status LIKE '%patient_med%' OR patient_id LIKE '%patient_med%' OR vct_id LIKE '%patient_med%' OR LIKE '%patient_med%'"

  8. #8
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    How is that possible? Your code says "StrOpen= "SELECT * FROM followup..." but the value you posted is "SELECT * FROM WHERE..." Where is the table name ("followup")?
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  9. #9
    Join Date
    Nov 2006
    Posts
    9
    yes "followup" is the name of my table

  10. #10
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    yes, but it's not in the strOpen value that you posted. Why not?
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  11. #11
    Join Date
    Nov 2006
    Posts
    9
    i don't understand what you want to tell . can you write the exact sql statement i should write so i can find myself! thank for your help!

  12. #12
    Join Date
    Nov 2006
    Posts
    9
    i figured this out thanks

Similar Threads

  1. Access.mdb maximum fields per table?
    By jctvb6 in forum VB Classic
    Replies: 9
    Last Post: 09-30-2006, 06:26 AM
  2. Attn: Daniel Reber
    By joe in forum Database
    Replies: 0
    Last Post: 04-04-2003, 06:25 PM
  3. Replies: 0
    Last Post: 08-10-2001, 04:35 PM
  4. Clearing Pivot table fields "memory" in VB?
    By Fred Giesen in forum authorevents.patrick
    Replies: 2
    Last Post: 09-06-2000, 06:17 PM
  5. How do I Null out fields in a table?
    By Russ in forum VB Classic
    Replies: 1
    Last Post: 05-19-2000, 09:03 PM

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