Recordset to DataTable

[Guest]

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
Data.DataTable
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.Open(CursorType:=ADODB.CursorTypeEnum.adOpenStatic,LockType:=ADODB.LockTypeEnum.adLockOptimistic)

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

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

Return NewDataSource
End Function

 

[RobinS]

Why are you using ADODB?

Robin S.
---------------------------------

 

[Guest]

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.

 

[RobinS]

I find it difficult to believe that you can do a Fill on a table
adapter,
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.
------------------------------------------------------------

 

[Cor Ligthert [MVP]]

Giovanni,

Are you sure that there is a method that returns a datatable, I thought it
was a dataset.

http://msdn.microsoft.com/library/d.../cpguide/html/cpconaccessingadofromadonet.asp

Cor