output of data not coming out right

[pcdebb]

I seem to be very stumped on some asp code and I just don't know where to turn. I have a query that returns data and I want to show three of the fields. The fields/data (a general example):

area: this area
date: 4/25/04
counts: 3

area: that area
date: 4/25/04
counts: 6

area: another area
date: 4/25/04
counts: 4

area: this area
date: 4/26/04
counts: 6

area: another area
date: 4/26/04
counts: 4

etc......

now the way i would like to display this data:

date | this area | that area | another area (column headers)
4/25 | 3 | 6 | 4
4/26 | 6 | 0 | 4

notice that "that area" doesnt have any data for 4/26. if that is the case I want it to show a zero in the output. right now the data doesnt insert my zero and that second 4 will be there in its place, putting everything after it out of place.

in my asp code, after i spit out the column headers, i goto the next row, spit out the date, and I'm "trying" to correctly match the date with the column header, if the data is there, put that value, if there's no column header match for the date, then there's no data, enter the zero. I've run into alot of wierd problems (such as subscript out of range for the array i'm storing the info in, i got it to work), but the numbers come out, just not where they should go. the second row of data (after the column headers) the numbers seem to be right, but after that, they also seem to repeat.

I don't know what I"m doing wrong, but I've tried so many different things for the last four days and I'm getting myself more confused and frustrated as I try to get it to work correctly. Staying away from the code for a whole day didn't even help, as I'm still utterly confused as to the structure of this. I thank any brave soul in advance for any kind of guidance in the right direction

here's the snippet in question if it helps:

for i = 0 to 9
rdate(i) = cmdate(i)
thisDate = rdate(i)
response.write "<tr><td width='9%'><font size='1' face='Veranda, Arial, Helvetica, sans-serif'>"& thisdate & "</font></td>"
t = "select * from qgetCountsWeek where ((qdate = #"&thisDate&"#) AND (division = '"&getdivision&"'));"
rs2.open t, db, adOpenStatic
do while not rs2.eof
for t = 0 to aZoneCount
if aZone(t) = rs2("zone") then
countme = rs2("countme")
else
countme = 0
end if
response.Write "<td width='9%'><font size='1' face='Veranda, Arial, Helvetica, sans-serif'>" & countme & "</font></td>"
rs2.movenext
next
loop
response.write "</tr>"
rs2.close
next

[damonsiusta]

1. You should not be using’t’ as a counter. 't' has previously been defined as your select statement (i.e. a string), probably best to Use a new variable.

2. Remember that it's probably a good idea to check the recordcount before you enter your loop (i.e. if rs2.recordcount <> 0) and then call rs2.movefirst to be sure you are at the very first record.

3. Your for loop is iterating through the recordset (i.e. calling the movenext function) but then your while loop is checking for a eof. This will more than likely cause an error unless you call rs2.movefirst after you have finished checking your array.

example:

for t = 0 to aZoneCount
rs2.movefirst
do while not rs2.eof
if aZone(t) = rs2("zone") then
countme = rs2("countme")
else
countme = 0
end if

response.Write "<td width='9%'><font size='1' face='Veranda, Arial, Helvetica, sans-serif'>" & countme & "</font></td>"
rs2.movenext
loop
next

What I have done is move your while loop inside the array for loop, I don't know hoe your array is defined but hopefully this will solve your problem.

4.BTW, I also noticed you are giving the td cell a width of 9%, This might become a problem if you have more than 10 items in the array.

5. Another solution to your problem would probably be to change your select statement to do all this work for you i.e. return a resultset with the recordset setout like you defined.

Hope this helps, otherwise give me more info and i'll look at the problem a bit more... :)