Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 22378
  • Last Modified:

Update Database using datagrid based on dataset

I have a windows forms datagrid which shows one table.  It is solely based on the dataset.  I want to make changes on datagrid and when I click save button.  All the updates should be saved to database.  I am using the following code but it is not working........

The if condition is never true.  It says that there are not changes in the dataset but I changed the values on datagrid ( which is based on dataset)
    If ds.HasChanges(DataRowState.Modified) Then
        End If
3 Solutions
I'd suggest the following:

       dim dt_changes as datatable
       dim cb As OleDbCommandBuilder
       dim rows as integer

       dt_changes = ds.tables(0).GetChanges()

        If Not IsNothing(dt_changes) Then
                cb = New OleDbCommandBuilder(da)
                rows = da.Update(dt_changes)
                MsgBox(rows & " rows updated")
            Catch ex As Exception
                MsgBox("Yikes, Can't save the changes!" & vbCrLf & ex.Message, MsgBoxStyle.Exclamation)
            End Try
            MsgBox("Hey, there's nothing to udpate")
        End If
tgatifAuthor Commented:
I have tried the following code.  It gives me an exception error!
DataAdapter.SelectCommand needs to be initialized
You need to define your dataadapter parameters before you connect
The OleDdCommandBuilder does that for you... it populates the SelectCommand, Insert, delete, and update commands.  It does this based upon the initial query string that was used when the da.fill() was used.

Here is an entire example (that I use in the classes that I teach)

' Imports is not required, but make the syntax reasier to read
Imports System.Data.oledb

Public Class Form1
    Inherits System.Windows.Forms.Form

    Dim da As New OleDbDataAdapter
    Dim dt As New DataTable
#Region " Windows Form Designer generated code "

    Public Sub New()

        'This call is required by the Windows Form Designer.

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
            End If
        End If
    End Sub
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    Friend WithEvents ComboBox1 As System.Windows.Forms.ComboBox

    'Required by the Windows Form Designer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents ToolTip1 As System.Windows.Forms.ToolTip
    Private components As System.ComponentModel.IContainer
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.components = New System.ComponentModel.Container
        Me.DataGrid1 = New System.Windows.Forms.DataGrid
        Me.ComboBox1 = New System.Windows.Forms.ComboBox
        Me.Button1 = New System.Windows.Forms.Button
        Me.ToolTip1 = New System.Windows.Forms.ToolTip(Me.components)
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point(38, 92)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(586, 185)
        Me.DataGrid1.TabIndex = 0
        Me.ComboBox1.DropDownWidth = 200
        Me.ComboBox1.Location = New System.Drawing.Point(48, 28)
        Me.ComboBox1.Name = "ComboBox1"
        Me.ComboBox1.Size = New System.Drawing.Size(240, 24)
        Me.ComboBox1.TabIndex = 1
        Me.ComboBox1.Text = "ComboBox1"
        Me.Button1.Location = New System.Drawing.Point(557, 18)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(57, 56)
        Me.Button1.TabIndex = 2
        Me.Button1.Text = "Update"
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 15)
        Me.ClientSize = New System.Drawing.Size(662, 306)
        Me.Name = "Form1"
        Me.Text = "Form1"
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()

    End Sub

#End Region

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' The connection object is now OleDbConnection
        Dim con As New OleDbConnection()
        Dim cmd As New OleDbCommand()
        Dim dr As OleDbDataReader

        ' Can't put the connection string as argument to open method
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\local\src\\Test.mdb"

        ' Create a DataReader object.  You use the "ligthweight" data reader
        ' object for doing things like populating a combobox.  The data reader
        ' is not well suited for doing much else.
        cmd.Connection = con
        cmd.CommandText = "Select * from Table1"
        dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess)

        ' Fill a combo box with the datareader
        Do While dr.Read = True
            ' the GetString() method assumes you already know the
            ' schema of the table, and that the first element is a string
        ' The close to the DataReader is required, because the DataAdapter
        ' below internally uses a datareader object... and you can only have
        ' one datareader object open at a time.

        ' Use a DataAdapter to fill a DataTable.  This is the "heavyweight"
        ' method used to fill either tables or the new "dataset" object.
        ' There is an implied and con.close() within the DataAdapter
        ' object itself... so the data adapter is only connected to the database
        ' as long as it takes to do the fill()/update()
        da.SelectCommand = cmd

        ' Bind the datagrid with the new dataset
        DataGrid1.DataSource = dt

        ' no need to close any database connections... it's already closed!
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim dt_changes As DataTable
        Dim cb As OleDbCommandBuilder
        Dim rows As Integer

        ' Let's see if anybody made any changes to the table via the datagrid
        dt_changes = dt.GetChanges()

        If Not IsNothing(dt_changes) Then
                ' Create the SQL insert/delete/update commands that will be
                ' used by the data adapter's update method below.  This is
                ' optional... you *could* populate the 3 properties of the
                ' data adapter yourself (but why?) You might also noice that
                ' the cb object is not reference anywhere... it's magic!
                cb = New OleDbCommandBuilder(da)
                ' Update the changes to the database.  Remember, You can only
                ' update a table that has a primary key!
                rows = da.Update(dt_changes)
                MsgBox(rows & " updated")
            Catch ex As Exception
                MsgBox("Yikes, Can't save the changes!" & vbCrLf & ex.Message, MsgBoxStyle.Exclamation)
            End Try
            MsgBox("Hey, there's nothing to udpate")
        End If

        ' no need to close any database connections... it's already closed!
    End Sub
End Class
Hi tgatif:
>             ds.AcceptChanges()

Beware of AcceptChanges! Its name is misleading!

----Quote form .NET help on AcceptChanges---------
When AcceptChanges is called, any DataRow object still in edit mode successfully ends its edits. The DataRowState also changes: all Added and Modified rows become Unchanged; Deleted rows are removed.

The AcceptChanges method is generally called on a DataTable after you attempt to update the DataSet using the DbDataAdapter.Update method.

-------End of Quote------
Note: This will cause all added and modified rows to become Unchanged. This means that when you invoked the Update command, the DataAdaptor does not see any modified rows, and therefore does not updating!
Note: Notice that the help mentions using AcceptChanges AFTER calling Update

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now