Miles,
A couple of ideas here. First, are you wanting to perform an exact search or
a wildcard search? If you want a wildcard search, you should say something like:
<CFQUERY Name="knowledge" DataSource="ccmast">
select id, entered_date, synopsis, knowledge
from nxsknow
where knowledge LIKE '%#search_string#%'
order by entered_date desc
</CFQUERY>
With a LIKE statement, you are looking for a pattern match. A percent sign in front
of your variable will produce a record set with that variable at the beginning of the
field data, a percent sign at the end of your variable will produce a record set with
that variable at the end of the field data, and a percent sign at both sides will
produce a record set with that variable appearing anywhere in the field data.
As far as the " where synopsis = '#search_string#' " question goes, it is the same
logic. If "site" is contained in the synopsis, you need to have it say " where
synopsis LIKE '%#search_string#%' " to catch that word anywhere in that data.
Also, what is being passed as #search_string#? That may be part of the problem as
well.
Chris Ivey
MTS Systems Engineer
GTE Data Services: Temple Terrace, Florida
TSS Distributed - WAN Tools Group
Office: (813) 978-4844
Pager: (813) 303-1177
AIM: IveyAtGTEDS
Date: Wed, 12 Jul 2000 13:23:15 -0400
From: miles <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Subject: searches failing...help.
Message-ID: <p04310109b5924b732a53@[192.168.2.113]>
> Hi,
>
> Ive got myself a tiny problem...which should be a snap for
> you folks that are more skilled than myself right now...
>
> <CFQUERY Name="knowledge" DataSource="ccmast">
> select id, entered_date, synopsis, knowledge
> from nxsknow
> where knowledge = '#search_string#'
> order by entered_date desc
> </CFQUERY>
>
> this query will fail each and every time with the following
> error.
>
> [microsoft][ODBC SQL Server Driver][SQL Server]TEXT and IMAGE datatypes
> may not be used in the WHERE or HAVING clause, except with the LIKE
> predicate and the IS NULL predicate.
>
> HOWEVER when I change the WHERE statement to the following...
>
> where knowledge LIKE '#search_string#'
>
> I get no results. It doesn't matter what I enter as a variable.
> I should get something back....shouldn't I ?
>
> The somewhat annoying part about this is if I change the where
> statement to the following
>
> where id = '#search_string#'
>
> and id is equal to some number...this query works...and pulls up a
> single record...or if I change the where statement to
>
> where synopsis = '#search_string#'
>
> and synopsis is equal to "site". It returns nothing....it should
> return something
> because half the entries in this table half the word "site" in the
> field synopsis.
>
> And before you think that there's nothing in the table...there is plenty
> of data. What's up with this query...why is this happening ? Any
> clues ?
>
> Miles.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
