Copy records to another recordset using Active Server Page

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.

Not sure, but this looks wrong

Set objFields = rsTemp.Fields

Shouldn't it be

Set objFields = rs.Fields

With the comments and fixed in the above comments, the function should be updated Set objFields = rs.Fields to:

Usage:

Dim rsTemp
Set rsTemp = CopyRecordset(rsPadicon)

Update Code

Public Function CopyRecordset(rs)

    Dim rsTemp, objFields, intFieldsCount, intPageSize
    Set rsTemp = CopyRecordsetStructure(rs)
    rsTemp.Open

    Set objFields = rs.Fields
    intFieldsCount = objFields.Count-1 

    response.write("<li> rs.RecordCount  :" & rs.RecordCount  & "</li>")
    ' response.write("<li> intFieldsCount  :" & intFieldsCount & "</li>")

    rs.MoveFirst 
    Do While Not rs.EOF
        rsTemp.AddNew

        Dim i
        For i = 0 to intFieldsCount 'use i as a counter
            ' response.write("<li> Name :" & rs.Fields(i).Name & "</li>")
            ' response.write("<li> Value :" & rs.Fields(i).Value & "</li>")
            if Not IsNull(rs.Fields(i).Value) then 
                rsTemp.Fields(i).Value = rs.Fields(i).Value
            End if
        Next

        rsTemp.Update

        rs.MoveNext
    Loop

    Set CopyRecordset = rsTemp


End Function


Public Function CopyRecordsetStructure(ByVal rs)
     Dim rsTemp, objFields, intFieldCount, Idx
     Set rsTemp = CreateObject("ADODB.Recordset")
     Set objFields = rs.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