Help - 'update is not valid updatecommand'

7 Feb 2007 1:26 AM

guzman.dan

I have tried everything I can find on the internet, and I keep getting
the same error message:
"Update requires a valid UpdateCommand when passed DataRow collection
with modified rows."

Here is the simplest iteration of the code that generates the error.
I've tried the commandbuilder, and that doesn't work either.

Essentially, I'm grabing a table with only one record in it and
changing some of the values. Thats it. Why doesn't it work?!?!?!?!

Thanks

        Dim AdaptSql As OleDb.OleDbDataAdapter ' adapter is use to
update the dataset and datasource
        Dim DatComps As DataSet

        DatComps = New DataSet
        AdaptSql = New OleDb.OleDbDataAdapter("SELECT ComputerName,
PERSON, EPAID, ROOM, BUILDING, PCIMAGE, SERIAL FROM(Inventory) WHERE
ComputerName='" & strComputer & "' AND EPAIS Is NULL", conADT)
        AdaptSql.Fill(DatComps, "Computers")

        Dim drEditrow As DataRow

        drEditrow = DatComps.Tables("Computers").Rows(0)

        drEditrow.BeginEdit()
        For Each objItem In objEnvironment
            Select Case objItem.NAme.ToString
                Case "#ID"
                    drEditrow("EPAID") = "R4T678"
                Case "#Room"
                    drEditrow("ROOM") = "A123"
                Case "#Building"
                    drEditrow("BUILDING") = "WHTRF"
                Case "#Person"
                    drEditrow("PERSON") = strMyName
                Case "#PCImage"
                    drEditrow("PCIMAGE") = "Dell"
                Case "#Serial"
                    drEditrow("SERIAL") = "R244Q8"
            End Select
        Next objItem
        drEditrow.EndEdit()
        AdaptSql.Update(DatComps, "Computers")

7 Feb 2007 6:51 AM

RobinS

You need to set up the UpdateCommand object on the DataAdapter. It's not
generated automatically.

The command builder only works for the most simple applications.

Here's an example of setting up an Update Command on the Order_Details
table in Northwind using SQLServer. It uses parameters to do the updates
rather than dynamic SQL; it's much safer and you don't have to worry about
your quote marks matching.

SQLString = "UPDATE [Order Details] SET OrderID = @OrderID_New, " & _
" ProductID = @ProductID_New, Quantity = @Quantity_New, " & _
" UnitPrice = @UnitPrice_New WHERE OrderID = @OrderID_old " & _
" AND ProductId = @ProductID_Old")
da.UpdateCommand = New SqlCommand(SQLString, cn)
pc = da.UpdateCommand.Parameters
pc.Add("@OrderID_New", SqlDbType.Int, 0, "OrderID")
pc.Add("@ProductID_New", SqlDbType.Int, 0, "ProductID")
pc.Add("@Quantity_New", SqlDbType.SmallInt, 0, "Quantity")
pc.Add("UnitPrice_New", SqlDbType.Money, 0, "UnitPrice")
p = pc.add("@OrderID_Old", SqlDbType.Int, 0, "OrderID")
p.SourceVersion = DataRowVersion.Original
p = pc.add("@ProductID_Old", SqlDbType.Int, 0, "ProductID")
p.SourceVersion = DataRowVersion.Original

Then when I do da.Update(datatable), it works.

You should be able to use this, but you'll have to use ? instead of
@OrderID_New, etc., and OLEDB instead of Sql.

Robin S.
------------------------------------------------------------------
<guzman.***@epa.gov> wrote in message
Show quote

