Update Database using datagrid based on dataset

Posted on 2004-04-14
Last Modified: 2008-09-12
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
Question by:tgatif
LVL 41

Expert Comment

ID: 10825835
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

Author Comment

ID: 10826076
I have tried the following code.  It gives me an exception error!
DataAdapter.SelectCommand needs to be initialized
LVL 10

Accepted Solution

Kavar earned 168 total points
ID: 10826280
You need to define your dataadapter parameters before you connect
LVL 41

Assisted Solution

graye earned 166 total points
ID: 10826435
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
LVL 27

Assisted Solution

Dabas earned 166 total points
ID: 10826880
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


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question