Link to home
Start Free TrialLog in
Avatar of SysCapstone
SysCapstone

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial