I currently have a few ASP pages, each filtering the same table using a different field. Since this is not very elegant, I'd like to dynamically set the filtering criterion (e.g. with a querystring). Unfortunately the Simple view of the RS behavior does not allow WHERE to be set dynamically. I imagine that would require the advanced dialog box. Can someone suggest a strategy, tutorial, or any suggestions as to how this can be achieved WITHOUT generating the UD4 red alerts associated with handcoding?

Thanx!

At the top of each column place an image or som text, something like:

<a href ="ThisPage.asp?SortBy=ThisFieldName ASC>Ascending</a>

Set up links for Ascending and Descending for each column.

Then in the advanced view, in the SQL box:

SELECT Whatever, WhateverElse, AndTheRest FROM TableName
ORDER BY varSortBy

Then add a variable:
Name - varSortBy
Default Value - YourFieldName ASC (select a field you want as a default sort)
Run Time Value - Request.QueryString(SortBy)

Regards

Owen.

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

Thanks a lot! [BTW, that tutorial is pretty cool.]

I have another, related question (for anyone, not necessarily Owen):

I also have two nearly identical pages, the difference being one is unfiltered (no WHERE), the other is filtered, both using the standard UD recordset behavior (Simple view). It'd be nice that a single page goes for the unfiltered set when, e.g., abc.asp?ID= or ID=0.

Now I notice in UD's SQL statement, WHERE is either absent or its built in (i.e. not a separate string one can toggle on or off). Is there any way around this?

---------
rsStuff.Source = "SELECT ID FROM Table WHERE CatID = " + Replace(rsStuff__MMColParam, "'", "''" + ""
---------

Edited by - akc on 27 Jan 2002 06:27:24

Yup:

If Request.QueryString("ID" = "" Then
strSQL = "SELECT * FROM TableName"
Else
strSQL = "SELECT * ROM TableName WHERE ID = " & Request.QueryString("ID"
End If


Then:

RecordsetName.Source = strSQL


Regards

Owen.

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

I combined Owen's tip with an idea I glimpsed from Tom Muck's Sort Repeat Region extension. The latter gave me a simple idea about avoiding those annoying (but useful) UD <font color=red>RED FLAGS</font id=red>:

In Advanced SQL view, I used

<font color=blue>SELECT * FROM TableName sql_option</font id=blue>

Then in the variables section, I gave sql_option a default value of

<font color=blue>WHERE ID = 0</font id=blue> with run-time value set to <font color=blue>sql_value</font id=blue>.

UD then creates the following SQL statement:
<font color=blue><pre id=code><font face=courier size=2 id=code>rs.Source = "SELECT * FROM TableName " + Replace(rs__sql_option, "'", "''" + ""</font id=code></pre id=code></font id=blue>

All very legit according to UD. Then it's a matter of switching the content of sql_option depending on a querystring:

<font color=blue><pre id=code><font face=courier size=2 id=code>If CSTR(Request.Querystring("ID") &lt;&gt; "" Then
sql_value = "WHERE ID = " & CSTR(Request.Querystring("ID")
Else
sql_value = " " ' single space
End If</font id=code></pre id=code></font id=blue>

And that made my day.

Edited by - akc on 28 Jan 2002 04:19:58

Well done.

One thing though, you and lots of other people seem to be obsessed with keeping UD happy and not prompting those little exclamation marks.

If it works why worry? UD's code is not "better" than hand written code. In fact it's often quite inneficient, it often adds lots of stuff that isn't necessary, because it produces general purpose "one size fits all code". For example:

Replace(rs__sql_option, "'", "''" + ""

In this instance it's entirely unnecessary, we know all the parameters that are coded, they are all integers, so we know there aren't going to be any problem single quotes. The only time this is really necessary is for user entered text strings.

With code, the less steps the better, the pages will execute faster. So if you hand code stuff that has less steps and less lines of code than a UD behaviour it's an improvement. Think of the red exclamations as merrit marks. <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Regards

Owen.

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

Quite true, certainly the efficiency factor is less with UD. For small personal projects, though, it's less of a concern.

And for beginners like me the red alerts are annoying because we still depend on the SB's user interfaces quite a bit.