...

View Full Version : Rendering a table grid from ASP/Access



MattiMan
06-10-2007, 03:21 PM
I've been to two other Forums with this question and no one seems able to help.

This has been driving me up the wall for a couple of weeks. I set out to convert my comic collection check list (geek that I am!) from a static HTML table to a dynamic ASP-generated one.

My intention is that the final result should look like this (achieved statically):
www.thestoryworks.com/publishing/comics/collecting/wants63.htm

but it currently looks like this:
www.tmcreative.co.uk/db/buildtable03a_orig_fix.asp

The code I've created looks like this:


<%
Option Explicit
Dim strConnect
%>
<!-- #include file="admin/connect_local.asp" -->
<!-- METADATA TYPE="typelib"
FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->

<HTML>
<HEAD>
<title>TheStoryWorks.com :: Comics :: Collecting :: Marvel 1963</title>
<link href="style.css" rel="stylesheet" type="text/css">
</pre></HEAD>
<BODY>

<%

Dim objCommand, objRS, varCounter, varMonthCount, varMonthCount10, varLoop, num, tName
Set objCommand = Server.CreateObject("ADODB.Command")

objCommand.ActiveConnection = strConnect

objCommand.CommandText = "SELECT Title.titleName, Item.issue, Item.month, Item.year, Item.have " & _
"FROM Title INNER JOIN Item ON Title.idTitle = Item.idTitle " & _
"WHERE (Item.year LIKE '63') ORDER BY titleName,month;"

objCommand.CommandType = adCmdText

Set objRS = objCommand.Execute
Set objCommand = Nothing
varMonthCount = 1
varMonthCount10 = 10

Response.Write "<table width='100%' border='0' cellspacing='0' cellpadding='2'>"
Response.Write "<tr bgcolor='cccccc'><td class='bold'>Date Published</td>"
For varCounter = 1 to 9
Response.Write "<td class='bold'>0" & varMonthCount & "/" & objRS("year") & "</td>"
varMonthCount = varMonthCount + 1
Next
For varCounter = 10 to 12
Response.Write "<td class='bold'>" & varMonthCount10 & "/" & objRS("year") & "</td>"
varMonthCount10 = varMonthCount10 + 1
Next
Response.Write "<tr>"
For varCounter = 1 to 13
Response.Write "<td>&nbsp;</td>"
Next
Response.Write "</tr>"

' RENDER BODY OF TABLE

num = 0

Do While Not objRS.EOF
Response.Write "<tr"

' WE WANT ALTERNATING ROWS, GREY AND WHITE, SO WE USE THE
' NUM VARIABLE TO TELL US WHAT COLOUR TO MAKE EACH TABLE ROW...

if num mod 2 = 0 then Response.Write " bgcolor=#ffffff" Else Response.Write " bgcolor=#cccccc"
if num <> 0 then
end if

Response.Write "><td class='bold'>" & objRS("titleName") & "</td>"

' BEGIN TO RENDER THE ISSUE NUMBERS

varloop = 1
For varloop = 1 to 12

If objRS("month") <> varLoop then ' IS THE ISSUE IN THE RIGHT COLUMN?
Response.Write "<td align='center'>-</td>"
Else
Response.Write "<td align='center'>" & objRS("issue") & "</td>" ' IF SO, RENDER THE ISSUE NUMBER
'objRS.MoveNext ' MOVE TO THE NEXT ROW
End If
Next ' GO ROUND THE LOOP AGAIN

Response.Write "</tr>"
num = num + 1
objRS.MoveNext ' EMERGENCY MOVENEXT
Loop

objRS.Close
Set objRS = Nothing
%>
</table>
</BODY>
</HTML>


The first objRS.MoveNext (currently commented out) is where I thought the command to move to the next row of the database SHOULD be, but when it's uncommented, I get an Exception error message.

By having the objRS.MoveNext just before the Loop, then at least the required data from the Access DB displays, but it displays incorrectly, placing each issue of a Comic Title on a separate line.

It SHOULD be placing all the issues of say, Amazing Spider-Man, for that year on a single row.

