Need help updating table

16 Apr 2005 12:00 AM

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

16 Apr 2005 12:13 AM

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

Show quoteHide quote

"Gary Paris" <y***@somewhereovertherainbow.com> wrote in message
news:%23mY6cdhQFHA.2384@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
>
>

16 Apr 2005 12:25 AM

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

Show quoteHide quote

"Beth Massi [Architect MVP]" <bma***@comcast.net> wrote in message
news:eR8IZkhQFHA.3988@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" <y***@somewhereovertherainbow.com> wrote in message
> news:%23mY6cdhQFHA.2384@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
>>
>>
>
>

16 Apr 2005 7:45 AM

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

16 Apr 2005 5:39 AM

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:
Show quoteHide quote

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