Set rs = objConn.execute(Select_Statement)

mac_doggie

Hi,

Does anyone know the awnser to the following ?

I want a user on my site to type his/her query into a text field and then display the output of the select query in a table on screen.

let's say that the user typed in the following query:

Select My_Field from My_Table WHERE My_Field > 10;
and that it is stored in a variable called Select_Statement

I'd do:

[code]
Set rs = objConn.execute(Select_Statement)
[/code]

I know I can now acces the result with the following code:

[code]
Set rs = objConn.execute(Select_Statement)
response.write("")
while not rs.EOF
response.write("")
loop
response.write("

" & rs.Fields("My_Field").value & "

")
[/code]

But in this case I didn't now in designtime what the query would look like. I'd rather type something like this:

[code]
Set rs = objConn.execute(Select_Statement)
response.write("")
Field_nr = 1 'initialize
while rs.Fields(Fieldnr)
response.write("")
Field_nr = Field_nr + 1 'increment
loop

response.write("") ' Start a new row

while not rs.EOF
Field_nr = 1 'initialize
while rs.Fields(Fieldnr)
response.write("")
Field_nr = Field_nr + 1 'increment
loop
rs.movenext
response.write("") ' Start a new row
Loop
response.write("

" & rs.Fields(Field_nr).name & "
" & rs.Fields(Field_nr).value & "

")
[/code]

Does Anyone know how I should write this in proper ASP...


;-)
-mac-
mailto:[email protected]
the Netherlands...

Anjuna Moon

Hi,
this is a way of doing it (just showing you the relevant code):

set rs=conn.execute(query)
do while not rs.eof
for t=0 to rs.Fields.count-1
nextFieldValue=rs(t)

' Then do what it is you do with the field value...

next
loop
rs.close





: Hi,
:
: Does anyone know the awnser to the following ?
:
: I want a user on my site to type his/her query into a text field and then display the output of the select query in a table on screen.
:
: let's say that the user typed in the following query:
:
: Select My_Field from My_Table WHERE My_Field > 10;
: and that it is stored in a variable called Select_Statement
:
: I'd do:
:
: [code]
: Set rs = objConn.execute(Select_Statement)
: [/code]
:
: I know I can now acces the result with the following code:
:
: [code]
: Set rs = objConn.execute(Select_Statement)
: response.write("")
: while not rs.EOF
: response.write("")
: loop
: response.write("

" & rs.Fields("My_Field").value & "

")
: [/code]
:
: But in this case I didn't now in designtime what the query would look like. I'd rather type something like this:
:
: [code]
: Set rs = objConn.execute(Select_Statement)
: response.write("")
: Field_nr = 1 'initialize
: while rs.Fields(Fieldnr)
: response.write("")
: Field_nr = Field_nr + 1 'increment
: loop
:
: response.write("") ' Start a new row
:
: while not rs.EOF
: Field_nr = 1 'initialize
: while rs.Fields(Fieldnr)
: response.write("")
: Field_nr = Field_nr + 1 'increment
: loop
: rs.movenext
: response.write("") ' Start a new row
: Loop
: response.write("

" & rs.Fields(Field_nr).name & "
" & rs.Fields(Field_nr).value & "

")
: [/code]
:
: Does Anyone know how I should write this in proper ASP...
:
:
: ;-)
: -mac-
: mailto:[email protected]
: the Netherlands...
:
:

roy_tech

Let the user type the sql in the text box and you can get that sql into a variable. Let us say that variable is select_sql.

When you create the recordset...

Set rs = objconn.execute(select_sql)
Dim ArrRS
ArrRS= rs.getrows() ' This will get you all the selected rows into the ArrRS array.

ArrRS is a two dimensional array.

UBound(ArrRS,1) has all the columns and UBound(arrRS,2) has all the rows from the select query.

Now you can use the loop(Do While) to generate the HTML.