How to save Datagrid changes?

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

BenvorAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jorge PaulinoConnect With a Mentor IT Pro/DeveloperCommented:
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
 
JackOfPHCommented:
Put this in the save button, for this will commit changes made the user.

ds3.AcceptChanges()


0
 
JackOfPHCommented:
Please read this article, it will help you...

http://www.programmersheaven.com/2/FAQ-ADONET-Save-Dataset-Changes
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
JackOfPHCommented:
da3.update
ds3.AcceptChanges
0
 
BenvorAuthor Commented:
The "ds3.AcceptChanges" doesn't work,
and the "da3.update" says: "Overload resolution failed because no accessible 'Update' accepts this number of arguments"
0
 
BenvorAuthor Commented:
Nevermind, the da3.Update(ds3, "Invoice_Detail") ran successfully but the changes aren't saved
0
 
JackOfPHCommented:
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
 
BenvorAuthor Commented:
When I tried it like this I get an error: "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
0
 
BenvorAuthor Commented:
None of the examples saves the changes to the database
0
 
Jorge PaulinoIT Pro/DeveloperCommented:

What have you tried ?
You need to have an unique item in the database (ID) and defined as master key
0
 
BenvorAuthor Commented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
What error do you got and can you show how do you fill the datagridview ?
0
 
BenvorAuthor Commented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
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
 
BenvorAuthor Commented:
That still doesn't work
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Can you show ALL code ?
 
0
 
BenvorAuthor Commented:
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
 
BenvorAuthor Commented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
Do you have your ID in the selection/datagridview ?
0
 
BenvorAuthor Commented:
Yes, but I don't display it, it is called "LineNo". Could that be why the save doesn't want to work?
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
>> Could that be why the save doesn't want to work?
No!
Does LineNo field is a primary key ?
0
 
BenvorAuthor Commented:
Yes, here is a screenshot of the table
untitled159.JPG
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Try to include this after the Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da3)

da.UpdateCommand = cb.GetUpdateCommand()
0
 
BenvorAuthor Commented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
Strange ... you have two primary keys on the database. Can you remove the other one and try it ?
0
 
BenvorAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.