Dynamic Query (JavaScript ASP)

[Reesy]

I want to create a page that displays a number of columns selected from an Oracle database. (That's easy enough)

The twist being I need the select statement, where clause and the display to be 'user defined'.

For example the user selects the filtering criteria from dropdown boxes containing the columns on the table, an operator and a value i.e. they may choose to view all records on the ACCOUNTS table with the column CREDIT_VALUE greater than £5,000 and the column TRADING_STATUS = 1.

Can anyone give me any pointers (if this is actually possible) - I am having problems trying to work out
1) how to construct the query
2) how to display the values from the recordset because the field names are variable.

(note: I am using Javascript / jscript)

Any help would be greatly appreciated! Blood, Sweat But No Tears!

 

[disord3r]

Tweak as necessary, but this has come in very handy for me:

--------
query = "select " & query_select & " from " & query_from

if query_where then
query = query & " where " & query_where_this & query_where_op & "'" & query_where_that & "'"
end if

if query_order then
query = query & " order by " & query_order
end if

if query_group then
query = query & " group by " & query_group
end if
--------

if these are your variables and values (set by the submitting form)....
query_select = "field1"
query_from = "table1"
query_where_this = "this"
query_where_op = "="
quere_where_that = "that"
query_order = "field1 desc"
query_group = "field1"

...then this is the resulting query string:
select field1 from table1 where this='that' group by field1 order by field1 desc

If these are your values and variables...
query_select = "field1"
query_from = "table1"
query_where_this = "this"
query_where_op = ">"
query_where_that = "that"

... then this is the resulting query string:
select field1 from table1 where this>'that'

 

[disord3r]

Doh. small change,

query = "select " & query_select & " from " & query_from

if query_where_this <> &quot;&quot; and query_where_op <> &quot;&quot; and query_where_that <> &quot;&quot; then
query = query & &quot; where &quot; & query_where_this & query_where_op & &quot;'&quot; & query_where_that & &quot;'&quot;
end if

if query_group <> &quot;&quot; then
query = query & &quot; group by &quot; & query_group
end if

if query_order <> &quot;&quot; then
query = query & &quot; order by &quot; & query_order
end if

 

[Reesy]

To reference a selected column such as account_name normally you'd use...

<% myRecordSet.Fields.Item(&quot;ACCOUNT_NAME&quot.Value %>

If your select statement is dynamically built how do you reference the columns? Blood, Sweat But No Tears!