Having Trouble Updating Values [Resolved]

[mendhak]

Creating a simple db application. 4 textboxes, and this is the code I am using in the click event of the update button:

'dsDataSet is my dataset, already declared.
 
 Dim dr As DataRow
 
        'Set the primary key for the table
        With dsDataSet.Tables("Customers")
            dsDataSet.Tables("Customers").PrimaryKey = New DataColumn() {.Columns("CustomerID")}
        End With
 
        Dim pkey As Object
        pkey = TextBox1.Text
 
        dr = dsDataSet.Tables("Customers").Rows.Find(pkey)
        dr.BeginEdit()     'Start editing process
 
        'Enter new field values in the row
        dr.Item(0) = TextBox1.Text
        dr.Item(1) = TextBox2.Text
        dr.Item(2) = TextBox3.Text
        dr.Item(3) = TextBox4.Text
 
 
        dr.EndEdit() 'End editing process
        dsDataSet.AcceptChanges()  'Submit changes to dataset
 
 
      
        MessageBox.Show("Successfully Updated")

It seems that the dataset is being updated, but the values are not being changed in the database. What am I missing?

[pax]

Hi.

I'm not sure, but I think you need to call the Update command instead of AcceptChanges.

dsDataSet.Tables("Customers").Update(dsDataSet.Tables("Customers")) 'Submit changes to dataset

[mendhak]

The dataset doesn't have an update command. I did eventually figure out two ways of updating the database.

Here is the first way:

Dim dr As DataRow
 
        'Set the primary key for the table
        With dsDataSet.Tables("Customers")
            dsDataSet.Tables("Customers").PrimaryKey = New DataColumn() {.Columns("CustomerID")}
        End With
 
        Dim pkey As Object
        pkey = TextBox1.Text
 
        dr = dsDataSet.Tables("Customers").Rows.Find(pkey)
        'dr.BeginEdit()     'Start editing process
 
        'Enter new field values in the row
        dr.Item(0) = TextBox1.Text
        dr.Item(1) = TextBox2.Text
        dr.Item(2) = TextBox3.Text
        dr.Item(3) = TextBox4.Text
 
 
        'now we must explicitly set an UPDATE command to update the database. 
 
        daDataAdapter.UpdateCommand = New SqlCommand("UPDATE Customers SET CompanyName = @CompanyName, ContactName = @ContactName, ContactTitle = @ContactTitle WHERE CustomerID = @CustomerID")
        daDataAdapter.UpdateCommand.Parameters.Add("@CustomerID", TextBox1.Text)
        daDataAdapter.UpdateCommand.Parameters.Add("@CompanyName", TextBox2.Text)
        daDataAdapter.UpdateCommand.Parameters.Add("@ContactName", TextBox3.Text)
        daDataAdapter.UpdateCommand.Parameters.Add("@ContactTitle", TextBox4.Text)
        daDataAdapter.UpdateCommand.Connection = sqlConn
 
      
 
       daDataAdapter.Update(dsDataSet, "Customers")
 
 
 
        MessageBox.Show("Successfully Updated")

This uses the parameters.

This is the other way:

Dim dr As DataRow
 
        'Set the primary key for the table
        With dsDataSet.Tables("Customers")
            dsDataSet.Tables("Customers").PrimaryKey = New DataColumn() {.Columns("CustomerID")}
        End With
 
        Dim pkey As Object
        pkey = TextBox1.Text
 
        dr = dsDataSet.Tables("Customers").Rows.Find(pkey)
        'dr.BeginEdit()     'Start editing process
 
        'Enter new field values in the row
        dr.Item(0) = TextBox1.Text
        dr.Item(1) = TextBox2.Text
        dr.Item(2) = TextBox3.Text
        dr.Item(3) = TextBox4.Text
 
 
        'dr.EndEdit() 'End editing process
        'dsDataSet.AcceptChanges()  'Submit changes to dataset
 
        'now we must explicitly set an UPDATE command to update the database. 
 
 
      
 
        Dim cb As New SqlCommandBuilder(daDataAdapter)
 
        daDataAdapter.Update(dsDataSet, "Customers")
 
 
 
        MessageBox.Show("Successfully Updated")

MSDN told me that the command builder is an inefficient way. So I'd say that the parameter way (first method) is right.

I hope that this is what everyone else does as well.

[pax]

Hi.

Well, at least it was some update command

I usually just drag & drop the table I want from the Server Explorer. Then rightclick the form and there's an option generate a dataset. That dataset has some classes to interact directly with the tables, with field names and all.
And that has an update command allready attached.

I find that to be the easiest way (at least that I know of).

But anyway, I'm glad you solved you're problem.

[mendhak]

Yeah, I did know how to do it that way, but I wasn't comfortable with it. If you'll see my other thread, I asked about "binding"... it might be :evil:

[pax]

Hi.

Perhaps you're right...But I'm not too much into DB's so I'll use it anyway I can

But I never bind to a control such as comboboxes or textboxes. I always fill those out manually, so I didn't really think of it as binding, but ofcourse you're right, alot of binding is probably going on "under cover".