|
Sample HSP Page
The HSP code below generated the Invoice in the
screenshot at the end of the page.
<html>
<head>
<meta http-equiv="Content-Language"
content="en-us">
<meta http-equiv="Content-Type"
content="text/html; charset=windows-1252">
<title>INVOICE</title>
</head>
<body>
<%
Dim cnn, rs, strOrderId, strShipName, strShipAddr1,
strShipAddr2, strShipCountry
Dim strBillName, strBillAddr1, strBillAddr2,
strBillCountry
Dim bOK
bOK = False
strOrderId = Request.QueryString("OrderID")
if Not IsEmpty(strOrderId) then
set cnn = CreateObject("ADODB.Connection")
cnn.Open("Northwind")
set rs = cnn.Execute( "SELECT DISTINCtrOW
Orders.OrderID, Orders.CustomerID, " & _
"Orders.EmployeeID, Orders.OrderDate,
Orders.RequiredDate, Orders.ShippedDate, " & _
"Orders.ShipVia, Orders.Freight, Orders.ShipName,
Orders.ShipAddress, " & _
"Orders.ShipCity, Orders.ShipRegion,
Orders.ShipPostalCode, Orders.ShipCountry, " & _
"Employees.LastName, Employees.FirstName,
Shippers.CompanyName AS ShipperName, " & _
"Customers.CompanyName, Customers.Address,
Customers.City, Customers.PostalCode, " & _
"Customers.Country " & _
"FROM Customers INNER JOIN (Shippers INNER JOIN
(Employees INNER JOIN Orders " & _
"ON Employees.EmployeeID = Orders.EmployeeID) ON
Shippers.ShipperID = " & _
"Orders.ShipVia) ON Customers.CustomerID =
Orders.CustomerID " & _
"WHERE Orders.OrderID = " & strOrderId )
if rs.EOF <> true then
bOK = true
strShipName = rs("ShipName")
strShipAddr1 = rs("ShipAddress")
strShipAddr2 = rs("ShipCity") & "
" & rs("ShipPostalCode")
strShipCountry = rs("ShipCountry")
strBillName = rs("CompanyName")
strBillAddr1 = rs("Address")
strBillAddr2 = rs("City") & " "
& rs("PostalCode")
strBillCountry = rs("Country")
if IsNull(strShipName) then strShipName = ""
if IsNull(strShipAddr1) then strShipAddr1 = ""
if IsNull(strShipAddr2) then strShipAddr2 = ""
if IsNull(strShipCountry) then strShipCountry =
""
if IsNull(strBillName) then strBillName = ""
if IsNull(strBillAddr1) then strBillAddr1 = ""
if IsNull(strBillAddr2) then strBillAddr2 = ""
if IsNull(strBillCountry) then strBillCountry =
""
end if
end if
%>
<% if IsEmpty(strOrderId) then %>
<p>Error Retrieving Order</p>
<% else %>
<div align="center">
<center>
<table border="0" cellpadding="5"
cellspacing="0" width="97%">
<tr>
<td width="50%" valign="middle"
align="left"><img border="0"
src="images/logo.gif" width="84"
height="84">
<img border="0"
src="images/northwind.gif" width="156"
height="42"></td>
<td width="50%" align="right"
valign="top"><font face="Arial"
color="#000080"
size="5">INVOICE</font></td>
</tr>
</table>
</center>
</div>
<hr color="#000080" width="97%">
<div align="center">
<center>
<table border="0" cellpadding="5"
cellspacing="0" width="97%">
<tr>
<td width="50%"><i><font
size="2" color="#000080">One
Portals Way, Twin
Points WA 98156<br>
Phone: 1-206-555-1417 FAX:
1-206-555-5938</font></i></td>
<td width="50%"
align="right"><font
face="Arial"><font
color="#000080">Date:</font>
<% =
rs("OrderDate")%></font></td>
</tr>
</table>
</center>
</div>
<br>
<div align="center">
<center>
<table border="0" cellspacing="0"
width="740">
<tr>
<td width="65"><font
face="Arial" color="#000080">Ship
To:</font></td>
<td width="298"><font
face="Arial"><%=
strShipName%></font></td>
<td width="55"><font
face="Arial" color="#000080">Bill
To:</font></td>
<td width="306"><font
face="Arial"><%=
strBillName%></font></td>
</tr>
<tr>
<td width="65"></td>
<td width="298"><font
face="Arial"><%=
strShipAddr1%></font></td>
<td width="55"></td>
<td width="306"><font
face="Arial"><%=
strBillAddr1%></font></td>
</tr>
<tr>
<td width="65"></td>
<td width="298"><font
face="Arial"><%=
strShipAddr2%></font></td>
<td width="55"></td>
<td width="306"><font
face="Arial"><%=
strBillAddr2%></font></td>
</tr>
<tr>
<td width="65"></td>
<td width="298"><font
face="Arial"><%=
strShipCountry%></font></td>
<td width="55"></td>
<td width="306"><font
face="Arial"><%=
strBillCountry%></font></td>
</tr>
</table>
</center>
</div>
<br>
<div align="center">
<center>
<table border="0" cellpadding="5"
cellspacing="0" bgcolor="#000080"
width="90%">
<tr>
<td>
<table border="0" cellpadding="0"
cellspacing="0" width="100%">
<tr>
<td width="10%"
align="center"><font
face="Arial" size="2"
color="#FFFFFF">Order
ID</font></td>
<td width="16%"
align="center"><font
face="Arial" size="2"
color="#FFFFFF">Customer
ID</font></td>
<td width="18%"
align="center"><font
face="Arial" size="2"
color="#FFFFFF">SalesPerson</font></td>
<td width="10%"
align="center"><font
face="Arial" size="2"
color="#FFFFFF">Order
Date</font></td>
<td width="11%"
align="center"><font
face="Arial" size="2"
color="#FFFFFF">Required
Date</font></td>
<td width="11%"
align="center"><font
face="Arial" size="2"
color="#FFFFFF">Shipped
Date</font></td>
<td width="14%"
align="center"><font
face="Arial" size="2"
color="#FFFFFF">Ship
Via</font></td>
</tr>
</table>
</tr>
</table>
</center>
</div>
<div align="center">
<center>
<table border="1" cellpadding="5"
cellspacing="0" width="90%">
<tr>
<% Dim strReqDate,
strShippedDate, strShiperName
strReqDate = rs("RequiredDate")
strShippedDate = rs("ShippedDate")
strShiperName = rs("ShipperName")
if IsNull(strReqDate) then strReqDate = ""
if IsNull(strShippedDate) then strShippedDate =
""
if IsNull(strShiperName) then strShiperName = ""
%>
<td width="10%"
align="center"><font
face="Arial"><%=
strOrderId%></font></td>
<td width="16%"
align="center"><font
face="Arial"><%=
rs("CustomerID")%></font></td>
<td width="18%"
align="center"><font
face="Arial"><%= rs("FirstName")
& " " &
rs("LastName")%></font></td>
<td width="10%"
align="center"><font
face="Arial"><%=
rs("OrderDate")%></font></td>
<td width="11%"
align="center"><font
face="Arial"><%=
strReqDate%></font></td>
<td width="11%"
align="center"><font
face="Arial"><%=
strShippedDate%></font></td>
<td width="14%"
align="center"><font
face="Arial"><%=
strShiperName%></font></td>
</tr>
</table>
</center>
</div>
<p> </p>
<div align="center">
<center>
<table border="0" cellpadding="5"
cellspacing="0" bgcolor="#000080"
width="90%">
<tr>
<td>
<table border="0" cellpadding="0"
cellspacing="0" width="100%">
<tr>
<td width="12%"
align="center"><font
face="Arial" size="2"
color="#FFFFFF">Product
ID</font></td>
<td width="32%"
align="left"><font face="Arial"
size="2" color="#FFFFFF">Product
Name</font></td>
<td width="12%"
align="right"><font face="Arial"
size="2"
color="#FFFFFF">Quantity</font></td>
<td width="12%"
align="right"><font face="Arial"
size="2" color="#FFFFFF">Unit
Price</font></td>
<td width="14%"
align="right"><font face="Arial"
size="2"
color="#FFFFFF">Discount</font></td>
<td width="18%"
align="right"><font face="Arial"
size="2" color="#FFFFFF">Extended
Price</font></td>
</tr>
</table>
</tr>
</table>
<%
Dim strSQL
strSQL = "SELECT DISTINCtrOW OrderID,
Products.ProductID, Products.UnitPrice, Quantity,
Discount, " & _
"Products.ProductName FROM Products INNER JOIN "
& _
"`Order Details` ON Products.ProductID = `Order
Details`.ProductID " & _
"WHERE OrderId = " & strOrderId
set rs = cnn.Execute( strSQL )
rs.MoveFirst
if rs.EOF = true then
%>
<p>There was an error retrieving the Order Details
for this Order</p>
<% else %>
</center>
</div>
<div align="center">
<center>
<table border="0" cellpadding="0"
cellspacing="0" width="90%">
<%
dim discount, qty, price, subTotal, total
while rs.EOF <> true
qty = rs("Quantity")
price = rs("UnitPrice")
discount = rs("Discount")
subTotal = qty*price
subTotal = Round(subTotal - (subTotal*discount),2)
total = total + subTotal
Response.Write "<tr>"
Response.Write "<td width='100%'>"
Response.Write "<table border='0' cellpadding='0'
cellspacing='0' width='100%'>"
Response.Write "<tr>"
Response.Write "<td width='12%'
align='center'><font face='Arial'>" &
rs("ProductID") &
"</font></td>"
Response.Write "<td width='32%'
align='left'><font face='Arial'>" &
rs("ProductName") &
"</font></td>"
Response.Write "<td width='12%'
align='right'><font face='Arial'>" & qty
& "</font></td>"
Response.Write "<td width='12%'
align='right'><font face='Arial'>" &
price & "</font></td>"
Response.Write "<td width='14%'
align='right'><font face='Arial'>" &
discount * 100 & "%</font></td>"
Response.Write "<td width='18%'
align='right'><font face='Arial'>$" &
subTotal & "</font></td>"
Response.Write "</tr>"
Response.Write "</table>"
Response.Write "</td>"
Response.Write "</tr>"
Response.Write "<tr>"
Response.Write "<td width='100%'>"
Response.Write "<hr color='#000080' width='100%'
size='1'>"
Response.Write "</td>"
Response.Write "</tr>"
rs.MoveNext
wend
%>
</table>
</center>
</div>
<p></p>
<div align="center">
<center>
<table border="0" cellpadding="0"
cellspacing="0" width="90%">
<tr>
<td width="100%">
<div align="right">
<table border="1"
bordercolor="#000080" cellpadding="1"
cellspacing="0" width="200">
<tr>
<td width="50%"
align="center"><font
color="#000080"
face="Arial"><b>Total</b></font></td>
<td width="50%"
align="right"><font
face="Arial"><b>$<%=total%></b></font></td>
</tr>
</table>
</div>
</td>
</tr>
</table>
</center>
</div>
<% end if %>
<% end if %>
<%
If Not IsEmpty(cnn) then
cnn.Close
set cnn = Nothing
set rs = Nothing
end if
%>
</body>
</html>
|
Access any SQL database through
Database Products
