Multiple Tables Linked By ID
I have to write an ASP page that has connection to 1 database and then queries two tables one which has the header detail in and then the second which has the开发者_开发知识库 order lines in, each table has a ORDER_NUMNER.
These Tables contain a sales orders which I need to print out into an HTML page any help on this would be great as ASP is not my main language.
In general:
Instantiate and open your database connection: (see www.connectionstrings.com for more information)
dim conn
conn.open "your connection string goes here"
Open a recordset for the master table and detail table:
dim rst
rst.open "select * from tblMaster left join tblDetail on tblMaster.ORDER_NUMBER = tblDetail.ORDER_NUMBER where ORDER_NUMBER = 4",conn,1,3
Exit out of there are not records
if rst.eof then
rst.close
conn.close
Response.end
end if
Print header info (for fields order_date, order_number, and order_company:
response.write "Company: " & rst.fields("order_company") & "<br>"
response.write "Date: " & rst.fields("order_date") & "<br>"
response.write "Order Number: " & rst.fields("order_number") & "<br>"
Loop through records, reading all records from detail table: (for fields item_desc, item_qty, item_cost)
while not rst.eof
response.write "Item: " & rst.fields("item_desc") & "<br>"
response.write "Qty: " & rst.fields("item_qty") & "<br>"
response.write "Cost: " & rst.fields("item_cost") & "<br>"
rst.MoveNext
wend
Close the recordset
rst.close
Close the connection
conn.close
<%
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Mode = 3
objConn.Open "driver definition and connection string"
SQLStrJ = "SELECT * FROM table1 t1 JOIN table2 t2 ON t1.ORDER_NUMBER=t2.ORDERNUMBER;"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open SQLStrJ, objConn, 1, 3
Do Until objRS.EOF = True %>
<html><%=objRs("field")%></html>
<%
objRs.MoveNext
Loop
%>
You'll see in the loop an example of dropping field data into HTML. You will probably want to put the connection and recordset definition in an includable function that you can just pass a SQL string into since you'll be using it a lot. And don't forget to close the connection when you're done. Good luck.. classic ASP is a bit of a mess.
加载中,请稍侯......
精彩评论