<font face='Verdana'><font size=2>
I have a problem with the query builder in Ultradev.

I have a Real Estate search page which has just 2 input boxes at the moment; Town and bedrooms. I want to be able to search the Database on either field.

I can do this by hand coding the ASP page, it's just a matter of checking for user input and writing some IF THEN statements to build the query.

I'm new to Ultradev but I managed to figure out that to do this in Ultradev, you need to use the Advanced Recordset option. Then define a Variable for the last part of the SQL statement. This part of the statement can then be built in the normal way.

<b>
SELECT *
FROM dbo.houses
MM_SQL
</b>

Where <b>MM_SQL</b> is the Ultradev variable that is populated by my own piece of code.

It all works fine if I search on the number of rooms, but when I try to search on a town I get a vague ODBC error with the Ultradev Test button and in the live page.

I've checked the output of the SQL statement and it works fine in my SQL client program. Substituting the variable with the actual SQL works fine, it just doesn't like using the variable to pass the code over.

Works with the MM variable
<b>
SELECT *
FROM dbo.houses
WHERE bedrooms = 1
</b>

Doesn't work with the MM variable
<b>
SELECT *
FROM dbo.houses
WHERE town LIKE 'London'
</b>

Has anyone seen this one before?

</font id='Verdana'></font id=size2>

well, I may be wrong, but I don't think the query builder is meant to handle coding like that. it's a bit too advanced for it.

I personally would suggest handcoding it... unfortunately this would negate the use of the live preview...

Joel Martinez [ ]
----------
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/

A great oportunity to blow my own trumpet, take a look at the link below:

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

<font face='Verdana'><font size=2>
I'm not sure if the Query Builder was designed to handle this, but from what I've read a lot of people are using this method. I just can't seem to solve this one, it looks like a bug to me.

I got 'round this in the end by manipulating the forms values on the submit page.

<font color=red><b>WHERE town LIKE 'MM_Town'</b></font id=red>
Now if town is submitted as <b>Any</b> the submit value is <b>%</b>. (which selects all towns)

<font color=red><b>AND bedrooms BETWEEN MM_Beds1 AND MM_Beds2</b></font id=red>
If 3 beds is submitted, both the lower and upper limit are set to 3, which selects only 3 bed properties. The <b>Any</b> value for bedrooms is now <b>99</b>. A small piece of code at the start of the page checks for a 99 value. If it finds it it just sets the lower limit to 1, and the upper limit to 99. ie. All properties having BETWEEN 1 AND 99 bedrooms.

<b>
SELECT *
FROM dbo.houses
WHERE town LIKE 'MM_Town'
AND bedrooms between MM_Beds1 and MM_Beds2
AND price between MM_Min and MM_Max
ORDER BY price ASC
</b>

</font id='Verdana'></font id=size2>