«

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

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
>

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

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
>