none
How do I update a single row in a dataset? RRS feed

  • Question

  • Background VB.NET 2010

    2 datasets with multiple tables in each dataset. Each dataset was configured using a wizard.

    I have a form in which I am trying to update a dataset. The user knows or can look up the PrimaryKey. I am using this key for my updates.

    This is the scenario:

    On the form there are:

    • 3 Checkboxes
    • 3 textboxes
    • 1 datetimepicker
    • 2 buttons

    the three checkboxes and 2 of the three textboxes and the datetimepicker update the database. the last textbox is used to reference the primary key.

    Private Sub SubmitButton_Click(sender As System.Object, e As System.EventArgs) Handles SubmitButton.Click

            CommitForm.ShowDialog()

            If CommitForm.YesNoBoolean = True Then

                'Update Table based on the PrimaryKey

                Try

                    

                  'Method 1 

                    DataSet.DataTable.FindByPrimaryKey(PrimaryKeyTextBox.Text).ColumnID = CheckBox.Checked

                   

                    DataSet.DataTable.FindByPrimaryKey(PrimaryKeyTextBox.Text).ColumnID = TextBox.Text

                    DataSet.DataTable.FindByPrimaryKey(PrimaryKeyTextBox.Text).ColumnID = DateTimePicker.Text

                    Me.TableNameTableAdapter.Update(DataSet)

    'This methodology returns Error Object reference not set to an instance of an object.

    'Method 2

    Dim IndexInteger AsInteger = PrimaryKeyTextBox.Text - 2 'The way the table is set up, this index works.

                    DataSet.DataTable.Rows(IndexInteger).Item(DataSet.DataTable.ColumnID) = CheckBox.Checked

                    DataSet.DataTable.Rows(IndexInteger).Item(DataSet.DataTable.ColumnID) = TextBox.Text

                    DataSet.DataTable.Rows(IndexInteger).Item(DataSet.DataTable.ColumnID) = DateTimePicker.Text

                               Me.TableNameTableAdapter.Update(DataSet)

    'This methodology returns "there is no row at position (index Position).

    'Method 3

                    Dim UpdateRow As DataSet.TableRow

                    UpdateRow = DataSet.Table.FindByPrimaryKey(PrimaryKeyTextBox.Text)

                    With UpdateRow

                        .PrimaryKey = PrimaryKeyTextBox.Text 'Tried both with and without this column

                        .Checkbox1 = CheckBox.Checked

                        .checkbox2 = CheckBox.Checked

                        .Checkbox3 = CheckBox.Checked

                        .Textbox1 = TextBox.Text

                        .TextBox2 = TextBox.Text

                        .DateTimePicker = DateTimePicker.Text

                    End With

    'Returns Object reference not set to an instance of an object.

                    Me.TableNameTableAdapter.Update(UpdateRow)

                Catch Ex As Exception

                    MessageBox.Show("Error inserting the information into the database." & Ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

                End Try

            Else

                ClearButton.PerformClick()

            End If

        End Sub

    So, I'm trying to update a single row based on the PrimaryKey of a strongly-typed dataset. I have tried three different methods (they are together here, but I have tried them separately). Any suggestions?

    • Moved by Val MazurModerator Wednesday, July 18, 2012 4:27 PM (From:ADO.NET Managed Providers)
    Wednesday, July 11, 2012 2:38 PM

Answers

  • See if the below link helps. I think what you need to do is return the DataRow and then perform the update. You should also account for the scenario where the query does not return data.

    How to: Edit Rows in a DataTable


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by lj1360central Wednesday, July 11, 2012 6:59 PM
    Wednesday, July 11, 2012 4:11 PM