Question Changes to datatable aren't being committed to the database. Why?

[thebatfink]

Hi, I am retrieving records from an external data source, displaying them in a datagridview and making changes to the data (manually) in the gridview. I have a button on the form containing the datagridview which triggers a sub I am expecting to commit the changes back to the external source, but it isn't making those changes. If anyone in the know could nudge me in the correct direction with this it would be much appreciated!

Here is my data retrieval and display code (in form1 - works ok):

Public Class Form1
Dim dbSource As String
~
Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
        Dim dbConnection As New SQLite.SQLiteConnection()
        Dim dbCommand As SQLite.SQLiteCommand
        dbConnection.ConnectionString = "Data Source= " & dbSource & ";"
        dbConnection.Open()
        dbCommand = dbConnection.CreateCommand
        dbCommand.CommandText = "SELECT id,title,certification FROM movie_info WHERE certification='12'"
        'retrieve records from the database and fill the datatable
        Dim myAdapter As New SQLite.SQLiteDataAdapter(dbCommand)
        Dim myBuilder As New SQLite.SQLiteCommandBuilder(myAdapter)
        myAdapter.Fill(myTable)
        'display the datatable contents in a gridview
        Form2.DataGridView1.DataSource = myTable
        'show the form containing the gridview
        Form2.ShowDialog()
    End Sub
End Class

Here is my commit code (in form2 - doesnt seem to work, but doesnt error either):

Public Class Form2
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        'commit changes in the datatable to the database
        myTable.AcceptChanges()
        myAdapter.Update(myTable)
    End Sub
End Class

Variables (in module1):

Module Module1
    Public myTable As New DataTable()
    Public myAdapter As New SQLite.SQLiteDataAdapter()
    Public myBuilder As New SQLite.SQLiteCommandBuilder()
End Module

Thanks!

 

[jmcilhinney]

The issue is that you're calling AcceptChanges. If you've already accepted the changes in the DataTable then, when you call Update, there are no changes to save. AcceptChanges is called AFTER saving the changes, not before. In fact, Update actually calls AcceptChanges implicitly for you by default. You have to set the AcceptChangesDuringUpdate to False on the adapter to prevent it, which you would only do under special circumstances.

 

[thebatfink]

Hi, thank you very much for the reply. That makes perfect sense thanks for the explanation. It also explains why it was suppressing the error message I now see when I remove that line from the code..

Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

I thought the buildcommand was supposed to handle this for me and build the Update, Delete and Insert commands based on the Select statement used with the Adapter? Why is that failing?

Could it have something to do with calling it outside the initial subs? I already made public declared variables for buildcommand?

Thanks for the help. Much appreciated.

 

[jmcilhinney]

A command builder cannot build UPDATE and DELETE commands if the database table has no primary key. I suspect that yours doesn't.

 

[thebatfink]

A command builder cannot build UPDATE and DELETE commands if the database table has no primary key. I suspect that yours doesn't.

Hi, I had read that a primary key was required, originally I wasn't selecting the [movie_info].[id] field in my SELECT statement, so added this in (and is in the code I posted on my original post).

Attached is a screen shot of the SQLite DB Viewer and the table in question.. it's definitely set as 'INT Primary Key'. Something else must be upsetting it? Don't suppose you have any other ideas, I'd love to get this working!

Thanks again.

 

[jmcilhinney]

Try setting the MissingSchemaAction property of your data adapter to AddWithKey before calling Fill.

 

[thebatfink]

Hi, new code..

    Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
        Dim dbConnection As New SQLite.SQLiteConnection()
        Dim dbCommand As SQLite.SQLiteCommand
        dbConnection.ConnectionString = "Data Source= " & dbSource & ";"
        dbConnection.Open()
        dbCommand = dbConnection.CreateCommand
        dbCommand.CommandText = "SELECT id,title,certification FROM movie_info WHERE certification='12'"
        'retrieve records from the database and fill the datatable
        Dim myAdapter As New SQLite.SQLiteDataAdapter(dbCommand)
        Dim myBuilder As New SQLite.SQLiteCommandBuilder(myAdapter)
        myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
        myAdapter.Fill(myTable)
        'display the datatable contents in a gridview
        Form2.DataGridView1.DataSource = myTable
        Form2.DataGridView1.AutoResizeColumns()
        'show the form containing the gridview
        Form2.ShowDialog()
    End Sub

.. same error

Is the way I have setup where the Adapter / builder objects are ok for referencing them in the second form when they are being filled in the first form? ..if that makes sense?

 

[thebatfink]

Ok I figured this out.. How and where to dimension objects and assign values to them so they are available outside of the current form / module has been bugging me for a while. I can't quite seem to get my head around it. If you know of any online / offline sources that would be good for teaching this would be helpful!

So I had a play, it didn't feel right dimming the Adapter and the Builder in a module, and then dimming it again as a new instance locally in the sub of the form, then trying to reference it in another form. Turns out that is the problem..

This code in Button5_Click -

Dim myAdapter As New SQLite.SQLiteDataAdapter(dbCommand)
Dim myBuilder As New SQLite.SQLiteCommandBuilder(myAdapter)

Should actually be..

myAdapter = New SQLite.SQLiteDataAdapter(dbCommand)
myBuilder = New SQLite.SQLiteCommandBuilder(myAdapter)

and now it works Thanks for taking the time to help me out with this. Much appreciated, Merry Christmas