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,
<% [at] 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
