Using AccessDataSource and ListView when I hit
del in browser following error is shown
: 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)) "
<asp:Parameter Name="pkeyCustomerID" Type="String" /> from
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.