Display complex table from Access DB - ASP / Active Server Pages

peck2000

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.

The static one looks like this:
www.thestoryworks.com/publishing/comics/collecting/wants63.htm
which is the look I'm trying to replicate in a dynamic version.

My dynamic one currently looks like this:
www.tmcreative.co.uk/db/buildtable01_orig.asp
As you can see, it is starting a new line for each issue, rather than rendering a year of Spider-Man issues on a single line.

The code looks like this:

Expand|Select|Wrap|Line Numbers

 <%

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

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

    Response.Write "<tr>"
 
' RENDER BODY OF TABLE
 
num = 0
 
While Not objRS.EOF

  Response.Write "<tr><td class='bold'"
 
' 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 ">" & objRS("titleName") & "</td>"
 
' BEGIN TO RENDER THE ISSUE NUMBERS
 
    varloop = 1

    For varloop = 1 to 12

    Response.Write "<td align='center'"
 
' CHOOSE ROW COLOUR FOR ISSUE MUMBERS
 
      if num mod 2 = 0 then Response.Write " bgcolor=#ffffff" Else Response.Write " bgcolor=#cccccc"

      if num <> 0 then

      end if
 
' IF I HAVE THE ISSUE, RENDER IT IN BOLD
 
    If objRS("have") = True Then Response.Write " class='bold'>" Else Response.Write ">" End If
 
' RENDER THE ISSUE NUMBER
 
  If objRS("month") = varLoop then Response.Write objRS("issue") else Response.Write "-" end if

  Response.Write "</td>"
 
'    LOOP TO THE NEXT ISSUE

    'objRS.MoveNext

    Next
 
' MOVE TO THE NEXT TITLE AND LOOP AGAIN
 
Response.Write "</tr>"

objRS.MoveNext

num = num + 1
 
wEnd
 
objRS.Close

Set objRS = Nothing

%>

</table>

</BODY>

</HTML>

So ... right after the comment "RENDER THE ISSUE NUMBER" I know I have to moveNext somehow, but when I uncomment that line, I get an error.

So I tried this:

Expand|Select|Wrap|Line Numbers

 
' RENDER THE ISSUE NUMBER
 
  If objRS("month") = varLoop _

  then Response.Write objRS("issue")

  objRS.MoveNext

  else Response.Write "-"

  end if

  Response.Write "</td>"

So that where an issue wasn't published that month, the code would render a "-". Should work, right? Uh-uh - another error. Says, "expected Next after the objRS.MoveNext".

I can't figure what I'm doing wrong ... thought it's probably screamingly obvious to the proper ASP coders on this forum.

Anyone have any ideas?

Peck2000
London

Jun 4 '07 #1

Subscribe Post Reply

1459

jhardman

3,406

Expert 2GB

why is line 89 commented out? as it stands now you make a new </tr><tr> for each record, and it seems like you should just make a new </td><td>

Jared

Jun 4 '07 #2

jhardman

3,406

Expert 2GB

Sorry, I didn't read it too close. You did answer that.

I've handled a similar question. Let me see if I can work up a solution.

Jared

Jun 5 '07 #3

jhardman

3,406

Expert 2GB

look at line 85. This should read:

Expand|Select|Wrap|Line Numbers

 If objRS("month") = varLoop then

    Response.Write objRS("issue") 

    objRS.moveNext

else

    Response.Write "-" 

end if

If you find a match (the month of the table cell indicated matches the month of the issue) you should advance then. You don't need to wait til the end of the table row.

I would also keep track of the titleName in a separate variable, so if that ever changes I know I need to change rows:

Expand|Select|Wrap|Line Numbers

 
tName = objRS("titleName")

'etc etc

objRS.moveNext

if objRS("titleName") <> tName then

   response.write "</tr><tr>"

   'strat the next row

end if

Jared

Jun 5 '07 #4

peck2000

Hey, Jared ...

This is a great help ... thanks!

Implementing your changes, I now have a page that displays like this:

www.tmcreative.co.uk/db/buildtable02_orig_fix.asp

which is close ... real close :-)

However, It's still not displaying right. The problem is a complex one and took me a little while to figure out. The problem arises when you have a sequence of issues like The Avengers for 1963. Issue 1 was September, issue 2 was November and issue 3 was Jan 1964.

On the twelfth trip round the loop, the code doesn't find an Avengers issue for December in the database. So it moves to the row, changing the Title (Fantastic Four), and compares the month of the issue (10, Jan) with the varloop variable, doesn't get a match and moves to the next issue (11), doesn't get a match, prints a "-".

Then it loops round again, gets a matching month (Feb) for FF11, and prints the issue number in the table ... missing out issue 10!

Soooo ... I know what's going wrong, but I can't figure out how to programme round the glitch.

The easy way would be to place a dummy issue of Avengers in the datbase dated Dec with an issue number of "-", but that's a cheat and doesn't address the real problem.

I'm sure there's a clue in the bit of code you posted last time:

