|
| |
|
|
pmagas
Posts: 367
Joined: 3/26/2002
From: St. Louis MO USA
Status: offline
 |
DRW dieted and modified - 5/9/2007 21:00:36
Hi, all! I'm modifying a search that I set up quite some time ago and have modified along the way. I believe it's close, but I keep getting this error and I can't figure out why. The error is quote:
Database Results Error
Description: No value given for one or more required parameters.
Number: -2147217904 (0x80040E10)
Source: Microsoft JET Database Engine
One or more form fields were empty. You should provide default values for all form fields that are used in the query.
The query is as follows:
quote:
<!--#include file="_fpclass/fpdblib.inc"-->
<%
myStr = Request.Form("Type")
If Instr(myStr,",") > 0 Then
arrType = Replace(myStr,", ","%' OR [Type of Units Desc] LIKE '%")
Else
arrType = myStr
End If
myStr = Request.Form("Area")
If Instr(myStr,",") > 0 Then
arrArea = Replace(myStr,", ","%' OR [Area Desc] LIKE '%")
Else
arrArea = myStr
End If
fp_sQry="SELECT * FROM MF2analysis WHERE ('::Type of Units Desc::' = '::Type of Units Desc::' AND [Type] LIKE '%"&arrType&"%' AND '::Area Desc::' = '::Area Desc::' AND [Area] LIKE '%"&arrArea&"%' AND [Listing Price] >= ::From:: AND [Listing Price] <= ::To:: AND [Unit 1-# of Bedrooms] >= ::Bedrooms::) ORDER BY [Net Income] DESC,[Listing Price] DESC"
fp_sDefault="Type='2-family'&Area='Affton'&From=75000&To=500000&Bedrooms=1"
fp_sNoRecords="Sorry - no properties match your criteria today. Please try another search or request a Custom Property Search."
fp_sDataConn="mf2analysis"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=5
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=31
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
The IF statements before the query are to enable me to use multiple choices for those. The database is downloaded, so I can't change the field names to not include spaces.  (Those are 'Type of Units Desc' and 'Area Desc') I've tried the default line with just quote:
fp_sDefault="Type=&Area=&From=75000&To=500000&Bedrooms=1" and quote:
fp_sDefault="Type=%&Area=%&From=75000&To=500000&Bedrooms=1" and got the same error message each time.
Thank you for any suggestions you might offer!
Cheers!
_____________________________
Penny
humble webmaster of
We Sell St. Louis
http://www.WeSellStLouis.com
http://www.MarkSellsStLouis.net
http://www.St-Louis-Mo.info
http://www.NealSellsStLouis.com
|
|
|
|
BeTheBall
Posts: 6365
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: DRW dieted and modified - 5/10/2007 0:31:20
fp_sQry="SELECT * FROM MF2analysis WHERE ('::Type of Units Desc::' = '::Type of Units Desc::' AND [Type] LIKE '%"&arrType&"%' AND '::Area Desc::' = '::Area Desc::' AND [Area] LIKE '%"&arrArea&"%' AND [Listing Price] >= ::From:: AND [Listing Price] <= ::To:: AND [Unit 1-# of Bedrooms] >= ::Bedrooms::) ORDER BY [Net Income] DESC,[Listing Price] DESC"
The bold part is clearly wrong. If the db field is Type of Units Desc, then the above should be:
fp_sQry="SELECT * FROM MF2analysis WHERE ([Type of Units Desc] = '::Type of Units Desc::' AND [Type] LIKE '%"&arrType&"%' AND [Area Desc] = '::Area Desc::' AND [Area] LIKE '%"&arrArea&"%' AND [Listing Price] >= ::From:: AND [Listing Price] <= ::To:: AND [Unit 1-# of Bedrooms] >= ::Bedrooms::) ORDER BY [Net Income] DESC,[Listing Price] 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.
| |
|
|
|
BeTheBall
Posts: 6365
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: DRW dieted and modified - 5/10/2007 9:53:06
The :: :: is a FrontPage thing. It replaces Request.Form or Request.Querystring used in classic ASP. Generally, the :: :: is used around a form field name. The [] are needed when a db field has spaces or its name is a reserved word.
As for the problem at hand, I would suggest you take pieces out of the SQL one at a time to see what the offending part is.
For example, what happens if you remove:
AND [Unit 1-# of Bedrooms] >= ::Bedrooms::)
If you get the same error, put the above back in and remove something else.
_____________________________
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.
| |
|
|
|
pmagas
Posts: 367
Joined: 3/26/2002
From: St. Louis MO USA
Status: offline
|
RE: DRW dieted and modified - 5/10/2007 13:18:47
Thank you, Duane! What I ended up doing was started with the search 'empty' - ie, no criteria. And that worked OK. Then I added them on one at a time. Where it first breaks down is when I add criteria for 'Type.' And because it's telling me I haven't given it a default value, it occurs to me that this line is wrong:
quote:
fp_sDefault="Type='2-Family'&From=75000&T0=500000&Bedrooms=1"
I've tried all of the variations for I can for the Type criteria, but I wonder is 'Type' the problem?
Should it be quote:
fp_sDefault="arrType='2-Family'&From=75000&T0=500000&Bedrooms=1" or quote:
fp_sDefault="[Type of Units Desc]='2-Family'&From=75000&T0=500000&Bedrooms=1"
???
Yet another question - in the default line, if a value is text rather than a number, should it be surrounded by single quotes?
Thank you SO much for all your help!!!
Cheers
_____________________________
Penny
humble webmaster of
We Sell St. Louis
http://www.WeSellStLouis.com
http://www.MarkSellsStLouis.net
http://www.St-Louis-Mo.info
http://www.NealSellsStLouis.com
| |
|
|
|
BeTheBall
Posts: 6365
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: DRW dieted and modified - 5/10/2007 14:52:18
The default line is not the problem. However, to answer your question, no you do not differentiate between numeric values and text in that line.
What do you see if you change the following two lines:
BOTID=0
%>
to
BOTID=0
response.write fp_sQry
response.end
%>
This isn't a fix, but rather a diagnostic check to see what SQL is being generated.
_____________________________
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: 6365
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: DRW dieted and modified - 5/10/2007 15:46:07
In the database, what kind of datatype is Type, is it numeric or text? What is the full error message you are getting?
_____________________________
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: 6365
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: DRW dieted and modified - 5/10/2007 16:35:46
OK, but what is your error message if your only criteria is the portion dealing with Type, for example:
fp_sQry="SELECT * FROM MF2analysis WHERE ([Type] LIKE '%"&arrType&"%') ORDER BY [Net Income] DESC,[Listing Price] 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.
| |
|
|
|
pmagas
Posts: 367
Joined: 3/26/2002
From: St. Louis MO USA
Status: offline
|
RE: DRW dieted and modified - 5/10/2007 16:53:34
Thanks for your patience.
The response I get with the above is:
quote:
Database Results Error
Description: No value given for one or more required parameters.
Number: -2147217904 (0x80040E10)
Source: Microsoft JET Database Engine
I used this as the default:
quote:
fp_sDefault="Type="
_____________________________
Penny
humble webmaster of
We Sell St. Louis
http://www.WeSellStLouis.com
http://www.MarkSellsStLouis.net
http://www.St-Louis-Mo.info
http://www.NealSellsStLouis.com
| |
|
|
|
BeTheBall
Posts: 6365
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: DRW dieted and modified - 5/10/2007 16:59:13
OK, let's shorten a bit more. What about:
fp_sQry="SELECT * FROM MF2analysis WHERE ([Type] LIKE '%"&arrType&"%')"
_____________________________
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.
| |
|
|
|
pmagas
Posts: 367
Joined: 3/26/2002
From: St. Louis MO USA
Status: offline
|
RE: DRW dieted and modified - 5/10/2007 18:09:01
I hadn't thought of that! I tried this, both with and without the default line:
quote:
fp_sQry="SELECT * FROM MF2analysis WHERE ([Type] LIKE '2-Family')"
I also tried it without the single quotes around 2-Family
In all cases, I get this message:
quote:
Database Results Error
Description: No value given for one or more required parameters.
Number: -2147217904 (0x80040E10)
Source: Microsoft JET Database Engine
Color me confused.....
_____________________________
Penny
humble webmaster of
We Sell St. Louis
http://www.WeSellStLouis.com
http://www.MarkSellsStLouis.net
http://www.St-Louis-Mo.info
http://www.NealSellsStLouis.com
| |
|
|
|
BeTheBall
Posts: 6365
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: DRW dieted and modified - 5/10/2007 18:15:27
That causes some serious doubt as to whether the table really has a field named Type. Are you 100% sure that the db you are testing against has a field named Type within a table named MF2analysis?
_____________________________
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.
| |
|
|
|
pmagas
Posts: 367
Joined: 3/26/2002
From: St. Louis MO USA
Status: offline
|
RE: DRW dieted and modified - 5/10/2007 18:45:00
Argg.... I get the 'duh' award. No - the field is Type of Units Desc and when I use quote:
fp_sQry="SELECT * FROM MF2analysis WHERE ([Type of Units Desc] LIKE '2-Family')"
it gives me results.
I continue to get confused about my little bit at the top that allows me to use multiple choices. So now that we know this part works, where do I take it? I think my brain needs a little break and sustenance. Thank you SO much for your help with this!!!
Cheers!
_____________________________
Penny
humble webmaster of
We Sell St. Louis
http://www.WeSellStLouis.com
http://www.MarkSellsStLouis.net
http://www.St-Louis-Mo.info
http://www.NealSellsStLouis.com
| |
|
|
|
BeTheBall
Posts: 6365
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: DRW dieted and modified - 5/10/2007 20:46:59
OK, then:
fp_sQry="SELECT * FROM MF2analysis WHERE ([Type of Units Desc] LIKE '%"&arrType&"%' AND [Area Desc] LIKE '%"&arrArea&"%' AND [Listing Price] >= ::From:: AND [Listing Price] <= ::To:: AND [Unit 1-# of Bedrooms] >= ::Bedrooms::) ORDER BY [Net Income] DESC,[Listing Price] 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.
| |
|
|
|
BeTheBall
Posts: 6365
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: DRW dieted and modified - 5/10/2007 21:19:27
quote:
Duane - You rock!!! It works!! I left out the line with the defaults - is that OK? It seems to work fine, so I'm inclined just to keep it out. But will I regret it later?
It is easy enough to test. Run a few searches leaving one or more form fields blank. If you get an error on a blank field, then that field probably needs a default value.
_____________________________
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: 6365
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: DRW dieted and modified-new problem - 5/11/2007 11:26:20
quote:
I have a new problem. While that search works, it stops after the first page of results. It shows that I should have 169 pages (or some other large number) but when I click Next or End, it gives me my no records response. Also, I have it repeating the criteria at the top of each page (for the users reference) and it seems to have lost the Type and Area on subsequent pages.
Now I remember why your SQL included:
'::Type of Units Desc::' = '::Type of Units Desc::' and '::Area Desc::' = '::Area Desc::'
It was so those values would be retained and passed from page to page. So, change your SQL to:
fp_sQry="SELECT * FROM MF2analysis WHERE ([Type of Units Desc] LIKE '%"&arrType&"%' AND [Area Desc] LIKE '%"&arrArea&"%' AND [Listing Price] >= ::From:: AND [Listing Price] <= ::To:: AND [Unit 1-# of Bedrooms] >= ::Bedrooms::) AND '::Type of Units Desc::' = '::Type of Units Desc::' and '::Area Desc::' = '::Area Desc::' ORDER BY [Net Income] DESC,[Listing Price] DESC"
Doubt this will help with the second issue you were seeing. I'll have to get back with you on that.
_____________________________
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.
| |
|
|
|
pmagas
Posts: 367
Joined: 3/26/2002
From: St. Louis MO USA
Status: offline
|
RE: DRW dieted and modified-new problem - 5/11/2007 13:42:18
Duane - Thank you. I modified yours ever so slightly - I moved the close parenthesis to just before the ORDER BY. Did you mean for it to have a second set pf parentheses?
It works, but has the same problems as before - still no second page. And it isn't accepting all of my criteria. (I'm leaving the zip out at this point, even though it's in the form.) When I choose just one type and just one area, it works (still no 2nd page, though.) But when I expand either or both type and area to include more than one, it's losing the price and bedrooms limitations. That is, it's giving me all properties. But it's a little trickier than that as I study it.
This is a doozy, isn't it? It doesn't seem as though it should be this difficult. I can't thank you enough for your help!
Warm regards,
_____________________________
Penny
humble webmaster of
We Sell St. Louis
http://www.WeSellStLouis.com
http://www.MarkSellsStLouis.net
http://www.St-Louis-Mo.info
http://www.NealSellsStLouis.com
| |
|
 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 
k¥ 
