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