Search Query Problem

Hi,
>
have a search.asp page with results.asp page drawing data from an SQL db,
problem is the user has to type the whole field value into the search box to
retrieve the value on results.asp, what I need is to type in just a few
characters e.g. at the moment to search for all pickups at Heathrow Terminal
1 the user has to type in
>
Heathrow Terminal 1
>
When really I just want them to type in
>
Heathrow or even Heath etc.
>
Query below
>
SELECT * FROM booking_form WHERE AirportStation LIKE '" +
Replace(BookingForm__varAirportStation, "'", "''") + "'
>
anyone help?
>
Simon
--
Simon Gare
The Gare Group Limited
>
website: www.thegaregroup.co.uk
website: www.privatehiresolutions.co.uk

Hi,
>
have a search.asp page with results.asp page drawing data from an SQL
db, problem is the user has to type the whole field value into the
search box to retrieve the value on results.asp, what I need is to
type in just a few characters e.g. at the moment to search for all
pickups at Heathrow Terminal 1 the user has to type in
>
Heathrow Terminal 1
>
When really I just want them to type in
>
Heathrow or even Heath etc.
>
Query below
>
SELECT * FROM booking_form WHERE AirportStation LIKE '" +
Replace(BookingForm__varAirportStation, "'", "''") + "'

Hi,
>
have a search.asp page with results.asp page drawing data from an SQL db,
problem is the user has to type the whole field value into the search box

retrieve the value on results.asp, what I need is to type in just a few
characters e.g. at the moment to search for all pickups at Heathrow

1 the user has to type in
>
Heathrow Terminal 1
>
When really I just want them to type in
>
Heathrow or even Heath etc.
>
Query below
>
SELECT * FROM booking_form WHERE AirportStation LIKE '" +
Replace(BookingForm__varAirportStation, "'", "''") + "'
>
>
anyone help?
>
Simon
--
Simon Gare
The Gare Group Limited
>
website: www.thegaregroup.co.uk
website: www.privatehiresolutions.co.uk
>
>

Just came across a problem, when searching the date field it returns
nothing, current entry format is
>
dd/mm/yyyy hh:mm:ss
02/05/2007 10:26:00
>
when I search for 02/05/2007 nothing is returned even though I have the % in
place.
>
All other search criteria is working ok?
>
Simon
>
"Simon Gare" <s...@simongare.comwrote in message
>
news:etrCUBJjHHA.4064@TK2MSFTNGP02.phx.gbl...
>
>
>>toTerminal>>>>>>>>>
- Zitierten Text anzeigen -

Just came across a problem, when searching the date field it returns
nothing, current entry format is
>
dd/mm/yyyy hh:mm:ss
02/05/2007 10:26:00
>
when I search for 02/05/2007 nothing is returned even though I have the %
in
place.
>
All other search criteria is working ok?
>
Simon
>
>
"Simon Gare" <simon@simongare.comwrote in message
news:etrCUBJjHHA.4064@TK2MSFTNGP02.phx.gbl...toTerminal>
>

i am not sure but i think that like is not working on data columns ...
you should be fine with
WHERE datCol = '02/05/2007'
>
On 2 Mai, 11:28, "Simon Gare" <s...@simongare.comwrote:

>
>

Michal is correct. LIKE doesn't work with datetime. You will have to use
Between to find datetimes in a range. If you want to find items with
today's date, your range will be from 02/05/2007 00:00:00 to 02/05/2007
23:59:59.
>
--
Mike Brind
>
"Simon Gare" <simon@simongare.comwrote in message
news:ux8L7yJjHHA.4552@TK2MSFTNGP04.phx.gbl...

>
>

Just came across a problem, when searching the date field it returns
nothing, current entry format is
>
dd/mm/yyyy hh:mm:ss
02/05/2007 10:26:00
>

ok, so if I want to find all entries an the results page with the date
specified in the querystring which is entered manually by the user on the
search page how do I go about that? do I use 2 BETWEEN statements ie
>
Select * FROM BookingForm WHERE DATE2 BETWEEN Request.Querystring("DATE2")
and BETWEEN Request.Querystring("DATE2")
>
something like that?
>
Simon
>
>
"Mike Brind" <dummy@newsgroups.comwrote in message
news:us2kmEKjHHA.3928@TK2MSFTNGP03.phx.gbl...%db,box>
>

If the likely value of Request.QueryString("yourdate") is eg 02/05/2007,
then the query would be
>
Select * From BookingForm Where datefield Between '" &
Request.QueryString("yourdate") & " 00:00:00' AND '" &
Request.QueryString("yourdate") & " 23:59:59'
>
--
Mike
>
"Simon Gare" <simon@simongare.comwrote in message
news:uYVmhOKjHHA.4676@TK2MSFTNGP02.phx.gbl...

>
>

Thanks Mike, works ok when looking for a date but upsets the other search
criteria, SQL Query looks at 1 value in the querystring and compares
across
many fields
>
>
SELECT * FROM booking_form WHERE ID LIKE '" +
Replace(BookingForm__varID,
"'", "''") + "' OR TimeOfBooking BETWEEN '" + Replace(BookingForm__varID,
"'", "''") + " 00:00:00' AND '" + Replace(BookingForm__varID, "'", "''") +
"
23:59:59' OR PAX_NAME1 LIKE '%" + Replace(BookingForm__varID, "'", "''") +
"%' OR COLL_STREET LIKE '%" + Replace(BookingForm__varID, "'", "''") + "%'
OR COLL_POST_CODE LIKE '%" + Replace(BookingForm__varID, "'", "''") + "%'
OR
MOBILE_NO LIKE '%" + Replace(BookingForm__varID, "'", "''") + "%' OR
COLL_BUILDING_NAME_NO LIKE '%" + Replace(BookingForm__varID, "'", "''") +
"%' ORDER BY TimeOfBooking DESC"
>
>
When i put in the date it works fine but if i put in a name which looks in
PAX_NAME1 field the error reads
>
Syntax error converting datetime from character string.
>
>
Any ideas?
>
Simon
>
>
"Mike Brind" <dummy@newsgroups.comwrote in message
news:%231GqxcKjHHA.1900@TK2MSFTNGP04.phx.gbl...theRequest.Querystring("DATE2")02/05/2007theSQLsearch>
>

Thanks Mike, works ok when looking for a date but upsets the other
search criteria, SQL Query looks at 1 value in the querystring and
compares across many fields
>

Simon Gare wrote:Well, forget this idea. it cannot work. You cannot search a column with
one datatype using a value with an incompatible datatype.