Displaying questions grouped into categories and subcategories

am 23.01.2007 22:52:51 von Drew

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

<%
'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("

" & rsCat.Fields.Item("QuestionCat").Value)
'Go through all questions
Response.Write("
")
%>

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

Thanks,
Drew

Re: Displaying questions grouped into categories and subcategories

am 24.01.2007 14:55:41 von Drew

I have posted this question over at the asp.general group.

Thanks,
Drew

"Drew" wrote in message
news:eaa$VjzPHHA.2140@TK2MSFTNGP03.phx.gbl...
>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"%>
>
>
> <%
> '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("

" & rsCat.Fields.Item("QuestionCat").Value)
> 'Go through all questions
> Response.Write("
")
> %>
>
> I am having severe brain block at the moment, can anyone nudge me in the
> right direction?
>
> Thanks,
> Drew
>