Copy records to another recordset using Active Server Page

Question

I'm programming in Classic ASP. I'm trying to do the paging. My backend is SQL CE 3.5. Unfortunetly, it doesn't support paging in SQL Query (Like row_number() in sql server).

So I go with ASP Paging. But when i ask to the recordset, give me the first 10 records by setting the rs.PageSize and rs.AbsolutePage and all, it gives me all records. So I planned to copy only first 10 rows from the resultant recordset to another new recordset. So I coded like below:

Set rsTemp = CopyRecordsetStructure(objRs)
rsTemp.Open
iRecordsShown = 0
Set objFields = objRs.Fields
intFieldsCount = objFields.Count-1 
Do While iRecordsShown < intPageSize And Not objRs.EOF
 rsTemp.AddNew
 For Idx = 0 To intFieldsCount
  rsTemp.Fields(Idx).Value = objRs.Fields(Idx).Value
 Next
 rsTemp.Update
 iRecordsShown = iRecordsShown + 1
 objRs.MoveNext
Loop


Public Function CopyRecordsetStructure(ByVal rs)
 Dim rsTemp
 Set rsTemp = CreateObject("ADODB.Recordset")
 Set objFields = rsTemp.Fields
 intFieldCount = objFields.Count - 1
 For Idx = 0 To intFieldCount
  rsTemp.Fields.Append objFields(Idx).Name, _
        objFields(Idx).Type, _
        objFields(Idx).DefinedSize
 Next
 Set CopyRecordsetStructure = rsTemp
End Function

The issue is i could not open the" rsTemp". It throws me error

The connection cannot be used to perform this operation. It is either closed or invalid in this context.

If I use some dummy query and connection it doesn't work.

Please help to copy the records from one recordset to another new record set.

Thanks in advance Ganesh.

Answer 1

Not sure, but this looks wrong

Set objFields = rsTemp.Fields

Shouldn't it be

Set objFields = rs.Fields