How to save changes from DataGridView to the database?

Question

I would like to save the changes made to a DataGridView into the database MS SQL CE, but i can't, the changes are not saved to the database....

This the code (VB.net):

    Private Sub Form1_Load(ByVal sender As System.Object, 
        ByVal e As System.EventArgs) handles MyBase.Load

        Dim con As SqlCeConnection = New SqlCeConnection(@"Data  Source=C:\Users\utente\Documents\test.sdf")

        Dim cmd As SqlCeCommand = New SqlCeCommand("SELECT * FROM mytable", con)

        con.Open()
        myDA = New SqlCeDataAdapter(cmd)
        Dim builder As SqlCeCommandBuilder = New SqlCeCommandBuilder(myDA)
        myDataSet = New DataSet()
        myDA.Fill(myDataSet, "MyTable")
        DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
        con.Close()
        con = Nothing

    End Sub



    Private Sub edit_rec()

        Dim txt1, txt2 As String
        Dim indice As Integer = DataGridView1.CurrentRow.Index

        txt1 = DataGridView1(0, indice).Value.ToString '(0 is the first column of datagridview)
        txt2 = DataGridView1(1, indice).Value.ToString '(1 is the second)        MsgBox(txt1 + "  " + txt2)
        '
        DataGridView1(0, indice).Value = "Pippo"
        DataGridView1(1, indice).Value = "Pluto"
        '
        Me.Validate()
        Me.myDA.Update(Me.myDataSet.Tables("MyTable"))
        Me.myDataSet.AcceptChanges()
        '
    End Sub

Thank you for any suggestion.

Answer 1

You need to use myDA.Update, that will commit the changes. This is because any changes that you are making are made in the local instance of that dataset. And therefore disposed of just like any other variable.

... I can see that in your edit_rec sub, but what is calling that - there is nothing in the code that you have posted.

Answer 2

A little late perhaps.

In the Form load event, you open and close the connection. And furthermore, all are local variables who loose any value or data when leaving the sub

Public Class Form1



        Dim con As SqlCeConnection

        Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

            con = New SqlCeConnection(@"Data  Source=C:\Users\utente\Documents\test.sdf")

            Dim cmd As SqlCeCommand = New SqlCeCommand("SELECT * FROM mytable", con)

            con.Open()
            myDA = New SqlCeDataAdapter(cmd)
            Dim builder As SqlCeCommandBuilder = New SqlCeCommandBuilder(myDA)
            myDataSet = New DataSet()
            myDA.Fill(myDataSet, "MyTable")
            DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
            cmd.Dispose()

        End Sub

***Put the closing of connection here instead or somewhere else suitable when you don't use it anymore***

        Private Sub Form1_FormClosed(sender As Object, e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed

            con.Close()
            con = Nothing

        End Sub

        Private Sub edit_rec()

            Dim txt1, txt2 As String
            Dim indice As Integer = DataGridView1.CurrentRow.Index

            txt1 = DataGridView1(0, indice).Value.ToString '(0 is the first column of datagridview)
            txt2 = DataGridView1(1, indice).Value.ToString '(1 is the second)        MsgBox(txt1 + "  " + txt2)
            '
            DataGridView1(0, indice).Value = "Pippo"
            DataGridView1(1, indice).Value = "Pluto"
            'You code to update goes here and not in my scope of answer

       End Sub

    End Class

Answer 3

I think you want to add @ with your connection string

SqlConnection con;

      con = New SqlConnection(@"Data  Source=C:\Users\utente\Documents\test.sdf");