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

I think you need to use SqlConnection, SqlDataAdapter, etc...

UpdateBatchSize is only supported by SQL Server and Oracle providers IIRC, so you will need to use the DB Specific classes rather than OleDb.

Here is an example:

http://msdn2.microsoft.com/en-us/library/kbbwt18a(VS.80).aspx

Hope it helps,

Alex


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

Ignorance of certain subjects is a great part of wisdom