[absta]

Guys
I have three categories I need to display as hyperlinks on my site but I was told using Do While Not ... Loop was a bad use of resources as the categories are likely never to change.
So I'm using this code:

Dim mySQL, myRS
Set myRS = Server.CreateObject("ADODB.Recordset")
mySQL = "SELECT lngCategoryID,strCategory FROM tblCategories ORDER BY strCategory"
myRS.Open mySQL, strconnect
Response.Write "<h2>Perfume Categories</h2>"
Response.Write "<ul><li><a href='catresults.asp?CategoryID=" &_
myRS("lngCategoryID") & "&CartID=" & CartID &_
"'>" & myRS("strCategory") & "</a></li>"&_
"</ul>"
myRS.Close
%>


BUT can't figure out how to display the other 2 categories because with this code only it display only one of them - I know I'm missing something really obvious ..... advice much appreciated

[Amadeus]

Do While Not myRS.EOF
'your code here
myRS.Next
Loop

[absta]

Cheers Amadeus you are a star - still a total newbie to ASP...

It works but I was told it wasn't an efficient use of code as a list of categories isn't a particularly dynamic set of data so the constant calls to the database isn't a good use of resources.

Is there another way I could have done it?

[Amadeus]

There are a few things you could do...one item that comes to mind is to get the list of categories on the first page of the set and store the values in the recordsets into a simple array of strings. That array can then be passed page to page and accessed as required.

[absta]

 <%Dim strPerfume
strPerfume(0) = "Eau de Cologne"
strPerfume(1) = "Eau de Toilette"
strPerfume(2) = "Eau de Parfum"
Response.Write "<p>"

For intCounter = 0 - 2
Response.Write strPerfume(intCounter) & "..."
Next
%>

Is this something along the right lines ??

This post has been edited by absta: 13 August 2006 - 11:56 AM

[Amadeus]

Well, that depends...are you going to be getting the categories from the database at all? Will they ever change? If there is a possibility that they change, you'd be better of having them in a database, and populating the arrays from that initially, then passing the array variable along.

Traditionally, the VBScript method for decalring an array of strings would be like so:

Dim strCategories(2)

This would give you an array of 3 elements (arrays are zero based in VBScript, and the parameter provided to the dim statement actuall is the final element you want). This is useful if you have a known number of categories.

If, however, you are storing the categories in a database, and the number is unknown, you can dynamically decalre the array,then redim it once you know the number of categories:

Dim strCategories()
Redim strCategories(myRS.RecordCount)

You can also go with your initial way of doing it directly with the database. Unless that page is being accessed multiple times per minute, the query should be fine - just remember to destroy the object when you're done. Although ASP objects should technically be destoyed automaticaaly once out of scope, we all know this does not always happen.

[absta]

Thanks Amadeus so ...

<%
Dim mySQL, myRS, strCategory(3), Item
Set myRS = Server.CreateObject("ADODB.Recordset")
mySQL = "SELECT lngCategoryID,strCategory FROM tblCategories ORDER BY strCategory"
myRS.Open mySQL, strconnect
Response.Write "<h2>Perfume Categories</h2>"

strCategory(0) = "Eau de Toilette" 
strCategory(1) = "Eau de Cologne" 
strCategory(2) = "Eau de Parfum"	

For Each Item In strCategory
Response.Write Item & "<br>"
Next

myRS.Close
%> 

Ok, this is what i've got and it's displaying fine, how do I turn these into hyperlinks and also generate the CartID? the idea is for the user to click on these then drill down to the category display page. Should I be generating the CartID here? this is what was happening before with the Do While ... Loop code and it looks pretty cool.

[Amadeus]

To be honest, I'd say you should go with your original code...if you're planning on using the array, you'd have to store the ID variables as well, unless you were keeping the recordset open. If that's the case, you can just go with your original stuff...there was nothing wrong with it.

If you really want to use the array method, it may be better to use the GetRows() method of the recordset...it will return the recordset in an appropriate array for usage, and you would access the elements the same way you would access the recordset elements - there is a good article here:
http://www.aspfaqs.c...Q.asp?FAQID=161

[absta]

thanks Amadeus, food for thought
I've checked out 4GuysFromRolla.com before, it's a cool site

This post has been edited by absta: 14 August 2006 - 11:20 AM

[absta]

Hey Amadeus

I've gone for this but the CategoryID stays the same !

<%
Dim mySQL, myRS, strCategory(3), Item
Set myRS = Server.CreateObject("ADODB.Recordset")
mySQL = "SELECT intCategoryID, strCategory FROM tblCategories ORDER BY strCategory"
myRS.Open mySQL, strconnect
Response.Write "<h2>Perfume Categories</h2>"
strCategory(0) = "Eau de Toilette"
strCategory(1) = "Eau de Cologne"
strCategory(2) = "Eau de Parfum"
								 
For Each Item In strCategory 
Response.Write "<ul><li><a href='catresults.asp?CategoryID=" &_
 myRS("intCategoryID") & "&CartID=" & CartID &_
"'>" & Item & "</a></ul></li>"
Next
myRS.Close
%>

Me thinks I'm missing a minor detail somewhere, I'll get it one day .......

[Amadeus]

myRS.Next

make that the last line in the for loop...the line before Next

[absta]

ok well i did that but the categoryID doesn't increment.
Someone suggested I should build the Array in the Session as I'm using Sessions

So build a 2 dimensional array so I end up with 6 values then I could bring back the CartID and Category name ..... any ideas ? simple right ?

[Amadeus]

CartID simply appears to be avariable...where are you setting it? What value does it have initially? As it seems to be a variables outside of the recordset, you'll have to increment it manually, but we have to determine where it is being set in the first place.

[absta]

finally sussed it out after wading through my book

<%
Dim mySQL, myRS, avarFields, localArray, i
If Not IsArray(Session(avarFields)) Then ' Check if the session item avarFields is an array
	Set myRS = Server.CreateObject("ADODB.Recordset")

	mySQL = "SELECT intCategoryID, strCategory FROM tblCategories ORDER BY strCategory" ' Query database for categoryID and Category
	myRS.Open mySQL, strconnect, adOpenKeySet, adLockReadOnly, adCmdText
	
		avarFields = myRS.GetRows			' Assign the rows of information to the array
		Session("avarfields") = avarfields   ' Assign the array to the session
	myRS.Close
	Set myRS = Nothing

End If
localArray = Session("avarFields") ' Assign the Session item avarFields to localArray
response.write "<ul>"
	For i = 0 to UBound(localArray,2)   ' Loop through array
		Response.Write "<li><a href='filename.asp?categoryID=" & localArray(0,i) & "'>" & localArray(1,i) & "</a></li>"
	Next 
response.write "</ul>"
%>							

this lot is in a <div> too 'cos i'm using the power of CSS, oh yes