Search code examples
vb.netadodbrecordset

VB.NET oledbDataAdapter fill work only once

So I have a recordset and I want to fill two datatable with it but it only fill the first datatable.

Dim da As New OleDb.OleDbDataAdapter
Dim dt1 As New DataTable
Dim rs1 As New ADODB.Recordset
rs1.Open("select * from table",conn,ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)
da.Fill(dt1, rs1)

rs1.MoveFirst()

da = New OleDb.OleDbDataAdapter
Dim dt2 As New DataTable
da.Fill(dt2, rs1)

So the first datatable (dt1) has data in it but dt2 is empty and I'm not sure why. I tried the MoveFirst() but it change nothing

Solution

  • If you're using VB.Net, instead of VBA or VBScript, you SHOULD NOT use the old ADODB types like RecordSet. They exist for backwards compatibility only when porting forward old code, and should not be used for new development. You definitely shouldn't mix and match between the old adodb and new ADO.Net in the same place. Stick with ADO.Net.

    In this case, we can replace old RecordSet with also-20yrs-old-now-but-not-as-old-as-adodb OleDbCommand:

    Dim dt1 As New DataTable()
Dim dt2 As New DataTable()
Using conn As New OleDb.OleConnection("connection string here"), _
      cmd  As New OleDb.OleDbCommand("select * from table", conn), _
      da   As New OleDb.OleDbAdataAdapter(cmd)

    ' When using .Fill() you don't even need to call .Open()
    da.Fill(dt1)

    ' Optional
    cmd.CommandText = "select * from othertable"

    da.Fill(dt2)

End Using