Displaying questions grouped into categories and subcategories

D

Drew

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
 
G

Guest

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 said:
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
Click to expand...
 
D

Drew

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



Anon User said:
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 said:
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
Click to expand...
Click to expand...
 
D

Daniel Crichton

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"
Click to expand...

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"
Click to expand...

Missing an ORDER BY again.
'Move to first record of Cat
rsCat.MoveFirst
rsSubCat.MoveFirst
Click to expand...

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
Click to expand...

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
 
D

Drew

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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with a login script, SESSION user rights and put this together so it works with the other pages and MySQL. Code examples. 2
Survey details won't go through using php, ajax, Mysql 0
How to change star rating color on mouseenter, on mouseout, and on onclick 0
Problem with IDENTITY 0
Exporting strange characteres to Excel from SQL Server 1
Infinite Product Category Display 0
Need Help With Custom Profile Providers 0
Shopping Cart Site Map - Sooo Sloooow! 7

Members online

No members online now.
Total: 28 (members: 0, guests: 28)
Robots: 218

Forum statistics

Threads
473,755
Messages
2,569,537
Members
45,022
Latest member
MaybelleMa

Latest Threads

Top