ADO.NET batchUpdate doesn't work with OleDbDataAdapter

[TheDrider]

I am trying to import an ADO Recordset into a SQL Server table. All works well if I want to do each row individually, but I'd like to take advantage of batching.

I'm using VB.NET and OleDB objects to load a DataAdapter from my RecordSet object. I tie the da to an update command. I also have to loop through the data to simulate a change for the update to have any affect.

It all works fine until I try to use the updateBatchSize property. I get a nice unhelpful exception:
[red]A first chance exception of type 'System.NotSupportedException' occurred in System.Data.dll
Specified method is not supported.[/red]

Is batching not supported this way? If not, is there a better/faster way to load data into SQL from an ADO RecordSet?

    Public Function importTest(ByRef rs As ADODB._Recordset) As String

        Dim dt As DataTable = New DataTable()
        Dim da As OleDbDataAdapter = New OleDbDataAdapter()

        Dim cmd As OleDbCommand = New OleDbCommand("spSoFLineItemImport4", conOLEDB)
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.Add("@test_tx", VarChar, 25, "col_01")
        cmd.Parameters.Add("@more_tx", VarChar, 25, "col_02")
        cmd.Parameters.Add("@is_tx", VarChar, 2, "col_03")
        cmd.Parameters.Add("@less_tx", VarChar, 2, "col_04")

        cmd.Prepare()

        da.UpdateCommand = cmd
        da.Fill(dt, rs)


        ' simulate a change
        Dim fld As String
        Dim idx As Integer

        For idx = 0 To dt.Rows.Count - 1
            fld = dt.Rows(idx).Item(2).ToString
            dt.Rows(idx).Item(2) = fld
        Next

        [red][b]da.UpdateBatchSize = 1000[/b] 'FAILS - UNSUPPORTED[/red]
        da.Update(dt)

Any thoughts would be appreciated.

 

[AlexCuse]

In the future, please don't cross post.

thread796-1403090


[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom