------=_NextPart_000_008E_01BD20F6.82F01E20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Okay lets see f someone can help me out. Im trying to do a web based =
report utilizing the emerald database to report the total payments =
grouped by login ID and Date of payment for people with a certain region =
and account type. With subtotals for each user and a grand total for =
the region and account type combo at the bottom. I need to have it set =
up to only select records that have payment dates between a range that =
is entered on a form on the page. I have been trying to get this to =
work for two weeks now (Im not the greatest at sql or asp design. If =
anyone could spare a minute to take a look at this code and see if you =
can see where I am making my mistakes I would really appreciate it. I =
don't get any sql errors now but it still is ust bouncing people back to =
the form without displaying any records.
<% StartDate =3D Request("StartDate")
EndDate =3D Request("EndDate") %>
<% IF (StartDate <> "") AND (EndDate <> "") THEN
Session.Timeout =3D 1
IF IsObject(Session("EmeraldDSN_conn")) THEN
Set conn =3D Session("EmeraldDSN_conn")
ELSE
Set conn =3D Server.CreateObject("ADODB.Connection")
conn.open "DSN=3DEmeraldDSN; UID=3Dsa; PWD=3Dauth5434sq"
Set Session("EmeraldDSN_conn") =3D conn
END IF
sql1 =3D "SELECT Payments.PaymentID, Payments.Amount, Payments.Date, =
MasterAccounts.CustomerID, MasterAccounts.Region, =
MasterAccounts.PayMethod, SubAccounts.Login, SubAccounts.LastName, =
SubAccounts.AccountType, Invoices.InvoiceID, InvoiceItems.ItemID "
sql1 =3D sql1 & "FROM Emerald.dbo.Payments Payments, =
Emerald.dbo.MasterAccounts MasterAccounts, Emerald.dbo.SubAccounts =
SubAccounts, Emerald.dbo.Invoices Invoices, Emerald.dbo.InvoiceItems =
InvoiceItems "
sql1 =3D sql1 & "WHERE Payments.CustomerID =3D MasterAccounts.CustomerID =
AND"
sql1 =3D sql1 & " MasterAccounts.CustomerID =3D SubAccounts.CustomerID =
AND"
sql1 =3D sql1 & " SubAccounts.CustomerID =3D Invoices.CustomerID AND"
sql1 =3D sql1 & " Invoices.InvoiceID =3D InvoiceItems.InvoiceID AND"
sql1 =3D sql1 & " MasterAccounts.Region =3D 'SEV' AND"
sql1 =3D sql1 & " SubAccounts.AccountType =3D 'SEV-Residential' AND "
sql1 =3D sql1 & "(Payments.Date >=3D CONVERT(datetime, '" & StartDate & =
"')) AND "
sql1 =3D sql1 & "(Payments.Date <=3D CONVERT(datetime, '" & EndDate & =
"')) "
sql1 =3D sql1 & "ORDER BY SubAccounts.Login ASC, Payments.Date ASC, =
MasterAccounts.CustomerID ASC, Invoices.InvoiceID ASC, =
InvoiceItems.ItemID ASC"
sql2 =3D "SELECT Payments.PaymentID, Payments.Amount, Payments.Date, =
MasterAccounts.CustomerID, MasterAccounts.Region, =
MasterAccounts.PayMethod, SubAccounts.Login, SubAccounts.LastName, =
SubAccounts.AccountType, Invoices.InvoiceID, InvoiceItems.ItemID "
sql2 =3D sql2 & "FROM Emerald.dbo.Payments Payments, =
Emerald.dbo.MasterAccounts MasterAccounts, Emerald.dbo.SubAccounts =
SubAccounts, Emerald.dbo.Invoices Invoices, Emerald.dbo.InvoiceItems =
InvoiceItems "
sql2 =3D sql2 & "WHERE Payments.CustomerID =3D MasterAccounts.CustomerID =
AND"
sql2 =3D sql2 & " MasterAccounts.CustomerID =3D SubAccounts.CustomerID =
AND"
sql2 =3D sql2 & " SubAccounts.CustomerID =3D Invoices.CustomerID AND"
sql2 =3D sql2 & " Invoices.InvoiceID =3D InvoiceItems.InvoiceID AND"
sql2 =3D sql2 & " MasterAccounts.Region =3D 'SEV' AND"
sql2 =3D sql2 & " SubAccounts.AccountType =3D 'SEV-Residential' AND "
sql2 =3D sql2 & "(Payments.Date >=3D CONVERT(datetime, '" & StartDate & =
"')) AND "
sql2 =3D sql2 & "(Payments.Date <=3D CONVERT(datetime, '" & EndDate & =
"')) "
sql2 =3D sql2 & "ORDER BY SubAccounts.Login ASC, Payments.Date ASC, =
MasterAccounts.CustomerID ASC, Invoices.InvoiceID ASC, =
InvoiceItems.ItemID ASC "
sql2 =3D sql2 & "COMPUTE SUM(ISNULL(Payments.Amount,0)) BY =
SubAccounts.Login ASC, Payments.Date ASC, MasterAccounts.CustomerID ASC, =
Invoices.InvoiceID ASC, InvoiceItems.ItemID ASC"
sql3 =3D "SELECT Payments.PaymentID, Payments.Amount, Payments.Date, =
MasterAccounts.CustomerID, MasterAccounts.Region, =
MasterAccounts.PayMethod, SubAccounts.Login, SubAccounts.LastName, =
SubAccounts.AccountType, Invoices.InvoiceID, InvoiceItems.ItemID "
sql3 =3D sql3 & "FROM Emerald.dbo.Payments Payments, =
Emerald.dbo.MasterAccounts MasterAccounts, Emerald.dbo.SubAccounts =
SubAccounts, Emerald.dbo.Invoices Invoices, Emerald.dbo.InvoiceItems =
InvoiceItems "
sql3 =3D sql3 & "WHERE Payments.CustomerID =3D MasterAccounts.CustomerID =
AND"
sql3 =3D sql3 & " MasterAccounts.CustomerID =3D SubAccounts.CustomerID =
AND"
sql3 =3D sql3 & " SubAccounts.CustomerID =3D Invoices.CustomerID AND"
sql3 =3D sql3 & " Invoices.InvoiceID =3D InvoiceItems.InvoiceID AND"
sql3 =3D sql3 & " MasterAccounts.Region =3D 'SEV' AND"
sql3 =3D sql3 & " SubAccounts.AccountType =3D 'SEV-Residential' AND "
sql3 =3D sql3 & "(Payments.Date >=3D CONVERT(datetime, '" & StartDate & =
"')) AND "
sql3 =3D sql3 & "(Payments.Date <=3D CONVERT(datetime, '" & EndDate & =
"')) "
sql3 =3D sql3 & "ORDER BY SubAccounts.Login ASC, Payments.Date ASC, =
MasterAccounts.CustomerID ASC, Invoices.InvoiceID ASC, =
InvoiceItems.ItemID ASC "
sql3 =3D sql3 & "COMPUTE SUM(ISNULL(Payments.Amount,0))"
Set rs =3D Server.CreateObject("ADODB.Recordset")
rs.Open sql1, conn, 3, 3
On Error Resume Next
Set rs =3D Server.CreateObject("ADODB.Recordset")
rs.Open sql2, conn, 3, 3
On Error Resume Next
total_dyt_u_336 =3D rs.Fields("subtotals").value
Set rs =3D Server.CreateObject("ADODB.Recordset")
rs.Open sql3, conn, 3, 3
On Error Resume Next
total_dyt_u_336 =3D rs.Fields("totals").value
END IF %>
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">
<% ELSE %>
Glass City Internet SEV =Users. |
Residential Users |
UserPayment Report For Period ( <% =3D StartDate%> through <% =3D EndDate =%>)
<% On Error Resume Nextrs.MoveFirstdo while not rs.EOF %>CustomerID | Dateof Payment | AmountPaid | Login =ID | lastName | PaymentID | InvoiceID | PaymentMethod |
<% =3D rs.Fields("CustomerID").Value =%> | <% =3D rs.Fields("Date").Value %> = | <% =3D rs.Fields("Amount").Value =%> | <% =3D rs.Fields("Login").Value =%> | <% =3D rs.Fields("LastName").Value =%> | <% =3D rs.Fields("PaymentID").Value =%> | <% =3D rs.Fields("InvoiceID").Value =%> | <% =3D rs.Fields("PayMethod").Value =%> |
Subtotal of Payments | <% =3D =rs.Fields("subtotals").Value %> |
Total Payments <% =3D =rs.Fields("totals").Value %>
<% END IF %>
------=_NextPart_000_008E_01BD20F6.82F01E20
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN">
<
% =StartDate =3D =Request("StartDate")EndDate =3D Request("EndDate") %
><
% =IF (StartDate <> ="") AND=20(EndDate <> "") THEN
Session.Timeout =3D 1
IF IsObject(Session("EmeraldDSN_conn")) THEN
Set conn =3D Session("EmeraldDSN_conn")
ELSE
Set conn =3D Server.CreateObject("ADODB.Connection")
conn.open "DSN=3DEmeraldDSN; UID=3Dsa; =PWD=3Dauth5434sq"
Set Session("EmeraldDSN_conn") =3D conn
END IF
sql1 =3D "SELECT Payments.PaymentID, Payments.Amount, =Payments.Date,=20MasterAccounts.CustomerID, MasterAccounts.Region, =MasterAccounts.PayMethod,=20SubAccounts.Login, SubAccounts.LastName, SubAccounts.AccountType,=20Invoices.InvoiceID, InvoiceItems.ItemID "
sql1 =3D sql1 & "FROM Emerald.dbo.Payments Payments,=20Emerald.dbo.MasterAccounts MasterAccounts, Emerald.dbo.SubAccounts =SubAccounts,=20Emerald.dbo.Invoices Invoices, Emerald.dbo.InvoiceItems InvoiceItems ="
sql1 =3D sql1 & "WHERE Payments.CustomerID =3D =MasterAccounts.CustomerID=20AND"
sql1 =3D sql1 & " MasterAccounts.CustomerID =3D =SubAccounts.CustomerID=20AND"
sql1 =3D sql1 & " SubAccounts.CustomerID =3D =Invoices.CustomerID=20AND"
sql1 =3D sql1 & " Invoices.InvoiceID =3D =InvoiceItems.InvoiceID=20AND"
sql1 =3D sql1 & " MasterAccounts.Region =3D 'SEV' =AND"
sql1 =3D sql1 & " SubAccounts.AccountType =3D ='SEV-Residential' AND=20"
sql1 =3D sql1 & "(Payments.Date >=3D CONVERT(datetime, ='" &=20StartDate & "')) AND "
sql1 =3D sql1 & "(Payments.Date <=3D CONVERT(datetime, ='" &=20EndDate & "')) "
sql1 =3D sql1 & "ORDER BY SubAccounts.Login ASC, =Payments.Date ASC,=20MasterAccounts.CustomerID ASC, Invoices.InvoiceID ASC, =InvoiceItems.ItemID=20ASC"
sql2 =3D "SELECT Payments.PaymentID, Payments.Amount, =Payments.Date,=20MasterAccounts.CustomerID, MasterAccounts.Region, =MasterAccounts.PayMethod,=20SubAccounts.Login, SubAccounts.LastName, SubAccounts.AccountType,=20Invoices.InvoiceID, InvoiceItems.ItemID "
sql2 =3D sql2 & "FROM Emerald.dbo.Payments Payments,=20Emerald.dbo.MasterAccounts MasterAccounts, Emerald.dbo.SubAccounts =SubAccounts,=20Emerald.dbo.Invoices Invoices, Emerald.dbo.InvoiceItems InvoiceItems ="
sql2 =3D sql2 & "WHERE Payments.CustomerID =3D =MasterAccounts.CustomerID=20AND"
sql2 =3D sql2 & " MasterAccounts.CustomerID =3D =SubAccounts.CustomerID=20AND"
sql2 =3D sql2 & " SubAccounts.CustomerID =3D =Invoices.CustomerID=20AND"
sql2 =3D sql2 & " Invoices.InvoiceID =3D =InvoiceItems.InvoiceID=20AND"
sql2 =3D sql2 & " MasterAccounts.Region =3D 'SEV' =AND"
sql2 =3D sql2 & " SubAccounts.AccountType =3D ='SEV-Residential' AND=20"
sql2 =3D sql2 & "(Payments.Date >=3D CONVERT(datetime, ='" &=20StartDate & "')) AND "
sql2 =3D sql2 & "(Payments.Date <=3D CONVERT(datetime, ='" &=20EndDate & "')) "
sql2 =3D sql2 & "ORDER BY SubAccounts.Login ASC, =Payments.Date ASC,=20MasterAccounts.CustomerID ASC, Invoices.InvoiceID ASC, =InvoiceItems.ItemID ASC=20"
sql2 =3D sql2 & "COMPUTE SUM(ISNULL(Payments.Amount,0)) BY=20SubAccounts.Login ASC, Payments.Date ASC, MasterAccounts.CustomerID ASC, =Invoices.InvoiceID ASC, InvoiceItems.ItemID ASC"
sql3 =3D "SELECT Payments.PaymentID, Payments.Amount, =Payments.Date,=20MasterAccounts.CustomerID, MasterAccounts.Region, =MasterAccounts.PayMethod,=20SubAccounts.Login, SubAccounts.LastName, SubAccounts.AccountType,=20Invoices.InvoiceID, InvoiceItems.ItemID "
sql3 =3D sql3 & "FROM Emerald.dbo.Payments Payments,=20Emerald.dbo.MasterAccounts MasterAccounts, Emerald.dbo.SubAccounts =SubAccounts,=20Emerald.dbo.Invoices Invoices, Emerald.dbo.InvoiceItems InvoiceItems ="
sql3 =3D sql3 & "WHERE Payments.CustomerID =3D =MasterAccounts.CustomerID=20AND"
sql3 =3D sql3 & " MasterAccounts.CustomerID =3D =SubAccounts.CustomerID=20AND"
sql3 =3D sql3 & " SubAccounts.CustomerID =3D =Invoices.CustomerID=20AND"
sql3 =3D sql3 & " Invoices.InvoiceID =3D =InvoiceItems.InvoiceID=20AND"
sql3 =3D sql3 & " MasterAccounts.Region =3D 'SEV' =AND"
sql3 =3D sql3 & " SubAccounts.AccountType =3D ='SEV-Residential' AND=20"
sql3 =3D sql3 & "(Payments.Date >=3D CONVERT(datetime, ='" &=20StartDate & "')) AND "
sql3 =3D sql3 & "(Payments.Date <=3D CONVERT(datetime, ='" &=20EndDate & "')) "
sql3 =3D sql3 & "ORDER BY SubAccounts.Login ASC, =Payments.Date ASC,=20MasterAccounts.CustomerID ASC, Invoices.InvoiceID ASC, =InvoiceItems.ItemID ASC=20"
sql3 =3D sql3 & "COMPUTE =SUM(ISNULL(Payments.Amount,0))"
Set rs =3D Server.CreateObject("ADODB.Recordset")
rs.Open sql1, conn, 3, 3
On Error Resume Next
Set rs =3D Server.CreateObject("ADODB.Recordset")
rs.Open sql2, conn, 3, 3
On Error Resume Next
total_dyt_u_336 =3D rs.Fields("subtotals").value
Set rs =3D Server.CreateObject("ADODB.Recordset")
rs.Open sql3, conn, 3, 3
On Error Resume Next
total_dyt_u_336 =3D rs.Fields("totals").value
END IF %
><
!DOCTYPE =HTML PUBLIC "-//IETF//DTD HTML//EN"><
html><
head><
meta =http-equiv=3D"Content-Type"content=3D"
text/html;=20charset=3Diso-8859-1"><
meta =name=3D"GENERATOR" content=3D"Microsoft FrontPage 2.0"><
title>Sev Payments Residential =report</title><
/head><
body =bgcolor=3D"#FFFF00"><
form =action=3D"sev_res_pay.asp" method=3D"POST"><
div =align=3D"center"><center><table border=3D"0"><
tr><
td><font=20color=3D"#000000" face=3D"Arial"><strong>StartingDate:<
/strong></font><font face=3D"Arial">(mm/dd/yy)</font></td><
td><font=20face=3D"Arial"><input type=3D"text" =size=3D"10"name=3D"
StartDate"></font></td><
/tr><
tr><
td><font=20color=3D"#000000" face=3D"Arial"><strong>EndingDate:<
/strong></font><font face=3D"Arial">(mm/dd/yy)</font></td><
td><font=20face=3D"Arial"><input type=3D"text" =size=3D"10"name=3D"
EndDate" =value=3D"<% =3DDate %>"></font></td><
/tr><
/table><
/center></div><p align=3D"center"><font=20face=3D"Arial"><inputtype=3D"
submit" name=3D"B1"=20value=3D"View=20Report"></font></p><
/form><
p> </p><
p><font face=3D"Arial"><% ELSE=20%></font></p><
div =align=3D"center"><center><
table =border=3D"3"=20cellpadding=3D"2" =cellspacing=3D"3" width=3D"100%"bgcolor=3D"
#000080"><
tr><
td =align=3D"center"=20width=3D"100%" =bgcolor=3D"#FFFFFF"bordercolor=3D"
#0000FF"=20bordercolordark=3D"#000080"bordercolorlight=3D"
#00FFFF"><font=20color=3D"#000000" size=3D"7"face=3D"
Arial"><strong>Glass City Internet SEV =Users.</strong></font></td><
/tr><
/table><
/center></div><div align=3D"center"><center><
table =border=3D"1"=20cellpadding=3D"2" =cellspacing=3D"3" width=3D"100%"bgcolor=3D"
#0000FF"><
tr><
td =width=3D"100%"><p align=3D"center"><font=20color=3D"#FFFF00"size=3D"
6">Residential Users</font></p><
/td><
/tr><
/table><
/center></div><
p =align=3D"center"><font color=3D"#000080"=20size=3D"6" face=3D"Arial"><strong>UserPayment Report For Period ( <
% =3D =StartDate%> through <% =3D =EndDate %>)</strong></font></p><
% =On Error Resume Nextrs.MoveFirst
do while not rs.EOF %
><div align=3D"center"><center><
table =border=3D"1"=20cellpadding=3D"2" =cellspacing=3D"1" width=3D"100%"bgcolor=3D"
#0000FF"><
tr><
td =width=3D"50"=20bgcolor=3D"#FFFF00"><font face=3D"Arial"><strong>CustomerID<
/strong></font></td><
td =width=3D"50"=20bgcolor=3D"#FFFF00"><font face=3D"Arial"><strong>Dateof Payment<
/strong></font></td><
td =width=3D"50"=20bgcolor=3D"#FFFF00"><font face=3D"Arial"><strong>AmountPaid<
/strong></font></td><
td =bgcolor=3D"#FFFF00"><font face=3D"Arial"><strong>Login =ID</strong></font></td><
td =bgcolor=3D"#FFFF00"><font face=3D"Arial"><strong>lastName<
/strong></font></td><
td =bgcolor=3D"#FFFF00"><font face=3D"Arial"><strong>PaymentID<
/strong></font></td><
td =bgcolor=3D"#FFFF00"><font face=3D"arial"><strong>InvoiceID</strong></font></td><
td =bgcolor=3D"#FFFF00"><font face=3D"arial"><strong>PaymentMethod<
/strong></font></td><
/tr><
tr><
td =bgcolor=3D"#FFFFFF"><font color=3D"#000000"=20size=3D"4"face=3D"
Arial"><strong><%=20=3D=20rs.Fields("CustomerID").Value %></strong></font></td><
td =bgcolor=3D"#FFFFFF"><font color=3D"#000000"=20size=3D"3"face=3D"
Arial"><strong><%=20=3D=20rs.Fields("Date").Value %>=20</strong></font></td><
td =bgcolor=3D"#FFFFFF"><font color=3D"#000000"=20size=3D"3"face=3D"
Arial"><strong><%=20=3D=20rs.Fields("Amount").Value %></strong></font></td><
td =bgcolor=3D"#FFFFFF"><font color=3D"#000000"=20size=3D"3"face=3D"
Arial"><strong><%=20=3D=20rs.Fields("Login").Value %></strong></font></td><
td =bgcolor=3D"#FFFFFF"><font color=3D"#000000"=20size=3D"3"face=3D"
Arial"><strong><%=20=3D=20rs.Fields("LastName").Value %></strong></font></td><
td =bgcolor=3D"#FFFFFF"><font color=3D"#000000"=20size=3D"3"face=3D"
arial"><strong><%=20=3D=20rs.Fields("PaymentID").Value %></strong></font></td><
td =bgcolor=3D"#FFFFFF"><font color=3D"#000000"=20size=3D"3"face=3D"
arial"><strong><%=20=3D=20rs.Fields("InvoiceID").Value %></strong></font></td><
td =bgcolor=3D"#FFFFFF"><font color=3D"#000000"=20size=3D"3"face=3D"
arial"><strong><%=20=3D=20rs.Fields("PayMethod").Value %></strong></font></td><
/tr><
tr><
td =width=3D"50"=20bgcolor=3D"#000000"><font color=3D"#FFFF00"face=3D"
Arial"><strong>Subtotal of Payments</strong></font></td><
td =bgcolor=3D"#FFFFFF"><font face=3D"Arial"><strong><% =3D=20rs.Fields("subtotals").Value %></strong></font></td><
/tr><
font =face=3D"Arial"><strong><% rs.MoveNextloop %
></strong></font><
/table><
/center></div><
p =align=3D"center"><br>Total Payments <
font=20face=3D"Arial"><strong><% =3D rs.Fields("totals").Value %></strong></font><br><
/p><
form =action=3D"new_users.asp" method=3D"POST"><
p =align=3D"center"><font face=3D"Arial"><strong><inputtype=3D"
submit" name=3D"B1"=20value=3D"New=20Report"></strong></font></p><
/form><
p><font face=3D"Arial"><% END IF=20%></font></p><
/body><
/html>