Here is the function to create deep copy of a recordset in VB.NET (for C# code click here) :-
Public Shared Function RSDeepCopy(ByRef RSSrc As ADODB.Recordset) As ADODB.Recordset
Dim rsCopy As New ADODB.Recordset
Try
'Take care of Null recordSet
If IsNothing(RSSrc) Then
Return Nothing
End If
'Either Open Closed RecordSet Or Note its Position if Already Open
Dim AbsPos As Long = -100
If (RSSrc.State = 0) Then
RSSrc.Open()
Else
AbsPos = RSSrc.AbsolutePosition
End If
'Go to Begining of Recordset
If (RSSrc.RecordCount > 0) Then
RSSrc.MoveFirst()
End If
'Copy Source Recordset to Stream
Dim RSStrm As New ADODB.Stream
RSStrm.Type = ADODB.StreamTypeEnum.adTypeBinary
RSSrc.Save(RSStrm)
'Load Stream into RecordSet Mirror
rsCopy.Open(RSStrm)
'Close the Stream(Not Required)
RSStrm.Close()
'If Recordset was open restore its position otherwise close
If (AbsPos <> -100) Then
If (RSSrc.RecordCount > 0) Then
If (AbsPos <> -3) Then
RSSrc.AbsolutePosition = AbsPos
Else
RSSrc.MoveLast()
RSSrc.MoveNext()
End If
End If
Else
RSSrc.Close()
End If
'Stream is not Required
RSStrm = Nothing
Catch ex As Exception
End Try
'Return the Cloned Recordset
Return rsCopy
End Function
Dim rsCopy As New ADODB.Recordset
Try
'Take care of Null recordSet
If IsNothing(RSSrc) Then
Return Nothing
End If
'Either Open Closed RecordSet Or Note its Position if Already Open
Dim AbsPos As Long = -100
If (RSSrc.State = 0) Then
RSSrc.Open()
Else
AbsPos = RSSrc.AbsolutePosition
End If
'Go to Begining of Recordset
If (RSSrc.RecordCount > 0) Then
RSSrc.MoveFirst()
End If
'Copy Source Recordset to Stream
Dim RSStrm As New ADODB.Stream
RSStrm.Type = ADODB.StreamTypeEnum.adTypeBinary
RSSrc.Save(RSStrm)
'Load Stream into RecordSet Mirror
rsCopy.Open(RSStrm)
'Close the Stream(Not Required)
RSStrm.Close()
'If Recordset was open restore its position otherwise close
If (AbsPos <> -100) Then
If (RSSrc.RecordCount > 0) Then
If (AbsPos <> -3) Then
RSSrc.AbsolutePosition = AbsPos
Else
RSSrc.MoveLast()
RSSrc.MoveNext()
End If
End If
Else
RSSrc.Close()
End If
'Stream is not Required
RSStrm = Nothing
Catch ex As Exception
End Try
'Return the Cloned Recordset
Return rsCopy
End Function