473,715 Members | 2,163 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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(Booking Form__varAirpor tStation, "'", "''") + "'
anyone help?

Simon
--
Simon Gare
The Gare Group Limited

website: www.thegaregroup.co.uk
website: www.privatehiresolutions.co.uk
May 2 '07 #1
14 2263
SELECT * FROM booking_form WHERE AirportStation LIKE ('%" +
Replace(Booking Form__varAirpor tStation, "'", "''") + "%')"

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(Booking Form__varAirpor tStation, "'", "''") + "'

anyone help?

Simon
--
Simon Gare
The Gare Group Limited

website: www.thegaregroup.co.uk
website: www.privatehiresolutions.co.uk

May 2 '07 #2
Simon Gare wrote on 02 mei 2007 in
microsoft.publi c.inetserver.as p.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(Booking Form__varAirpor tStation, "'", "''") + "'
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)
May 2 '07 #3
Thanks all worked perfectly.

Simon
May 2 '07 #4
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***@simongar e.comwrote in message
news:et******** ******@TK2MSFTN GP02.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(Booking Form__varAirpor tStation, "'", "''") + "'
anyone help?

Simon
--
Simon Gare
The Gare Group Limited

website: www.thegaregroup.co.uk
website: www.privatehiresolutions.co.uk


May 2 '07 #5
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******** ******@TK2MSFTN GP02.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(Booking Form__varAirpor tStation, "'", "''") + "'
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 -

May 2 '07 #6
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***@simongar e.comwrote in message
news:ux******** ******@TK2MSFTN GP04.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***@simongar e.comwrote in message
news:et******** ******@TK2MSFTN GP02.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(Bookin gForm__varAirpo rtStation, "'", "''") + "'
anyone help?

Simon
--
Simon Gare
The Gare Group Limited

website: www.thegaregroup.co.uk
website: www.privatehiresolutions.co.uk



May 2 '07 #7
but we retrieve the value from a querystring that is entered on a search
page

BookingForm__va rID = Request.Queryst ring DATE2

TimeOfBooking LIKE '%" + Replace(Booking Form__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.c omwrote in message
news:11******** **************@ o5g2000hsb.goog legroups.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******** ******@TK2MSFTN GP02.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(Booking Form__varAirpor tStation, "'", "''") + "'
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 -


May 2 '07 #8
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.Queryst ring("DATE2")
and BETWEEN Request.Queryst ring("DATE2")

something like that?

Simon
"Mike Brind" <du***@newsgrou ps.comwrote in message
news:us******** ******@TK2MSFTN GP03.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***@simongar e.comwrote in message
news:ux******** ******@TK2MSFTN GP04.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***@simongar e.comwrote in message
news:et******** ******@TK2MSFTN GP02.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(Booking Form__varAirpor tStation, "'", "''") + "'
anyone help?

Simon
--
Simon Gare
The Gare Group Limited

website: www.thegaregroup.co.uk
website: www.privatehiresolutions.co.uk



May 2 '07 #9
Simon Gare wrote:
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
http://www.aspfaq.com/show.asp?id=2313 vbscript
http://www.aspfaq.com/show.asp?id=2040 help with dates
http://www.aspfaq.com/show.asp?id=2260 dd/mm/yyy confusion
--
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"
May 2 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
5770
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 setting whereby the minimum amount of characters is 4 for a search. Being that we're government and full of TLA (three-letter acronyms), that is not practical, and furthermore, the app I'm building must be fully portable, so having MySQL tweaked...
2
2474
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, location, price, color mode etc. So our database don?t include the images itself, just a path to the location of every image. Keywords -field have data for example like this: cat,animal,pet,home,child with pet,child. Now our search use Full-Text...
0
1422
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). I'm using MySQL 4.2.x. Here's my dilemma. 1) --------- I have two tables that have records with a FULLTEXT index text field in each of them. The problem is the relationship between the tables is a
31
2943
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 the user has to find his record to add or modify existing data. Originally, I built the form based on a query with an input criteria for the last name and first name. The Last_Name critera is Like &"*" so that partial names will work. The...
0
2078
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 end of this message, but I will start with an overview of the problem. I've made a content management solution for my work with a decently structured relational database system. The CMS stores articles. The CMS also stores related items --...
1
1762
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 of my query, So i formatted the query properly Thanks to steve Klein :) , and all worked fine because there was only 1 show in my data base, I tryed to search for the show and it returned properly, and then i searched for the show with a different...
6
2640
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 the fields chosen. The first page, where they select the search fields and submit: <?php $db = mysql_connect("localhost", "root", "yeahright"); if (!$db) { die('Could not connect:'.mysql_error);
5
2629
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 $string = @$_GET ; $trimmed = trim($string); //trim whitespace from the stored variable // rows to return
8
5115
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 indexed). dbo.maintable(ProfileID int pk) dbo.fts_table(ProfileID int pk fk, col1 xml, col2 xml, col3 xml) I want to perform a query that will return any rows that contain ‘x’ and ‘y’ in any columns. I.e. ‘x’ could be in col1 and ‘y’ could be in
0
8715
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9193
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9092
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6641
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5964
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4734
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3170
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2531
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.