当前位置: 动力学知识库 > 问答 > 编程问答 >

asp.net - Appropriate AccessDataSource Delete command or tweaking?

问题描述:

Using AccessDataSource and ListView when I hit del in browser following error is shown

OleDbException (0x80004005)

: The record cannot be deleted or changed because table 'tblOrders' includes related records.

I am trying to solve the problem through a couple of ways which are as follows

DeleteCommand="DELETE FROM [tblCustomers] , [tblOrders] WHERE [pkeyCustomerID] = ? "

The Default command generated by ASP is

DeleteCommand="DELETE FROM [tblCustomers] WHERE (([pkeyCustomerID] = ?) OR ([pkeyCustomerID] IS NULL AND ? IS NULL)) "

Or

I removed <asp:Parameter Name="pkeyCustomerID" Type="String" /> from <DeleteParameters>

and replace it with the parameters of selected table

so the foreign key issue isnt affected

tag now there are no errors but the record isnt deleted either

How do i get around this?

网友答案:

Your first query:

DELETE FROM [tblCustomers] , [tblOrders] WHERE [pkeyCustomerID] = ? 

Is not valid syntax, you cannot delete from two tables simulatenously, you would need something like

DELETE FROM [tblOrders] WHERE [pkeyCustomerID] = ?;
DELETE FROM [tblCustomers] WHERE [pkeyCustomerID] = ?;

However I don't think Access supports multiple statements. The best solution would be to edit this relationship to use the cascade delete referential action trigger.

To do this go into the relationships in Access:

Then double click on the relationship between tblOrders and tblCustomers which will bring up the properties. Then either uncheck "Enforce Referential Integrity", or ensure that the cascade referential action options are checked (ignore the field names, it was the first relation I came across in a test database I have):

This will ensure that when you delete a customer, you delete all related orders too.

分享给朋友:
您可能感兴趣的文章:
随机阅读: