|
| |
|
|
mrmcse25
Posts: 46
Joined: 10/3/2003
Status: offline
 |
Default Values - 10/3/2003 18:08:49
I have two questions about querying a database with a form.
Question # 1: I've set this up and am using two search criteria form fields to search by. I don't want to display all information when loading the page. I know that if you put NULL in the field if it is a single field you can do this, but how do you do it with multiple search fields and still be able to type in and search?
Question # 2: I'm using 'contains' in my search field. How do I search for multiple words in that field?
|
|
|
|
mrmcse25
Posts: 46
Joined: 10/3/2003
Status: offline
|
RE: Default Values - 10/6/2003 9:59:20
#1 Worked great! Thx!!!
#2 FP doesn't seem to want to let me add the code. I've removed the meta tag for the fp editor but it keeps coming back. Will putting my site on the spooky diet fix this?
| |
|
|
|
mrmcse25
Posts: 46
Joined: 10/3/2003
Status: offline
|
RE: Default Values - 10/6/2003 13:47:36
Ok. I trimmed it, and it worked great! But after I added the new code for the multiple word search I get this error when displaying the results? any ideas?
Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/kbissues_results.asp, line 54, column 60
fp_sQry="SELECT * FROM ISSUES WHERE (IM_SUMMARY LIKE '%"&IM_SUMMARY&"%' AND IM_DESCRIPTION LIKE '%"&IM_DESCRIPTION&"%') ORDER BY ::IM_RECEIVED_DATE::"
Here is my code:
Search=Replace(Request("Search")," ","%")
fp_sQry="SELECT * FROM ISSUES WHERE (IM_SUMMARY LIKE '%"&IM_SUMMARY&"%' AND IM_DESCRIPTION LIKE '%"&IM_DESCRIPTION&"%') ORDER BY ::IM_RECEIVED_DATE:: DESC"
| |
|
|
|
mrmcse25
Posts: 46
Joined: 10/3/2003
Status: offline
|
RE: Default Values - 10/8/2003 9:16:27
I'm using the code just as you have it above. What I'm wanting to accomplish is to search by either or of those fields, and be able to use multiple words to search by? Sorry, I'm a newbie at the behind the seens coding stuff accept for html.
| |
|
|
|
mrmcse25
Posts: 46
Joined: 10/3/2003
Status: offline
|
RE: Default Values - 10/13/2003 9:40:08
Hey Spooky, You did you have any other suggestions on this one?
| |
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Default Values - 10/13/2003 11:05:53
First a question. A few posts up, you said:
quote:
Here is my code:
Search=Replace(Request("Search")," ","%")
fp_sQry="SELECT * FROM ISSUES WHERE (IM_SUMMARY LIKE '%"&IM_SUMMARY&"%' AND IM_DESCRIPTION LIKE '%"&IM_DESCRIPTION&"%') ORDER BY ::IM_RECEIVED_DATE:: DESC"
Spooky then asked,
quote:
Where are you using the "search" parameter as in the example below?
Search=Replace(Request("Search")," ","%")
fp_sQry="SELECT * FROM ISSUES WHERE (IM_SUMMARY LIKE '%"&Search&"%' AND IM_DESCRIPTION LIKE '%"&Search&"%') ORDER BY ::IM_RECEIVED_DATE:: DESC"
You said you were using the code just as Spooky wrote it, but above where you said here is my code, does not match the code Spooky displayed in his post. Which are you using?
Now a suggestion, I do not believe you want to surround your ORDER BY parameter with "::". It appears IM_RECEIVED_DATE is a field from your db, not a field from your form. Only form fields are surrounded by "::". Change Spooky's code to this and see if that helps:
Search=Replace(Request("Search")," ","%")
fp_sQry="SELECT * FROM ISSUES WHERE (IM_SUMMARY LIKE '%"&Search&"%' AND IM_DESCRIPTION LIKE '%"&Search&"%') ORDER BY IM_RECEIVED_DATE DESC"
| |
|
|
|
mrmcse25
Posts: 46
Joined: 10/3/2003
Status: offline
|
RE: Default Values - 10/13/2003 12:42:27
quote:
Search=Replace(Request("Search")," ","%")
fp_sQry="SELECT * FROM ISSUES WHERE (IM_SUMMARY LIKE '%"&Search&"%' AND IM_DESCRIPTION LIKE '%"&Search&"%') ORDER BY IM_RECEIVED_DATE DESC"
Ok. Checked it again. Found one thing was that FP was putting an "amp" inplace of the ampersand when I pasted, so I changed it manually. it now looks exactly like this:
Search=Replace(Request("Search")," ","%")
fp_sQry="SELECT * FROM ISSUES WHERE (IM_SUMMARY LIKE '%"&Search&"%' AND IM_DESCRIPTION LIKE '%"&Search&"%') ORDER BY IM_RECEIVED_DATE DESC"
Now it pulls back buy only pulls back the records that IM_SUMMARY was left blank? Any ideas?
| |
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Default Values - 10/13/2003 13:16:58
Did you not say your search form had two fields? If so, the only field that is being used in your query is the one titled "Search". Please clarify how many form fields are in your search form and their names.
Just an FYI
quote:
Found one thing was that FP was putting an "amp" inplace of the ampersand when I pasted
It's always best to paste into Notepad or some other text editor, then to FP. Otherwise, you will get the problem you mentioned.
< Message edited by betheball -- 10/13/2003 1:19:00 PM >
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
mrmcse25
Posts: 46
Joined: 10/3/2003
Status: offline
|
RE: Default Values - 10/13/2003 14:06:11
i have two form fields I'm searchin buy. IM_SUMMARY and IM_DESCRIPTION. Guess I can't do &Search& for both?
| |
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Default Values - 10/13/2003 14:13:35
quote:
Guess I can't do &Search& for both?
Correct.
Try this:
Search=Replace(Request("IM_SUMMARY")," ","%")
Search2=Replace(Request("IM_DESCRIPTION")," ","%")
fp_sQry="SELECT * FROM ISSUES WHERE (IM_SUMMARY LIKE '%"&Search&"%' AND IM_DESCRIPTION LIKE '%"&Search2&"%') ORDER BY IM_RECEIVED_DATE DESC"
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
mrmcse25
Posts: 46
Joined: 10/3/2003
Status: offline
|
RE: Default Values - 10/13/2003 14:52:33
ok. I put it in like this:
Search=Replace(Request("IM_SUMMARY")," ","%")
Search2=Replace(Request("IM_DESCRIPTION")," ","%")
fp_sQry="SELECT * FROM ISSUES WHERE (IM_SUMMARY LIKE '%"&Search&"%' AND IM_DESCRIPTION LIKE '%"&Search2&"%') ORDER BY IM_RECEIVED_DATE DESC"
Still not working. Wierd thing is why does it pull back articles where Summary was left blank? Any other suggestions?
| |
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Default Values - 10/13/2003 15:02:37
Try this. Get rid of the 2 lines dealing with "Search=" and "Search2=". Then change your SQL to:
fp_sQry="SELECT * FROM ISSUES WHERE (IM_SUMMARY LIKE '%::IM_SUMMARY::%' AND IM_DESCRIPTION LIKE '%::IM_DESCRIPTION%') ORDER BY IM_RECEIVED_DATE DESC"
A blank form field should be treated as a "%" by default. One question, are you sure you want an "AND" and not an "OR"???
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
mrmcse25
Posts: 46
Joined: 10/3/2003
Status: offline
|
RE: Default Values - 10/13/2003 15:31:59
Ok. <Kicking myself in the butt> oopps I was working with the wrong file. No wonder. duhhh. Ok. I put it in the orignal way you suggested:
Search=Replace(Request("IM_SUMMARY")," ","%")
Search2=Replace(Request("IM_DESCRIPTION")," ","%")
fp_sQry="SELECT * FROM ISSUES WHERE (IM_SUMMARY LIKE '%"&Search&"%' OR IM_DESCRIPTION LIKE '%"&Search2&"%') ORDER BY IM_RECEIVED_DATE DESC"
I'm now getting a Database Results Error: mismatched parameter delimiters error when I do the search.
| |
|
|
|
mrmcse25
Posts: 46
Joined: 10/3/2003
Status: offline
|
RE: Default Values - 10/13/2003 17:16:43
output:
SELECT * FROM ISSUES WHERE (IM_SUMMARY LIKE '%429%' OR IM_DESCRIPTION LIKE '%%') ORDER BY IM_RECEIVED_DATE DESC
| |
|
|
|
mrmcse25
Posts: 46
Joined: 10/3/2003
Status: offline
|
RE: Default Values - 10/13/2003 17:28:54
IM_SUMMARY is text and IM_DESCRIPTION is text. I want to be able to search by either or with mulitple words. Do they need default values?
On the previous page which returns to this one is my search form. Here are the field values:
<%=Request("IM_SUMMARY")%>
<%=Request("IM_DESCRIPTION")%>
| |
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Default Values - 10/13/2003 19:52:24
quote:
Search=Replace(Request("IM_SUMMARY")," ","%")
Search2=Replace(Request("IM_DESCRIPTION")," ","%
Try deleting the space between the double quotation marks, i.e.,
Search=Replace(Request("IM_SUMMARY"),"","%")
Search2=Replace(Request("IM_DESCRIPTION"),"","%
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Default Values - 10/13/2003 20:19:49
Mike, what exactly do you want to have happen? What kind of information appears in IM_SUMMARY and IM_DESCRIPTION? Do you want all results where the search term appears in IM_SUMMARY or IM_Description? If so, you can do it by deleting one text box from your form and renaming the other "Search". Then change your SQL to like you had it before with the exception of replacing the AND with OR:
Search=Replace(Request("Search")," ","%")
fp_sQry="SELECT * FROM ISSUES WHERE (IM_SUMMARY LIKE '%"&Search&"%' OR IM_DESCRIPTION LIKE '%"&Search&"%') ORDER BY IM_RECEIVED_DATE DESC"
If this is off-base, let us know.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
mrmcse25
Posts: 46
Joined: 10/3/2003
Status: offline
|
RE: Default Values - 10/14/2003 8:45:56
You are a genius! That should work, but how do I structure the query from the search form to search both and pass. Here is what I have currently. Also, do I have to change the "Search" in the
Search=Replace(Request("Search")," ","%")
Here is my current search form code:
<form BOTID="0" METHOD="POST" action="kbissues_results.asp">
<table BORDER="0">
<tr>
<td><font color="#800000"><b>Search by Summary:</b></font></td>
<td><input NAME="IM_SUMMARY" VALUE="<%=Request("IM_SUMMARY")%>" size="20"></td>
</tr>
<tr>
<td><font color="#800000"><b>Search by Description:</b></font></td>
<td>
<input NAME="IM_DESCRIPTION" VALUE="<%=Request("IM_DESCRIPTION")%>" size="20"></td>
</tr>
</table>
<p><input TYPE="submit" value="Search"><input TYPE="Reset"><!--webbot bot="SaveAsASP" clientside suggestedext="asp" preview=" " startspan --><!--webbot bot="SaveAsASP" endspan --></p>
</form>
And for what I'm trying to do is to search two fields in a "ticket tracking" database (Summary and Description) and display any tickets where the keywords typed in are in either one of those fields. Hope this is enough info..
| |
|
|
|
mrmcse25
Posts: 46
Joined: 10/3/2003
Status: offline
|
RE: Default Values - 10/14/2003 8:53:11
Don't worry bout this one guys! Figured it out!! I just had to read your instruction a bit closer to change the Search field to Search.. Worked like a charm!! Thanks for all of both of your helps!
| |
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Default Values - 10/14/2003 10:30:24
quote:
You are a genius!
Hardly. Just got lucky in suspecting that what you were asking for was not what you really wanted/needed. As a sidenote, I created a page similar to yours on my own site and was able to duplicate the "mismatched parameter delimiters " error. As long as I entered something or nothing in both search boxes, I was fine. If I left one blank, but not the other it threw the error. However, when I deleted the "Search" and "Search2" variables and used the SQL shown below I was error free. Anyway, glad you got what you need.
fp_sQry="SELECT * FROM ISSUES WHERE (IM_SUMMARY LIKE '%::IM_SUMMARY::%' AND IM_DESCRIPTION LIKE '%::IM_DESCRIPTION%') ORDER BY IM_RECEIVED_DATE DESC"
< Message edited by betheball -- 10/14/2003 10:31:08 AM >
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
 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 
ky 