Searching a database

Hi

I am using asp to search a database.

I have created the search page and the results page and the detailed page and
it works ok but
I have a keyword field, which is the main search box and was wondering if two
'Keywords' can be searched. As when i put in two keywords it shows no result

This is my SQL

SELECT *
FROM database.table1
WHERE Description LIKE '%MMColParam%' AND Location LIKE '%varLo%' AND InDate
LIKE '%strDate%'
ORDER BY InDate DESC

Is there something wrong with this code that why i can?t search with two
keywords
or
is there something else i could do

Many thanks

Adam

Check out www.dmxzone.com they have a free tutorial at the moment for
creating multi-parameter, multi-word searches

Cheers

Dale

"aDAMFOX" <webforumsuser (AT) macromedia (DOT) com> wrote

Hi

I am using asp to search a database.

I have created the search page and the results page and the detailed page
and
it works ok but
I have a keyword field, which is the main search box and was wondering if
two
'Keywords' can be searched. As when i put in two keywords it shows no
result

This is my SQL

SELECT *
FROM database.table1
WHERE Description LIKE '%MMColParam%' AND Location LIKE '%varLo%' AND
InDate
LIKE '%strDate%'
ORDER BY InDate DESC

Is there something wrong with this code that why i can?t search with two
keywords
or
is there something else i could do

Many thanks

Adam

Hi,

I got the multi word search working from that tutorial on DMXZOne but........

I did have multi fields like date and location but i cant get them to work
with the code

My browser says there is Incorrect Syntax near 'WHERE' .

<%
Dim rsSearch
Dim rsSearch_numRows

Set rsSearch = Server.CreateObject("ADODB.Recordset")
rsSearch.ActiveConnection = MM_connData_STRING
rsSearch.Source = "SELECT * FROM database11.Data "+
Replace(rsSearch__t3_String, "'", "'") + " AND WHERE Location LIKE '%" +
Replace(rsSearch__varLo, "'", "''") + "%' AND Color LIKE '%" +
Replace(rsSearch__varSal, "'", "''") + "%' AND InDate BETWEEN #" +
Replace(rsSearch__vartoday, "'", "''") + "# AND #" + Replace(rsSearch__varDate,
"'", "''") + "# ORDER BY InDate "
rsSearch.CursorType = 0
rsSearch.CursorLocation = 2
rsSearch.LockType = 1
rsSearch.Open()

rsSearch_numRows = 0
%>

I just cant see it

Any help would be great

thanks

Adam

You have the word "AND" before "WHERE". It's meaningless in that context, and so generates an error.

Thanks cmbergin,

I had a play around with the code and I see what I was doing wrong. I even
understand the code little better (emphasis on the little)

One thing I am having trouble with is my between statement.
Here is my code
(I think it is something to do with the way the replace varDay thingy is
encased in the ? and %s but I not sure, maybe it even the LIKE statement
altogether, should use = instead I don?t know.)

<%
Dim rsSearch
Dim rsSearch_numRows

Set rsSearch = Server.CreateObject("ADODB.Recordset")
rsSearch.ActiveConnection = MM_connjobs_STRING
rsSearch.Source = "SELECT * FROM database11.Data " +
Replace(rsSearch__t3_String, "'", "''") + " AND Location LIKE '%" +
Replace(rsSearch__varLo, "'", "''") + "%' AND Color LIKE '%" +
Replace(rsSearch__varSal, "'", "''") + "%' AND InDate BETWEEN '%" +
Replace(rsSearch__varDay, "'", "''") + "%' AND '%" +
Replace(rsSearch__vartoday, "'", "''") + "%' ORDER BY InDate DESC"
rsSearch.CursorType = 0
rsSearch.CursorLocation = 2
rsSearch.LockType = 1
rsSearch.Open()

rsSearch_numRows = 0
%>

It works as in it does not generate errors but it does not do what I want.
Basically I want to be able to search between to dates one which is always set
to today using a <%=Date()%> function and another date which the user can
select from a drop down menu which would say <%DateAdd(?d?, -1, Date())%>
(think that the code I using) so it says yesterdays date.
I would like to show dates between yesterday and today, 2 days ago and today
etc. and have a function to search all dates.

Any ideas what I doing wrong???

Thanks
Adam

There are a couple of things wrong. First, I'm surprised you're not getting
errors.

You don't have a WHERE clause (unless it's somehow part of
rsSearch__t3_String). Did you copy the recordset code completely? Also, the %
sign cannot be used with dates (unless you're just storing your dates in a text
field).

Try this:
rsSearch.Source = "SELECT * FROM database11.Data " +
Replace(rsSearch__t3_String, "'", "''") + " AND Location LIKE '%" +
Replace(rsSearch__varLo, "'", "''") + "%' AND Color LIKE '%" +
Replace(rsSearch__varSal, "'", "''") + "%' AND InDate BETWEEN '" +
Replace(rsSearch__varDay, "'", "''") + "' AND GETDATE() ORDER BY InDate"

Without knowing what's going on with rsSearch__t3_String, that's all I can
offer.

Cheers for getting back to me

I tried what you said and it gave this error is that some thing to do with
using asp date function and sql GetDate() together?? (as i said i was using the
<%=DateAdd....%> function could i maybe use an sql one instead to minus that
days from todays date?)

Microsoft OLE DB Provider for SQL Server error '80040e07'

The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.


I think that the where clause is some where in the asp script at the top of my
page i got it fom dmxzone site as a tutorial.
I can post that code if you think it will help.

Thanks for getting back to me so fast.

Adam[

Thanks cmbergin for your help with the date search,

I've cracked it what i was doing wrong was in my database the date column was
set to char so there was something wrong with the conversion of values.
So i changed that and i have used just one variable "date" which is checked
against the database and used a >= clause instead of between.
BUT (theres always a but)
the way that the dates are stored in my data base is the american way ie
month/day/year and the function for dateadd..etc uses the british way of
formatting dates day/month/year so i not sure what to do.

Thanks anyway for all your help

Adam

When you pass the date, first convert it to yyyy-dd-mm using the VBScript
functions Year(), Day(), and Month(). So, get the date from your form, but
pass it to the database differently:

myDateVariable =
Year(Request.Form("date")&"-"&Day(Request.Form("Date"))&"-"&Month(Request.Form("
Date"))

Then,

WHERE InDate >= 'myDateVariable'

Thnaks

Expected end of statement

/adv_search_results.asp, line 66

Dim myDateVariable =
Month(Request.Form("date")&"/"&Day(Request.Form("Date"))&"/"&Year(Request.Form("
Date"))

it giving this error where should it be put in me code in its own <%%>
as i put it here

<%
Dim myDateVariable =
Month(Request.Form("date")&"/"&Day(Request.Form("Date"))&"/"&Year(Request.Form("
Date"))
Dim rsSearch
Dim rsSearch_numRows

Set rsSearch = Server.CreateObject("ADODB.Recordset")
ABOVE BUT IN THE SQL STATEMENT

thanks
adam