Displaying questions grouped into categories and subcategories

Discussion in 'ASP General' started by Drew, Jan 24, 2007.

  1. Drew

    Drew Guest

    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"%>
    <!--#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
     
    Drew, Jan 24, 2007
    #1
    1. Advertising

  2. 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" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
    news:...
    >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"%>
    > <!--#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
    >
     
    Alexey Smirnov, Jan 26, 2007
    #2
    1. Advertising

  3. Drew

    Drew Guest

    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"%>
    <!--#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
    If Not rsQuestions.EOF Then
    '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 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("<li>" & rsQuestions.Fields.Item("QuestionSubCat"))
    'Start UL for subcategory
    Response.Write("<ul>")
    '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("<li>" & rsQuestions.Fields.Item("QuestionText").Value &
    "</li>")
    rsQuestions.MoveNext
    Loop
    'End UL
    Response.Write("</ul>")
    Response.Write("</li>")
    'If there is no subcat, write out the questions
    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>")
    End If
    Loop
    'End the UL
    Response.Write("</ul>")
    %>

    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

    Thanks,
    Drew



    "Alexey Smirnov" <removeit.hello_at_smalig.com> wrote in message
    news:Ock$...
    > 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" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
    > news:...
    >>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"%>
    >> <!--#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
    >>

    >
    >
     
    Drew, Jan 26, 2007
    #3
  4. 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
     
    Daniel Crichton, Jan 26, 2007
    #4
  5. Drew

    Drew Guest

    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$...
    > 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
    >
     
    Drew, Jan 26, 2007
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Manuel

    Displaying subcategories

    Manuel, Dec 6, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    290
    Manuel
    Dec 6, 2004
  2. Tony

    comp.lang.c: Why no subcategories?

    Tony, Jan 10, 2009, in forum: C Programming
    Replies:
    3
    Views:
    297
    James Kuyper
    Jan 11, 2009
  3. Bill
    Replies:
    1
    Views:
    127
    Ray at
    Oct 31, 2003
  4. Ken Fine
    Replies:
    4
    Views:
    142
    Evertjan.
    Apr 5, 2004
  5. David K. Wall

    Categories of Perl questions

    David K. Wall, Mar 7, 2005, in forum: Perl Misc
    Replies:
    0
    Views:
    99
    David K. Wall
    Mar 7, 2005
Loading...

Share This Page