.Fill in OleDbDataAdapter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We are having a problem with OleDbDataAdapters. We have found that if you
call the .Fill method twice the second time it returns no rows. You can also
reproducs this by persisting the recordset to XML first. If you persist it to
binary format it works fine.

Does anyone have a solution for this?

Thanks,
 
Here is the source code to duplicate the problem

Sub Main()
'Use ADO objects from ADO library (msado15.dll) imported as .NET
library ADODB.dll using TlbImp.exe

Dim myAdoConnection As ADODB.Connection = New ADODB.Connection

myAdoConnection.CursorLocation = ADODB.CursorLocationEnum.adUseClient
'change connection string as necessary

myAdoConnection.Open("your connection string here")

Dim myAdoRecordset As ADODB.Recordset = New ADODB.Recordset
myAdoRecordset.Open("SELECT * FROM titles", myAdoConnection,
ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 1)

myAdoRecordset.ActiveConnection = Nothing
myAdoConnection.Close()


Console.WriteLine("Recordset.RecordCount: " &
myAdoRecordset.RecordCount)

Dim myDataTable As DataTable = New DataTable

'fill datatable with ADODB.Recordset first time
myDataTable = AdoToDataTable(myAdoRecordset)
Console.WriteLine("(1) DataTable.Rows.Count: " &
myDataTable.Rows.Count)

'fill datatable with ADODB.Recordset second time
myDataTable = AdoToDataTable(myAdoRecordset)
Console.WriteLine("(2) DataTable.Rows.Count: " &
myDataTable.Rows.Count)

Console.ReadLine()
End Sub

Private Function AdoToDataTable(ByVal adoRecordset As ADODB.Recordset)
As DataTable
Dim myDataTable As DataTable = New DataTable
Dim myDataAdapter As OleDbDataAdapter = New OleDbDataAdapter
myDataAdapter.Fill(myDataTable, adoRecordset)
Return myDataTable
End Function
 
Thanks for your reply.

This code works fine except as you can see if you run it that the second
call to .Fill returns 0 rows.
The output is:

Recordset.RecordCount: 18
(1) DataTable.Rows.Count: 18
(2) DataTable.Rows.Count: 0

Do you have any suggestions for this?

Thanks,
 
From the documentation -

CAUTION This overload of the Fill method does not implicitly call Close on
the ADO object when the fill operation is complete. Therefore, always call
Close when you are finished using ADO Recordset or Record objects. This
ensures that the underlying connection to a data source is released in a
timely manner, and also prevents possible access violations due to unmanaged
ADO objects being reclaimed by garbage collection when existing references
still exist.

Check and see if that might be your problem :)

Secondly, you say that it doesn't work with XML persistence, can you post
the XML here too (so we don't have to screw around with setting up a d/b
that isn't exactly like yours).

- Sahil Malik
http://www.dotnetjunkies.com/weblog/sahilmalik
Please reply to the newsgroups instead of email so everyone can benefit from
your reply.
 
I contacted Microsoft support in China and they found a fix for this over the
weekend. Please see the revised code below.


Private Sub RestartPosition(ByVal adoRecordset As ADODB.Recordset)
Dim myAdoRecordsetConstruction As ADODB.ADORecordsetConstruction
myAdoRecordsetConstruction = CType(adoRecordset,
ADODB.ADORecordsetConstruction)
Dim myIRowset As IRowset
myIRowset = myAdoRecordsetConstruction.Rowset
myIRowset.RestartPosition(New
IntPtr(myAdoRecordsetConstruction.Chapter))
End Sub


<System.Runtime.InteropServices.ComImport(), _

System.Runtime.InteropServices.Guid("0C733A7C-2A1C-11CE-ADE5-00AA0044773D"), _

System.Runtime.InteropServices.InterfaceType(System.Runtime.InteropServices.ComInterfaceType.InterfaceIsIUnknown)> _
Private Interface IRowset
<System.Obsolete("not defined", True)> Sub AddRefRows()
<System.Obsolete("not defined", True)> Sub GetData()
<System.Obsolete("not defined", True)> Sub GetNextRows()
<System.Obsolete("not defined", True)> Sub ReleaseRows()

'http://msdn.microsoft.com/library/d...s/oledb/htm/oledbirowset__restartposition.asp
Sub RestartPosition(ByVal hChapter As System.IntPtr)
'alternate definition to see success codes is
'<System.Runtime.InteropServices.PreserveSigAttribute> function
RestartPosition(hChapter as System.IntPtr) as System.Int32
End Interface

Sub Main()

Dim myAdoConnection As ADODB.Connection = New ADODB.Connection
myAdoConnection.CursorLocation = ADODB.CursorLocationEnum.adUseClient
'change connection string as necessary
myAdoConnection.Open("my connection here.")

Dim myAdoRecordset As ADODB.Recordset = New ADODB.Recordset
myAdoRecordset.Open("SELECT * FROM titles", myAdoConnection,
ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 1)
myAdoRecordset.ActiveConnection = Nothing
myAdoConnection.Close()
Console.WriteLine("Recordset.RecordCount: " &
myAdoRecordset.RecordCount)

Dim myDataTable As DataTable '= New DataTable
Dim rs As ADODB.Recordset

'fill datatable with ADODB.Recordset first time
myDataTable = AdoToDataTable(myAdoRecordset)
Console.WriteLine("(1) DataTable.Rows.Count: " &
myDataTable.Rows.Count)
'Add this line to reset position
RestartPosition(myAdoRecordset)

'fill datatable with ADODB.Recordset second time
myDataTable = AdoToDataTable(myAdoRecordset)
Console.WriteLine("(2) DataTable.Rows.Count: " &
myDataTable.Rows.Count)
'Add this line to reset position
RestartPosition(myAdoRecordset)

Console.ReadLine()
End Sub
Private Function AdoToDataTable(ByVal adoRecordset As ADODB.Recordset)
As DataTable

Dim myDataTable As DataTable = New DataTable
Dim myDataAdapter As OleDbDataAdapter = New OleDbDataAdapter
myDataAdapter.Fill(myDataTable, adoRecordset)
Return myDataTable

End Function


"Keith" เขียน:
 
Back
Top