dynamic SQL Statements using ASP

[kneebar]

Hi Everyone!

I am trying to build a dynamically generated SQL Statement. I was wondering if anyone could possibly help me. I have tried stored procedures but they do not work out as well for the scope of my project. In any case, here is some background as to what I am trying to do.

I have 2 tables. One is called ‘orders’ and the other is called ‘itemsOrdered’.

The ‘orders’ table is composed of 2 columns: orderID and person.

The ‘itemsOrdered’ table is composed of 5 columns called orderID, productnumber, proddescription, price, and quantity.

I am trying to build a dynamically built HTML table so that when the user clicks on a person’s name (built already dynamically from a database), another table opens up to reveal all the products – past and present – associated with that person.

I did a query build in Access and got his general SQL statement that works:

SELECT * FROM orders.A, itemsOrdered.B where a.orderID = B.orderID and A.person = “Tony Hawk”

My question is: how do I dynamically generate in ASP “Tony Hawk” or the person? ASP request object? I am confused.

Any sample code would be most helpful and appreciated. Thanks in advanced!

Kneebar

 

[GaryC123]

Basically

SELECT * FROM orders.A, itemsOrdered.B where a.orderID = B.orderID and A.person = '" & request("person&quot & "'"

http://www.intouch-media.co.uk

 

[kneebar]

wow. that was quick. thanks Gary!
anyone else with suggestions?

 

[GaryC123]

what else do u want?

http://www.intouch-media.co.uk

 

[kneebar]

how do i pass the 'person' variable over from another page once they clcik on a respective name in asp? thanks!

would oyu do it like this?

http://www.randomasppage.asp?person=

???????

also. what other pieces of ASP am i missing to make this work? thanks again!

 

[GaryC123]

I assume you have a drop down box on the previous page say called "personlist". You should enclose this in a Form tag. With a submit button within the form tag.
I personally prefer Post method to Get so all variables are passed invisibly to the next page.
Then your select statement would be like this
SELECT * FROM orders.A, itemsOrdered.B where a.orderID = B.orderID and A.person = '" & request("personlist&quot & "'"

To be programmatically correct you should use request.form when using Post and request.querystring when using Get but just request pulls the data from whichever method.
So take for example your site

<form method=&quot;post&quot; name=&quot;yourpage&quot; action=&quot;nextpage.asp&quot;>
'your various HTMl
<select name=&quot;personlist&quot;>
<option>blah</option>
<option>blah</option>...etc
</select>
<input type=submit>
</form>

Next Page
<form method=&quot;post&quot; name=&quot;nextpage&quot; action=&quot;&quot;>
'create db connection
SELECT * FROM orders.A, itemsOrdered.B where a.orderID = B.orderID and A.person = '&quot; & request(&quot;personlist&quot & &quot;'&quot;
conn.execute (sql)
<table><TR>
<TD><%=rs(&quot;name&quot</TD>
</TR><TR>
<TD><%=rs(&quot;address&quot%></TD>... and so on

http://www.intouch-media.co.uk

 

[kneebar]

you are the man! thank you! it works!