Solved

How to save Datagrid changes?

Posted on 2009-05-04
27
404 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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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 discusses moving either the default database or any database to a new volume.

707 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

14 Experts available now in Live!

Get 1:1 Help Now