Help - 'update is not valid updatecommand'

[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")

 

[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.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

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.

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?!?!?!?!

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 -

 

[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.
-----------------------------------------------

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

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.
------------------------------------------------------------------<[email protected]>
wrote in message

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?!?!?!?!

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 -