Search Query Problem

Discussion in 'ASP General' started by Simon Gare, May 2, 2007.

  1. Simon Gare

    Simon Gare Guest

    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, May 2, 2007
    #1
    1. Advertising

  2. Simon Gare

    michal Guest

    SELECT * FROM booking_form WHERE AirportStation LIKE ('%" +
    Replace(BookingForm__varAirportStation, "'", "''") + "%')"

    should do it

    On 2 Mai, 10:01, "Simon Gare" <> wrote:
    > 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
    michal, May 2, 2007
    #2
    1. Advertising

  3. Simon Gare

    Evertjan. Guest

    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)
    Evertjan., May 2, 2007
    #3
  4. Simon Gare

    Simon Gare Guest

    Thanks all worked perfectly.

    Simon
    Simon Gare, May 2, 2007
    #4
  5. Simon Gare

    Simon Gare Guest

    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" <> wrote in message
    news:...
    > 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, May 2, 2007
    #5
  6. Simon Gare

    michal Guest

    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" <> 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
    >
    > 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" <> wrote in message
    >
    > news:...
    >
    >
    >
    > > 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, May 2, 2007
    #6
  7. Simon Gare

    Simon Gare Guest

    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" <> wrote in message
    news:...
    > 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" <> 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
    > >
    > > 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" <> wrote in message
    > >
    > > news:...
    > >
    > >
    > >
    > > > 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 -

    >
    >
    Simon Gare, May 2, 2007
    #7
  8. Simon Gare

    Mike Brind Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:...
    >> 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
    >>
    >>

    >
    >
    Mike Brind, May 2, 2007
    #8
  9. Simon Gare

    Simon Gare Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:...
    > > 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" <> wrote in message
    > > news:...
    > >> 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, May 2, 2007
    #9
  10. 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"
    Bob Barrows [MVP], May 2, 2007
    #10
  11. Simon Gare

    Mike Brind Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:...
    >> 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" <> wrote in message
    >> news:...
    >> > 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" <> wrote in message
    >> > news:...
    >> >> 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
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
    Mike Brind, May 2, 2007
    #11
  12. Simon Gare

    Simon Gare Guest

    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" <> wrote in message
    news:%...
    > 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" <> wrote in message
    > news:...
    > > 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" <> wrote in message
    > > news:...
    > >> 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" <> wrote in message
    > >> news:...
    > >> > 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" <> wrote in message
    > >> > news:...
    > >> >> 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, May 2, 2007
    #12
  13. Simon Gare

    Mike Brind Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:%...
    >> 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" <> wrote in message
    >> news:...
    >> > 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" <> wrote in message
    >> > news:...
    >> >> 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" <> wrote in message
    >> >> news:...
    >> >> > 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" <> wrote in message
    >> >> > news:...
    >> >> >> 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
    >> >> >>
    >> >> >>
    >> >> >
    >> >> >
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
    Mike Brind, May 3, 2007
    #13
  14. 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], May 3, 2007
    #14
  15. Simon Gare

    Mike Brind Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > 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.
    Mike Brind, May 3, 2007
    #15
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Learner
    Replies:
    1
    Views:
    984
    Marina Levit [MVP]
    Jan 30, 2006
  2. Anonymous
    Replies:
    0
    Views:
    1,457
    Anonymous
    Oct 13, 2005
  3. David Gordon

    xpath query query

    David Gordon, May 18, 2005, in forum: XML
    Replies:
    2
    Views:
    787
    David Gordon
    May 18, 2005
  4. abcdef
    Replies:
    0
    Views:
    117
    abcdef
    Oct 7, 2003
  5. Abby Lee
    Replies:
    5
    Views:
    398
    Abby Lee
    Aug 2, 2004
Loading...

Share This Page