Expand|Select|Wrap|Line Numbers

 
 tName = objRS("titleName")

 'etc etc

 objRS.moveNext

 if objRS("titleName") <> tName then

 response.write "</tr><tr>"

 'start the next row

 end if

But implementing this didn't solve the problem. By the time weve tested to see if the Title has changed we're already in that database row, and going round the loop again causes us to moveNext before we're ready.

So, while I'm trying to think this through, I could use any help or advice anyone can offer ...

So close ... so close :-)

peck2000

Jun 6 '07 #5

jhardman

3,406

Expert 2GB

I almost suggested starting over, but I don't think you have to yet. I might make you retract what I originally told you to do, though.

Think of the logic like this. your list is always going to be in the same order but there are some holes in the list. As you loop through the list you will see occasionally that there is a month that doesn't have an issue for that title. If you want to print something (albeit a "-") then you will need to loop through every month for every title and check that you are on the right month and the right title at every step.

So say your first record is for Avengers and it is in June. You print "Avengers" because you know that that is your title, and save this name as some variable like "currentTitle". Then you go to the January spot. Is the month january AND the title=currentTitle? no, so print "-" and go to the next month. Is the month Feb AND the title=currentTitle? no, so go to the next month ...Is the month June AND the the title=currentTitle? yes, so you have to do three things: Print the issue number and indicate whether you have it. Second move to the next record in youjr recordset. Third, continue on to the next month. Finally you will get to December. At this point you will either have a December issue for the Avengers and that is your current record, or your current record is the next title. After you print the "-" or issue number and do all the things you needed to do to advance the recordset, you need to draw the next line. Write the next title. Change the variable "currentTitle" to the title of the current record, and check January. Is the month of the current record January and the title = currentTitle? etc.

so i guess I would organize it in a loop like this:

Expand|Select|Wrap|Line Numbers

 dim mon, currentTitle

mon = 1
 
do until objRS.eof

   if mon = 1 then  

      currentTitle = objRS("title") 

      response.write "<tr><td>" & currentTitle & "</td>" & vbNewLine

   end if 
 
   if objRS("month") = mon AND objRS("title") = currentTitle then

      response.write "<td>" & objRS("issueNum") & "</td>" & vbNewLine

      objRS.moveNext

   else

      response.write "<td>-</td>" & vbNewLine

   end if
 
   mon = mon + 1

   if mon = 13 THEN

      mon = 1

      response.write "</tr>"

   end if

loop

response.write "</table>"

Now I know some of this isn't compatible with how you set up your script and db etc. but looking at what you have done so far I'm pretty confident that you can adapt it to fit what you need. Let me know if this helps.

Jared

Jun 14 '07 #6

Similar topics

Single Complex Query Vs Temoprary Table

by: Mahesh Hardikar | last post by:

Hi , Oracle 8.1.7.0.0 on HP-UX 11.0 I am a newbie as far as PL-SQL is concerned . I have Sybase/MSSQL T-SQL background. We have a report which uses a select statement . This select...

Oracle Database

Which Operation of the Complex SQL is doing full table scan

by: Marcus | last post by:

Hi I have a very complex sql query and a explain plan. I found there is a full table scan in ID=9 9 8 TABLE ACCESS (FULL) OF 'F_LOTTXNHIST' (Cost=84573 Card=185892...

Oracle Database

116

Security - more complex than I thought

by: Mike MacSween | last post by:

S**t for brains strikes again! Why did I do that? When I met the clients and at some point they vaguely asked whether eventually would it be possible to have some people who could read the data...

Microsoft Access / VBA

Creating an automatic Record Display mechanism

by: DataBard007 | last post by:

My Access 97 database has a form that contains text boxes that display people's names, addresses, phone numbers, etc. The record source is a single table. I created a NextRecord and Previous...

Microsoft Access / VBA

UI Metaphors for complex criteria selection

by: Steve Jorgensen | last post by:

Mailing List management is a good example of a case where my conundrum arises. Say there is a m-m relationship between parties and groups - anyone can be a member of any combintation of groups. ...

Microsoft Access / VBA

Data display? (Continued)

by: Ben R. | last post by:

Since the original thread is marked as answered, I thought I'd post here to ensure visibility. Hi John and Steven, I'm going the dataset route as access doesn't seem to be too keen on that...

ASP.NET

A complex SELECT in two phases

by: Jon Bilbao | last post by:

I´m trying a select clause in two steps because it´s too complex. First: SELECT Reference, Results.idEnsayo, Results.Num_taladro, min(Results.dTime) + 500 AS tIni, max(Results.dTime) - 500 AS...

C# / C Sharp

linking a research form with a display page

by: phil67b | last post by:

Hello everybody, I have a page rech.php where I'm doing a multi-criteria research Ex. choose your car model, choose your country. After validation of my form, on the same page, the lines will be...

PHP

Dynamic display in Access Form - possible?

by: blaze77 | last post by:

Hi, I'm somewhat of a power user in excel and a newbie to Access though the possibilities are exciting me :-) I am trying to create a tool in Access to replace an existing tool in Excel. My...

Microsoft Access / VBA

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server