search fields in a table

**dollakay** · 11-16-2006 11:42 AM

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"

**Phil Weber** · 11-16-2006 02:21 PM

When you Response.Write(StrOpen), what does it say? (Please copy and paste.)

**dollakay** · 11-16-2006 05:28 PM

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%'"

**Phil Weber** · 11-16-2006 08:18 PM

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.

**dollakay** · 11-17-2006 09:21 AM

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>

**Phil Weber** · 11-17-2006 09:48 AM

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.

**dollakay** · 11-17-2006 02:52 PM

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%'"

**Phil Weber** · 11-17-2006 03:10 PM

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")?

**dollakay** · 11-17-2006 03:35 PM

yes "followup" is the name of my table

**Phil Weber** · 11-17-2006 03:58 PM

yes, but it's not in the strOpen value that you posted. Why not?

**dollakay** · 11-18-2006 04:24 AM

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!

**dollakay** · 11-19-2006 05:19 PM

i figured this out thanks

Thread: search fields in a table

Thread Tools

Display

search fields in a table

search

search fields in tables

search the table

Similar Threads

Access.mdb maximum fields per table?

Attn: Daniel Reber

Problem with Boolean fields from Access 97 table under Windows 98

Clearing Pivot table fields "memory" in VB?

How do I Null out fields in a table?

Bookmarks

Bookmarks

Posting Permissions