Solved

How to save Datagrid changes?

Posted on 2009-05-04
27
414 Views
Last Modified: 2013-11-27
Hi Experts

I have a datagrid on my form where users can see content of a Database table and they can also change thing in the cells. But now I would like the users to be able to save the changes that they made to the database table. Can anyone please help me out?

I've attached the code that I use to populate the datagrid for your'e use.

Thanx in advance
Try
 
            Dim con As New OleDb.OleDbConnection
            Dim ds As New System.Data.DataSet
            Dim da As New OleDb.OleDbDataAdapter
 
 
            Dim P As Integer
            dbpath = GetDBPath()
            con.ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & dbpath
 
            con.Open()
 
            sql3 = "SELECT LineNo, Description, FCurrUnitPrice, Fees, Disbursements, Tax FROM Invoice_Detail WHERE Inv_no = '" & inv_no & "'"
 
            da3 = New OleDb.OleDbDataAdapter(sql3, con)
            da3.Fill(ds3, "Invoice_Detail")
 
            con.Close()
 
            P = ds3.Tables("Invoice_Detail").Rows.Count
 
            If P >= 1 Then
                'frmInvoicing.DataGridView1.ReadOnly = True
 
                frmInvoicing.DataGridView1.DataSource = ds3.Tables("Invoice_Detail")
                frmInvoicing.DataGridView1.Columns("LineNo").Visible = False
                PopulateDatagrid = True
            Else
                PopulateDatagrid = False
            End If
 
        Catch ex As Exception
            MessageBox.Show("Error trying to Populate DG" & vbCrLf & vbLf & ex.Message.ToString())
        End Try

Open in new window

0
Comment
Question by:Benvor
  • 13
  • 10
  • 4
27 Comments
 
LVL 15

Expert Comment

by:JackOfPH
ID: 24293360
Put this in the save button, for this will commit changes made the user.

ds3.AcceptChanges()


0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 24293364
Please read this article, it will help you...

http://www.programmersheaven.com/2/FAQ-ADONET-Save-Dataset-Changes
0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 24293370
da3.update
ds3.AcceptChanges
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:Benvor
ID: 24293417
The "ds3.AcceptChanges" doesn't work,
and the "da3.update" says: "Overload resolution failed because no accessible 'Update' accepts this number of arguments"
0
 

Author Comment

by:Benvor
ID: 24293426
Nevermind, the da3.Update(ds3, "Invoice_Detail") ran successfully but the changes aren't saved
0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 24293449
After the da3.Update(ds3, "Invoice_Detail")  you should execute the

ds3.AcceptChanges

So your code should be like this...

da3.Update(ds3, "Invoice_Detail")
ds3.AcceptChanges
0
 

Author Comment

by:Benvor
ID: 24293493
When I tried it like this I get an error: "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24293680
0
 

Author Comment

by:Benvor
ID: 24293915
None of the examples saves the changes to the database
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24293949

What have you tried ?
You need to have an unique item in the database (ID) and defined as master key
0
 

Author Comment

by:Benvor
ID: 24294021
I've tried this:
        Try
            Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder()
            cb.QuotePrefix = "["
            cb.QuoteSuffix = "]"
            da3.Update(ds3.Tables(0))
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
And This:
        con3.Open()
        da3.Update(ds3, "Invoice_Detail")
        ds3.AcceptChanges()
        con3.Close()

And there is an unique item in the database.
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24294052
What error do you got and can you show how do you fill the datagridview ?
0
 

Author Comment

by:Benvor
ID: 24294120
There is no error, the changes just doesnt save. And the code I use to populate the datagrid is at the top of the question
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24294222
You need to define some variables as module scope (like ds, da) and not inside the sub.
Check the example that I have posted
0
 

Author Comment

by:Benvor
ID: 24294294
That still doesn't work
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24294365
Can you show ALL code ?
 
0
 

Author Comment

by:Benvor
ID: 24294450
Here is my code that is relevant to this problem. I use a module to do all the "heavy" code in the program

MODULE1:
    Public ds3 As New System.Data.DataSet
    Public da3 As New OleDb.OleDbDataAdapter
    Public sql3 As String
    Public con3 As New OleDb.OleDbConnection
 
    Function PopulateDatagrid(ByVal inv_no As String) As Boolean
 
        Try
            ds3.Clear()
            Dim P As Integer
            dbpath = GetDBPath()
            con3.ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & dbpath
 
            con3.Open()
 
            sql3 = "SELECT LineNo, Description, FCurrUnitPrice, Fees, Disbursements, Tax FROM Invoice_Detail WHERE Inv_no = '" & inv_no & "'"
 
            da3 = New OleDb.OleDbDataAdapter(sql3, con3)
            da3.Fill(ds3, "Invoice_Detail")
 
            con3.Close()
 
            P = ds3.Tables("Invoice_Detail").Rows.Count
 
            If P >= 1 Then
 
                frmInvoicing.DataGridView1.DataSource = ds3.Tables("Invoice_Detail")
                frmInvoicing.DataGridView1.Columns("LineNo").Visible = False
                frmInvoicing.DataGridView1.Columns("Description").ReadOnly = False
                frmInvoicing.DataGridView1.Columns("Tax").ReadOnly = False
                PopulateDatagrid = False
            Else
                PopulateDatagrid = True
            End If
 
        Catch ex As Exception
            MessageBox.Show("Error trying to Populate DG" & vbCrLf & vbLf & ex.Message.ToString())
        End Try
    End Function
 
END OF MODULE1 CODE >>>>>>>>>>>>>>>>>>>>>>>>>>>
 
 
FRMInvoicing CODE:
 
    Private Sub cmdSaveLines_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSaveLines.Click
        Try
 
            Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder()
            cb.QuotePrefix = "["
            cb.QuoteSuffix = "]"
            da3.Update(ds3.Tables(0))
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        'con3.Open()
        'da3.Update(ds3, "Invoice_Detail")
        'ds3.AcceptChanges()
        'con3.Close()
 
    End Sub
 
END OF FRMInvoicing CODE >>>>>>>>>>>>>>>>>>>>>>>>>>>

Open in new window

0
 
LVL 48

Accepted Solution

by:
jpaulino earned 500 total points
ID: 24294768
Change this:
Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder()
da3.Update(ds3.Tables(0))
To:
Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da3)
da3.Update(ds3.Tables("Invoice_Detail") )
0
 

Author Comment

by:Benvor
ID: 24294891
Now I get a Different error:
"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information"
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24294936
Do you have your ID in the selection/datagridview ?
0
 

Author Comment

by:Benvor
ID: 24294987
Yes, but I don't display it, it is called "LineNo". Could that be why the save doesn't want to work?
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24295063
>> Could that be why the save doesn't want to work?
No!
Does LineNo field is a primary key ?
0
 

Author Comment

by:Benvor
ID: 24295113
Yes, here is a screenshot of the table
untitled159.JPG
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24295339
Try to include this after the Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da3)

da.UpdateCommand = cb.GetUpdateCommand()
0
 

Author Comment

by:Benvor
ID: 24301765
I still get the same error:

"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information"
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24302027
Strange ... you have two primary keys on the database. Can you remove the other one and try it ?
0
 

Author Comment

by:Benvor
ID: 24302112
Unfortunately I can't make just one row a Primary key. I get an error the whole time, because it would create duplicates, and I tried to change the indexed to No but it doesn't want to change
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

860 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