This is a multi-part message in MIME format.



------=_NextPart_000_0029_01C09AF0.EDEF37C0

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable



Hi,



I am trying to set up a sql command that will retrieve a particular 

record based on a function defined variable, within the page.



how would I do that?



also, do you know of any great books to get going with the code?



Thanks,



James Cox

Beginning ASP databases by wrox press is really really good!

and 

http://w3.one.net/~jhoffman/sqltut.htm

is a good beginning SQL web page :-)

Kat

James,

I am far from a programmer, actually just dabbling in it, but from what I

have learned so far, your scenario sounds like it would be an ideal

candidate for using Select Case.

You can enumarate any number of possible given scenarios dependent on the

value given to your function variable.



Just an idea, hope its helpful....



Regards,

Barry

Thanks Barry, and everyone else.



Actually - it's not the sql that is the hard bit :)



the query I would have is



Select data from scripts where script_id = variable



So, what I don't know is how to set up so that I can declare that variable per instance,

ie, in the page, I want to make it paste the value of a script, based on which id it is.



in php, I would set up a function and have as it's variable the script id - and then have something

like <% script("5") %> to display the fifth script.





make any more sense ? :)



Thanks,



James



----- Original Message -----

From: "Barry Oxenberg" <boxenberg@l...>

To: "ASP Databases" <asp_databases@p...>

Sent: Tuesday, February 20, 2001 8:37 AM

Subject: [asp_databases] Re: Setting up a dynamic sql command





> James,

> I am far from a programmer, actually just dabbling in it, but from what I

> have learned so far, your scenario sounds like it would be an ideal

> candidate for using Select Case.

> You can enumarate any number of possible given scenarios dependent on the

> value given to your function variable.

>

> Just an idea, hope its helpful....

>

> Regards,

> Barry

>

>

Hi James!

Alright, let me clarify a little with a crude example.  Using Select Case

you could do something like this:

<%

    Select Case varVariable 'this variable is defined by form input or

whatever

        Case "Script1"

            Response.Write "Script 1" 'or you could even make this a link to

the actual text if             'you have it in a file by doing this:

        Case "Script1"

            Response.Write "<a href='script1.asp'>Script 1</a>"

        Case "Script2"

            Response.Write "Script 2"

    End Select

%>



hth,

Barry







----- Original Message -----

From: "James Cox" <james.cox3@n...>

To: "ASP Databases" <asp_databases@p...>

Sent: Tuesday, February 20, 2001 7:25 AM

Subject: [asp_databases] Re: Setting up a dynamic sql command





> Thanks Barry, and everyone else.

>

> Actually - it's not the sql that is the hard bit :)

>

> the query I would have is

>

> Select data from scripts where script_id = variable

>

> So, what I don't know is how to set up so that I can declare that variable

per instance,

> ie, in the page, I want to make it paste the value of a script, based on

which id it is.

>

> in php, I would set up a function and have as it's variable the script

id - and then have something

> like <% script("5") %> to display the fifth script.

>

>

> make any more sense ? :)

>

> Thanks,

>

> James

>

.wrox.com

Hi Barry (or anyone else that can help :)),



Ok, so that helps some.



But let's say that I open a recordset, and I need to specify the source.



so my code might look like :



set rs_tbl_page_data = Server.CreateObject("ADODB.Recordset")

rs_tbl_page_data.ActiveConnection = MM_braughingsausage_STRING

rs_tbl_page_data.Source = "select * from tbl_page_data where page_id = pageid"

rs_tbl_page_data.CursorType = 0

rs_tbl_page_data.CursorLocation = 2

rs_tbl_page_data.LockType = 3

rs_tbl_page_data.Open()

rs_tbl_page_data_numRows = 0



or whatever.



let's say in this example, that I want to declare the value of pageid for each different page - but

I don't want to have to set up a specific rs for each page. - ie, i can have a page.asp which, based

on cgi params can spit out a page using a default template. how would i do that?



then, let's say i had an open call to a table, (ie like the scripts example) and want to insert

various scripts into different parts of the page. is there anyway to build a function that retrieves

the value of the field given a piece of information (like the record id)



so, in the middle of the page, I can insert <% script("idnumber") %> and that'd just spit out the

contents of the script?



help is appreciated :)



Yours,



James Cox

Of course you can do this. Just write generic functions that accept input

parameters, and output a result.



Cheers

Ken



----- Original Message -----

From: "James Cox" <james.cox3@n...>

To: "ASP Databases" <asp_databases@p...>

Cc: "Barry Oxenberg" <boxenberg@l...>

Sent: Wednesday, February 21, 2001 8:37 PM

Subject: [asp_databases] Re: Setting up a dynamic sql command





> Hi Barry (or anyone else that can help :)),

>

> Ok, so that helps some.

>

> But let's say that I open a recordset, and I need to specify the source.

>

> so my code might look like :

>

> set rs_tbl_page_data = Server.CreateObject("ADODB.Recordset")

> rs_tbl_page_data.ActiveConnection = MM_braughingsausage_STRING

> rs_tbl_page_data.Source = "select * from tbl_page_data where page_id 

pageid"

> rs_tbl_page_data.CursorType = 0

> rs_tbl_page_data.CursorLocation = 2

> rs_tbl_page_data.LockType = 3

> rs_tbl_page_data.Open()

> rs_tbl_page_data_numRows = 0

>

> or whatever.

>

> let's say in this example, that I want to declare the value of pageid for

each different page - but

> I don't want to have to set up a specific rs for each page. - ie, i can

have a page.asp which, based

> on cgi params can spit out a page using a default template. how would i do

that?

>

> then, let's say i had an open call to a table, (ie like the scripts

example) and want to insert

> various scripts into different parts of the page. is there anyway to build

a function that retrieves

> the value of the field given a piece of information (like the record id)

>

> so, in the middle of the page, I can insert <% script("idnumber") %> and

that'd just spit out the

> contents of the script?

>

> help is appreciated :)

>

> Yours,

>

> James Cox

Thought so.



Uh.. how?



:)



Help appreciated..



James



----- Original Message ----- 

From: "Ken Schaefer" <ken@a...>

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, February 21, 2001 11:15 AM

Subject: [asp_databases] Re: Setting up a dynamic sql command





> Of course you can do this. Just write generic functions that accept input

> parameters, and output a result.

> 

> Cheers

> Ken

> 

> ----- Original Message -----

> From: "James Cox" <james.cox3@n...>

> To: "ASP Databases" <asp_databases@p...>

> Cc: "Barry Oxenberg" <boxenberg@l...>

> Sent: Wednesday, February 21, 2001 8:37 PM

> Subject: [asp_databases] Re: Setting up a dynamic sql command

> 

> 

> > Hi Barry (or anyone else that can help :)),

> >

> > Ok, so that helps some.

> >

> > But let's say that I open a recordset, and I need to specify the source.

> >

> > so my code might look like :

> >

> > set rs_tbl_page_data = Server.CreateObject("ADODB.Recordset")

> > rs_tbl_page_data.ActiveConnection = MM_braughingsausage_STRING

> > rs_tbl_page_data.Source = "select * from tbl_page_data where page_id 

> pageid"

> > rs_tbl_page_data.CursorType = 0

> > rs_tbl_page_data.CursorLocation = 2

> > rs_tbl_page_data.LockType = 3

> > rs_tbl_page_data.Open()

> > rs_tbl_page_data_numRows = 0

> >

> > or whatever.

> >

> > let's say in this example, that I want to declare the value of pageid for

> each different page - but

> > I don't want to have to set up a specific rs for each page. - ie, i can

> have a page.asp which, based

> > on cgi params can spit out a page using a default template. how would i do

> that?

> >

> > then, let's say i had an open call to a table, (ie like the scripts

> example) and want to insert

> > various scripts into different parts of the page. is there anyway to build

> a function that retrieves

> > the value of the field given a piece of information (like the record id)

> >

> > so, in the middle of the page, I can insert <% script("idnumber") %> and

> that'd just spit out the

> > contents of the script?

> >

> > help is appreciated :)

> >

> > Yours,

> >

> > James Cox

> 

> 

> 

> Thought so.

>

> Uh.. how?

>

> :)



From your post, I'm not sure what you want to be dynamic.



You say:



> But let's say that I open a recordset, and I need to specify the source.



So, you want to dynamically name the source? But later on you say:



> > > let's say in this example, that I want to declare the value of pageid

for

> > each different page - but

> > > I don't want to have to set up a specific rs for each page. - ie, i

can

> > have a page.asp which, based

> > > on cgi params can spit out a page using a default template. how would

i do

> > that?



Do you want to set the pageID (whatever that is) dynamically? Or the name of

the RS (you will need an RS to get stuff out of DB)?



You need to know what you want to be dynamic, and what you don't.



As for a "generic template" - if you are just talking about HTML stuff on

the page, then you can use Server-side includes to "include" a template (eg

common navigation items) on each page.



Simple example of doing something dynamically: You want to close an ADO

object, but you don't know in advance what the name will be:



Sub subADOClose( _

    ByRef objToClose _

    )



    On Error Resume Next



    If objToClose.State = adStateOpen then

        objToClose.Close

    End if



    If isObject(objToClose) then

        Set objToClose = Nothing

    End if



End Function ' subADOClose

...



Call subADOClose(objRS)

Call subADOClose(objCommand)

Call SubADOClose(objConn)



etc...



Cheers

Ken

Hi Ken,



I use UltraDev 4 (can you guess ;)) and have defined a recordset, which will define the layout

variables for each page. I use SQL to get the record which relates to the page.



What I wondered, was if there was a way to make the sql statement dynamic (sorry for not being clear

later)



ie,



if I had Select * from page_data where page_id = xxx



I wondered if there was a  way to get this id from a variable already defined.



so, in my rs object, it would almost "build" the sql statement based on submitted variables

(ideally, a cgi param)



I am also a newbie to the principles of ASP - I have come from an ultradev / php hack-at-code

background, so a pointer to a good book would probably also help :)



Thanks,



James

----- Original Message -----

From: "Ken Schaefer" <ken@a...>

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, February 21, 2001 11:46 AM

Subject: [asp_databases] Re: Setting up a dynamic sql command





> > Thought so.

> >

> > Uh.. how?

> >

> > :)

>

> From your post, I'm not sure what you want to be dynamic.

>

> You say:

>

> > But let's say that I open a recordset, and I need to specify the source.

>

> So, you want to dynamically name the source? But later on you say:

>

> > > > let's say in this example, that I want to declare the value of pageid

> for

> > > each different page - but

> > > > I don't want to have to set up a specific rs for each page. - ie, i

> can

> > > have a page.asp which, based

> > > > on cgi params can spit out a page using a default template. how would

> i do

> > > that?

>

> Do you want to set the pageID (whatever that is) dynamically? Or the name of

> the RS (you will need an RS to get stuff out of DB)?

>

> You need to know what you want to be dynamic, and what you don't.

>

> As for a "generic template" - if you are just talking about HTML stuff on

> the page, then you can use Server-side includes to "include" a template (eg

> common navigation items) on each page.

>

> Simple example of doing something dynamically: You want to close an ADO

> object, but you don't know in advance what the name will be:

>

> Sub subADOClose( _

>     ByRef objToClose _

>     )

>

>     On Error Resume Next

>

>     If objToClose.State = adStateOpen then

>         objToClose.Close

>     End if

>

>     If isObject(objToClose) then

>         Set objToClose = Nothing

>     End if

>

> End Function ' subADOClose

> ...

>

> Call subADOClose(objRS)

> Call subADOClose(objCommand)

> Call SubADOClose(objConn)

>

> etc...

>

> Cheers

> Ken

>

James,



I think I'm over-estimating what you're trying to do! :-)

http://www.amazon.com/exec/obidos/ASIN/1861003382/qid%3D967421606/sr%3D1-2/1

02-1476379-0074537



(watch for wrapping) - the above book is a very good place to start -

published by the people who are responsible for these lists. You don't have

to buy from Amazon, but I thought you might want to check the reviews.



OK - with what you want to do, I think you have two choices - passing via

the URI, or passing via a form post.



If you pass variables via a form post, the values end up in the

Request.Form() collection on the next page. If you pass variables via the

URI, they end up in the Request.QueryString() collection.



eg, on page1.asp,  I have:



<a href="page2.asp?ID=1">Click here</a> to view book 1<br>

<a href="page2.asp?ID=2">Click here</a> to view book 2<br>

<a href="page2.asp?ID=3">Click here</a> to view book 3<br>



on page2.asp,



I can get the value of ID, but doing this:



Response.Write(Request.QueryString("ID"))



So, for you, you could do something like:



<%

intID = Request.QueryString("ID")



If isNumeric(intID) then



    strSQL = "SELECT * "

    strSQL = strSQL & "FROM Page_Data "

    strSQL = strSQL & "WHERE PageID = " & intID



    Set objCOnn = Server.CreateObject("ADODB.Connection")

    objConn.Open strConnect



    Set objRS = Server.CreateObject("ADODB.Recordset")

    objRS.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText



    ...



    Now you have a recordset based on a dynamically created SQL string.



HTH



Cheers

Ken



----- Original Message -----

From: "James Cox" <james.cox3@n...>

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, February 21, 2001 10:58 PM

Subject: [asp_databases] Re: Setting up a dynamic sql command





> Hi Ken,

>

> I use UltraDev 4 (can you guess ;)) and have defined a recordset, which

will define the layout

> variables for each page. I use SQL to get the record which relates to the

page.

>

> What I wondered, was if there was a way to make the sql statement dynamic

(sorry for not being clear

> later)

>

> ie,

>

> if I had Select * from page_data where page_id = xxx

>

> I wondered if there was a  way to get this id from a variable already

defined.

>

> so, in my rs object, it would almost "build" the sql statement based on

submitted variables

> (ideally, a cgi param)

>

> I am also a newbie to the principles of ASP - I have come from an ultradev

/ php hack-at-code

> background, so a pointer to a good book would probably also help :)

>

> Thanks,

>

> James

> ----- Original Message -----

Yeah, that does help, Thanks :)



Ok, now to get a bit clever.



What if I wanted to execute the sql, to return different values of the data spread across the page?



ie, is there anyway to turn that sql object into a function run every time i want to make a  query?



james



----- Original Message ----- 

From: "Ken Schaefer" <ken@a...>

To: "ASP Databases" <asp_databases@p...>

Sent: Thursday, February 22, 2001 2:10 AM

Subject: [asp_databases] Re: Setting up a dynamic sql command





> James,

> 

> I think I'm over-estimating what you're trying to do! :-)

> http://www.amazon.com/exec/obidos/ASIN/1861003382/qid%3D967421606/sr%3D1-2/1

> 02-1476379-0074537

> 

> (watch for wrapping) - the above book is a very good place to start -

> published by the people who are responsible for these lists. You don't have

> to buy from Amazon, but I thought you might want to check the reviews.

> 

> OK - with what you want to do, I think you have two choices - passing via

> the URI, or passing via a form post.

> 

> If you pass variables via a form post, the values end up in the

> Request.Form() collection on the next page. If you pass variables via the

> URI, they end up in the Request.QueryString() collection.

> 

> eg, on page1.asp,  I have:

> 

> <a href="page2.asp?ID=1">Click here</a> to view book 1<br>

> <a href="page2.asp?ID=2">Click here</a> to view book 2<br>

> <a href="page2.asp?ID=3">Click here</a> to view book 3<br>

> 

> on page2.asp,

> 

> I can get the value of ID, but doing this:

> 

> Response.Write(Request.QueryString("ID"))

> 

> So, for you, you could do something like:

> 

> <%

> intID = Request.QueryString("ID")

> 

> If isNumeric(intID) then

> 

>     strSQL = "SELECT * "

>     strSQL = strSQL & "FROM Page_Data "

>     strSQL = strSQL & "WHERE PageID = " & intID

> 

>     Set objCOnn = Server.CreateObject("ADODB.Connection")

>     objConn.Open strConnect

> 

>     Set objRS = Server.CreateObject("ADODB.Recordset")

>     objRS.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText

> 

>     ...

> 

>     Now you have a recordset based on a dynamically created SQL string.

> 

> HTH

> 

> Cheers

> Ken

> 

> ----- Original Message -----

> From: "James Cox" <james.cox3@n...>

> To: "ASP Databases" <asp_databases@p...>

> Sent: Wednesday, February 21, 2001 10:58 PM

> Subject: [asp_databases] Re: Setting up a dynamic sql command

> 

> 

> > Hi Ken,

> >

> > I use UltraDev 4 (can you guess ;)) and have defined a recordset, which

> will define the layout

> > variables for each page. I use SQL to get the record which relates to the

> page.

> >

> > What I wondered, was if there was a way to make the sql statement dynamic

> (sorry for not being clear

> > later)

> >

> > ie,

> >

> > if I had Select * from page_data where page_id = xxx

> >

> > I wondered if there was a  way to get this id from a variable already

> defined.

> >

> > so, in my rs object, it would almost "build" the sql statement based on

> submitted variables

> > (ideally, a cgi param)

> >

> > I am also a newbie to the principles of ASP - I have come from an ultradev

> / php hack-at-code

> > background, so a pointer to a good book would probably also help :)

> >

> > Thanks,

> >

> > James

> > ----- Original Message -----

James,



I don't understand what you're trying to do.



What SQL object? What function? huh?



Maybe an example...?



Cheers

Ken



----- Original Message -----

From: "James Cox" <james.cox3@n...>

To: "ASP Databases" <asp_databases@p...>

Sent: Thursday, February 22, 2001 11:42 PM

Subject: [asp_databases] Re: Setting up a dynamic sql command





> Yeah, that does help, Thanks :)

>

> Ok, now to get a bit clever.

>

> What if I wanted to execute the sql, to return different values of the

data spread across the page?

>

> ie, is there anyway to turn that sql object into a function run every time

i want to make a  query?

>

> james

 Ken,



 Sorry for being confusing! :)



 Ok. So I have my asp page.



 it has a whole bunch of different javascript files in it, some to display news, others to display

 other things.



 My page looks very bloated at the moment, since it is full of all the javascripts (the page is a

 template, so i have to have them all there)



 so, I went and created a table in my db for all these scripts.



 Now, I need a way to call a script, and spit it out where it occurs on the page.



 so, instead of having <%=(rs_tbl_scripts.Fields.Item("script_data").Value)%> , and having the

 recordset connection defind at the top of the page, for each instance of the value of this field,



 I want to be able to have a connection to the table which is open (ie, SELECT * FROM tbl_scripts )

 and then, define which record to spit out at *each* instance on the page.



 So, for example if in the middle of the page, i want to have script x, with id 1 , how would i make

 it do that?



 I hope that is clearer, and thank you for all your help.. :)



 james

To be perfectly honest I don't think this is the ideal solution for your

problem.



To be able to do what you want you need to hardcode the IDs into your ASP

pages, which becomes a pain to maintain if you decide to add extra records

to the DB.



Further, you'd have to loop through the recordset that you created to find

the idea, and doing that loop over and over would involve some kind of

performance penalty.



Better, IMHO, would to be to extract all the javascript functions into

external .js include files, include them at the top of your page - rather

than relying on a whole heap of server-side processing to work out which

function to write where.



But if you really wanted to you could do something like:



<%

' Now we want to write out the function with TableID = 1



Do While Not objRS.EOF



    If objRS("TableID") = 1 then



        Response.Write(objRS("javascriptfuction"))

        Exit Do



    End if



Loop

%>



Cheers

Ken



----- Original Message -----

From: "James Cox" <james.cox3@n...>

To: "ASP Databases" <asp_databases@p...>

Sent: Friday, February 23, 2001 1:38 PM

Subject: [asp_databases] Re: Setting up a dynamic sql command





> Ken,

>

>  Sorry for being confusing! :)

>

>  Ok. So I have my asp page.

>

>  it has a whole bunch of different javascript files in it, some to display

news, others to display

>  other things.

>

>  My page looks very bloated at the moment, since it is full of all the

javascripts (the page is a

>  template, so i have to have them all there)

>

>  so, I went and created a table in my db for all these scripts.

>

>  Now, I need a way to call a script, and spit it out where it occurs on

the page.

>

>  so, instead of having

<%=(rs_tbl_scripts.Fields.Item("script_data").Value)%> , and having the

>  recordset connection defind at the top of the page, for each instance of

the value of this field,

>

>  I want to be able to have a connection to the table which is open (ie,

SELECT * FROM tbl_scripts )

>  and then, define which record to spit out at *each* instance on the page.

>

>  So, for example if in the middle of the page, i want to have script x,

with id 1 , how would i make

>  it do that?

>

>  I hope that is clearer, and thank you for all your help.. :)

>

>  james

Thanks Ken -  



I see what you mean though. (This is why I have now got an includes directory.. ;))



Thanks for all your help, however :)



James



----- Original Message ----- 

From: "Ken Schaefer" <ken@a...>

To: "ASP Databases" <asp_databases@p...>

Sent: Sunday, February 25, 2001 9:14 AM

Subject: [asp_databases] Re: Setting up a dynamic sql command





> To be perfectly honest I don't think this is the ideal solution for your

> problem.

> 

> To be able to do what you want you need to hardcode the IDs into your ASP

> pages, which becomes a pain to maintain if you decide to add extra records

> to the DB.

> 

> Further, you'd have to loop through the recordset that you created to find

> the idea, and doing that loop over and over would involve some kind of

> performance penalty.

> 

> Better, IMHO, would to be to extract all the javascript functions into

> external .js include files, include them at the top of your page - rather

> than relying on a whole heap of server-side processing to work out which

> function to write where.

> 

> But if you really wanted to you could do something like:

> 

> <%

> ' Now we want to write out the function with TableID = 1

> 

> Do While Not objRS.EOF

> 

>     If objRS("TableID") = 1 then

> 

>         Response.Write(objRS("javascriptfuction"))

>         Exit Do

> 

>     End if

> 

> Loop

> %>

> 

> Cheers

> Ken

> 

> ----- Original Message -----

> From: "James Cox" <james.cox3@n...>

> To: "ASP Databases" <asp_databases@p...>

> Sent: Friday, February 23, 2001 1:38 PM

> Subject: [asp_databases] Re: Setting up a dynamic sql command

> 

> 

> > Ken,

> >

> >  Sorry for being confusing! :)

> >

> >  Ok. So I have my asp page.

> >

> >  it has a whole bunch of different javascript files in it, some to display

> news, others to display

> >  other things.

> >

> >  My page looks very bloated at the moment, since it is full of all the

> javascripts (the page is a

> >  template, so i have to have them all there)

> >

> >  so, I went and created a table in my db for all these scripts.

> >

> >  Now, I need a way to call a script, and spit it out where it occurs on

> the page.

> >

> >  so, instead of having

> <%=(rs_tbl_scripts.Fields.Item("script_data").Value)%> , and having the

> >  recordset connection defind at the top of the page, for each instance of

> the value of this field,

> >

> >  I want to be able to have a connection to the table which is open (ie,

> SELECT * FROM tbl_scripts )

> >  and then, define which record to spit out at *each* instance on the page.

> >

> >  So, for example if in the middle of the page, i want to have script x,

> with id 1 , how would i make

> >  it do that?

> >

> >  I hope that is clearer, and thank you for all your help.. :)

> >

> >  james