Recordset to DataTable



Hi All,

I am having the strangest and most frustrating time trying to load a
recordset into a data table more than once. For some reason, when I execute
the following code, the first messagebox displays a record count of 2 and a
second call to it displays a count of 0. Why the inconsistent result. The
recordset seems to be open and I even perform a MoveFirst, etc. Nothing
works. Should it not consistently return a record count of 2 since I am
executing the same code twice. The code is extremly simple, but for some
reason, it seems as if the recordset object itself becomes invalid after the
..Fill operation is complete. The only way I can find this is to perform my
BuildDataSource (real world scenario involves connecting to a database, etc.)
function which simply creates and returns an ADODB recordset. Any idea or
help. Note: I reference the ADODB 2.7 InterOp library installed with VS
2005. Are there any specifications as to what the cursorlocation, cursor
type, or connection open parameters. I've tried many combinations.

Dim DataSource As ADODB.Recordset
Dim DataTable As Data.DataTable

DataSource = BuildDataSource() 'Builds test recordset with 2 records.
DataTable = RecordsetToDataTable(DataSource)

MessageBox.Show(DataTable.Rows.Count) ' Displays 2. Correct.
DataTable = RecordsetToDataTable(DataSource)
MessageBox.Show(DataTable.Rows.Count) ' Displays 0. Incorrect

Public Function RecordsetToDataTable(ByVal recordset As ADODB.Recordset) As
Dim OleDbDataAdapter As Data.OleDb.OleDbDataAdapter
Dim DataTable As Data.DataTable

OleDbDataAdapter = New OleDb.OleDbDataAdapter
DataTable = New Data.DataTable("Employees")
OleDbDataAdapter.Fill(DataTable, recordset)
Return DataTable
End Function

Public Function BuildDataSource() As ADODB.Recordset
Dim NewDataSource As ADODB.Recordset

NewDataSource = New ADODB.Recordset
NewDataSource.Fields.Append("FirstName", ADODB.DataTypeEnum.adVariant,
100, ADODB.FieldAttributeEnum.adFldIsNullable
NewDataSource.Fields.Append("LastName", ADODB.DataTypeEnum.adVariant,
100, ADODB.FieldAttributeEnum.adFldIsNullable
NewDataSource.CursorLocation = ADODB.CursorLocationEnum.adUseClient
NewDataSource.CursorType = ADODB.CursorTypeEnum.adOpenDynamic


NewDataSource.Fields("FirstName").Value = "Uncle"
NewDataSource.Fields("LastName").Value = "Bob"

NewDataSource.Fields("FirstName").Value = "Aunty"
NewDataSource.Fields("LastName").Value = "Jane"

Return NewDataSource
End Function


Hi Robin,

I need to as I am interacting with a legacy VB6 application. I have
built an InterOp Class Library and ave referenced the ADODB Primary InterOp
Assembly library to help me accomplish this. As stated, the problem occurs
after I execute the .Fill method. For some reason, my original recordset is
left in limbo. I cannot execute the same procedure on it again.


I find it difficult to believe that you can do a Fill on a table
and it would fill an ADODB recordset correctly. Do you have
Option Strict On at the top of your program, and/or for your project?

What I would try if I were you is when moving data from an ADODB
recordset to a .Net DataSet or DataTable, read it from ADODB the
VB6/ADODB way, then read through it and stick it in a .Net DataTable.
And vice versa. And see if it works right. Just out of curiousity.

Robin S.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question