更新数据库时DeleteRowInaccessibleException [英] DeleteRowInaccessibleException when updating the database
问题描述
也许有人可以帮我弄清楚当我从内存数据集中删除一行然后尝试更新物理数据源时出现上述错误的原因。
我有一个简单的Windows应用程序，允许用户从TreeView控件中选择项目并将所选项目的数据存储到Microsoft Access 2010数据库。
I有两个子程序来更新数据库。一个用于父表，另一个用于子表。
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
'Insert/Add Query
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
'Delete Query
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
'Modified/Update Query
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
'Log error
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
添加项目时，UpdateChildTable过程的Row.RowState行将它们标记为AddNew，但是当我从子表中删除一个项目时。数据集不再显示它。但是，当我按下保存按钮更新物理数据库时，我收到DeletedRowInaccessibleException错误。
当我单步执行代码时，RowState是'Unchanged'。这个我不明白。如果DataSet将此行标记为已删除，为什么行状态不标记为已删除，因此当我更新数据源时，代码将看到行的状态并执行正确的查询？
一如既往欢迎批评和建议，
Quecumber256
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
推荐答案
我的开发人员熟人; GeekGoddess向我指出，如果从DataSet中删除行，则需要将DataRowVersion.Original添加到参数声明中。
示例：
A developer acquaintance of mine; GeekGoddess pointed out to me that if you delete rows from the DataSet you need to add the DataRowVersion.Original to your parameters declaration.
Example:
cmd.CommandText = _
"DELETE tblArchives.* FROM tblArchives " & _
"WHERE tblArchives.PCID = @PCID;"
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@PCID", _
Row("PCID", _
DataRowVersion.Original))
cmd.ExecuteNonQuery()
由于某些奇怪的原因，这个重要的细节在某种程度上不见了。
MRM256
For some strange reason this important detail is somehow missing.
MRM256
这篇关于更新数据库时DeleteRowInaccessibleException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
