|
| |
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
 |
A unique SQL problem??? - 8/17/2002 11:04:31
Has anyone ever experienced this problem:
I have a database of parts, when I perform searches for parts my SQL query only works in " SOME" situations. For example, one of the parts is a:
Compact Disk Case
When I search by: " Compact" , nothing is returned.
When I search by: " Disk" , nothing is returned.
When I search by: " Case" , the record shows up.
I am perplexed...... Here is my SQL statement:
SELECT * FROM PartsDatabase WHERE (Product Like ' %::Keyword::%' )
I' m using FP 2000, Access 2000 and the DRW. This is the first time I have run across this problem. Has anybody seen this problem before or have any ideas how to solve it???
Thanks in advance.
LLL
|
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: A unique SQL problem??? - 8/18/2002 1:59:06
Bobby,
Thanks for your info. I' ll tell ya, I think this is a MS SQL weakness. And the thing is - it makes ME look sloppy. It makes me think I should jump to Oracle. Easier said than done. Your problem is defintely similar to what I am expericeing. There is no reason why the original SQL query I created shouldn' t work. I thought that maybe there was a trick that I didn' t know. I did a Google on SQL and visited 20 sites today. Nobody had anything to say about this problem. Not even Microsoft. Like you said, you learned to live with it. That was probably a smart, stressless descision to make. I' m still going to search for an answer to this crazy problem. It' s a real weakness that you would think Microsoft would have tackled by now!!! If I find the answer, I' ll post it to this posting for you. So blank-respond and click on " Notify me via-email when someone replies" if you want to know.
Thanks
LLL
| |
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: A unique SQL problem??? - 8/18/2002 23:40:42
I don' t know.... Today it is working.... I don' t understand what is going on but now I can search by Disk, Compact or Case and the record appears. I do not know what happened. But it works!!! Hmmmmmmmmmm...
LLL
| |
|
|
|
Vince from Spain
Posts: 658
From: Madrid Spain
Status: offline
|
RE: A unique SQL problem??? - 8/19/2002 13:34:36
Hi Bobby,
I did a little search routine a short while ago that would take account of multiple words where you wanted ALL of them to appear, but not necessarily in the order given. The whole thing is quite long, but I' ll see if I can chop it into some kind of pseudocode that will give the idea.
<%
sText = request.form(" searchphrase" )
aText = Split(sText) ' so split searchphrase into an array on the spaces
sSQL = " SELECT * FROM myTable WHERE "
for rowcounter=0 to UBOUND(aText)
sSQL=sSQL & " myField LIKE ' %" &aText(rowcounter)&" %' "
if rowcounter<UBOUND(aText) then sSQL=sSQL & " AND "
next
' Now go submit the query
%>
This isn' t meant to be runable code, but hopefully it will give the idea.
LLL, sounds like gremlins, but normally I would say that the search you are making should work under MS SQL. It sounds like the second % was being missed off, as that would be expected behaviour if your search was . . .
SELECT * FROM PartsDatabase WHERE (Product Like ' %::Keyword::' )
Vince
< Message edited by Vince from Spain -- 8/18/2002 1:37:49 PM >
| |
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: A unique SQL problem??? - 8/19/2002 16:02:00
Vince,
Good code. This algroithm worked for you??? On the page that I was working on, the DRW did most of the work. I always curse when I use the DRW for anything that is not simple. At first I thought it was simple. Silly me. Your remedy demonstrated hand-coding in .asp.
This is what I think happened:
Did you ever notice how when you save a page in FP, inside a large site that sometimes things get changed??? I' ve seen fonts change, colors change, things dissapear, code inside the webbot area get erased??? 
What I did:
I wiped OUT the entire display page and rebuilt it. There must have been something not kosher in the page itself. I have done this 1 million times in my career so I don' t think it was me. The original page I played with. I' m admitting it. When I saved it, something was saved incorrectly. So when I rebuilt the page from scratch, I used the exact SQL Query that I mentioned in the very beginning of this post. Once it was done, I tested it in PWS. It worked great. I uploaded it to my server and it worked great. So when something all of a sudden starts working for no apparent reason, I have to think FP and it' s " save" routine had someting to do with it. That' s only an educated guess. 
LLL
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
