E
eyoung
Can someone tell me what I've done wrong here...this kinda works...but
no cigar!
I want to loop threw each "Service Number" and get all entries before
going on to the next.
What looks something like this (didn't include all fields in example):
5554930631
Cellular Toll Charges 5553452212 11/30/05
2.47
Cellular Roming 9999999999 11/29/05
0.69
Cellular Air Time 5553455555 11/12/05
3.45
Cellular Air Time 9006661234 11/17/05
9.99
5556491514
5556493465
5556496458
it works the first time threw but then just gives me the Service
Number.
<!--#include file="include.asp" -->
<%
Dim connect, rs, connect2, rs2
Dim sSQL, sSQL2
Set connect = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateOBject("ADODB.Recordset")
connect.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sTelecom
connect.open
Set connect2 = Server.CreateObject("ADODB.Connection")
Set rs2 = Server.CreateOBject("ADODB.Recordset")
connect2.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sTelecom
connect2.open
%>
<%
sSQL = "SELECT *" & _
" FROM 06_Statements" & _
" WHERE [Billing Date] = '" & Request.Form("Month") & "'" & _
"And [Total Charge] > 0" & _
" AND Description LIKE'%Cellular%'"
set rs = Connect.Execute(sSQL)
sSQL2 = "SELECT DISTINCT [Service Number]" & _
" FROM 06_Statements" & _
" WHERE [Billing Date] = '" & Request.Form("Month") & "'" & _
" And [Total Charge] > 0" & _
" AND Description LIKE'%Cellular%'" & _
" ORDER BY [Service Number]"
set rs2 = Connect2.Execute(sSQL2)
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1">
</head>
<body>
<table>
<tr>
<td>Service Number</td>
<td>Subscriber</td>
<td>Description</td>
<td>To Number</td>
<td>Duration</td>
<td>Connect Date</td>
<td>Amount</td>
</tr>
<%
Do until rs2.eof
%>
<tr>
<td><%=rs2("Service Number")%></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<%
Dim sBackgroundColor
sBackgroundColor = "#ffffff"
Do until rs.eof
If rs("Service Number") = rs2("Service Number") Then
If sBackgroundColor = "#ffffff" Then
sBackgroundColor = "#f7f7f7"
Else
sBackgroundColor = "#ffffff"
End If
%>
<tr bgcolor="<%=sBackgroundColor%>">
<td><%=rs("Service Number")%></td>
<td><%=rs("Subscriber User Name")%></td>
<td><%=rs("Description")%></td>
<td><%=rs("To Number")%></td>
<td align="center"><%=rs("Duration")%></td>
<td><%=rs("Connect Date")%></td>
<td align="right"><%=rs("Total Charge")%></td>
</tr>
<% End If
rs.MoveNext
Loop %>
<%
rs2.MoveNext
Loop %>
</table>
</body>
</html>
no cigar!
I want to loop threw each "Service Number" and get all entries before
going on to the next.
What looks something like this (didn't include all fields in example):
5554930631
Cellular Toll Charges 5553452212 11/30/05
2.47
Cellular Roming 9999999999 11/29/05
0.69
Cellular Air Time 5553455555 11/12/05
3.45
Cellular Air Time 9006661234 11/17/05
9.99
5556491514
5556493465
5556496458
it works the first time threw but then just gives me the Service
Number.
<!--#include file="include.asp" -->
<%
Dim connect, rs, connect2, rs2
Dim sSQL, sSQL2
Set connect = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateOBject("ADODB.Recordset")
connect.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sTelecom
connect.open
Set connect2 = Server.CreateObject("ADODB.Connection")
Set rs2 = Server.CreateOBject("ADODB.Recordset")
connect2.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sTelecom
connect2.open
%>
<%
sSQL = "SELECT *" & _
" FROM 06_Statements" & _
" WHERE [Billing Date] = '" & Request.Form("Month") & "'" & _
"And [Total Charge] > 0" & _
" AND Description LIKE'%Cellular%'"
set rs = Connect.Execute(sSQL)
sSQL2 = "SELECT DISTINCT [Service Number]" & _
" FROM 06_Statements" & _
" WHERE [Billing Date] = '" & Request.Form("Month") & "'" & _
" And [Total Charge] > 0" & _
" AND Description LIKE'%Cellular%'" & _
" ORDER BY [Service Number]"
set rs2 = Connect2.Execute(sSQL2)
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1">
</head>
<body>
<table>
<tr>
<td>Service Number</td>
<td>Subscriber</td>
<td>Description</td>
<td>To Number</td>
<td>Duration</td>
<td>Connect Date</td>
<td>Amount</td>
</tr>
<%
Do until rs2.eof
%>
<tr>
<td><%=rs2("Service Number")%></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<%
Dim sBackgroundColor
sBackgroundColor = "#ffffff"
Do until rs.eof
If rs("Service Number") = rs2("Service Number") Then
If sBackgroundColor = "#ffffff" Then
sBackgroundColor = "#f7f7f7"
Else
sBackgroundColor = "#ffffff"
End If
%>
<tr bgcolor="<%=sBackgroundColor%>">
<td><%=rs("Service Number")%></td>
<td><%=rs("Subscriber User Name")%></td>
<td><%=rs("Description")%></td>
<td><%=rs("To Number")%></td>
<td align="center"><%=rs("Duration")%></td>
<td><%=rs("Connect Date")%></td>
<td align="right"><%=rs("Total Charge")%></td>
</tr>
<% End If
rs.MoveNext
Loop %>
<%
rs2.MoveNext
Loop %>
</table>
</body>
</html>