Second fill in Ado.recordset does not fill table

[Jamal Samedov]

Hi,
I have an issue with filling datatables from ADO.Recordset.
May be somebody had an experience or clue with this behavior, please, share
your knowledge

Problem is following:
Ado.Recordset is opened from a table in MS SQL 2005 database and queried
from they: namely it is opened one of the tables with following attributes:
pRecordset = New ADODB.Recordset()

pRecordset.CursorLocation = CursorLocationEnum.adUseClient

pRecordset.CursorType = CursorTypeEnum.adOpenStatic

pRecordset.LockType = LockTypeEnum.adLockOptimistic

pRecordset.ActiveConnection = mConnection

pRecordset.Open(pTableName)

locResult = pRecordset.State = ObjectStateEnum.adStateOpen



then I fill a table with following procedure:

Friend Sub FillTable(ByRef pTable As DataTable, ByVal pRecordset As
ADODB.Recordset)
Dim locAdapter As OleDbDataAdapter
Try
locAdapter = New OleDbDataAdapter
pTable = New DataTable
pTable.TableName = pRecordset.Source.ToString

locAdapter.Fill(pTable, pRecordset)

Catch ex As Exception
LogErrorMessage("Unexpected error", ex)
End Try
End Sub

This works fine, however if I call the same procedure second type it does
not fill table without Requery

First call gives filled table and second time table is empty. Call is like
this:

FillTable(mTable, mRecordset)
FillTable(mTable, mRecordset)

If I put bitween the calls additional line:

mRecordset.Requery then in both calls tables are filled with data.

With ado.recordset is nothing happend in debug time I see that there is the
same amount of records. I thought may be cursor in recordset can move only
in one direction, but this is not a case. I have chacked this with methods
of ado.recordset like MoveFirst, MoveNext and see that the current record is
changing. However if I put between calls

mRecordset.MoveFirst it does not help.

I just do not like every time request data from database.

Any ideas are appreciated.

Regards,

 

[Norman Yuan]

Why do you use ADO.Recordset here? OldDbDataAdapter here is designed for
retrieve data from data source and fill data table (BTW, if the data source
is SQL Server, you should use SqlDataAdapter, instead of OldDbDataAdapter).

The only reason one would use ADO.Recordset in .NET that I can think of is
that the .NET uses an existing COM object that returns a ADO.Recordset and
you do not want to re-write data access code. Obviuosly, it is not the case
for you. So, you'd better throw away the ADO.Recordset portion of code,
DataAdapter retrieves data and fill datatable better with a lot less code.
More importantly, no extra/unnecessary dependency (ADO2.x interop) is added
to your code.

 

[Jamal Samedov]

Hi, Yuan
thank you for the response.
but the using sqldata in place of adodb is out of discussion.
It has own advances and I have completely different opinion of yours.
Anyway thanks.
Regards
J.N. Samedov,

www.coin-masters.com

 

[Jamal Samedov]

However I have found the reason:
Microsoft said
(http://msdn.microsoft.com/en-us/library/aa720078(VS.71).aspx)
that:
Note that the OleDbDataAdapter.Fill overload that takes a DataSet and an ADO
object implicitly calls Close on the ADO object when the Fill operation is
complete.

So the object should be reconnected or Fill should be done from disconnected
inmemory copy of the object
Now the issue is clear

J.N. Samedov,

 

[Ata Hanov]

Salam Jamal,
Pishet tebe Ata! Kak twoyi dela? Kak jizn'! Cto to ya tebe pishu, ty ne otvechaesh, vse li u tebya normalno!?
Otvet mne po etomu adresu!
S pojelaniyemi, Ata Hanov!