Update Database only with CHANGED DataGrid/DataSet values

I have this code....

Private Sub DataSaveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataSaveButton.Click
        Dim RecordCount, RecordIndex As Integer
        Dim sSQL As String
        Dim rSQL As String
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand
        Dim da As OleDbDataAdapter
        Dim ds As New DataSet
        Dim oldQty As Integer

        Dim connectionString As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename
        con = New OleDbConnection(connectionString)
        con.Open()

        RecordCount = CrimeData.Tables(0).Rows.Count

        RecordIndex = 0

        While (RecordCount > RecordIndex)

            rSQL = "SELECT Quantity FROM Virginia WHERE Month = #" + CrimeData.Tables(0).Rows(RecordIndex).Item(0) + "# AND Type = '" + dbClass.GetTypeList(dbClass.GetJurisdictionList().Tables(0).Rows(JurisdictionComboBox.SelectedIndex).Item(0).ToString()).Tables(0).Rows(CrimeTypeComboBox.SelectedIndex).Item(0).ToString() + "' AND Jurisdiction = '" + dbClass.GetJurisdictionList().Tables(0).Rows(JurisdictionComboBox.SelectedIndex).Item(0).ToString() + "'"

            sSQL = "UPDATE Virginia SET Quantity = " + CrimeData.Tables(0).Rows(RecordIndex).Item(1).ToString() + " WHERE Month = #" + CrimeData.Tables(0).Rows(RecordIndex).Item(0) + "# AND Type = '" + dbClass.GetTypeList(dbClass.GetJurisdictionList().Tables(0).Rows(JurisdictionComboBox.SelectedIndex).Item(0).ToString()).Tables(0).Rows(CrimeTypeComboBox.SelectedIndex).Item(0).ToString() + "' AND Jurisdiction = '" + dbClass.GetJurisdictionList().Tables(0).Rows(JurisdictionComboBox.SelectedIndex).Item(0).ToString() + "'"

            'MessageBox.Show(rSQL)

            da = New OleDbDataAdapter(sSQL, con)
            da.Fill(ds)

            ds.Tables(0).TableName = "Quantity"

            oldQty = ds.Tables(0).Rows(0).Item(0).ToInteger()

            If (CrimeData.Tables(0).Rows(RecordIndex).Item("Quantity") <> oldQty) Then
                cmd = New OleDbCommand(sSQL, con)
                cmd.ExecuteNonQuery()
            End If


            RecordIndex += 1
        End While

        con.Close()
        DataDisplay.Refresh()

What I would like to do is run through the displayed rows of the DataSet in the DataGrid, check to see which ones have changed, and then only update the ones that have been changed.  This would seem to work, but when I run this I get an error that says, in reference to DataSet ds, that "Table 0 cannot be found".  The query rSQL displays correct syntax when output in the message box.  Does anybody know why I cannot access this "table" (really it is only one value, a single number)??  Or, does anybody know a quick way to update only changed values in a DataGrid that is different and more efficient than what I have here??  For information, I am using SQL to update specifically an Access 2000 database.

Thanks a lot for the help.
SysCapstoneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
You have to call the dataadapter.Update method to write changes back to the database.  You need to define yoru dataadapter outside the scope of your event.  If done correctly your procedure would look like...

Private Sub DataSaveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataSaveButton.Click

try
   daVirginia.Update(ds)
catch ex as exception
   messagebox.show(ex.message)
end try
end sub

you may have to throw in a call to endcurrentedit but that's about it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cubixSoftwareCommented:
Hi

I answered this question to help someone use a dataset to update an access database - hopefully this will help you as well

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21780852.html


0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

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.