Batch Update Problem

[Guest]

Hi,
I am porting an ADO.NET 1.1 project to version 2.0 and trying to take
advantage of the dataAdapter's UpdateBatchSize property. When I set this to
0 and change my updatedRowSource to either NONE or OUTPUT PARAMS, I get the
following error when trying to update more than one row.

5/10/2006 12:52:18 - Exception occurred ---> Description:
ExceptionHandling.CustomException: The ConnectionString property has not been
initialized. ---> System.InvalidOperationException: The ConnectionString
property has not been initialized.
at
System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable
dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)

My update method is as follows. I initialize the data adapter's update
related sql command objects' connections at run-time. (I am not using
'USING' as I want to catch exceptions)

Private Sub DoUpdate()

Dim cn As New SqlConnection(strConn)

Me.dataAdapter1UpdateCommand.Connection = cn
Me.dataAdapter1DeleteCommand.Connection = cn
Me.dataAdapter1InsertCommand.Connection = cn

Try

Me.dataAdapter1.Update(Me.Ds.dtMyTable)

Catch ex As Exception
LogExceptions(Me.Name, "DoUpdate", ex)
MessageBox.Show(strChgError, strSrvrError, MessageBoxButtons.OK,
MessageBoxIcon.Error)

Finally
cn.Dispose()

End Try


End Sub

Can anyone tell me why this happens and how to fix it. I can correct the
problem by setting the updateBatchSize to 1, and the problem does not occur
when only one row is being updated. I am connecting to SQL Server 2000 SP4.

Thanks.

 

[Kevin Yu [MSFT]]

Hi John,

I tried the code on my machine and set the UpdatedRowSource to None. My
UpdateBatchSize has been set to 0. However, the problem didn't get
reproduced. Could you please set a breakpoint in the update method line.and
check if the connectionstring has been initialized for sqlDataAdapter
commands. Also, you can set a breakpoint in the RowUpdating event to check
the connectionstring.

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Guest]

Hi Kevin,
The test shows that the connection is initialized for the dataAdapter
commands. I am not tracking the RowUpdating event, and have no event handler
constructed to use. In this manner, I am sure no connection string has been
initialized for this event. In my project, once the various dataAdapter's
and other data objects were built, I explicitly removed any and all
connection objects from the designer to ensure that I was explicitly managing
all connection resources. So, to replicate my issue, you need to remove any
connection object you have in your test project and explicitly set the
connection at runtime.

 

[Guest]

Hi Kevin,
I fixed the problem. In my code, I was setting the connection for the
dataAdapter's delete, insert, and update commands, and ignoring the select
command. This works fine with updatebatchsize = 1, but NOT with 0 or > 1. I
went back and explicitly set the adapter's select command connection in my
update method (This connection had previously been set, but then disposed,
on form load). Things then worked perfectly.

If you don't mind, can you tell me why this is necessary? Is it because of
the rowupdated event's involvement when batch size is <> 1?

Thanks.

 

[Kevin Yu [MSFT]]

Hi John,

This is the implement of the DataAdapter, maybe it is using the Select
command to get the updated data. I'm not quite sure. Sorry. But it was nice
to hear that you have had the problem resolved.

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)