news:1170811563.152555.104030@p10g2000cwp.googlegroups.com...
>I have tried everything I can find on the internet, and I keep getting
> the same error message:
> "Update requires a valid UpdateCommand when passed DataRow collection
> with modified rows."
>
> Here is the simplest iteration of the code that generates the error.
> I've tried the commandbuilder, and that doesn't work either.
>
> Essentially, I'm grabing a table with only one record in it and
> changing some of the values. Thats it. Why doesn't it work?!?!?!?!
>
>
> Thanks
>
>        Dim AdaptSql As OleDb.OleDbDataAdapter ' adapter is use to
> update the dataset and datasource
>        Dim DatComps As DataSet
>
>        DatComps = New DataSet
>        AdaptSql = New OleDb.OleDbDataAdapter("SELECT ComputerName,
> PERSON, EPAID, ROOM, BUILDING, PCIMAGE, SERIAL FROM(Inventory) WHERE
> ComputerName='" & strComputer & "' AND EPAIS Is NULL", conADT)
>        AdaptSql.Fill(DatComps, "Computers")
>
>        Dim drEditrow As DataRow
>
>        drEditrow = DatComps.Tables("Computers").Rows(0)
>
>        drEditrow.BeginEdit()
>        For Each objItem In objEnvironment
>            Select Case objItem.NAme.ToString
>                Case "#ID"
>                    drEditrow("EPAID") = "R4T678"
>                Case "#Room"
>                    drEditrow("ROOM") = "A123"
>                Case "#Building"
>                    drEditrow("BUILDING") = "WHTRF"
>                Case "#Person"
>                    drEditrow("PERSON") = strMyName
>                Case "#PCImage"
>                    drEditrow("PCIMAGE") = "Dell"
>                Case "#Serial"
>                    drEditrow("SERIAL") = "R244Q8"
>            End Select
>        Next objItem
>        drEditrow.EndEdit()
>        AdaptSql.Update(DatComps, "Computers")
>

7 Feb 2007 4:02 PM

guzman.dan

Thanks Robin.

Why did you repeat 'p.SourceVersion = DataRowVersion.Original' twice?
The line before it seems the same as all the other add statements, why
is the last one treated differently?

Dan
Show quote

On Feb 6, 10:51 pm, "RobinS" <Rob...@NoSpam.yah.none> wrote:
> You need to set up the UpdateCommand object on the DataAdapter. It'snot
> generated automatically.
>
> The command builder only works for the most simple applications.
>
> Here's an example of setting up anUpdateCommand on the Order_Details
> table in Northwind using SQLServer. It uses parameters to do the updates
> rather than dynamic SQL; it's much safer and you don't have to worry about
> your quote marks matching.
>
> SQLString = "UPDATE[Order Details] SET OrderID = @OrderID_New, " & _
>   " ProductID = @ProductID_New, Quantity = @Quantity_New, " & _
>   " UnitPrice = @UnitPrice_New WHERE OrderID = @OrderID_old " & _
>   " AND ProductId = @ProductID_Old")
> da.UpdateCommand = New SqlCommand(SQLString, cn)
> pc = da.UpdateCommand.Parameters
> pc.Add("@OrderID_New", SqlDbType.Int, 0, "OrderID")
> pc.Add("@ProductID_New", SqlDbType.Int, 0, "ProductID")
> pc.Add("@Quantity_New", SqlDbType.SmallInt, 0, "Quantity")
> pc.Add("UnitPrice_New", SqlDbType.Money, 0, "UnitPrice")
> p = pc.add("@OrderID_Old", SqlDbType.Int, 0, "OrderID")
> p.SourceVersion = DataRowVersion.Original
> p = pc.add("@ProductID_Old", SqlDbType.Int, 0, "ProductID")
> p.SourceVersion = DataRowVersion.Original
>
> Then when I do da.Update(datatable), it works.
>
> You should be able to use this, but you'll have to use ? instead of
> @OrderID_New, etc., and OLEDB instead of Sql.
>
> Robin S.
> ------------------------------------------------------------------<guzman.***@epa.gov> wrote in message
>
> news:1170811563.152555.104030@p10g2000cwp.googlegroups.com...
>
>
>
> >I have tried everything I can find on the internet, and I keep getting
> > the same error message:
> > "Updaterequires avalidUpdateCommand when passed DataRow collection
> > with modified rows."
>
> > Here is the simplest iteration of the code that generates the error.
> > I've tried the commandbuilder, and that doesn't work either.
>
> > Essentially, I'm grabing a table with only one record in it and
> > changing some of the values. Thats it. Why doesn't it work?!?!?!?!
>
> > Thanks
>
> >        Dim AdaptSql As OleDb.OleDbDataAdapter ' adapter is use to
> >updatethe dataset and datasource
> >        Dim DatComps As DataSet
>
> >        DatComps = New DataSet
> >        AdaptSql = New OleDb.OleDbDataAdapter("SELECT ComputerName,
> > PERSON, EPAID, ROOM, BUILDING, PCIMAGE, SERIAL FROM(Inventory) WHERE
> > ComputerName='" & strComputer & "' AND EPAIS Is NULL", conADT)
> >        AdaptSql.Fill(DatComps, "Computers")
>
> >        Dim drEditrow As DataRow
>
> >        drEditrow = DatComps.Tables("Computers").Rows(0)
>
> >        drEditrow.BeginEdit()
> >        For Each objItem In objEnvironment
> >            Select Case objItem.NAme.ToString
> >                Case "#ID"
> >                    drEditrow("EPAID") = "R4T678"
> >                Case "#Room"
> >                    drEditrow("ROOM") = "A123"
> >                Case "#Building"
> >                    drEditrow("BUILDING") = "WHTRF"
> >                Case "#Person"
> >                    drEditrow("PERSON") = strMyName
> >                Case "#PCImage"
> >                    drEditrow("PCIMAGE") = "Dell"
> >                Case "#Serial"
> >                    drEditrow("SERIAL") = "R244Q8"
> >            End Select
> >        Next objItem
> >        drEditrow.EndEdit()
> >        AdaptSql.Update(DatComps, "Computers")- Hide quoted text -
>
> - Show quoted text -

7 Feb 2007 5:46 PM

RobinS

It's set for the OrderID_Old parameter and then for the ProductID_Old
parameter.

You have to set it on every parameter where you want it to use the original
value instead of the value the user might have changed it to. If you don't
specify it, it uses the current value of the field.

If they changed the primary key fields, I want to be sure that my WHERE
clause uses the original values so it actually finds the record to be
updated.

Does that make sense?

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.
-----------------------------------------------
<guzman.***@epa.gov> wrote in message
Show quote

news:1170864162.502952.96090@l53g2000cwa.googlegroups.com...
> Thanks Robin.
>
> Why did you repeat 'p.SourceVersion = DataRowVersion.Original' twice?
> The line before it seems the same as all the other add statements, why
> is the last one treated differently?
>
> Dan
> On Feb 6, 10:51 pm, "RobinS" <Rob...@NoSpam.yah.none> wrote:
>> You need to set up the UpdateCommand object on the DataAdapter. It'snot
>> generated automatically.
>>
>> The command builder only works for the most simple applications.
>>
>> Here's an example of setting up anUpdateCommand on the Order_Details
>> table in Northwind using SQLServer. It uses parameters to do the
>> updates
>> rather than dynamic SQL; it's much safer and you don't have to worry
>> about
>> your quote marks matching.
>>
>> SQLString = "UPDATE[Order Details] SET OrderID = @OrderID_New, " & _
>>   " ProductID = @ProductID_New, Quantity = @Quantity_New, " & _
>>   " UnitPrice = @UnitPrice_New WHERE OrderID = @OrderID_old " & _
>>   " AND ProductId = @ProductID_Old")
>> da.UpdateCommand = New SqlCommand(SQLString, cn)
>> pc = da.UpdateCommand.Parameters
>> pc.Add("@OrderID_New", SqlDbType.Int, 0, "OrderID")
>> pc.Add("@ProductID_New", SqlDbType.Int, 0, "ProductID")
>> pc.Add("@Quantity_New", SqlDbType.SmallInt, 0, "Quantity")
>> pc.Add("UnitPrice_New", SqlDbType.Money, 0, "UnitPrice")
>> p = pc.add("@OrderID_Old", SqlDbType.Int, 0, "OrderID")
>> p.SourceVersion = DataRowVersion.Original
>> p = pc.add("@ProductID_Old", SqlDbType.Int, 0, "ProductID")
>> p.SourceVersion = DataRowVersion.Original
>>
>> Then when I do da.Update(datatable), it works.
>>
>> You should be able to use this, but you'll have to use ? instead of
>> @OrderID_New, etc., and OLEDB instead of Sql.
>>
>> Robin S.
>> ------------------------------------------------------------------<guzman.***@epa.gov>
>> wrote in message
>>
>> news:1170811563.152555.104030@p10g2000cwp.googlegroups.com...
>>
>>
>>
>> >I have tried everything I can find on the internet, and I keep getting
>> > the same error message:
>> > "Updaterequires avalidUpdateCommand when passed DataRow collection
>> > with modified rows."
>>
>> > Here is the simplest iteration of the code that generates the error.
>> > I've tried the commandbuilder, and that doesn't work either.
>>
>> > Essentially, I'm grabing a table with only one record in it and
>> > changing some of the values. Thats it. Why doesn't it work?!?!?!?!
>>
>> > Thanks
>>
>> >        Dim AdaptSql As OleDb.OleDbDataAdapter ' adapter is use to
>> >updatethe dataset and datasource
>> >        Dim DatComps As DataSet
>>
>> >        DatComps = New DataSet
>> >        AdaptSql = New OleDb.OleDbDataAdapter("SELECT ComputerName,
>> > PERSON, EPAID, ROOM, BUILDING, PCIMAGE, SERIAL FROM(Inventory) WHERE
>> > ComputerName='" & strComputer & "' AND EPAIS Is NULL", conADT)
>> >        AdaptSql.Fill(DatComps, "Computers")
>>
>> >        Dim drEditrow As DataRow
>>
>> >        drEditrow = DatComps.Tables("Computers").Rows(0)
>>
>> >        drEditrow.BeginEdit()
>> >        For Each objItem In objEnvironment
>> >            Select Case objItem.NAme.ToString
>> >                Case "#ID"
>> >                    drEditrow("EPAID") = "R4T678"
>> >                Case "#Room"
>> >                    drEditrow("ROOM") = "A123"
>> >                Case "#Building"
>> >                    drEditrow("BUILDING") = "WHTRF"
>> >                Case "#Person"
>> >                    drEditrow("PERSON") = strMyName
>> >                Case "#PCImage"
>> >                    drEditrow("PCIMAGE") = "Dell"
>> >                Case "#Serial"
>> >                    drEditrow("SERIAL") = "R244Q8"
>> >            End Select
>> >        Next objItem
>> >        drEditrow.EndEdit()
>> >        AdaptSql.Update(DatComps, "Computers")- Hide quoted text -
>>
>> - Show quoted text -
>
>