WhiteZiggy
Programmer
Hello all. I am pretty new to ASP so this is my first crack at it. I have some code I wrote, could anyone give me their comments/suggestions ..
<HTML>
<BODY>
<%
'Variable Declarations
Dim SQL ' Used for building SQL strings to open recordsets
Dim CONN ' Connection Object
Dim rsInvoices ' Invoices recordset
Dim rsInvoiceLineItems ' Invoice Line items recordset
Dim InvoiceTotal ' Used to calculate Invoice Total Amount
'Create/Initialize Database Objects
Set CONN = Server.CreateObject("ADODB.connection"
CONN.Open "Driver={SQL Server};Server=DBServer01;Database=Billing; Trusted_Connection=yes;"
Set rsInvoices = Server.CreateObject("ADODB.Recordset"
'Write Customer number header
Response.Write "Invoices for Customer Number " & Request.QueryString("CustomerID" & "<BR>"
'Retrieve invoice records for the selected date range.
SQL ="SELECT * FROM Invoices WHERE (CustomerID = " & Request.QueryString("CustomerID" & "
ORDER BY InvoiceDate"
rsInvoices.Open SQL,CONN
WHILE NOT rsInvoices.EOF
'Calculate Invoice Total
SQL = "Select * from InvoiceLineItems WHERE InvoiceNumber=" & rs("InvoiceNumber"
Set rsInvoiceLineItems = CONN.Execute(SQL)
InvoiceTotal = 0
WHILE NOT rsInvoiceLineItems.EOF
InvoiceTotal = InvoiceTotal + rsInvoiceLineItems("UnitPrice" *
rsInvoiceLineItems("Quantity"
rsInvoiceLineItems.MoveNext
WEND
'Display Invoice Information
Response.write "Invoice Number: " & RS("InvoiceNumber" & "<BR>"
Response.write "Payment Terms: " & GetPmtTerms(Request.QueryString("CustomerID") & "<BR>"
Response.write "Invoice Date: " & RS("InvoiceNumber" & "<BR>"
Response.write "Invoice Total: " & InvoiceTotal & "<HR>"
WEND
%>
</BODY>
</HTML>
<%
Function GetPmtTerms(CustomerID)
Dim CustomerRS 'Customer Table Recordset
Dim SQL ‘For building Query string
'Get record for customer
Set CustomerRS = server.CreateObject("ADODB.recordset"
SQL = "SELECT PaymentTerms FROM Customers WHERE CustomerID=" & CustomerID
Set CustomerRS = CONN.execute(SQL)
'Handle CustomerID not found condition
IF Customer (RS.EOF AND CustomerRS.BOF) THEN
GetPmtTerms = "Unknown"
ELSE
GetPmtTerms = CustomerRS("PaymentTerms"
END IF
CustomerRS.Close
CONN.Close
End Function
%>
Expected Sample Output
Invoices for Customer number 5
Invoice Number: 1
Payment Terms: Net-30
Invoice Date: 12/12/2002
Invoice Total: $500.00
---------------------------
Invoice Number: 2
Payment Terms: Net-30
Invoice Date: 12/22/2002
Invoice Total: $50.00
Invoice Number: 3
---------------------------
Thanks in advance..WZ
<HTML>
<BODY>
<%
'Variable Declarations
Dim SQL ' Used for building SQL strings to open recordsets
Dim CONN ' Connection Object
Dim rsInvoices ' Invoices recordset
Dim rsInvoiceLineItems ' Invoice Line items recordset
Dim InvoiceTotal ' Used to calculate Invoice Total Amount
'Create/Initialize Database Objects
Set CONN = Server.CreateObject("ADODB.connection"
CONN.Open "Driver={SQL Server};Server=DBServer01;Database=Billing; Trusted_Connection=yes;"
Set rsInvoices = Server.CreateObject("ADODB.Recordset"
'Write Customer number header
Response.Write "Invoices for Customer Number " & Request.QueryString("CustomerID"
& "<BR>"'Retrieve invoice records for the selected date range.
SQL ="SELECT * FROM Invoices WHERE (CustomerID = " & Request.QueryString("CustomerID"
& "ORDER BY InvoiceDate"
rsInvoices.Open SQL,CONN
WHILE NOT rsInvoices.EOF
'Calculate Invoice Total
SQL = "Select * from InvoiceLineItems WHERE InvoiceNumber=" & rs("InvoiceNumber"
Set rsInvoiceLineItems = CONN.Execute(SQL)
InvoiceTotal = 0
WHILE NOT rsInvoiceLineItems.EOF
InvoiceTotal = InvoiceTotal + rsInvoiceLineItems("UnitPrice"
*rsInvoiceLineItems("Quantity"
rsInvoiceLineItems.MoveNext
WEND
'Display Invoice Information
Response.write "Invoice Number: " & RS("InvoiceNumber"
& "<BR>"Response.write "Payment Terms: " & GetPmtTerms(Request.QueryString("CustomerID"
) & "<BR>"Response.write "Invoice Date: " & RS("InvoiceNumber"
& "<BR>"Response.write "Invoice Total: " & InvoiceTotal & "<HR>"
WEND
%>
</BODY>
</HTML>
<%
Function GetPmtTerms(CustomerID)
Dim CustomerRS 'Customer Table Recordset
Dim SQL ‘For building Query string
'Get record for customer
Set CustomerRS = server.CreateObject("ADODB.recordset"
SQL = "SELECT PaymentTerms FROM Customers WHERE CustomerID=" & CustomerID
Set CustomerRS = CONN.execute(SQL)
'Handle CustomerID not found condition
IF Customer (RS.EOF AND CustomerRS.BOF) THEN
GetPmtTerms = "Unknown"
ELSE
GetPmtTerms = CustomerRS("PaymentTerms"
END IF
CustomerRS.Close
CONN.Close
End Function
%>
Expected Sample Output
Invoices for Customer number 5
Invoice Number: 1
Payment Terms: Net-30
Invoice Date: 12/12/2002
Invoice Total: $500.00
---------------------------
Invoice Number: 2
Payment Terms: Net-30
Invoice Date: 12/22/2002
Invoice Total: $50.00
Invoice Number: 3
---------------------------
Thanks in advance..WZ