Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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





TheStoryWorks.com :: Comics :: Collecting :: Marvel 1963




<%

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 "Date Published"
For varCounter = 1 to 9
Response.Write "0" & varMonthCount & "/" & objRS("year") & ""
varMonthCount = varMonthCount + 1
Next
For varCounter = 10 to 12
Response.Write "" & varMonthCount10 & "/" & objRS("year") & ""
varMonthCount10 = varMonthCount10 + 1
Next
Response.Write ""
For varCounter = 1 to 13
Response.Write " "
Next
Response.Write ""

' RENDER BODY OF TABLE

num = 0

Do While Not objRS.EOF
Response.Write " 0 then
end if

Response.Write ">" & objRS("titleName") & ""

' 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 "-"
Else
Response.Write "" & objRS("issue") & "" ' IF SO, RENDER THE ISSUE NUMBER
'objRS.MoveNext ' MOVE TO THE NEXT ROW
End If
Next ' GO ROUND THE LOOP AGAIN

Response.Write ""
num = num + 1
objRS.MoveNext ' EMERGENCY MOVENEXT
Loop

objRS.Close
Set objRS = Nothing
%>


[/code]

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

Comments

  • FlakesFlakes Posts: 642Member
    Try this:

    [code]
    <%
    Option Explicit
    Dim strConnect
    %>





    TheStoryWorks.com :: Comics :: Collecting :: Marvel 1963




    <%

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

    <%
    For varCounter = 1 to 9
    %>

    <%
    varMonthCount = varMonthCount + 1
    Next
    For varCounter = 10 to 12
    %>

    <%
    varMonthCount10 = varMonthCount10 + 1
    Next
    Response.Write "<tr>"
    For varCounter = 1 to 13
    Response.Write ""
    Next
    %>



    ' RENDER BODY OF TABLE
    <%
    num = 0

    Dim bgcolor

    Do While Not objRS.EOF
    ' 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
    bgcolor="#ffffff"
    Else
    bgcolor=#cccccc"
    End If
    %>
    ">

    <%
    ' 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?
    %> '

    <%
    Else ' IF SO, RENDER THE ISSUE NUMBER
    %>

    <%

    End If
    Next ' GO ROUND THE LOOP AGAIN
    %>


    <%
    num = num + 1
    objRS.MoveNext
    Loop

    objRS.Close
    Set objRS = Nothing
    %>
    Date Published0<%=varMonthCount & "/" & objRS("year")%><%=varMonthCount10 & "/" & objRS("year")%> 
    <%=objRS("titleName")%>  <%=objRS("issue")%>

    [/code]


    cannot see what your page looks like now, because the links are broken.
    Anyway, try it, if it is not as you want, give us the database table details and and what you want to show.





  • MattiManMattiMan Posts: 6Member
    : Try this:
    :
    : [code]:
    : <%
    : Option Explicit
    : Dim strConnect
    : %>
    :
    :
    :
    :
    :
    : TheStoryWorks.com :: Comics :: Collecting :: Marvel 1963
    :
    :
    :
    :
    : <%
    :
    : 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
    : %>
    : <%
    : <table width="100%" border="0" cellspacing="0" cellpadding="2">
    : Date Published
    : <%
    : For varCounter = 1 to 9
    : %>
    : 0<%=varMonthCount & "/" & objRS("year")%>
    : <%
    : varMonthCount = varMonthCount + 1
    : Next
    : For varCounter = 10 to 12
    : %>
    : <%=varMonthCount10 & "/" & objRS("year")%>
    : <%
    : varMonthCount10 = varMonthCount10 + 1
    : Next
    : Response.Write "<tr>"
    : For varCounter = 1 to 13
    : Response.Write " "
    : Next
    : %>
    :
    :
    :
    : ' RENDER BODY OF TABLE
    : <%
    : num = 0
    :
    : Dim bgcolor
    :
    : Do While Not objRS.EOF
    : ' 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
    : bgcolor="#ffffff"
    : Else
    : bgcolor=#cccccc"
    : End If
    : %>
    : ">
    : <%=objRS("titleName")%>
    : <%
    : ' 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?
    : %> '
    :  
    : <%
    : Else ' IF SO, RENDER THE ISSUE NUMBER
    : %>
    : <%=objRS("issue")%>
    : <%
    :
    : End If
    : Next ' GO ROUND THE LOOP AGAIN
    : %>
    :
    :
    : <%
    : num = num + 1
    : objRS.MoveNext
    : Loop
    :
    : objRS.Close
    : Set objRS = Nothing
    : %>
    :
    :
    :
    : [/code]:
    :
    :
    : cannot see what your page looks like now, because the links are
    : broken.
    : Anyway, try it, if it is not as you want, give us the database table
    : details and and what you want to show.
    :
    :
    Big "Thank You" for the reply.

    I'm sorry the links are broken, as this would enable you to see what I was trying to achieve and what I was getting with the code I have. The code you've posted above doesn't deliver a substantially different result from what I already have.

    Here's what I want the table to look like:

    thestoryworks/publishing/comics/collecting/wants63.htm
    (put www in front to make it a URL!)

    Here's what MY CODE got me ...

    tmcreative.co.uk/db/BuildTable03a_orig_fix.asp
    (again with the www!)

    Here's what your code delivered:
    tmcreative.co.uk/db/BuildTable03b_PH_fix.asp
    (www)

    The Access db is here, so you should be able to download it ...
    tmcreative.co.uk/db/admin/db/sw_com.mdb
    (www)

    I see that the main problem with the code is that the objRS.movenext instruction is in the wrong place. When I try to put that command in the right place (within the If...Then, right after the issue number is rendered) I get an error.

    When I put the objRS.movenext just before the Loop, the server likes that syntax better and runs the page, but each issue number is rendered on a new line, instead of being rendered across the row, with the months there was NO ISSUE having a "-" instead.

    You'll see what I mean when you look at the static version on thestoryworks.com

    The problem arises because there's only 7 issues of Amazing Spider-Man in 1963 and the first few were issued bi-monthly. So when there's no issue for that month, I can't do objRS.movenext, I have to move to the next column and render the correct issue there.

    It gets doubly tricky when we get to The Avengers line. Issue 1 is Sept, there's no Oct issue, issue 2 is Nov, then there's no Dec issue. BIG problem. Because the code doesn't render an issue for Dec, just a "-", there isn't an objRS.movenext either, so the ASP tries to render issue 10 of Fantastic Four in that slot instead.

    Funny, when I started to code this I thought, "How hard can it be? I've done a bit of ASP before and this is really straightforward."

    Guess I severely underestimated the trickiness of it all ...

    I really do appreciate you taking time to look at this and I apologise that I wasn't able to figure out how to get the message board to render the example URLs properly.

    Kind regards,

    MattiMan


  • FlakesFlakes Posts: 642Member
    Hi,

    I didn't really think my changes will do much for you, what i mainly did was make it a bit more readable. :-)

    Now I can see the links, and will try to make it work the way you want. But it's end of working day for me, I am at GMT + 5.30 . So maybe tomorrow ?


  • MattiManMattiMan Posts: 6Member
    Hey, Flakes,

    Thanks for taking the time to look at it. I do appeciate it.

    (GMT + 5.30 ... sounds like India to me. Only reason I know is becaue my wife has family in Pune!)

    Kind regards,

    MattiMan


    : Hi,
    :
    : I didn't really think my changes will do much for you, what i
    : mainly did was make it a bit more readable. :-)
    :
    : Now I can see the links, and will try to make it work the way you
    : want. But it's end of working day for me, I am at GMT + 5.30 . So
    : maybe tomorrow ?
    :
    :
    :

    MattiMan
    ---
    "In brightest day, in darkest night,
    no evil shall escape my sight ..."
  • FlakesFlakes Posts: 642Member
    Can you play with this for now :

    [code]
    <%
    Option Explicit
    Dim strConnect
    %>





    TheStoryWorks.com :: Comics :: Collecting :: Marvel 1963




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

    <%
    For varCounter = 1 to 9
    %>

    <%
    varMonthCount = varMonthCount + 1
    Next
    For varCounter = 10 to 12
    %>

    <%
    varMonthCount10 = varMonthCount10 + 1
    Next
    Response.Write "<tr>"
    For varCounter = 1 to 13
    Response.Write ""
    Next
    %>


    <%

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

    Conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath("..comicssw_com.mdb")&""


    Dim rs_titles,sql
    set rs_titles= conn.Execute("select Distinct(Title.titleName) from Title")

    While Not rs_titles.EOF
    num = 0
    Dim bgcolor
    If num mod 2 = 0 then
    bgcolor="#ffffff"
    Else
    bgcolor="#cccccc"
    End If

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



    Set objRS = conn.Execute(sql)
    %>

    <%


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


    %>
    ">

    <%
    ' BEGIN TO RENDER THE ISSUE NUMBERS

    varloop = 1
    For varloop = 1 to 12

    If ""&objRS("month") = varLoop then 'IF SO, RENDER THE ISSUE NUMBER
    %>
    '

    <%
    objRS.MoveNext
    Else '
    %>

    <%

    End If
    Next
    End If ' GO ROUND THE LOOP AGAIN
    objRS.Close
    %>


    <%
    num = num + 1

    rs_titles.MoveNext
    Wend


    Set objRS = Nothing
    %>
    Date Published0<%=varMonthCount & "/" & syear%><%=varMonthCount10 & "/" & syear%> 
    <%=rs_titles(0)%> <%=objRS("issue")%> nope


    [/code]

    And yes, I am from India.

    tomorrow, then.

  • MattiManMattiMan Posts: 6Member
    : Can you play with this for now :
    :
    : [code]:
    Code went here
    : [/code]:
    :
    : And yes, I am from India.
    :
    : tomorrow, then.
    :
    :
    Thanks for that second page of code. I made a couple of minor changes to get it to run with the database on the server and the result was this (make sure you put the "www." in front!):

    tmcreative.co.uk/db/BuildTable03c_PH_fix.asp

    Looks to me like the Loop's not finding any valid issue numbers in the array. (I did have another read through the GetRows section in Wrox's BEGINNING ACTIVE SERVER PAGES, but I still haven't got my head around it :-(

    Thanks for all your help with this!

    Kind regards,

    MattiMan
    MattiMan
    ---
    "In brightest day, in darkest night,
    no evil shall escape my sight ..."
  • FlakesFlakes Posts: 642Member
    This seems to work.Change the connection stuff to what you had before:

    [code]
    <%
    Option Explicit
    Dim strConnect
    %>





    TheStoryWorks.com :: Comics :: Collecting :: Marvel 1963




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

    <%
    Dim cntr
    For varCounter = 1 to 12
    cntr=""
    If Len(varCounter)=1 Then
    cntr="0"&varCounter
    Else
    cntr=varCounter
    End If
    %>

    <%
    varMonthCount = varMonthCount + 1
    Next
    %>

    <%
    Dim Conn,objCommand, objRS, varCounter, varMonthCount, varMonthCount10, varLoop, num, tName
    Set conn = Server.CreateObject("ADODB.Connection")

    Conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath("..comicssw_com.mdb")&""


    Dim rs_titles,sql
    set rs_titles= conn.Execute("select Distinct(Title.titleName),Title.idTitle from Title")
    Dim bgcolor
    num = 1

    While Not rs_titles.EOF
    bgcolor=""
    sql = "SELECT 00,Item.idItem,Item.issue, Item.month, Item.year, Item.have " & _
    "FROM Item " & _
    "WHERE Item.year = "&syear&" and Item.idTitle ="&rs_titles(1)&" ORDER BY Item.issue,Item.month;"
    Set objRS = conn.Execute(sql)
    %>
    <%
    ' 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
    bgcolor="#cccccc"
    Else
    bgcolor="#ffffff"
    End If
    %>
    ">


    <%
    ' BEGIN TO RENDER THE ISSUE NUMBERS
    For varloop = 1 to 12

    If not objRS.EOF Then
    If objRS("month") = varLoop then 'IF SO, RENDER THE ISSUE NUMBER
    %>

    <%End If%>
    <%
    if not objRS.EOF Then
    objRS.MoveNext
    End If
    Else '
    %>

    <%
    End If
    %>
    <%
    Else
    %>

    <%
    End If
    Next 'GO ROUND THE LOOP AGAIN

    objRS.Close
    set objRS=Nothing
    %>

    <%
    num=num+1
    rs_titles.MoveNext
    Wend
    Set objRS = Nothing
    %>
    Date Published<%=cntr & "/" & syear%>
    <%=rs_titles(0)%>
    <%If not objRS.EOF Then%>
    <%=objRS("issue")%>
    - -


    [/code]
  • FlakesFlakes Posts: 642Member
    I see you have found aspmessageboard.com :-)

    Listen to Bill, he is THE best around.
  • MattiManMattiMan Posts: 6Member
    : I see you have found aspmessageboard.com :-)
    :
    : Listen to Bill, he is THE best around.

    Hi, Flakes,

    Thank you for all your help. ASPmessageboard's Bill may be the best around, but it looks like you solved the problem, so B-I-G respect to you :-)

    I'll fiddle around with it some more, then send you a link to the page when I have it looking the way I want it to (with bolds for when I have the issue and the issue numbers as links to a detail page!)

    Thanks again!

    MattiMan
    MattiMan
    ---
    "In brightest day, in darkest night,
    no evil shall escape my sight ..."
  • FlakesFlakes Posts: 642Member
    : : I see you have found aspmessageboard.com :-)
    : :
    : : Listen to Bill, he is THE best around.
    :
    : Hi, Flakes,
    :
    : Thank you for all your help. ASPmessageboard's Bill may be the best
    : around, but it looks like you solved the problem, so B-I-G respect
    : to you :-)
    :
    : I'll fiddle around with it some more, then send you a link to the
    : page when I have it looking the way I want it to (with bolds for
    : when I have the issue and the issue numbers as links to a detail
    : page!)
    :
    : Thanks again!
    :
    : MattiMan
    : MattiMan
    : ---
    : "In brightest day, in darkest night,
    : no evil shall escape my sight ..."

    Hi Matt,

    Glad it's working for you.And thanks for the respect ;-)

    Faustine.
Sign In or Register to comment.