Solved

How to save Datagrid changes?

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

 

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

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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