Member Avatar for dougancil

I am trying to update a database table that's attached to a table adapter and can't quite seem to be able to get the code to work. It was suggested that I use an update statement, but the person I was working with had no idea how to update a table adapter. Can someone please tell me how to update a table adapter please.

Thank you

Doug

  • 2 Contributors
  • 2 Replies
  • 105 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by dougancil

Dani AI

Generated

and — the practical way to persist DataGridView edits when using a typed DataSet/TableAdapter is to commit any in-flight edits and call the TableAdapter (or TableAdapterManager) Update method so ADO.NET can run the generated INSERT/UPDATE/DELETE commands for changed rows.

A minimal, safe pattern (VB) is:

Try
    Me.Validate()
    MyBindingSource.EndEdit()
    ' Update a single table:
    MyTableAdapter.Update(MyDataSet.MyTable)

    ' Or if you have multiple related tables generated with the designer:
    MyTableAdapterManager.UpdateAll(MyDataSet)
Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

Key points and troubleshooting tips:

  • Do not call AcceptChanges() before Update(). Update() relies on each DataRow.RowState (Added/Modified/Deleted) to decide what SQL to run; AcceptChanges clears those states.
  • If the TableAdapter has no Insert/Update/Delete commands (designer didn’t generate them), use the TableAdapter Configuration wizard to regenerate or supply those commands; otherwise Update() will do nothing.
  • If you run a server-side stored procedure (to copy or modify rows) from code instead of letting the TableAdapter do it, use ExecuteNonQuery() for non-query SPs and then re-Fill the affected table so the grid shows server-side changes.
  • For batch/transactional safety or to reduce work, call DataSet.GetChanges() and update only changed rows, or wrap updates in a DB transaction.

Microsoft docs: reference on Update semantics and patterns is useful: and the non-query command method SqlCommand.ExecuteNonQuery.

Recommended Answers

All 2 Replies

Member Avatar for prvnkmr194

Please clear your question little bit more how and where you want to update, and if possible post your code.

Member Avatar for dougancil

Prvnkmr,

I would like to update from the following code:

Public Class ExceptionEdit
    Private Sub ExceptionEdit_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'MDRDataSet.scratchpad3' table. You can move, or remove it, as needed.
        Me.Scratchpad3TableAdapter.Fill(Me.MDRDataSet.scratchpad3)
    End Sub
    Private Sub SaveExceptionButton(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles saveexceptionsButton.Click
        Dim oCmd2 As System.Data.SqlClient.SqlCommand
        Dim oDr2 As System.Data.SqlClient.SqlDataReader
        oCmd2 = New System.Data.SqlClient.SqlCommand
        Try
            With oCmd2
                .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
                .Connection.Open()
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate)
                .Parameters.AddWithValue("@payperiodenddate", payperiodenddate)
                .CommandText = "sp_exceptioncopy"
                oDr2 = .ExecuteReader()
                oCmd2.Connection.Close()
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd2.Connection.Close()
        End Try
        ExceptionsFinal.Show()
    End Sub

and as you can see, I've included my tableadapter in my code. The dataset that I'm referencing in my table adapter is the dataset that I'll need to update. I hope that offers some more clarity.

Thank you

Doug

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.