Running SQL statement from table...

All,

I have a table with the following SQL statement inside it...

SELECT * from tblPerson Where PersonID=" & request.querystring("PersonID")

How can I retreive this and execute it via vbscript to populate another
recordset...

Cheers

Td

Ray
Wed Sep 13 09:49:39 CDT 2006

Mind if I ask why on earth you have that stored in a database? Why do you
have code in a database instead of in a source code file?

Ray at work

"Tony Doyle" <tony@nospam.com> wrote in message
news:uN6iOK01GHA.4448@TK2MSFTNGP04.phx.gbl...
> All,
>
> I have a table with the following SQL statement inside it...
>
> SELECT * from tblPerson Where PersonID=" & request.querystring("PersonID")
>
> How can I retreive this and execute it via vbscript to populate another
> recordset...
>
> Cheers
>
> Td
>
>
>

Tony
Wed Sep 13 10:03:34 CDT 2006

Basically,

I have been given a website to look after / upgrade, I discovered 28 pages
that were very alike... apart from the SQL.

So stored the SQL in a table, and then just call it via a
Request.QueryString("PageID") to determine which SQL to use.

So, my question is can it be done this way, or shall I find another tree to
bark at...



"Ray Costanzo [MVP]" <my first name at lane 34 dot commercial> wrote in
message news:uHoMYP01GHA.2196@TK2MSFTNGP06.phx.gbl...
> Mind if I ask why on earth you have that stored in a database? Why do you
> have code in a database instead of in a source code file?
>
> Ray at work
>
> "Tony Doyle" <tony@nospam.com> wrote in message
> news:uN6iOK01GHA.4448@TK2MSFTNGP04.phx.gbl...
>> All,
>>
>> I have a table with the following SQL statement inside it...
>>
>> SELECT * from tblPerson Where PersonID=" &
>> request.querystring("PersonID")
>>
>> How can I retreive this and execute it via vbscript to populate another
>> recordset...
>>
>> Cheers
>>
>> Td
>>
>>
>>
>
>

JHP
Wed Sep 13 12:08:56 CDT 2006

See if this works for you:
(watch for word wrap)

NB*: anything in a square bracket needs replacing
NB*: this has not been tested

Set objConn = CreateObject("ADODB.Connection")
objConn.Open "DSN=[dsnName]"
Set objRec = CreateObject("ADODB.RecordSet")

With objRec
.CursorLocation = 3
.CursorType = 3
.LockType = 4
.ActiveConnection = objConn
End With

If Err.Number = 0 Then
Set objRec2 = CreateObject("ADODB.RecordSet")

With objRec2
.CursorLocation = 3
.CursorType = 3
.LockType = 4
.ActiveConnection = objConn
End With
objConn.BeginTrans
objRec.Open "SELECT [column] FROM [table] WHERE [column] = '[value]'"

If objRec.RecordCount > 0 Then
strSQL = objRec.Fields("[column]").Value
objRec2.Open strSQL

If objRec2.RecordCount > 0 Then
objRec2.MoveFirst

Do Until objRec2.EOF
...
objRec2.MoveNext
Loop
End If
objRec2.Close
End If
Set objRec2 = Nothing
End If
objRec.Close
Set objRec = Nothing
Set objConn = Nothing


"Tony Doyle" <tony@nospam.com> wrote in message
news:ux50HX01GHA.1336@TK2MSFTNGP03.phx.gbl...
> Basically,
>
> I have been given a website to look after / upgrade, I discovered 28 pages
> that were very alike... apart from the SQL.
>
> So stored the SQL in a table, and then just call it via a
> Request.QueryString("PageID") to determine which SQL to use.
>
> So, my question is can it be done this way, or shall I find another tree
> to bark at...
>
>
>
> "Ray Costanzo [MVP]" <my first name at lane 34 dot commercial> wrote in
> message news:uHoMYP01GHA.2196@TK2MSFTNGP06.phx.gbl...
>> Mind if I ask why on earth you have that stored in a database? Why do
>> you have code in a database instead of in a source code file?
>>
>> Ray at work
>>
>> "Tony Doyle" <tony@nospam.com> wrote in message
>> news:uN6iOK01GHA.4448@TK2MSFTNGP04.phx.gbl...
>>> All,
>>>
>>> I have a table with the following SQL statement inside it...
>>>
>>> SELECT * from tblPerson Where PersonID=" &
>>> request.querystring("PersonID")
>>>
>>> How can I retreive this and execute it via vbscript to populate another
>>> recordset...
>>>
>>> Cheers
>>>
>>> Td
>>>
>>>
>>>
>>
>>
>
>