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 14  2231 
SELECT * FROM booking_form WHERE AirportStation LIKE ('%" +
Replace(BookingForm__varAirportStation, "'", "''") + "%')"
should do it
On 2 Mai, 10:01, "Simon Gare" <s...@simongare.comwrote:
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
Simon Gare wrote on 02 mei 2007 in
microsoft.public.inetserver.asp.general:
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, "'", "''") + "'
With "LIKE" you only would have to add a wildcard character.
SELECT * FROM booking_form WHERE AirportStation LIKE '" +_
yourSearchInput + "%" + "'
=============
"Heathrow Te" + "%"
will select:
Heathrow Terminal 1
Heathrow Terminal 2
Heathrow Terminal 3
Heathrow Terminal 4
Heathrow Terminal 5 [?]
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
Thanks all worked perfectly.
Simon
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" <si***@simongare.comwrote in message
news:et**************@TK2MSFTNGP02.phx.gbl...
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
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:
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:et**************@TK2MSFTNGP02.phx.gbl...
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- Zitierten Text ausblenden -
- Zitierten Text anzeigen -
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" <si***@simongare.comwrote in message
news:ux**************@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
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" <si***@simongare.comwrote in message
news:et**************@TK2MSFTNGP02.phx.gbl...
>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
but we retrieve the value from a querystring that is entered on a search
page
BookingForm__varID = Request.Querystring DATE2
TimeOfBooking LIKE '%" + Replace(BookingForm__varID, "'", "''") + "%'
but returns no values doesn't matter what format you put it in either, will
retrieve values when the format is correct though which is no good to us.
02/05/2007 11:02:35 etc
"michal" <mg****@gmail.comwrote in message
news:11**********************@o5g2000hsb.googlegro ups.com...
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:
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:et**************@TK2MSFTNGP02.phx.gbl...
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- Zitierten Text ausblenden -
- Zitierten Text anzeigen -
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" <du***@newsgroups.comwrote in message
news:us**************@TK2MSFTNGP03.phx.gbl...
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" <si***@simongare.comwrote in message
news:ux**************@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
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" <si***@simongare.comwrote in message
news:et**************@TK2MSFTNGP02.phx.gbl...
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
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" <si***@simongare.comwrote in message
news:uY**************@TK2MSFTNGP02.phx.gbl...
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" <du***@newsgroups.comwrote in message
news:us**************@TK2MSFTNGP03.phx.gbl...
>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" <si***@simongare.comwrote in message
news:ux**************@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
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" <si***@simongare.comwrote in message
news:et**************@TK2MSFTNGP02.phx.gbl...
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
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" <du***@newsgroups.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
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" <si***@simongare.comwrote in message
news:uY**************@TK2MSFTNGP02.phx.gbl...
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" <du***@newsgroups.comwrote in message
news:us**************@TK2MSFTNGP03.phx.gbl...
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" <si***@simongare.comwrote in message
news:ux**************@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
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" <si***@simongare.comwrote in message
news:et**************@TK2MSFTNGP02.phx.gbl...
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
Deary me. Just a glance at that SQL string makes me faint.
Try taking all those replace operations out of the concatention. Do them
before you build your SQL string. Better still, use parameter markers or
stored procedures.
Mike
"Simon Gare" <si***@simongare.comwrote in message
news:uo**************@TK2MSFTNGP03.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" <du***@newsgroups.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
>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" <si***@simongare.comwrote in message
news:uY**************@TK2MSFTNGP02.phx.gbl...
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" <du***@newsgroups.comwrote in message
news:us**************@TK2MSFTNGP03.phx.gbl...
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" <si***@simongare.comwrote in message
news:ux**************@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
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" <si***@simongare.comwrote in message
news:et**************@TK2MSFTNGP02.phx.gbl...
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
Simon Gare wrote:
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
Well, forget this idea. it cannot work. You cannot search a column with one
datatype using a value with an incompatible datatype. You will have to look
at the value supplied by the user and decide which columns to include in the
WHERE clause.
Personally, I would provide the user a means of indicating what type of data
is being supplied (a dropdown, or some radio buttons). In the responding
page, first validate that the data supplied for the search can be coerced
into the selected data type, returning an error to the user if it cannot be.
Once you have determined that the user has supplied compatible data and data
type, build your WHERE clause only including the columns that are compatible
with the selected data type.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:OT**************@TK2MSFTNGP02.phx.gbl...
Simon Gare wrote:
>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
Well, forget this idea. it cannot work. You cannot search a column with
one datatype using a value with an incompatible datatype.
I was so distracted by all the Replace() operations going on, I missed the
fact that Simon was using the same value for all the filters. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Phil Powell |
last post by:
The table already has a fulltext index and from there I can use the
MySQL fulltext search query to get results as well as the relevancy
score.
The problem I have is that MySQL has a default...
|
by: Zambo via SQLMonster.com |
last post by:
Hi!
We have Sql Server 2000 in our server (NT 4). Our database have now about
+350.000 rows with information of images. Table have lot of columns
including information about image name, keywords,...
|
by: Robert Oschler |
last post by:
I read a while back that MySQL will only use one index per query. (If this
is not so, please tell me and point me to a doc that gives a good
explanation of MySQL's current index usage policy). ...
|
by: DWolff |
last post by:
I'm somewhat new to Access, and have no VB experience at all (except for modifying existing code where obvious).
I built a data entry form that works fine. Typically, a client will call in and...
|
by: |
last post by:
I have a question about spawning and displaying subordinate list controls
within a list control. I'm also interested in feedback about the design of
my search application. Lots of code is at the...
|
by: thepresidentis |
last post by:
here is my problem,
i have a website that compiles concert listings for users to search for shows in their local area, i ran into a problem with returning search results due to an improper format...
|
by: jej1216 |
last post by:
I am trying to put together a PHP search page in which the user can select none, one, two, or three fields to search, and then the results php will build the SQL with dynamic where caluses to reflect...
|
by: th1982 |
last post by:
HI All
I have a search page' s result which view 3results/per page,but my "next" link to view next page is not working.
Here is my code :
<?php
// Get the search variable from URL
...
|
by: Sham |
last post by:
I am trying to perform the following query on a table that has been
indexed using Full Text Search.
The table contains multiple columns than have been indexed. (Below,
all xml columns are...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |
