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!

Colin

 

[awarnica]

Im rusty on ASP but once the text appears on a web page it is not ASP anymore, it is on the client.

So you treat it as you would any other input from a user, ie the VALUE of the textbox / label whatever it is.

SELECT * FROM orders.A, itemsOrdered.B where a.orderID = B.orderID and A.person = textbox1.value

You wouldn't use an ASP object because you have already gotten the data "Tony Hawk" from the server and displayed it on the web page. The action of displaying it puts it on the clients computer.
Remember that in ASP you are dealing in a sense with 2 different hardware components: the server computer that talks to the database and the clients computer.

You previously used the server to grab the tony hawk and display it on the web page (the clients computer). The user clicks on something to activate more server code, which in our case will build the SQL statement based on what appears on the clients page.


PS this should probably go in the ASP forum, not the SQL server one.

Andrew