Avatar of tjlemahieu
tjlemahieu asked on

How do I save updates to a bound DataGridView?

I am new to DataGridViews.  Also, I have used ADO with VB extensively, but using DataAdapters is also new to me.  I am able to populate the DataGridView.  I want the user to be able to edit the grid.  The editing works fine, but how to I commit the edits to the database.  I'm OK with a SAVE button that saves all the changes.

Using the code below, I define the sqlconnection, setup the grid and populate the grid.  

Dim sSqlCnxnDrain As String
Dim SqlCnxnDrain As New SqlConnection
Dim BindingSourceDrain As BindingSource
Dim DataAdapterDrain As New SqlDataAdapter
Dim DataSetDrain As New DataSet
 
Call DefineConnection()
Call SetupGrid()
Call PopulateGrid("287")
Call PopulateAmounts("287")
 
Private Sub DefineConnection()
	'Create Connection object
	sSqlCnxnDrain = "Data Source=LNDDCDA2P\Inst1;initial catalog=drnGIS;integrated security=SSPI;persist security info=False;"
	SqlCnxnDrain = New SqlConnection(sSqlCnxnDrain)
	SqlCnxnDrain.Open()
end sub
 
Private Sub SetupGrid()
 
        DataGridView1.AllowUserToOrderColumns = True
        DataGridView1.AllowUserToDeleteRows = False
        DataGridView1.AllowUserToAddRows = False
        DataGridView1.AutoGenerateColumns = False
        DataGridView1.MultiSelect = False
        DataGridView1.SelectionMode =   DataGridViewSelectionMode.FullRowSelect
        DataGridView1.VirtualMode = False
 
        Dim colSAD As New DataGridViewTextBoxColumn()
        colSAD.DataPropertyName = "SAD"
        colSAD.HeaderText = "SAD"
        colSAD.Name = "SAD"
        colSAD.ReadOnly = True
        colSAD.Visible = False
        DataGridView1.Columns.Add(colSAD)
 
        Dim colPIN As New DataGridViewTextBoxColumn()
        colPIN.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
        colPIN.SortMode = DataGridViewColumnSortMode.Automatic
        colPIN.DataPropertyName = "PIN"
        colPIN.HeaderText = "PIN"
        colPIN.Name = "PIN"
        colPIN.ReadOnly = True
        colPIN.Visible = True
        DataGridView1.Columns.Add(colPIN)
 
 End Sub
 
 Private Sub PopulateGrid(ByVal sSAD As String)
 
        Dim sSql As String
 
        'Define Sql String
        sSql = "SELECT DRAIN_APPORTIONMENT_TB.* FROM DRAIN_APPORTIONMENT_TB WHERE SAD='" & sSAD & "' ORDER BY PIN;"
 
 
        'Create Data Adapter
        DataAdapterDrain = New SqlDataAdapter(sSql, SqlCnxnDrain)
 
        'Create a Dataset of the Drain_Apportionment records  
        DataSetDrain = New DataSet
        DataAdapterDrain.Fill(DataSetDrain, "DRAINTABLE")
 
        ' Bind the data table to the data grid
        BindingSourceDrain = New BindingSource(DataSetDrain, "DRAINTABLE")
        DataGridView1.DataSource = BindingSourceDrain
 
End Sub

Open in new window

Visual Basic Classic

Avatar of undefined
Last Comment
tjlemahieu

8/22/2022 - Mon
sudhirkrishnan

As the Datagridview is bound to the datatable any edit on the datagrid is like editing the datatable. Now the datatable is connected through the dataadapter to the source in a dataset, so when we call accept changes for a dataset  the changes are updated to the source. You can have datarow.acceptchanges, datatable.accept changes or dataset.acceptchanges. So when you call dataset accept changes, all the datatables in the dataset is committed.

refer this msdn link for more info
http://msdn.microsoft.com/en-us/library/system.data.dataset.acceptchanges.aspx


ASKER
tjlemahieu

I added the following code to a Save button.

        DataSetDrain.AcceptChanges()

Then I updated a value on the grid and pressed save. Went back to the grid later and the value is not there.  Attached are the screen shots of my testing.  Note - I a SqlConnection, BindingSource, SqlDataAdapter and DataSet, but no DataTable.  Am I missing something?  
TestDataGridViewProblem1.doc
sudhirkrishnan

sorry tjlemahieu, you have to first update the dataadapter.

for eg:
it is dataadapter.update(datatable)
DataAdapterDrain.update(DataSetDrain.tables( "DRAINTABLE"))

Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
tjlemahieu

I think we're getting close.  When I make a change and execute this, I get the following error:

        DataAdapterDrain.Update(DataSetDrain.Tables("DRAINTABLE"))
        DataSetDrain.AcceptChanges()
"Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
sudhirkrishnan

Please add the command builder to the code. this will create an update command for you.

Dim cb As SqlCommandBuilder = New SqlCommandBuilder(DataAdapterDrain)
DataAdapterDrain.update(DataSetDrain.tables( "DRAINTABLE"))
ASKER CERTIFIED SOLUTION
sudhirkrishnan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
tjlemahieu

No, it did not.  I added an UpdateCommand and it now works.  Thanks for your help.  You got me on the right track.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.