473,241 Members | 1,446 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

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"%>
<!--#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
Jan 24 '07 #1
4 2469
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.govwrote in message
news:OZ**************@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"%>
<!--#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

Jan 26 '07 #2
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.comwrote in message
news:Oc**************@TK2MSFTNGP03.phx.gbl...
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.govwrote in message
news:OZ**************@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"%>
<!--#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").Val ue
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


Jan 26 '07 #3
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
Jan 26 '07 #4
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" <ms****@worldofspack.comwrote in message
news:%2****************@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

Jan 26 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Brian | last post by:
Greetings, I'm working on a project that involved that has need of a categorization system. Logically speaking, the system will have elements, these elements will belong to at least a single...
2
by: Troy Lynch | last post by:
I'm working on writing a website which I need to have lists of products listed in categories and subcategories, and need to keep track of whats in the tree. Like how many products from the root all...
4
by: Bill | last post by:
I've got a bookstore I'm developing, and I wanted to list all the categories on the home page of the site. However, there are so many, that they now extend way below the screen, making the page...
0
by: Ralph Guzman | last post by:
TASK: I have to generate a report with all categories, subcategories and products in database. PROBLEM: I want to write one query that will return: 1. category 2. subcategory: determined by...
1
by: Neil McGuigan | last post by:
Hi, I want to store product categories in my db and am a little lost as to where to start. They can be hierarchical, such as "Books" > "Cook Books", so my table is like this: int...
0
by: Manuel | last post by:
If I have 2 tables CATEGORIES and PRODUCTS. What's the most "elegant" way (in programming terms) of displaying all the Categories with it's Products on a web page? I would like to show a list or...
13
by: hornedw | last post by:
I have been working on a ecommerce website for myself. What I needed some assistance on was when i was trying to display the categories/subcategories for the different products. I decided to use...
3
Staria
by: Staria | last post by:
Hi... I'm currently working on a project where I would like to have Categories and SubCategories. I've banged my head on this for several days, but can't seem to get this the way I want it to...
16
by: =?Utf-8?B?VGFtbXkgTmVqYWRpYW4=?= | last post by:
Hi, I am using Visual C# window to dispaly a set of questions with their answers. The users should be able to move to the next question by clicking on next button. I am going to use only one panel...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.