...

View Full Version : RecordSet Display Issue. "Bookmark" Error.



DakotaChick
11-19-2007, 09:38 AM
I think I'm just missing something really simple but basicly what I'm trying to do is display data from a query using asp. I would like the table to display 2 records side by side before moving to the next row of the table:
IE: Table Cell with Record Info 1 | Table Cell with Record Info 2
Table Cell with Record Info 3 | Table Cell with Record Info 4

The problem I'm running into is when I try to use the MoveNext method inside the loop to display the 2nd record info before completing the loop and moving on to the next row, I get one of two error message depending on the Move method I use.
1) (Using MoveNext) - I get a BOF or EOF error
2) (Using Move 1,0) - I get a Bookmarks not supported error. (Access 2003)

What move method should I be using to move to the next record for the next table cell before moving on to the next row??

Heres the code I have:


<table width="299" border="0" align="center" cellpadding="0" cellspacing="5" class="ImageTables">
<%
Dim adoCon
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Mode = 3
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\Inetpub\wwwroot\Turn2\admin\db\db.mdb; User Id=admin; Password="

Dim rs, strSQL
Set rs = Server.CreateObject("ADODB.RecordSet")
strSQL = "SELECT ArtImages.* FROM ArtImages;"
rs.CursorType = 2

rs.Open strSQL, adoCon

rs.MoveFirst

Do While Not rs.EOF
Category = rs("Cat")
ImgName = rs("ImgName")
If Category <> Last_Category Then
%>
<tr>
<td colspan="2">&nbsp;</td>
</tr>
<tr>
<td colspan="2">
<div align="center">
<span class="Headings"><%=Category%></span><br />
<span class="Small">(Click image for larger view)</span>
</div>
</td>
</tr>
<%
End If
%>
<tr>
<td width="140">
<div align="center">
<% If ImgName = "comingsoon.jpg" Then %>
<img src="images/art/<%=ImgName%>" border="0" />
<% Else %>
<a href="images/art/large/<%=ImgName%>">
<img src="images/art/small/<%=ImgName%>" border="0" />
</a>
<% End If %>
</div>
</td>
<%
rs.Move 1,0 'Need Move Code Here
Category = rs("Cat")
ImgName = rs("ImgName")
%>
<td width="140">
<div align="center">
<a href="images/art/large/<%=ImgName%>">
<img src="images/art/small/<%=ImgName%>" border="0" />
</a>
</div>
</td>
</tr>
<%
Last_Category = Category
rs.MoveNext
Loop
%>
</table>

Spudhead
11-19-2007, 12:04 PM
Don't loop through recordsets. It's not an efficient way of doing it.

Get your data into an array using rs.getRows() (http://www.w3schools.com/ado/met_rs_getrows.asp).

You can then loop through your array, using the VBScript mod operator (http://www.devguru.com/Technologies/vbscript/quickref/mod.html) to see if the current record number is odd (ie: not divisible exactly by 2) and therefore requires a new table row.

Make sense?

DakotaChick
11-19-2007, 06:03 PM
Makes perfect sense, but...

I seem to remember trying to use this method in the past and having great difficulty telling it an unknown amount of records to grab. I want to grab the whole table but I dont know how many records will be in the table at any given time.

Its been a very long time since I messed with Arrays =( and even longer on dynamicly filled ones.

Any additional help with the arrays part would be greatly appreciated.

DakotaChick
11-19-2007, 07:45 PM
Here's what Ive come up with thus far. However this doesnt display things the way I'd like them. I only need the i (category) variable to display once for all the images in the same category. and 2 images displayed per row until the next category is reached.

Any help is appreciated.


strSQL = "SELECT ArtImages.Cat, ArtImages.ImgName FROM ArtImages;"
rs.Open strSQL, adoCon

'Dim TestArray()
TestArray=rs.GetRows()

'Loop For Each Row
For i = 0 To UBound(TestArray, 1)
Response.Write "<tr>" & vbcrlf
'Loop For Each Column In Each Row
For j = 0 To UBound(TestArray, 2)
Response.Write "<td>" & TestArray(i, j) & "</td>"
Next
Response.Write "</tr>" & vbcrlf
Next

Whatever Jr.
11-20-2007, 08:41 AM
The bookmark error you receive is because the specified cursortype doesn't support it. Use adOpenStatic instead.

HTH, Tom

Spudhead
11-20-2007, 01:42 PM
Ok, a bit more complicated then :D

Try this. Uncomment my lines at the top (and comment out my hardcoded test array) and it should plug into your existing SQL query.


<%

'strSQL = "SELECT ArtImages.Cat, ArtImages.ImgName FROM ArtImages;"
'set rs = adoCon.execute strSQL
'if not rs.EOF and not rs.BOF then
' TestArray=rs.GetRows()
'end if
'rs.close
'set rs = nothing

dim TestArray(2,7)
TestArray(0,0) = "cat1"
TestArray(1,0) = "img1"
TestArray(0,1) = "cat1"
TestArray(1,1) = "img2"
TestArray(0,2) = "cat1"
TestArray(1,2) = "img3"
TestArray(0,3) = "cat2"
TestArray(1,3) = "img4"
TestArray(0,4) = "cat2"
TestArray(1,4) = "img5"
TestArray(0,5) = "cat2"
TestArray(1,5) = "img6"
TestArray(0,6) = "cat2"
TestArray(1,6) = "img7"
TestArray(0,7) = "cat2"
TestArray(1,7) = "img8"

if isArray(TestArray) then

dim sCurrentCat : sCurrentCat = ""
dim iPicCount : iPicCount = 0

Response.Write "<table>"& vbcrlf

For i = 0 To UBound(TestArray, 2)

sThisCat = TestArray(0,i)
sThisImg = TestArray(1,i)

' write header row if new category
if sThisCat <> sCurrentCat then
Response.Write "<tr><td colspan=""2""><strong>" & sThisCat & "</strong></td></tr>" & vbcrlf
sCurrentCat = sThisCat
iPicCount = 0
end if

' new row every two pics
if i = 0 then
response.write("<tr>") & vbcrlf
elseif iPicCount = 2 then
response.write("</tr><tr>") & vbcrlf
iPicCount = 0
end if

Response.Write "<td>" & sThisImg & "</td>" & vbcrlf

iPicCount = iPicCount + 1
Next


Response.Write "</table>"

end if

%>

s'probably not the most efficient code in the world but it seems to work :)

Whatever Jr.
11-20-2007, 03:49 PM
Hmm, i wrote a similar code sample.

Add I'd like to add that the query will need a sort order for the categories.

HTH, Tom

Spudhead
11-20-2007, 04:22 PM
You're quite right. Doh :)

DakotaChick
11-20-2007, 09:00 PM
Hmm, i wrote a similar code sample.

Add I'd like to add that the query will need a sort order for the categories.

HTH, Tom

ahh..yes...forgot about that. Was too caught up getting the display right. Thanks for reminding me about that.:thumbsup:

Spud - Thank you greatly for the code. after looking over it, I see how much more difficult I was trying to make it vs how pretty simple it should have been. lol.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum