Webmaster Forum / ASP / Database Access / January 2007

I am trying to build a "checklist", where a user can navigate to an ASP page
on the intranet which shows a list of "questions" that the user can check
off.  I am trying to figure out how to do this so that it is scalable, but I
am having difficulty getting it outputted to the page.  Here are my database
tables,

--The table that holds the "answers" to the questions, it holds the
QuestionID from the CommIntegrationQuestions table,
--the date it was accomplished and comments.
CREATE TABLE [dbo].[CommIntegrationChecklist] (
[UID] [int] IDENTITY (1, 1) NOT NULL ,
[RegNo] [int] NOT NULL ,
[QuestionID] [int] NULL ,
[DateAccomplished] [datetime] NULL ,
[Completed] [bit] NULL ,
[Comments] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

--This is the category table for the questions.
CREATE TABLE [dbo].[CommIntegrationQuestionCat] (
[CatID] [int] IDENTITY (1, 1) NOT NULL ,
[QuestionCat] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

--This is the subcategory table for the questions
CREATE TABLE [dbo].[CommIntegrationQuestionSubCat] (
[SubCatID] [int] IDENTITY (1, 1) NOT NULL ,
[CatID] [int] NULL ,
[QuestionSubCat] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

--Here are where the questions are held.  Some may have a subcategory, some
may not.
CREATE TABLE [dbo].[CommIntegrationQuestions] (
[QuestionID] [int] IDENTITY (1, 1) NOT NULL ,
[QuestionCatID] [int] NULL ,
[QuestionSubCatID] [int] NULL ,
[QuestionText] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

I am trying to output this as an unordered list, but can't figure out how to
get the subcategory to output correctly.  Here is my current code, this is
just test stuff, nothing on production yet,

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="../../../Connections/CliCore.asp" -->
<!--#include file="../../../Connections/CliELPIHP.asp" -->
<%
'Recordset for Categories
Dim rsCat
Dim rsCat_numRows

Set rsCat = Server.CreateObject("ADODB.Recordset")
rsCat.ActiveConnection = MM_CliELPIHP_STRING
rsCat.Source = "SELECT CatID, QuestionCat FROM
dbo.CommIntegrationQuestionCat"
rsCat.CursorType = 0
rsCat.CursorLocation = 2
rsCat.LockType = 1
rsCat.Open()

rsCat_numRows = 0

'Recordset for Categories
Dim rsSubCat
Dim rsSubCat_numRows

Set rsSubCat = Server.CreateObject("ADODB.Recordset")
rsSubCat.ActiveConnection = MM_CliELPIHP_STRING
rsSubCat.Source = "SELECT SubCatID, CatID, QuestionSubCat  FROM
dbo.CommIntegrationQuestionSubCat"
rsSubCat.CursorType = 0
rsSubCat.CursorLocation = 2
rsSubCat.LockType = 1
rsSubCat.Open()

rsSubCat_numRows = 0

'Recordset for all questions
Dim rsQuestions
Dim rsQuestions_numRows

Set rsQuestions = Server.CreateObject("ADODB.Recordset")
rsQuestions.ActiveConnection = MM_CliELPIHP_STRING
rsQuestions.Source = "SELECT QuestionID, QuestionCatID, C.QuestionCat,
QuestionSubCatID, SC.QuestionSubCat, QuestionText FROM
dbo.CommIntegrationQuestions Q INNER JOIN CommIntegrationQuestionCat C ON
Q.QuestionCatID = C.CatID INNER JOIN CommIntegrationQuestionSubCat SC ON
Q.QuestionSubCatID = SC.SubCatID ORDER BY QuestionCatID, QuestionSubCatID"
rsQuestions.CursorType = 0
rsQuestions.CursorLocation = 2
rsQuestions.LockType = 1
rsQuestions.Open()

rsQuestions_numRows = 0

'Move to first record of Cat
rsCat.MoveFirst
rsSubCat.MoveFirst

'Get total records from rsQuestion
'Dim TotQuestions
'TotQuestions = rsQuestions.MaxRecord

'Start the ul to display questions from the database
If Not rsCat.EOF Then
   'Start the ul
   Response.Write("<ul>")
End If

'Write out all categories with their respected question(s)
Do While Not rsCat.EOF
rsCatID = rsCat.Fields.Item("CatID").Value
   'Write out Category name, then line break
   Response.Write ("<li>" & rsCat.Fields.Item("QuestionCat").Value)
   'Go through all questions
Response.Write("<ul>")
Do While rsCatID = rsQuestions.Fields.Item("QuestionCatID").Value 'AND Not
rsQuestions.EOF
 If rsQuestions.Fields.Item("QuestionSubCat") <> "None" Then
  'Start UL
  Response.Write("<ul>")
  'Write out Subcategory name
  Response.Write("<li>" & rsQuestions.Fields.Item("QuestionSubCat"))
  'Do While rsSubCatID = rsQuestions.Fields.Item("QuestionSubCatID").Value
  Do While Not rsSubCat.EOF AND rsSubCat.Fields.Item("CatID").Value =
rsCatID
   Response.Write("<li>" & rsQuestions.Fields.Item("QuestionText").Value &
"</li>")
   rsQuestions.MoveNext
  Loop
 Else
        Response.Write("<li>" &
rsQuestions.Fields.Item("QuestionText").Value & "</li>")
  rsQuestions.MoveNext
 End If
       'Go to the next Question and Category
   Loop
Response.Write("</ul>")
'Go to the next Category
rsCat.MoveNext
'End the li
Response.Write("</li>")
Loop
'End the UL
Response.Write("</ul>")
%>

I am having severe brain block at the moment, can anyone nudge me in the
right direction?

Thanks,
Drew