Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Jun 2007
    Location
    London
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rendering a table grid from ASP/Access

    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):
    http://www.thestoryworks.com/publish...ng/wants63.htm

    but it currently looks like this:
    http://www.tmcreative.co.uk/db/build...a_orig_fix.asp

    The code I've created looks like this:

    Code:
    <%
    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

  • #2
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Hmm - thought this would be easy but it is a little tricky, isn't it?

    This is what I've got:

    Code:
    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.

  • #3
    New Coder
    Join Date
    Jun 2007
    Location
    London
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:

    Code:
    <%
    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/publish...ng/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

  • #4
    New Coder
    Join Date
    Jun 2007
    Location
    London
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •