.Fill in OleDbDataAdapter

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,

Keith
Thu Oct 21 23:51:01 CDT 2004

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

"Keith" wrote:

> 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,

W
Fri Oct 22 00:07:41 CDT 2004

Can you post the code that works?

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Keith" <Keith@discussions.microsoft.com> wrote in message
news:9E3AC1E7-F3B1-4161-AD4C-94F22DD44CE7@microsoft.com...
> 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,

Keith
Fri Oct 22 01:11:03 CDT 2004

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,

"W.G. Ryan eMVP" wrote:

> Can you post the code that works?
>
> --
> W.G. Ryan MVP (Windows Embedded)
>
> TiBA Solutions
> www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
> "Keith" <Keith@discussions.microsoft.com> wrote in message
> news:9E3AC1E7-F3B1-4161-AD4C-94F22DD44CE7@microsoft.com...
> > 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,
>
>
>

Sahil
Fri Oct 22 10:15:12 CDT 2004

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.


"Keith" <Keith@discussions.microsoft.com> wrote in message
news:CB1D9C19-B4D9-487D-98FA-7BF34129F5FB@microsoft.com...
> 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
>
> "Keith" wrote:
>
> > 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,

Keith
Sun Oct 24 21:17:03 CDT 2004

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/default.asp?url=/library/en-us/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" ��ีย�:

> 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,
>
> "W.G. Ryan eMVP" wrote:
>
> > Can you post the code that works?
> >
> > --
> > W.G. Ryan MVP (Windows Embedded)
> >
> > TiBA Solutions
> > www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
> > "Keith" <Keith@discussions.microsoft.com> wrote in message
> > news:9E3AC1E7-F3B1-4161-AD4C-94F22DD44CE7@microsoft.com...
> > > 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,
> >
> >
> >