Loop results almost right...help.

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>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</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>
 
E

eyoung

oops...this thing cut off my rows..it should look like

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
 
L

Larry Bud

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):
Geez, you're doing it the hard (and wrong) way.

You need to create one SQL statement that joins the two tables.
 
E

eyoung

My inner until loop is only being activated during the first loop of
the outer until loop?
Both loops work apart.
 
B

Bob Barrows [MVP]

Wow...that wasn't really helpful
Actually, it could have been, if you understood what was meant by joining
the tables in your query. Is that the case? That you don't understand
joining tables?
 
E

eyoung

I didn't join tables because this is ONE table.

One query statement gets distinct 'Service Numbers' for my outer loop
The other query statment gets every record.

I then loop using each distinct 'Service Number' getting the records
associated with it before going to the next 'Service Number'

Again this is only ONE table. Because I was wanting distinct elements
for the outer loop I found it easier to query the table twice.
 
D

Dave Anderson

Can someone tell me what I've done wrong here...

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

1. You don't need two idential connections here.

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)

2. You omit the ORDER BY clause in the first query.
3. Aside from ordering, the second recordset is a subset of the first, so it
is unneccessary.

If rs("Service Number") = rs2("Service Number") Then

4. Use of default properties. Take your task seriously -- compare values to
values, not the inherently dicey objects to objects:

If rs.Fields("Service Number").Value = ...



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.
 
E

eyoung

I placed the ORDER BY in my first query
And I'm now comparing values instead of objects...I aget the same
results.

Granted two queries is not the best way to do something.

However there is something wrong with the logic of the 2 statments (not
queries) together.
Both queries get the info I want, and both statements work appart. But
the statements don't work together.
 
B

Bob Barrows [MVP]

I didn't join tables because this is ONE table.

Ah! Now we're getting somewhere.
That makes no difference. Using table aliases, you can join the same table
to itself as many times as you need to.

Let's go back to your queries and try to see what you should be doing ...
You don't need to join anything. You don't even need two queries! You're
overcomplicating this.
Try this (since I obviously cannot test this, there may be typos):


sSQL = "SELECT [Service Number],[Subscriber User Name]," & _
"[Description],[To Number],[Duration],[Connect Date], " & _
"[Total Charge] "
" FROM 06_Statements" & _
" WHERE [Billing Date] = ?" & _
"And [Total Charge] > 0" & _
" AND Description LIKE '%Cellular%'" & _
" ORDER BY [Service Number]"

dim cmd, arData
set cmd=createobject("adodb.command")
cmd.commandtype = 1 'adCmdText
cmd.commandtext = sSQL
set cmd.activeconnection=Connect
set rs=cmd.execute(,array(Request.Form("Month")))
if not rs.eof then arData = rs.getrows
rs.close: set rs = nothing
Connect.close: set Connect= nothing

if not isarray(arData) then
response.write "no data"
response.end
end if
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1">
</head>

<body>
<table>
<tr>
<th>Service Number</th>
<th>Subscriber</th>
<th>Description</th>
<th>To Number</th>
<th>Duration</th>
<th>Connect Date</th>
<th>Amount</th>
</tr>
<%
dim curServNum, newServNum, i, j
for i = 0 to ubound(arData,2)
newServNum=arData(0,i)
if newServNum<>curServNum then
curServNum = newServNum
response.write "<tr><td colspan=""7"">" & _
curServNum & "</td></tr>"
end if
response.write "<tr>"
for j=1 to 6
if j = 1 then
response.write "<td colspan=""2"">"
else
response.write "<td>"
end if
response.write arData(j,i) & "</td>"
next
response.write "</tr>"
next
%>

</table>
</body>
</html>

Bob Barrows
 
E

eyoung

Ok, this is what I did not understand

Do until rs2.eof
Do until rs.eof
rs.MoveNext
rs2.MoveNext


This is all anyone needs to know. Before you reach the second record of
rs2,
you have moved to the end of rs, so rs.EOF will be true from then on.
 
D

Dave Anderson

Please do not top post on USENET when interlacing comments. It breaks the
flow of the conversation for the reader.

Ok, this is what I did not understand

I recognize that you thought recordset iteration was an analog to array
iteration, and in many ways, it is. But even with arrays, your O(n*n)**
nested looping is seriously frowned on whether you are using arrays or any
other structure. This is the reason ALL of the responders have suggested
that you change your approach.

Think about it in terms of your goals. You want to produce a list. Ideally,
your data structure should look like that list. Then you merely step through
it once and display whatever is appropriate for the given row.

Aggregating, summarizing and ordering your data can add overhead, and you
will need to make decisions on the best place to do that. In general, your
database is going to be better at those (as in MORE EFFICIENT) than you are.

Nothing could be a better example of this than sorting. Databases use
sophisticated sorting techinques that may take advantage of any number of
algorithms, but you can generally no worse than expect O(n*log(n)) from the
database for a full sort, while your approach -- a less-sorted "sorting and
sieving" -- was a solid O(n*n).



**http://en.wikipedia.org/wiki/Computational_complexity_theory#Overview

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,901
Latest member
Noble71S45

Latest Threads

Top