DRW dieted and modified (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

pmagas -> 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!



BeTheBall -> 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"



pmagas -> RE: DRW dieted and modified (5/10/2007 9:03:28)

Yes, that's the db field name. I made those changes, but still it gives me the same error. [:'(]

Another question that I haven't been able to find the answer for: when do you use [] rather than ':: ::'?

Thank you for your help!! This one has me stumped.
Cheers!



BeTheBall -> 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.



pmagas -> 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



BeTheBall -> 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.



pmagas -> RE: DRW dieted and modified (5/10/2007 15:20:01)

This is what it gives me:
quote:

SELECT * FROM MF2analysis WHERE ([Type of Units Desc] = '::Type of Units Desc::' AND [Type] LIKE '%2-Family%' AND [Listing Price] >= ::From:: AND [Listing Price] <= ::To:: AND [Unit 1-# of Bedrooms] >= ::Bedrooms::) ORDER BY [Net Income] DESC,[Listing Price] DESC


Does it tell you anything? I don't understand what it means. [&:]

Cheers!



BeTheBall -> 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?



pmagas -> RE: DRW dieted and modified (5/10/2007 16:00:38)

Type is a text field. Without the response/write lines, my error message is
quote:

Database Results Error
Description: No value given for one or more required parameters.
Number: -2147217904 (0x80040E10)
Source: Microsoft JET Database Engine


When I include the lines with the response/write code you gave me, this is the entire response I get:
quote:

SELECT * FROM MF2analysis WHERE ([Type of Units Desc] = '::Type of Units Desc::' AND [Type] LIKE '%2-Family%' AND [Listing Price] >= ::From:: AND [Listing Price] <= ::To:: AND [Unit 1-# of Bedrooms] >= ::Bedrooms::) ORDER BY [Net Income] DESC,[Listing Price] DESC
- no error message too. No results, either.

Thanks for your help with this! The hair loss is becoming a problem for me. [;)]

Cheers!



BeTheBall -> 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"



pmagas -> 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="



BeTheBall -> 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&"%')"



pmagas -> RE: DRW dieted and modified (5/10/2007 17:11:43)

quote:

Database Results Error
Description: No value given for one or more required parameters.
Number: -2147217904 (0x80040E10)
Source: Microsoft JET Database Engine


[:'(] [sm=yikes.gif]



Spooky -> RE: DRW dieted and modified (5/10/2007 18:02:49)

And if you enter an actual value in the SQL string?



pmagas -> 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.....



BeTheBall -> 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?



pmagas -> 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!



BeTheBall -> 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"



pmagas -> RE: DRW dieted and modified (5/10/2007 21:14:17)

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?

I can't thank you enough!!!!!! [:)][:D][sm=boogie.gif]

Have a spectacular evening!!!!! Cheers!



BeTheBall -> 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.



pmagas -> RE: DRW dieted and modified-new problem (5/10/2007 22:12:37)

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.

It shouldn't be a problem with the _fpclass files because it's in a folder with 'old' searches that are working OK. I think it has to do with our modification of Type & Area to let me offer multiple choices. But I can't figure out how to fix the darn thing. [:o] Here it is if you'd like to see it in action.

I just found yet another problem. <sadness> When you choose all types or all areas, it doesn't use the price criteria. It shows them on the top of the page but the results aren't limited by the from and to prices.

This might be tricky to figure out. Thank you for your patience and continued help. Cheers!



BeTheBall -> 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.



pmagas -> 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,



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.09375