更新数据库时DeleteRowInaccessibleExcep

更新数据库时DeleteRowInaccessibleExcep

本文介绍了更新数据库时DeleteRowInaccessibleException的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

也许有人可以帮我弄清楚当我从内存数据集中删除一行然后尝试更新物理数据源时出现上述错误的原因。


我有一个简单的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

推荐答案

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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-28 03:01