My question is: Why can I not put the objRS.MoveNext as part of an If...Then conditional command right after I've rendered the issue number in the For...Next loop?

(Really perceptive coders will also see another problem looming: How will the code handle the row change when there is no issue of a title published in December? I wish I knew ...)

Best,

Mattiman

Spudhead
06-11-2007, 02:48 PM
Hmm - thought this would be easy but it is a little tricky, isn't it?

This is what I've got:



dim oCmd, oRS, aResults
set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = strConnect
oCmd.CommandText = "SELECT Title.idTitle. Title.titleName, Item.issue, Item.month, Item.year, Item.have " & _
"FROM Title INNER JOIN Item ON Title.idTitle = Item.idTitle " & _
"WHERE (Item.year LIKE '63') ORDER BY titleName,month;"
set oRS = oCmd.execute
set oCmd = nothing
if not oRS.EOF then aResults = oRS.getRows()
set oRS = nothing


if isArray(aResults) then

dim iCurrentTitleId, iCurrentMonth
iCurrentItemTitleId = 0
iCurrentMonth = 1
dim iThisItemTitleId, sThisItemTitleName, sThisItemIssue, iThisItemMonth, sThisItemYear, sThisItemHave

for i = lBound(aResults,2) to uBound(aResults,2)

iThisItemTitleId = cInt(aResults(0,i))
sThisItemTitleName = aResults(1,i)
sThisItemIssue = aResults(2,i)
iThisItemMonth = cInt(aResults(3,i))
sThisItemYear = aResults(4,i)
sThisItemHave = aResults(5,i)

sRowBg = "#ccc"
if i mod 2 = 0 then sRowBg = "#fff"


if iThisItemTitleId <> iCurrentItemTitleId then 'new title, so:

'write out remaining month cells for current row
for j = 1 to (12-iCurrentMonth)
response.write " <td align=""center"">-</td>" & vbCrLf
next

if iCurrentItemTitleId > 0 then response.write("</tr>" & vbCrLf)
'we know we're not on the first row of the results, so we've got an open tr to close

