Monday, December 4, 2006

ADODB.Recordset to ADO.NET DataTable

It turns out that an OleDbDataAdapter can be used to fill an ADO.NET DataTable object from an ADODB.Recordset object:

Imports System.Data.OleDb


Public Function RecordSetToDataTable(ByVal objRS As ADODB.RecordSet) As DataTable
    Dim objDA As New OleDbDataAdapter()
    Dim objDT As New DataTable()
    objDA.Fill(objDT, objRS)
    Return objDT
End Function


RavenManiac said...


I found a post elsewhere that created an arraylist and iterated through the recordset.

This is soooo much easier.

Thank you!

vbsurfer said...

I have tried using this but when I try to execute the Fill method I get the error - Row handles must all be released before new ones can be obtained.

Here is the code :

Dim rst As New ADODB.Recordset
Dim dt As New DataTable
Dim da As New OleDb.OleDbDataAdapte

SQL$ = "Select sku, lot, Cases_left, palletqty " & _
"FROM asn_grid_view_data " & _
"WHERE cases_left > 0 " & _
" Order By sku, lot"
rst.CursorType = ADODB.CursorTypeEnum.adOpenStatic
rst.Open(SQL$, cnSPSC)
If Not rst.BOF And Not rst.BOF Then
da.Fill(dt, rst)
CCLSMain.DGVPalletItems.DataSource = dt
End If

Any suggestions?