Maybe someone can help me figure out why the aforementioned error occurs when I 'delete' a row from the in memory data set and then try to update the physical data source.
I have a simple Windows application that lets the user select items from a TreeView control and store the selected item's data into a Microsoft Access 2010 database.
I have two subroutines that update the database. One for the parent table and another for the child table.
Public Sub UpdateParentTable(ByVal cnnAccess As String, _
ByVal dsAccess As DataSet)
Dim dt As New DataTable
dt = dsAccess.Tables("tblPCNames")
Dim cmd As New OleDbCommand()
Using cnn As New OleDbConnection(cnnAccess)
Try
If cnn.State = ConnectionState.Closed Then
cnn.Open()
cmd.Connection = cnn
For Each Row As DataRow In dt.Rows
Select Case Row.RowState
Case DataRowState.Added
cmd.CommandText = _
"INSERT INTO tblPCNames ( PCID, PC_Name ) " & _
"VALUES(@PCID, @PC_Name)"
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@PCID", _
Row("PCID"))
cmd.Parameters.AddWithValue("@PC_Name", _
Row("PC_Name"))
cmd.ExecuteNonQuery()
Exit Select
Case DataRowState.Deleted
cmd.CommandText = _
"DELETE tblPCNames.* FROM tblPCNames " & _
"WHERE (((tblPCNames.PCID) = @PCID)));"
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@PCID", _
Row("PCID"))
cmd.ExecuteNonQuery()
Exit Select
Case DataRowState.Modified
cmd.CommandText = _
"UPDATE tblPCNames SET " & _
"tblPCNames.PCID = [PCID], " & _
"tblPCNames.PC_Name = [PC_Name] " & _
"WHERE (((tblPCNames.PCID)=[@PCID]));"
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@PCID", _
Row("PCID"))
cmd.Parameters.AddWithValue("@PC_Name", _
Row("PC_Name"))
cmd.ExecuteNonQuery()
Exit Select
End Select
Next Row
End If
Catch ex As Exception
Dim el As New ErrorLogger
el.WriteToErrorLog(ex.Message, ex.StackTrace, "Error")
End Try
cnn.Close()
End Using
End Sub
Public Sub UpdateChildTable(ByVal cnnAccess As String, _
ByVal dsAccess As DataSet)
Dim dt As New DataTable
dt = dsAccess.Tables("tblArchives")
Dim cmd As New OleDbCommand()
Using cnn As New OleDbConnection(cnnAccess)
Try
If cnn.State = ConnectionState.Closed Then
cnn.Open()
cmd.Connection = cnn
For Each Row As DataRow In dt.Rows
Select Case Row.RowState
Case DataRowState.Added
'Insert/Add Query
cmd.CommandText = _
"INSERT INTO tblArchives ( PCID, FileID, " & _
"Path, Item, Date_Created, " & _
"Last_Accessed ) VALUES(@PCID, @FileID, " & _
"@Path, @Item, @Date_Created, " & _
"@Last_Accessed)"
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@PCID", _
Row("PCID"))
cmd.Parameters.AddWithValue("@FileID", _
Row("FileID"))
cmd.Parameters.AddWithValue("@Path", _
Row("Path"))
cmd.Parameters.AddWithValue("@Item", _
Row("Item"))
cmd.Parameters.AddWithValue("@Date_Created", _
Row("Date_Created"))
cmd.Parameters.AddWithValue("@Last_Accessed", _
Row("Last_Accessed"))
cmd.ExecuteNonQuery()
Exit Select
Case DataRowState.Deleted
'Delete Query
cmd.CommandText = _
"DELETE tblArchives.* FROM tblArchives " & _
"WHERE (((tblArchives.PCID) = @PCID) " & _
"AND ((tblArchives.FileID) = @FileID ));"
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@PCID", _
Row("PCID"))
cmd.Parameters.AddWithValue("@FileID", _
Row("FileID"))
cmd.ExecuteNonQuery()
Exit Select
Case DataRowState.Modified
'Modified/Update Query
cmd.CommandText = _
"UPDATE tblArchives SET tblArchives.PCID = @PCID, " & _
"tblArchives.FileID = @FileID, " & _
"tblArchives.Path = @Path, " & _
"tblArchives.Item = @Item, " & _
"tblArchives.Date_Created = @Date_Created, " & _
"tblArchives.Last_Accessed = @Last_Accessed;"
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@PCID", _
Row("PCID"))
cmd.Parameters.AddWithValue("@FileID", _
Row("FileID"))
cmd.Parameters.AddWithValue("@Path", _
Row("Path"))
cmd.Parameters.AddWithValue("@Item", _
Row("Item"))
cmd.Parameters.AddWithValue("@Date_Created", _
Row("Date_Created"))
cmd.Parameters.AddWithValue("@Last_Accessed", _
Row("Last_Accessed"))
cmd.ExecuteNonQuery()
Exit Select
End Select
Next Row
End If
Catch ex As Exception
'Log error
Dim el As New ErrorLogger
el.WriteToErrorLog(ex.Message, ex.StackTrace, "Error")
End Try
cnn.Close()
End Using
End Sub
When items are added the Row.RowState line of the UpdateChildTable procedure flags them as AddNew, but when I delete an item from the child's table. The data set no longer shows it. However, when I press the Save button to update the physical database I get the "DeletedRowInaccessibleException" error.
When I step through the code the RowState is 'Unchanged'. This I don't understand. If the DataSet flagged this row as deleted, why isn't the row state marked as deleted, so when I update the data source the code will see the state of the row and preform the proper queries?
As always critiques and suggestions are welcome,
Quecumber256
Submit an article or tip