Displaying questions grouped into categories and subcategories

I posted this to the asp.db group, but it doesn't look like there is much
activity on there, also I noticed that there are a bunch of posts on here
pertaining to database and asp. Sorry for cross-posting.

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

")
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 ("
• " & rsCat.Fields.Item("QuestionCat").Value)
  'Go through all questions
  Response.Write("
  ")
  Do While rsCatID = rsQuestions.Fields.Item("QuestionCatID").Value 'AND Not
  rsQuestions.EOF
  If rsQuestions.Fields.Item("QuestionSubCat") <> "None" Then
  'Start UL
  Response.Write("
  ")
  'Write out Subcategory name
  Response.Write("
  • " & 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("
  • " & rsQuestions.Fields.Item("QuestionText").Value &
    "
  ")
  rsQuestions.MoveNext
  Loop
  Else
  Response.Write("
  • " &
    rsQuestions.Fields.Item("QuestionText").Value & "
  ")
  rsQuestions.MoveNext
  End If
  'Go to the next Question and Category
  Loop
  Response.Write("
  ")
  'Go to the next Category
  rsCat.MoveNext
  'End the li
  Response.Write("")
  Loop
  'End the UL
  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

If I were you I would create a stored procedure to build a list of required
questions on the server side.
It makes your code clean and clear for all.

Regarding the problem, what kind of difficulty you get? Does sql returned
the right result?



"Drew" wrote in message
news:OZG3L97PHHA.4412@TK2MSFTNGP04.phx.gbl...
>I posted this to the asp.db group, but it doesn't look like there is much
>activity on there, also I noticed that there are a bunch of posts on here
>pertaining to database and asp. Sorry for cross-posting.
>
> 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("

")
> 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 ("
• " & rsCat.Fields.Item("QuestionCat").Value)
  > 'Go through all questions
  > Response.Write("
  ")
  > Do While rsCatID = rsQuestions.Fields.Item("QuestionCatID").Value 'AND Not
  > rsQuestions.EOF
  > If rsQuestions.Fields.Item("QuestionSubCat") <> "None" Then
  > 'Start UL
  > Response.Write("
  ")
  > 'Write out Subcategory name
  > Response.Write("
  • " & 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("
  • " & rsQuestions.Fields.Item("QuestionText").Value &
    > "
  ")
  > rsQuestions.MoveNext
  > Loop
  > Else
  > Response.Write("
  • " &
    > rsQuestions.Fields.Item("QuestionText").Value & "
  ")
  > rsQuestions.MoveNext
  > End If
  > 'Go to the next Question and Category
  > Loop
  > Response.Write("
  ")
  > 'Go to the next Category
  > rsCat.MoveNext
  > 'End the li
  > Response.Write("")
  > Loop
  > 'End the UL
  > 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

Thanks for your response... I have fiddled with the code and finally made it
work, although now I am getting a EOF/BOF error that I can't get rid of.
Also, I do plan on SP'ing the rs's, but just for testing I did the rs's...

Here is my current code,

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

")
End If

'Write out all categories with their respected question(s)
Do While Not rsCat.EOF
rsCatID = rsCat.Fields.Item("CatID").Value
If Not rsQuestions.EOF Then
'Write out Category name, then line break
Response.Write ("
• " & rsCat.Fields.Item("QuestionCat").Value)
  'Go through all questions
  Response.Write("
  ")
  Do While rsCatID = rsQuestions.Fields.Item("QuestionCatID").Value 'AND Not
  rsQuestions.EOF
  'If there is a subcategory, then show the subcat and loop through the
  subcat questions and display them
  If rsQuestions.Fields.Item("QuestionSubCat") <> "None" Then
  'Write out Subcategory name
  Response.Write("
  • " & rsQuestions.Fields.Item("QuestionSubCat"))
    'Start UL for subcategory
    Response.Write("
    ")
    'Set rsSubCatID to the SubCatID
    rsSubCatID = rsQuestions.Fields.Item("QuestionSubCatID")
    'Loop through and write out subcats and questions
    'If Not rsQuestions.EOF Then
    'QuestionSubCatID = rsQuestions.Fields.Item("QuestionSubCatID").Value
    Do While rsSubCatID = rsQuestions.Fields.Item("QuestionSubCatID").Value
    AND Not rsQuestions.EOF
    Response.Write("
    • " & rsQuestions.Fields.Item("QuestionText").Value &
      "
    ")
    rsQuestions.MoveNext
    Loop
    'End UL
    Response.Write("
    ")
    Response.Write("
  ")
  'If there is no subcat, write out the questions
  Else
  Response.Write("
  • " &
    rsQuestions.Fields.Item("QuestionText").Value & "
  ")
  rsQuestions.MoveNext
  End If
  'Go to the next Question and Category
  Loop
  Response.Write("
  ")
  'Go to the next Category
  rsCat.MoveNext
  'End the li
  Response.Write("
")
End If
Loop
'End the UL
Response.Write("

Re: Displaying questions grouped into categories and subcategories

Drew wrote on Fri, 26 Jan 2007 09:56:18 -0500:

> Thanks for your response... I have fiddled with the code and finally made
> it work, although now I am getting a EOF/BOF error that I can't get rid
> of. Also, I do plan on SP'ing the rs's, but just for testing I did the
> rs's...
>
> Here is my current code,
>
> rsCat.Source = "SELECT CatID, QuestionCat FROM
> dbo.CommIntegrationQuestionCat"

Where is the ORDER BY? Don't rely on the data being ordered in the way you
think it will be, always specifiy the ORDER BY

> rsSubCat.Source = "SELECT SubCatID, CatID, QuestionSubCat FROM
> dbo.CommIntegrationQuestionSubCat"

Missing an ORDER BY again.

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

Why don't you check rsCat and rsSubCat are not empty further up? The code
will error if for some reason either table is empty (during maintenance for
instance). Don't assume data will always be there.


> The code does fine, it writes out the Category name, then if there is a
> subcategory, it writes it out and then displays the questions below it.
> However I can't get my loop right, it gives me a BOF/EOF error on the
> following line,
>
> Do While rsSubCatID = rsQuestions.Fields.Item("QuestionSubCatID").Value
> AND Not rsQuestions.EOF

This implies that rsQuestions is empty (both EOF and BOF are true) or EOF
has been reached (which I'm guessing is your problem). If the first
expression can be evaluated, EOF will never be true. If EOF is true, the
first expression will cause an error. When you reach the end of the
recordset, you get an error because the next run of the loop is trying to
pull the value of QuestionSubCatID and EOF is true. Do something like this instead:

Do Until rsQuestions.EOF
If rsSubCatID = rsQuestions.Fields.Item("QuestionSubCatID").Value Then
...
Else
Exit Do
End If
rsQuestions.MoveNext
Loop


this way the loop kicks out if the questions recordset is empty, or you have
reached the end.

Also your current code assumes that every CatID and SubCatID combination
will have questions - so long as you have referential integrity, this is
fine. While your question recordset only returns the CatIDs and SubCatIDs
that have associated questions, your Cat and SubCat queries do not -
personally I'd add the joins and use SELECT DISTINCT to pull only the Cat
and SubCat rows that have questions associated with them, just in case
referential integrity is not enforced.

Dan

Re: Displaying questions grouped into categories and subcategories

Thank you for your help, I finally got it working without erroring on me. I
plan on fixing the queries, actually plan on making them SPs and calling
them, so the ORDER BY will be fixed.

Thanks!
Drew

"Daniel Crichton" wrote in message
news:%23u$qDWWQHHA.4744@TK2MSFTNGP02.phx.gbl...
> Drew wrote on Fri, 26 Jan 2007 09:56:18 -0500:
>
>> Thanks for your response... I have fiddled with the code and finally made
>> it work, although now I am getting a EOF/BOF error that I can't get rid
>> of. Also, I do plan on SP'ing the rs's, but just for testing I did the
>> rs's...
>>
>> Here is my current code,
>>
>> rsCat.Source = "SELECT CatID, QuestionCat FROM
>> dbo.CommIntegrationQuestionCat"
>
> Where is the ORDER BY? Don't rely on the data being ordered in the way you
> think it will be, always specifiy the ORDER BY
>
>> rsSubCat.Source = "SELECT SubCatID, CatID, QuestionSubCat FROM
>> dbo.CommIntegrationQuestionSubCat"
>
> Missing an ORDER BY again.
>
>> 'Move to first record of Cat
>> rsCat.MoveFirst
>> rsSubCat.MoveFirst
>
> Why don't you check rsCat and rsSubCat are not empty further up? The code
> will error if for some reason either table is empty (during maintenance
> for instance). Don't assume data will always be there.
>
>
>> The code does fine, it writes out the Category name, then if there is a
>> subcategory, it writes it out and then displays the questions below it.
>> However I can't get my loop right, it gives me a BOF/EOF error on the
>> following line,
>>
>> Do While rsSubCatID = rsQuestions.Fields.Item("QuestionSubCatID").Value
>> AND Not rsQuestions.EOF
>
> This implies that rsQuestions is empty (both EOF and BOF are true) or EOF
> has been reached (which I'm guessing is your problem). If the first
> expression can be evaluated, EOF will never be true. If EOF is true, the
> first expression will cause an error. When you reach the end of the
> recordset, you get an error because the next run of the loop is trying to
> pull the value of QuestionSubCatID and EOF is true. Do something like this
> instead:
>
> Do Until rsQuestions.EOF
> If rsSubCatID = rsQuestions.Fields.Item("QuestionSubCatID").Value Then
> ...
> Else
> Exit Do
> End If
> rsQuestions.MoveNext
> Loop
>
>
> this way the loop kicks out if the questions recordset is empty, or you
> have reached the end.
>
> Also your current code assumes that every CatID and SubCatID combination
> will have questions - so long as you have referential integrity, this is
> fine. While your question recordset only returns the CatIDs and SubCatIDs
> that have associated questions, your Cat and SubCat queries do not -
> personally I'd add the joins and use SELECT DISTINCT to pull only the Cat
> and SubCat rows that have questions associated with them, just in case
> referential integrity is not enforced.
>
> Dan
>