从两个访问表中删除行 [英] delete row from two access tables

问题描述

大家好，

我正在使用vb.net framework 4并访问2003 db.我想知道您能否协助或推荐实现以下目标的最佳方法:

在Access MDB数据库中，我有两个表TBLPerson和TBLHistory.
TBLPerson具有3个字段(personID(PK)，姓名，年龄)，TBLHistory具有4个字段(ID(PK)，PersonID，姓名，年龄).

我在vb.net中有1个表格，其中datagridview与TBLPerson绑定了源.我想要做的是，当用户在数据网格中选择一行并能够删除TBLPerson中的记录并删除与PERSONID字段相对应的TBLHistory中的所有记录.
到目前为止，我已经能够从TBLPerson中成功删除该人，但是不确定如何清除TBLhistory中的所有记录.

在这方面的任何帮助或协助都将是非常重要的.

谢谢

Hi All,

I am using vb.net framework 4 and access 2003 db. i was wondering you be able assist or recommend the best method to a achieve the following:

In the Access MDB database i have two tables TBLPerson & TBLHistory.
TBLPerson has 3 fields(personID(PK), Name, Age) and TBLHistory has 4 fields (ID(PK), PersonID, Name, Age).

i have 1 form in vb.net with datagridview with bound source to TBLPerson. What i want to be able to do is when the user selects a row in the datagrid and is able to delete the record in TBLPerson and delete all records in TBLHistory corrsponding to PERSONID Field.

So far i have been able to successfully delete the person from TBLPerson but bit unsure how to purge all records in TBLhistory.

Any help or assist in regards to this would be much appericated.

Thanks

推荐答案

尝试为

DELETE TableB.*, TableA.*
FROM TableA 
INNER JOIN TableB ON TableA.PID = TableB.PID
WHERE TableB.PID="123";

要使用ADO.NET删除行，可以使用以下代码

To delete the rows using ADO.NET the following code can be used

'At entry level say in Load event of Form set the DataRelation
    'DataRelation can alos be created in the DataSet designer
        Dim parentColumn As DataColumn = _
        DataSet1.Tables("TBLPerson").Columns("PersonID")
    Dim childColumn As DataColumn = DataSet1.Tables( _
        "TBLHistory").Columns("PersonID")

    ' Create a DataRelation between the tables
    Dim relPersonHistory As DataRelation
    relPersonHistory = New DataRelation( _
        "PersonHistory", parentColumn, childColumn)

    ' Add relation of tables to the DataSet
    DataSet1.Relations.Add(relPersonHistory)

    'To delete the rows
    For Each row As DataGridViewRow In dataGridView1.SelectedRows
        Dim personRow As DataRow = TryCast(row.DataBoundItem, DataRow)
        If personRow IsNot Nothing Then
            dim historyRows as DataRow() = personRow.GetChildRows(relPersonHistory)
          ' Delete child rows
          For i = 0 To historyRows.Length-1
             historyRows(i).Delete()
          Next i
          personRow.Delete()
        End If
    Next

这篇关于从两个访问表中删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！