'start a new row
response.write("<tr bgcolor=""" & sRowBg & """>" & vbCrLf)
response.write(" <td class=""bold"">"& sThisItemTitleName &"</td>" & vbCrLf)

'set this to be the "working" title ID
iCurrentItemTitleId = iThisItemTitleId

'reset the month count for the new row
iCurrentMonth = 1
end if


if iThisItemMonth = iCurrentMonth then
response.write " <td align=""center"">" & sThisItemIssue & "</td>" & vbCrLf
else
response.write " <td align=""center"">-</td>" & vbCrLf
end if
iCurrentMonth = iCurrentMonth + 1

next

end if


I THINK it'll do what you want, but I haven't tested it. The logic itself should be fairly self-explanatory (assuming I've got it right :)) - two other variations on your code are (a) I'm pulling in the title ID in the SQL to use as a unique identifier instead of the name, and (b) I'm using getrows() instead of looping through the recordset itself, just for performance.

MattiMan
06-11-2007, 05:56 PM
Hi, Spudhead,

Thanks for taking the time to look at this aggravating problem. Much appreciated!

I did look at the idea of going down the GetRows route, but it looked so complicated in my copy of Wrox's "Beginning ASP" that I completely chickened out ...

OK - I went over the code you posted and tweaked it to run with my DB up on the server. Here's what it looks like now:


<%
Option Explicit
Dim strConnect, varMonthCount, varMonthCount10, varCounter
%>
<!-- #include file="admin/connect.asp" -->
<!-- METADATA TYPE="typelib"
FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->

<HTML>
<HEAD>
<title>TheStoryWorks.com :: Comics :: Collecting :: Marvel 1963</title>
<link href="style.css" rel="stylesheet" type="text/css">
</pre></HEAD>
<BODY>

<%
Dim syear
syear="63"
varMonthCount = 1
varMonthCount10 = 10
%>

<table width="100%" border="0" cellspacing="0" cellpadding="2">
<tr bgcolor="cccccc"><td class="bold">Date Published</td>
<%
For varCounter = 1 to 9
%>
<td class="bold">0<%=varMonthCount & "/" & syear%></td>
<%
varMonthCount = varMonthCount + 1
Next
For varCounter = 10 to 12
%>
<td class="bold"><%=varMonthCount10 & "/" & syear%></td>
<%
varMonthCount10 = varMonthCount10 + 1
Next
Response.Write "<tr>"
For varCounter = 1 to 13
Response.Write "<td>&nbsp;</td>"
Next
%>

</tr>
<%

dim oCmd, oRS, aResults
set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = strConnect
oCmd.CommandText = "SELECT Title.idTitle, Title.titleName, Item.issue, Item.month, Item.year, Item.have " & _
"FROM Title INNER JOIN Item ON Title.idTitle = Item.idTitle " & _
"WHERE (Item.year LIKE '63') ORDER BY titleName,month;"
set oRS = oCmd.execute
set oCmd = nothing
if not oRS.EOF then aResults = oRS.getRows()
set oRS = nothing


if isArray(aResults) then

dim iCurrentItemTitleId, iCurrentMonth, i, j, sRowBg
iCurrentItemTitleId = 0
iCurrentMonth = 1
dim iThisItemTitleId, sThisItemTitleName, sThisItemIssue, iThisItemMonth, sThisItemYear, sThisItemHave

for i = lBound(aResults,2) to uBound(aResults,2)

iThisItemTitleId = cInt(aResults(0,i))
sThisItemTitleName = aResults(1,i)
sThisItemIssue = aResults(2,i)
iThisItemMonth = cInt(aResults(3,i))
sThisItemYear = aResults(4,i)
sThisItemHave = aResults(5,i)

sRowBg = "#cccccc"
if i mod 2 = 0 then sRowBg = "#ffffff"


if iThisItemTitleId <> iCurrentItemTitleId then 'new title, so:

'write out remaining month cells for current row
for j = 1 to (12-iCurrentMonth)
response.write " <td align=""center"">-</td>" & vbCrLf
next

if iCurrentItemTitleId > 0 then response.write("</tr>" & vbCrLf)
'we know we're not on the first row of the results, so we've got an open tr to close

'start a new row
response.write("<tr bgcolor=""" & sRowBg & """>" & vbCrLf)
response.write(" <td class=""bold"">"& sThisItemTitleName &"</td>" & vbCrLf)

'set this to be the "working" title ID
iCurrentItemTitleId = iThisItemTitleId

'reset the month count for the new row
iCurrentMonth = 1
end if


if iThisItemMonth = iCurrentMonth then
response.write " <td align=""center"">" & sThisItemIssue & "</td>" & vbCrLf
else
response.write " <td align=""center"">-</td>" & vbCrLf
end if
iCurrentMonth = iCurrentMonth + 1

next

end if


Set oRS = Nothing
%>
</table>
</BODY>
</HTML>

The page output this:

http://www.tmcreative.co.uk/db/BuildTable03d_CF_fix.asp

which has solved the problem of each issue of a title appearing on a separate row of the table. But if you look at the static version here:

http://www.thestoryworks.com/publishing/comics/collecting/wants63.htm

you'll see that some data has been left out of the loop (literally!). And the alternating grey/white rows isn't working quite right either.

If you need to grab a copy of the DB, it's here:

http://www.tmcreative.co.uk/db/admin/db/sw_com.mdb

Big thank you for taking the time to look at this (and relieved to hear that you think it's tricky, too!)

MattiMan

MattiMan
06-12-2007, 10:22 AM
Spudhead,

Sorry, but the URLs aren't displaying corrrectly (even though I put URL tags around them! They should be (just add www):

Static:
thestoryworks.com/publishing/comics/collecting/wants63.htm

Dynamic (not working right)
tmcreative.co.uk/db/BuildTable03d_CF_fix.asp

Database:
tmcreative.co.uk/db/admin/db/sw_com.mdb

Hope this is clearer ...

MattiMan



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum