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.OL EDB.4.0;Da ta Source=" + filename
con = New OleDbConnection(connection String)
con.Open()
RecordCount = CrimeData.Tables(0).Rows.C ount
RecordIndex = 0
While (RecordCount > RecordIndex)
rSQL = "SELECT Quantity FROM Virginia WHERE Month = #" + CrimeData.Tables(0).Rows(R ecordIndex ).Item(0) + "# AND Type = '" + dbClass.GetTypeList(dbClas s.GetJuris dictionLis t().Tables (0).Rows(J urisdictio nComboBox. SelectedIn dex).Item( 0).ToStrin g()).Table s(0).Rows( CrimeTypeC omboBox.Se lectedInde x).Item(0) .ToString( ) + "' AND Jurisdiction = '" + dbClass.GetJurisdictionLis t().Tables (0).Rows(J urisdictio nComboBox. SelectedIn dex).Item( 0).ToStrin g() + "'"
sSQL = "UPDATE Virginia SET Quantity = " + CrimeData.Tables(0).Rows(R ecordIndex ).Item(1). ToString() + " WHERE Month = #" + CrimeData.Tables(0).Rows(R ecordIndex ).Item(0) + "# AND Type = '" + dbClass.GetTypeList(dbClas s.GetJuris dictionLis t().Tables (0).Rows(J urisdictio nComboBox. SelectedIn dex).Item( 0).ToStrin g()).Table s(0).Rows( CrimeTypeC omboBox.Se lectedInde x).Item(0) .ToString( ) + "' AND Jurisdiction = '" + dbClass.GetJurisdictionLis t().Tables (0).Rows(J urisdictio nComboBox. SelectedIn dex).Item( 0).ToStrin g() + "'"
'MessageBox.Show(rSQL)
da = New OleDbDataAdapter(sSQL, con)
da.Fill(ds)
ds.Tables(0).TableName = "Quantity"
oldQty = ds.Tables(0).Rows(0).Item( 0).ToInteg er()
If (CrimeData.Tables(0).Rows( RecordInde x).Item("Q uantity") <> 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.
Private Sub DataSaveButton_Click(ByVal
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.OL
con = New OleDbConnection(connection
con.Open()
RecordCount = CrimeData.Tables(0).Rows.C
RecordIndex = 0
While (RecordCount > RecordIndex)
rSQL = "SELECT Quantity FROM Virginia WHERE Month = #" + CrimeData.Tables(0).Rows(R
sSQL = "UPDATE Virginia SET Quantity = " + CrimeData.Tables(0).Rows(R
'MessageBox.Show(rSQL)
da = New OleDbDataAdapter(sSQL, con)
da.Fill(ds)
ds.Tables(0).TableName = "Quantity"
oldQty = ds.Tables(0).Rows(0).Item(
If (CrimeData.Tables(0).Rows(
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.