What's going on in this ADO2 code

3 Mar 2006 4:39 PM

phonl

VB.NET 2005 and ADO2.NET

After years of VB6 and ADO coding, I'm checking out ADO2.NET.

I spent several hours trying to figure out why the line:
objDataAdapter.Update(objDataSet, "myTable")

caused this error:
Update requires a valid UpdateCommand when passed DataRow collection with
modified rows.

In this code:
        objDataSet.Tables("myTable").Rows(1).EndEdit()
        If objDataSet.HasChanges Then
            objDataAdapter.Update(objDataSet, "myTable") <--error here
        End If

After much Google, I found that adding this line solved the problem:
Dim cb As New OleDb.OleDbCommandBuilder(objDataAdapter)

So the working code is:
         Dim cb As New OleDb.OleDbCommandBuilder(objDataAdapter)
        objDataSet.Tables("myTable").Rows(1).EndEdit()
        If objDataSet.HasChanges Then
            objDataAdapter.Update(objDataSet, "myTable")
        End If

What's going on here? What is "cb" doing to the DataAdapter?

3 Mar 2006 4:46 PM

Marina Levit [MVP]

It's generate the update/insert/delete commands so that the adapter knows
what queries to run.

You either need to set the InsertCommand, UpdateCommand, DeleteCommand
properties to call appropriate queries or stored procedures with all the
parameters set up correctly, or use the commandbuilder and have it do it for
you.

There are issues with both approaches, and there have been many discussions
on the subject in this newsgroup.

Show quoteHide quote

"phonl" <phonl@newsgroups.nospam> wrote in message
news:%23MxmREuPGHA.1688@TK2MSFTNGP11.phx.gbl...
> VB.NET 2005 and ADO2.NET
>
> After years of VB6 and ADO coding, I'm checking out ADO2.NET.
>
> I spent several hours trying to figure out why the line:
> objDataAdapter.Update(objDataSet, "myTable")
>
> caused this error:
> Update requires a valid UpdateCommand when passed DataRow collection with
> modified rows.
>
> In this code:
>        objDataSet.Tables("myTable").Rows(1).EndEdit()
>        If objDataSet.HasChanges Then
>            objDataAdapter.Update(objDataSet, "myTable") <--error here
>        End If
>
> After much Google, I found that adding this line solved the problem:
> Dim cb As New OleDb.OleDbCommandBuilder(objDataAdapter)
>
> So the working code is:
>         Dim cb As New OleDb.OleDbCommandBuilder(objDataAdapter)
>        objDataSet.Tables("myTable").Rows(1).EndEdit()
>        If objDataSet.HasChanges Then
>            objDataAdapter.Update(objDataSet, "myTable")
>        End If
>
> What's going on here? What is "cb" doing to the DataAdapter?
>
>
>

3 Mar 2006 5:42 PM

Marina Levit [MVP]

I meant to say the subject has been discussed many times in the adonet
newsgroup.

Show quoteHide quote

"Marina Levit [MVP]" <someone@nospam.com> wrote in message
news:Om88VIuPGHA.4976@TK2MSFTNGP11.phx.gbl...
> It's generate the update/insert/delete commands so that the adapter knows
> what queries to run.
>
> You either need to set the InsertCommand, UpdateCommand, DeleteCommand
> properties to call appropriate queries or stored procedures with all the
> parameters set up correctly, or use the commandbuilder and have it do it
> for you.
>
> There are issues with both approaches, and there have been many
> discussions on the subject in this newsgroup.
>
> "phonl" <phonl@newsgroups.nospam> wrote in message
> news:%23MxmREuPGHA.1688@TK2MSFTNGP11.phx.gbl...
>> VB.NET 2005 and ADO2.NET
>>
>> After years of VB6 and ADO coding, I'm checking out ADO2.NET.
>>
>> I spent several hours trying to figure out why the line:
>> objDataAdapter.Update(objDataSet, "myTable")
>>
>> caused this error:
>> Update requires a valid UpdateCommand when passed DataRow collection with
>> modified rows.
>>
>> In this code:
>>        objDataSet.Tables("myTable").Rows(1).EndEdit()
>>        If objDataSet.HasChanges Then
>>            objDataAdapter.Update(objDataSet, "myTable") <--error here
>>        End If
>>
>> After much Google, I found that adding this line solved the problem:
>> Dim cb As New OleDb.OleDbCommandBuilder(objDataAdapter)
>>
>> So the working code is:
>>         Dim cb As New OleDb.OleDbCommandBuilder(objDataAdapter)
>>        objDataSet.Tables("myTable").Rows(1).EndEdit()
>>        If objDataSet.HasChanges Then
>>            objDataAdapter.Update(objDataSet, "myTable")
>>        End If
>>
>> What's going on here? What is "cb" doing to the DataAdapter?
>>
>>
>>
>
>

6 Mar 2006 5:31 AM

Yuan Ren[MSFT]

Hi Phonl,

Thanks for posting!

As Marina mentioned, the issue is caused when UpdateCommand isn't specified
for the DataAdapter. The following KB article demonstrates how to resolve
the problem when the error occurs:
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B310376

In addition, I think the specification from MSDN give us more details about
the current issue:
http://msdn2.microsoft.com/en-us/library/a19c81fk.aspx

I hope this will be helpful!

Regards,

Yuan Ren [MSFT]
Microsoft Online Support
======================================================
PLEASE NOTE the newsgroup SECURE CODE and PASSWORD were
updated on February 14, 2006. Please complete a re-registration process
by entering the secure code mmpng06 when prompted. Once you have
entered the secure code mmpng06, you will be able to update your profile
and access the partner newsgroups.
======================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================