Solved

Update Database using datagrid based on dataset

Posted on 2004-04-14
7
22,288 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
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 …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

910 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

19 Experts available now in Live!

Get 1:1 Help Now