Need help updating table

[Gary Paris]

This should be simple but I can't get it to work. I want to update a single
row of data in a table.

ES.Clear()
Dim strSQL As String = "Select * from Contact where sysid = '" &
g_sysID & "'"
DB = New SqlClient.SqlDataAdapter(strSQL, CN)
DB.Fill(ES, "Contact")

If ES.Tables("Contact").Rows.Count > 0 Then
txtAddress.Text =
ES.Tables("Contact").Rows(0).Item("con1_02_03")
txtFirst_Name.Text =
ES.Tables("Contact").Rows(0).Item("First_Name")
txtLast_Name.Text =
ES.Tables("Contact").Rows(0).Item("Last_Name")
End If
I hope the above is correct

Now in another routine I want to update

Dim objRow As Data.DataRow
objRow = ES.Tables("Contact").Rows(0)
objRow.BeginEdit()
objRow("First_Name") = "Gary"
objRow("Last_Name") = "Smith"
objRow("con1_02_03") = "New Address"
objRow.EndEdit()
objRow.AcceptChanges()
DB.Update(ES, "Contact")
What on earth am I doing wrong?

Thanks,

Gary

[Beth Massi [Architect MVP]]

Looks like you're calling AcceptChanges on the dataset before the Update to
the database. In your case you only want to call AcceptCahnges on the
dataset if the update succeeds. I'd pick up the book "ADO.NET - Core
Reference" by David Sceppa ... it't the ADO.NET bible around here.

HTH,
-B

"Gary Paris" <ya**@somewhereovertherainbow.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

This should be simple but I can't get it to work. I want to update a
single row of data in a table.

ES.Clear()
Dim strSQL As String = "Select * from Contact where sysid = '" &
g_sysID & "'"
DB = New SqlClient.SqlDataAdapter(strSQL, CN)
DB.Fill(ES, "Contact")

If ES.Tables("Contact").Rows.Count > 0 Then
txtAddress.Text =
ES.Tables("Contact").Rows(0).Item("con1_02_03")
txtFirst_Name.Text =
ES.Tables("Contact").Rows(0).Item("First_Name")
txtLast_Name.Text =
ES.Tables("Contact").Rows(0).Item("Last_Name")
End If
I hope the above is correct

Now in another routine I want to update

Dim objRow As Data.DataRow
objRow = ES.Tables("Contact").Rows(0)
objRow.BeginEdit()
objRow("First_Name") = "Gary"
objRow("Last_Name") = "Smith"
objRow("con1_02_03") = "New Address"
objRow.EndEdit()
objRow.AcceptChanges()
DB.Update(ES, "Contact")
What on earth am I doing wrong?

Thanks,

Gary

[Gary Paris]

I have the book in question and I am reading Chapter 10, "Submitting Updates
to Your Database". On page 403 there is an example, although not exactly
like mine, it does a delete then an update then an add.

I just wanted to do a simple change. They have an example of submitting the
changes:

da.Update(tbl)

but that doesn't work. What else am I missing? Do I need the BeginEdit or
EndEdit? Do I need the AcceptChanges?

HELP
"Beth Massi [Architect MVP]" <bm****@comcast.net> wrote in message
news:eR**************@tk2msftngp13.phx.gbl...

Looks like you're calling AcceptChanges on the dataset before the Update
to the database. In your case you only want to call AcceptCahnges on the
dataset if the update succeeds. I'd pick up the book "ADO.NET - Core
Reference" by David Sceppa ... it't the ADO.NET bible around here.

HTH,
-B

"Gary Paris" <ya**@somewhereovertherainbow.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

This should be simple but I can't get it to work. I want to update a
single row of data in a table.

ES.Clear()
Dim strSQL As String = "Select * from Contact where sysid = '" &
g_sysID & "'"
DB = New SqlClient.SqlDataAdapter(strSQL, CN)
DB.Fill(ES, "Contact")

If ES.Tables("Contact").Rows.Count > 0 Then
txtAddress.Text =
ES.Tables("Contact").Rows(0).Item("con1_02_03")
txtFirst_Name.Text =
ES.Tables("Contact").Rows(0).Item("First_Name")
txtLast_Name.Text =
ES.Tables("Contact").Rows(0).Item("Last_Name")
End If
I hope the above is correct

Now in another routine I want to update

Dim objRow As Data.DataRow
objRow = ES.Tables("Contact").Rows(0)
objRow.BeginEdit()
objRow("First_Name") = "Gary"
objRow("Last_Name") = "Smith"
objRow("con1_02_03") = "New Address"
objRow.EndEdit()
objRow.AcceptChanges()
DB.Update(ES, "Contact")
What on earth am I doing wrong?

Thanks,

Gary

[Adam Goossens]

Hi Gary,

Don't call AcceptChanges() on the row. Rather, make the changes and call
DB.Update(ES, "Contact").

If the update succeeds, then call AcceptChanges() on your DataSet to
commit the changes permanently.

Regards,
-Adam.

Gary Paris wrote:

This should be simple but I can't get it to work. I want to update a single
row of data in a table.

ES.Clear()
Dim strSQL As String = "Select * from Contact where sysid = '" &
g_sysID & "'"
DB = New SqlClient.SqlDataAdapter(strSQL, CN)
DB.Fill(ES, "Contact")

If ES.Tables("Contact").Rows.Count > 0 Then
txtAddress.Text =
ES.Tables("Contact").Rows(0).Item("con1_02_03")
txtFirst_Name.Text =
ES.Tables("Contact").Rows(0).Item("First_Name")
txtLast_Name.Text =
ES.Tables("Contact").Rows(0).Item("Last_Name")
End If
I hope the above is correct

Now in another routine I want to update

Dim objRow As Data.DataRow
objRow = ES.Tables("Contact").Rows(0)
objRow.BeginEdit()
objRow("First_Name") = "Gary"
objRow("Last_Name") = "Smith"
objRow("con1_02_03") = "New Address"
objRow.EndEdit()
objRow.AcceptChanges()
DB.Update(ES, "Contact")
What on earth am I doing wrong?

Thanks,

Gary

[Cor Ligthert]

Gary,

Exactly as Beth and Adam told you.

To explain what Acceptchanges does.

When you do a change, than there is set in the dataset for every row a value
(the rowstate) what change has be done. Accoording to that does the
dataadapter an update.

However think on the fact that you are using two datatables. One you use to
update the database and one you use for other things. (By instance when you
use ds.getchanges, than you create a copy of the dataset). Than still you
have to set all those changes back when the update is done in the original
dataset.

For that is the command acceptchanges (as if they are done correct).

So doing that before the update, gives the dataadapter not any information
at all what to update.

I hope that this explains it a little bit.

Cor