SQL inconsistencies - ASP

[mcpeekj]

Hello all. I have an ASP page (vbscript) that takes a date range as parameters and plugs them into a SQL query. The goal of the query is to bring back activity in the date range, grouped by day. The query works great in Query Analyzer, but doesn't work correctly in the ASP. When I put in the date range of 2/1/08-2/6/08, I correctly get 5 rows back in Query Analyzer. When I run it in the ASP, I get 1 row (with the first date in the range).

I am printing the SQL on the page for debugging. Here it is:

select 
'Date' = Convert(varchar, msl.datecreated,101), 
'Campaign' = 'N/A', 
'Source' = 'Portal', 
'Total Sends' = COUNT(DISTINCT (msl.mailid)), 
'Total Recipients' = COUNT(DISTINCT (msl.id)), 
'Total Received' = COUNT(DISTINCT (msl2.id)), 
'Total Reads' = COUNT(DISTINCT (mil.id)), 
'Unique Reads' = COUNT(DISTINCT (mil.mailid + mil.maildeliveryid + mil.remoteaddress)), 
'Total Opens' = COUNT(DISTINCT (mol.id)), 
'Unique Opens' = COUNT(DISTINCT (mol.maildeliveryid)), 
'Total Views' = COUNT(DISTINCT (mvl.datecreated)), 
'Unique Views' = COUNT(DISTINCT (mvl.maildeliveryid)) 
from mailsendlog_t msl 
left outer join mailopenlog_t mol ON mol.maildeliveryid = msl.[id] 
LEFT OUTER JOIN mailviewlog_t mvl ON mvl.openlogid = mol.[id] 
LEFT OUTER JOIN mailsendlog_t msl2 ON msl2.id = msl.[id] AND msl2.maildeliverystatusid = 3 
LEFT OUTER JOIN mailimpressionlog mil ON mil.maildeliveryid = msl.[id] 
LEFT OUTER JOIN mail_t m ON m.id = msl.mailid where msl.maildeliverystatusid != 1 AND 
(msl.datecreated between '2/1/2008' AND '2/6/2008') 
group by Convert(varchar, msl.datecreated,101)

The only thing I can think of is that it has to do with how I'm printing each recordset field (or that it just won't work at all). I've tried writing the output back into a SQL Server db, but it does the same thing (writes 1 row with the first date).

Do I need an additional loop in there? If so, where? Here's my output:

if not rs1.eof then
	response.write "<tr><td>" & rs("DBName") & "</td><td>" & rs1("Date") & "</td><td>" & rs1("Campaign") & "</td><td>" & rs1("Source") & "</td><td>" & rs1("Total Sends") & "</td><td>" & rs1("Total Recipients") & "</td><td>" & rs1("Total Received") & "</td><td>" & rs1("Total Reads") & "</td><td>" & rs1("Unique Reads") & "</td><td>" & rs1("Total Opens") & "</td><td>" & rs1("Unique Opens")  & "</td><td>" & rs1("Total Views") & "</td><td>" & rs1("Unique Views") & "</td></tr>"
end if
rs1.close

Any help would be appreciated.

 

[gmmastros]

[!]While[/!] not rs1.eof
    response.write "<tr><td>" & rs("DBName") & "</td><td>" & rs1("Date") & "</td><td>" & rs1("Campaign") & "</td><td>" & rs1("Source") & "</td><td>" & rs1("Total Sends") & "</td><td>" & rs1("Total Recipients") & "</td><td>" & rs1("Total Received") & "</td><td>" & rs1("Total Reads") & "</td><td>" & rs1("Unique Reads") & "</td><td>" & rs1("Total Opens") & "</td><td>" & rs1("Unique Opens")  & "</td><td>" & rs1("Total Views") & "</td><td>" & rs1("Unique Views") & "</td></tr>"
[!]Wend[/!]
rs1.close

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mcpeekj]

Thanks George. I obviously need to work on my loops.

Unfortunately, now I get stuck in some kind of infinite death spiral where IE locks up and takes up all my CPU. The query is only looking at no more than 1,000 records in this case. Any thoughts on that?

Thanks

 

[gmmastros]

The basic looping structure would be...

[blue]While not rs1.eof[/blue]
    response.write "<tr><td>" & rs("DBName") & "</td><td>" & rs1("Date") & "</td><td>" & rs1("Campaign") & "</td><td>" & rs1("Source") & "</td><td>" & rs1("Total Sends") & "</td><td>" & rs1("Total Recipients") & "</td><td>" & rs1("Total Received") & "</td><td>" & rs1("Total Reads") & "</td><td>" & rs1("Unique Reads") & "</td><td>" & rs1("Total Opens") & "</td><td>" & rs1("Unique Opens")  & "</td><td>" & rs1("Total Views") & "</td><td>" & rs1("Unique Views") & "</td></tr>"
    [blue]rs1.MoveNext[/blue]
[blue]Wend[/blue]
rs1.close

MoveNext causes the recordset to look at the next record.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mcpeekj]

Yes, that makes sense, thank you very much. Movenext is working.

One last thing (and I know it's loop related as I'm recalling my intro to programming class, just can't remember the solution)...

I have this query looping through 3 different databases right now (it will be up to 100 databases). Currently, the first database has 2 sets of identical output. All other databases have the expected 1 set of output.

What is the fix for this?