Failed to update datarow

[Wanda]

Hello,

I tried to update a row in a table by using the datarow and data
adapter. Unfortunately, it doesn't update.

I try finding that datarow and set it to "dr", I can see dr could be
just an instance of the row, but aren't linked to the table. Is that
why it doesn't update? If not, what is missing? Please help!!!

Dim da As New SqlDataAdapter("select * from Table1", cn)
Dim ds As New DataSet()
Dim dr As DataRow


da.Fill(ds, "Table1")
Dim keys(0) As DataColumn
keys(0) = ds.Tables(Table1").Columns("ID")
ds.Tables("Table1").PrimaryKey = keys


dr = ds.Tables("Table1").Rows.Find (1)
dr.BeginEdit()

dr("F1") = "xxx"
dr.EndEdit()
dr.AcceptChanges()
da.Update(ds, "Table1")


Thanks in advance.
Wanda

 

[Erik]

Hello,

I tried to update a row in a table by using the datarow and data
adapter. Unfortunately, it doesn't update.

I try finding that datarow and set it to "dr", I can see dr could be
just an instance of the row, but aren't linked to the table. Is that
why it doesn't update? If not, what is missing? Please help!!!

Dim da As New SqlDataAdapter("select * from Table1", cn)
Dim ds As New DataSet()
Dim dr As DataRow


da.Fill(ds, "Table1")
Dim keys(0) As DataColumn
keys(0) = ds.Tables(Table1").Columns("ID")
ds.Tables("Table1").PrimaryKey = keys


dr = ds.Tables("Table1").Rows.Find (1)
dr.BeginEdit()

dr("F1") = "xxx"
dr.EndEdit()
dr.AcceptChanges()
da.Update(ds, "Table1")


Thanks in advance.
Wanda

I think you have to do da.Update before dr.AcceptChanges.
After AcceptChanges your DataRow is considered to be the original one so

it won't lead to an update

 

[One Handed Man \( OHM - Terry Burns \)]

Remove the AcceptChanges(), if you leave it in, there will be nothing to
update as this sets the new or updated rows to the original rows and the
dataAdapter has nothing to work with.

--

OHM ( Terry Burns )
. . . One-Handed-Man . . .
If U Need My Email ,Ask Me

Time flies when you don't know what you're doing

 

[Wanda Li]

It doesn't work either if I do da.Update before dr.AcceptChanges,
instead I now get the following error message:

Update requires a valid UpdateCommand when passed DataRow collection
with modified rows.

Thanks
Wanda

 

[One Handed Man \( OHM - Terry Burns \)]

Yes, you need to give the DataAdapter a valid UpdateCommand, you need the
following Commands generally.


SelectCommand
DeleteCommand
InsertCommand
UpdateCommand

Yours will be empty, this is why you are getting the error message.


--

OHM ( Terry Burns )
. . . One-Handed-Man . . .
If U Need My Email ,Ask Me

Time flies when you don't know what you're doing

 

[Wanda Li]

Do you mean I have to use

"Update Table1 Set F1 = 'xxx'" ???

Can I do something as simple as

dr.BeginEdit
dr("f1") = "xxx"
dr.EndEdit

then update???

I don't want to use the update statement since I have so many fields to
update, even I know I can do it in one update statement.

Thanks
Wanda

 

[Cor Ligthert]

Wanda,

You can use the commandbuilder for this.

da.Fill(ds, "Table1")
Dim keys(0) As DataColumn
keys(0) = ds.Tables(Table1").Columns("ID")
ds.Tables("Table1").PrimaryKey = keys
dr = ds.Tables("Table1").Rows.Find (1)
dr.BeginEdit()
dr("F1") = "xxx"
dr.EndEdit()
dim cmb as New xxxCommandBuilder(da) 'xxx = SQL or OleDb
da.Update(ds, "Table1")

'the acceptchanges is normally done by the dataadapter.

(The commandbuilder will not work with complex selectstatements by instance
when there are more than 100 items in a datarow)

I hope this helps?

Cor