Multiple recordset problem

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Multiple recordset problem

Logged in as: Guest

arrSession:exec spGetSession 2,3,27008

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> ASP >> Multiple recordset problem
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Multiple recordset problem - 10/17/2005 7:23:32 AM

nicki_bee

Posts: 1
Score: 0
Joined: 10/17/2005
Status: offline

Original message moved by Admin
Reason : Moved to a more appropriate category
Hello

I hope someone can help. I am trying to build a photo album and i want my index page to display the list of albums available together with a randomly selected thumbnail from the collection. I have got soemthing which i think should work however a am having a problem opening a second recordset and querying it correctly. My code is posted below. what is happening is the first recordset rsRecordSet works fine and contains te correct records. then when i try to open te second recordset and query it

this new recordset contains the data from the first recordset! i am very confused why this is happening, i though you could create multiple recordsets and one connection, am i wrong here? Any help would be much appreciated.

thank you

<%
  
  on error resume next
  Dim dbConnection, rsRecordSet, strSQL, numRows, numPhotos, ranNum, CurrentTable, ranSQL, ranRecordset

strSQL = "SELECT * FROM Names ORDER BY table_name ASC"
  
  Set dbConnection = Server.CreateObject("ADODB.Connection")
  Set rsRecordSet = Server.CreateObject("ADODB.RecordSet")
  Set ranRecordset = Server.CreateObject("ADODB.RecordSet")
    
  dbConnection.Open "Driver={MySQL}; SERVER=mysql.co.uk; DATABASE=db; UID=db1; PASSWORD=*****"
  rsRecordSet.Open strSQL, dbConnection
  numRows = 0
  If not(rsRecordSet.EOF and rsRecordSet.BOF) then
   rsRecordSet.MoveFirst
   While not rsRecordSet.EOF 
    numRows = numRows + 1
    rsRecordSet.MoveNext
   Wend
  End if
  
  If not(rsRecordSet.EOF and rsRecordSet.BOF) then
   rsRecordSet.MoveFirst
   For i = 1 to numRows
    If not rsRecordSet.EOF then 
     
     currentTable = rsRecordSet.Fields.Item("table_name")
     ranSQL = "SELECT * FROM `" & currentTable & "`"
     response.Write(ranSQL)
     ranRecordset.Open ranSQL, dbConnection
     numPhotos = 0
     If not(ranRecordset.EOF and ranRecordset.BOF) then
      ranRecordset.MoveFirst
      While not ranRecordset.EOF
       numPhotos = numPhotos + 1
       ranRecordset.MoveNext
      Wend
     End if
     Randomize
     RandomNumber = int((numPhotos+1) * Rnd) - 1
     ranRecordset.MoveFirst 
     ranRecordset.Move RandomNumber, 0
     %>
     <br><br>
     <div id="index-space-thumb"><img src="<%Response.Write(ranRecordset.Fields.Item("thumb_url"))%>" alt="<%Response.Write(ranRecordset.Fields.Item("pic_desc"))%>"></div>
     <div id="index-space-text">
      <a class="categories" href="album.asp?albumName=<%Response.Write(rsRecordSet.Fields.Item("table_name"))%>">
      <%Response.Write(rsRecordSet.Fields.Item("table_name"))%></a>
      <br>
      <span class="phototext"><%Response.Write(rsRecordSet.Fields.Item("table_desc"))%></span>
     </div>
    <% 
    RsRecordSet.MoveNext
    End if 
   Next
  End If
  
  ranRecordset.Close
  RsRecordSet.Close
  DbConnection.Close
  set ranRecordset = Nothing
  Set rsRecordSet = Nothing
  Set dbConnection = Nothing
  
  %>

Post #: 1

RE: Multiple recordset problem - 12/12/2005 12:22:57 PM

johnrswanton

Posts: 4
Score: 0
Joined: 11/16/2005
Status: offline

im not sure but i think you have to close one recordset before you open another

(in reply to nicki_bee)

Post #: 2

RE: Multiple recordset problem - 12/13/2005 3:10:57 AM

csamuels

Posts: 42
Score: 0
Joined: 9/26/2005
Status: offline

better way to count records

you should be able to open two recordsets on one connection

whats your sql statement output look like?

< Message edited by csamuels -- 12/13/2005 3:14:16 AM >

(in reply to johnrswanton)