Solved

Update Database using datagrid based on dataset

Posted on 2004-04-14
7
22,271 Views
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
            ds.AcceptChanges()
            da.Update(ds.Tables(0))
        End If
   
0
Comment
Question by:tgatif
7 Comments
 
LVL 41

Expert Comment

by:graye
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
            Try
                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
        Else
            MsgBox("Hey, there's nothing to udpate")
        End If
0
 

Author Comment

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

Accepted Solution

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

Assisted Solution

by:graye
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()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        '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
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    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.SuspendLayout()
        '
        'DataGrid1
        '
        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
        '
        'ComboBox1
        '
        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"
        '
        'Button1
        '
        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"
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 15)
        Me.ClientSize = New System.Drawing.Size(662, 306)
        Me.Controls.Add(Me.Button1)
        Me.Controls.Add(Me.ComboBox1)
        Me.Controls.Add(Me.DataGrid1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    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\ado.net\Test.mdb"
        con.Open()

        ' 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
            ComboBox1.Items.Add(dr.GetString(0))
        Loop
        ' 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.
        dr.Close()

        ' 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 con.open() 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
        da.Fill(dt)

        ' 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
            Try
                ' 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
        Else
            MsgBox("Hey, there's nothing to udpate")
        End If

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

Assisted Solution

by:Dabas
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

Dabas
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now