ASP Loop Issue..

[Stoo]

Okay folks, it's gonna get messy

I've trying to build a basic FAQ engine so we can add additional information to our products as and when the call centre finds something.

I'm trying to get the code to first loop through the questions in one category before moving on to another (and eventually on to the FAQ answers in a similar fashion)..

I've got the code listing through the categories as I want, but I hit an exception error before I can move on to the rest of the code.

	<%
	strSQL = "SELECT * FROM FAQ WHERE SiteCode = '" & strSiteCode & "' AND InfoType = 'default' ORDER BY Category ASC;"
	OpenDB()

	Do While Not rsProdSearch.EOF

		strCat = rsProdSearch("Category")
		%><b><%=strCat%></b><br/><%

		Do While rsProdSearch("Category") = strCat

			%><a href="#<% = rsProdSearch("ID") %>"><%=rsProdSearch("Question")%>?</a><br/><%

			If Not rsProdSearch.EOF then
				rsProdSearch.MoveNext
			end if

		Loop

		%><p/>!<%

	Loop

	%>Blah<p/><%

	CloseDB()
	%>
	rest of code in here later..

OpenDB and CloseDB open up the connection to the database, and work fine, so no need to worry about those
The "!" and "Blah" are just a couple of pointers so I can see what the code is doing..

The result is more or less:

Information

Test
Is this a test??
Some Other Stuff?


!Test2
Some More Stuff to Do?
Is this another Test??
error '80020009' 

Exception occurred. 

/Intranet/includes/FAQ_page.asp, line 25

Line 25 is " Do While rsProdSearch("Category") = strCat "

I initally thought the MoveNext was causing the error from hitting the EOF while trying to loop, but adding the if statement didn't do anything, nor did changing the loop statement to add "AND Not rsProdSearch.EOF"..


Any ideas? (Apart from telling me I create the messiest code in the world )

[Spunkey]

what should come after 'Is this another Test??'

is there another category/question or is that it?

[Stoo]

There's only 2 categories at the moment, it should then break out of the loop and display the rest of the static content..

[Spunkey]

stwange... it seems to be looping around again even after it hits the EOF....

possibly because after the final time around the interior loop, it again calls the exterior loop to start again, even thogh the RS is now EOF. Shouldnt in theory be a problem, but try this just to cover all bases..

	Do 
		strCat = rsProdSearch("Category")
		%><b><%=strCat%></b><br/><%

		Do While rsProdSearch("Category") = strCat

			%><a href="#<% = rsProdSearch("ID") %>"><%=rsProdSearch("Question")%>?</a><br/><%

			If Not rsProdSearch.EOF then
				rsProdSearch.MoveNext
			end if

		Loop

		%><p/>!<%

	Loop Until Not rsProdSearch.EOF

hopefully that should work... although my brain is fried and i've lost al mental ability to run code in my head

[Stoo]

Okay, that works for the rest of the static page, but it misses the 2nd category out ..

[Stoo]

Using " Loop While Not rsProdSearch.EOF " on the outer loop has the same effect as the initial code (as it should)

[Stoo]

Changing the Do While to a Loop Until on the inner loop makes no difference either..

*confused*

Too Much Coding for a Monday..

[Stoo]

Oh, btw, it's currently runnning on the dev box which is a fairly elderly machine running IIS4 on NT4..

Could that be the issue?

[IBM]

It's been a while, but I couldn't see where you assigned your SQL result to your recordset...

like a line ....

Set rsProdSearch= objConn.Execute(strSQL)

Am I missing something, or are you having a really bad Monday?

[Stoo]

That's what the OpenDB() function does

[IBM]

So within your OpenDB() function you assign your SQL results to a hardcoded variable called rsProdSearch?

Doesn't that kind of limit your OpenDB() function to calls that expect back a ProdSearch type result?

[IBM]

Can you write down what your Category field contains in the DB, and alongside your rsProdSearch ID & Question fields?

[Stoo]

So within your OpenDB() function you assign your SQL results to a hardcoded variable called rsProdSearch?

Doesn't that kind of limit your OpenDB() function to calls that expect back a ProdSearch type result?

Stop getting hungup on the database connection, it's fine and works flawlessly in all situations

Can you write down what your Category field contains in the DB, and alongside your rsProdSearch ID & Question fields?

You can already see most of it in one of the earlier posts, but here's the full list of the database..

ID	SiteCode	Question		Answer			Category InfoType
1	TEST		Is this a test?		Stuff..			Test	default
2	TEST		Is this another Test??	More Stuff..		Test2	default
3	TEST		Some Other Stuff?	Yet More Stuff..	Test	default
4	TEST		Some More Stuff to Do?	Yadda Yadda Yadda..	Test2	default

But I really don't think it's a database problem, I think it's more likely to be hitting the EOF then trying to loop again..

[Mart]

Are you using BLOBS in the DB?

http://support.microsoft.com/kb/q175239/

[Stoo]

no blobs, it's just a little access database (yes, I know, eww access etc, but that's all I've got to use at the moment and I can't change that, so don't moan ) with exactly the data you just read, an auto number field and 5 text fields..

[Mart]

Actually...after reading your code again I would change your looping construct to:

Do While rsProdSearch("Category") = strCat AND rsProdSearch.EOF  = False
       %><a href="#<% = rsProdSearch("ID") %>"><%=rsProdSearch("Question")%>?</a><br/><%
       rsProdSearch.MoveNext
Loop

At the moment you movenext and it could hit EOF. If it is EOF and you try and access fields in the recordset but there is no valid record...*DANGER WILL ROBINSON*.