asp/sql

[colinod]

I have a web page that searches 2 fields of a database, this works fine if all the words serched are in one field but does not work if the words are in seperate fields.
It searched names of mp3 files and also a field that conatins various words to describe it, so if you type in female west country into the search field and none of the filenames contain the word female it returns no results unless the three words are in the description field, i am sure this can be resolves in the sql statement which is generated by asp.

here is the code

<%

if InStr(session("recordsInCart"), ","&request.form("recordNum")) = 0 then 
   session("recordsInCart") = session("recordsInCart") + request.form("recordNum") &","
else 
   'do nothing
end if 
%>
<%
Dim strSearch, myarray, strSQL, strSQLExtra 
If Not Request.Form("search") = "" Then 
session("search") = Request.Form("search") 
session("choice") = Request.Form("choice") 
session("queryterm") = session("queryterm")+" "+Request.Form("search")
End If 
  
'Loop is now in a function, so that we can feed it each field name. 
Function addparams(fieldname,wordArray)  
Dim tmp 
tmp = "" 
    For each item in wordArray 
    tmp = tmp & " (" & fieldname & " Like '%" & item & "%') " & session("choice") & " " 
    next 
    tmp = left(tmp, len(tmp)-4) ' Chops off the last "AND " 
    addparams = tmp 
End Function 
  
'Build the word array 
myarray = split(session("search"), " ") 
  
'Build the SQL  
strSQL = "SELECT * FROM mp3 INNER JOIN celebs ON mp3.celebid = celebs.idnumber WHERE (" 
  
strSQL = strSQL & addparams("filename", myarray) ' change field one! 
  
strSQL = strSQL & ") OR (" 
  
strSQL = strSQL & addparams("mp3type", myarray) ' change field two! 
  
strSQL = strSQL & ")order by surname, firstname, filename;" 
session("sqlset") = strSQL

  %>

i hope someone can help

[jhardman]

There are a few possible solutions to this problem, but let me tell you there are specialists who make the kind of query you are looking for professionally.

The simplest solution I can think of is to add the filename in the keyword field, then you only have to search one field.

Jared

[colinod]

yes i thought of that but it was a lot of copying and pasting i have 3800 records, if you know a quick way of doing this in access any help would be appreciated

[jhardman]

yes i thought of that but it was a lot of copying and pasting i have 3800 records, if you know a quick way of doing this in access any help would be appreciated

That shouldn't be a problem for someone who knows how to use ASP! Write a script that goes through the table one row at a time and makes the necessary change!

Jared

[colinod]

i did it with a update query in access thanks for your help