Re: searches failing...help
Hi, I wanted to thank everyone for chipping in on this one... you gave me some good ideas and eventually solved my problem, first I what I needed was a form precursor to the '#search_string#' because it was a form...secondly swapping out the = for a LIKE and a coupla % signs did the trick in the WHERE statement. So yippee THank you. > >select id, entered_date, synopsis, knowledge >from nxsknow >where knowledge = '#search_string#' >order by entered_date desc > I dont know where or how the synopsis column crept into this thread except when I introduced it as alternative to search onbut the column I really need to search on is the knowlede column. Synopsis is next to uselessright now it produces a result thanks to the suggestions of you folks and that is what I need - results. however when this baby goes live in another month...I need to be able to search the knowledge column yet each and everytime that I do...no matter what language I put after the WHERE knowledge ... I get 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. This is a SQL 6.5 database. The column is a TEXT column. If that means anything to anyone. There is no upper limit to the text on this column... Why am I getting this error ? -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ 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.
Re: searches failing...help
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: select id, entered_date, synopsis, knowledge from nxsknow where knowledge LIKE '%#search_string#%' order by entered_date desc 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... > > > select id, entered_date, synopsis, knowledge > from nxsknow > where knowledge = '#search_string#' > order by entered_date desc > > > 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 backshouldn'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 nothingit 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/cf-talk@houseoffusion.com/ 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.
RE: searches failing...help.
When you use "like" in a sql statement you will need to use wild-cards. In Oracle it would be "where knowledge like '%#search_string#%'. I believe Access uses the asterisk - '*#search_string#*'. Dan -Original Message- From: miles [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 12, 2000 10:23 AM To: [EMAIL PROTECTED] Subject: searches failing...help. Hi, Ive got myself a tiny problem...which should be a snap for you folks that are more skilled than myself right now... select id, entered_date, synopsis, knowledge from nxsknow where knowledge = '#search_string#' order by entered_date desc 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 backshouldn'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 nothingit 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/cf-talk@houseoffusion.com/ 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. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ 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.
RE: searches failing...help.
If you use 'WHERE knowledge = '#search_string#, you will only get records where knowledge matches the search string exactly. If you use LIKE, you will only get records where some part of knowledge matches the search string exactly, i.e. 'Jane Doe' is contained in 'Jane Doe Peterson', but 'Jane Doe' is not contained in 'Jane B. Doe'. In the case of "where synopsis = '#search_string#'", does synopsis contain the word 'site' only, or is 'site' only a part of the field content? -Original Message- From: miles [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 12, 2000 1:23 PM To: [EMAIL PROTECTED] Subject: searches failing...help. Hi, Ive got myself a tiny problem...which should be a snap for you folks that are more skilled than myself right now... select id, entered_date, synopsis, knowledge from nxsknow where knowledge = '#search_string#' order by entered_date desc 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 backshouldn'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 nothingit 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/cf-talk@houseoffusion.com/ 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. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ 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.
Re: searches failing...help.
> >select id, entered_date, synopsis, knowledge >from nxsknow >where knowledge = '#search_string#' >order by entered_date desc > There's a couple of possibilities that occour to me offhand... 1.) Case sensitivity - if everything in your DB is uppercase, try using where knowledge LIKE '#ucase(search_string)#' 2.) Unless you put in wildcards, using 'LIKE' will ONLY match on an EXACT string; thus: where knowledge = 'foobar' would match 'foobar', but NOT 'boofoobar', 'foobarbaz', 'FOOBAR', etc. If you wildcard with a percent sign, however: where knowledge = 'foobar%' Then the query would match 'foobar' AND 'foobarbaz'. At least, it's a percent sign in Oracle. I seem to recall having seen an asterisk used elsewhere, but I'm not certain. HTH... Brandon Whitaker [EMAIL PROTECTED] --- "It'll get used by the same people using Opera. People dressed in black wearing berets." - Dave Watts, on Mozilla "It makes you feel so welcome to have a greeter like Papa Dave at the door. Kind of like what Wal-Mart wishes it could do." - John Allred, on Dave Watts "The net interprets censorship as damage and routes around it." - John Gilmore -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ 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.
Re: searches failing...help.
You could add wild cards to LIKE: select id, entered_date, synopsis, knowledge from nxsknow where knowledge = '%#search_string#%' order by entered_date desc - Original Message - From: "miles" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, July 12, 2000 12:23 PM Subject: searches failing...help. > Hi, > > Ive got myself a tiny problem...which should be a snap for > you folks that are more skilled than myself right now... > > > select id, entered_date, synopsis, knowledge > from nxsknow > where knowledge = '#search_string#' > order by entered_date desc > > > 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 backshouldn'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 nothingit 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/cf-talk@houseoffusion.com/ > 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. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ 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.
RE: searches failing...help.
try using this: LIKE '%#yoursearchstringvariablehere#%' or LIKE '#yoursearchstringvariablehere#%' This will make the string a partial to the actual search string that may be returned (i.e. "sear" will find "search" using LIKE '#yoursearchstringvariablehere#%') Also, you may need '#form.search_string#' depending on how you are passing the string to the query. Sincerely, Shane Witbeck Webmaster -Original Message- From: miles [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 12, 2000 1:23 PM To: [EMAIL PROTECTED] Subject: searches failing...help. Hi, Ive got myself a tiny problem...which should be a snap for you folks that are more skilled than myself right now... select id, entered_date, synopsis, knowledge from nxsknow where knowledge = '#search_string#' order by entered_date desc 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 backshouldn'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 nothingit 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/cf-talk@houseoffusion.com/ 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. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ 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.
RE: searches failing...help.
When using the LIKE statement you need to provide proper wildcards unless you want to match the exact string. For example, if you want to find an occurrence of '#str_search#' in the 'Knowledge' column your query would look something like this ... SELECT PID, Date, Knowledge FROM NXSKnow WHERE Knowledge LIKE '%#FORM.str_search#%' ORDER BY Date DESC See SQL documentation for a listing of applicable wildcards and their use. Steve -Original Message- From: miles [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 12, 2000 1:23 PM To: [EMAIL PROTECTED] Subject: searches failing...help. Hi, Ive got myself a tiny problem...which should be a snap for you folks that are more skilled than myself right now... select id, entered_date, synopsis, knowledge from nxsknow where knowledge = '#search_string#' order by entered_date desc 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 backshouldn'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 nothingit 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/cf-talk@houseoffusion.com/ 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. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ 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.
Re: searches failing...help.
I wanted to use... select stock, title, url, company, subcatalog, synopsis from movies where synopsis = 'X' but synopsis was a text field. I had to resort to select stock, title, url, company, subcatalog, synopsis from movies where synopsis is not null bla bla bla It was slower, but it worked. I do not know if this would be the best query, but It may work. select id, entered_date, synopsis, knowledge from nxsknow where knowledge is not null order by entered_date desc bla bla bla jacob At 01:23 PM 7/12/00 -0400, you wrote: >Hi, > >Ive got myself a tiny problem...which should be a snap for >you folks that are more skilled than myself right now... > > >select id, entered_date, synopsis, knowledge >from nxsknow >where knowledge = '#search_string#' >order by entered_date desc > > >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 backshouldn'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 nothingit 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/cf-talk@houseoffusion.com/ >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. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ 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.
Re: searches failing...help.
Miles try adding a % sign to your LIKE statement WHERE knowledge = '#search_string#%' - Original Message - From: "miles" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, July 12, 2000 12:23 PM Subject: searches failing...help. > Hi, > > Ive got myself a tiny problem...which should be a snap for > you folks that are more skilled than myself right now... > > > select id, entered_date, synopsis, knowledge > from nxsknow > where knowledge = '#search_string#' > order by entered_date desc > > > 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 backshouldn'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 nothingit 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/cf-talk@houseoffusion.com/ > 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. > -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ 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.
searches failing...help.
Hi, Ive got myself a tiny problem...which should be a snap for you folks that are more skilled than myself right now... select id, entered_date, synopsis, knowledge from nxsknow where knowledge = '#search_string#' order by entered_date desc 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 backshouldn'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 nothingit 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/cf-talk@houseoffusion.com/ 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.
