Delete from 3 tables
I am working on IBUYSTORE's db. I have added a backoffice/admin to the site.
I have a page where I retrieve a customer's details based on the customerid such as the following:
Dim Ssql As String = "SELECT O.CUSTOMERID, C.CUSTOMERID, C.PASSWORD, C.FULLNAME, C.EMAILADDRESS, O.ORDERID, OD.ORDERID , OD.PRODUCTID FROM customers AS C , ORDERS AS O , ORDERDETAILS AS OD WHERE C.customerID=O.CUSTOMERID AND OD.ORDERID=OD.ORDERID AND C.customerID = @cust_ID"
CMD As New SqlCommand(Ssql, oConn)
I am also using a sqldatareader.</b>
I have a delete button. If the site manager clicks on that button i want to delete all that customer's details including all his orders from both orders and orderdetails tables.
I am not sure how to accomplish it.
The tables are as follows:
Customers: Orders: OrderDetails
customerid customerid orderid
I tried the following delete:
strDelete = "DELETE FROM CUSTOMERS where CustomerID=@CUST_id; DELETE FROM ORDERS WHERE CUSTOMERID=@CUST_id; DELETE FROM ORDERDETAILS WHERE ORDERID=@orderID "</b>
Dim objCMD As New SqlCommand(strDelete, oConn)
I get the following error:
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Orders_Customers'. The conflict occurred in database 'Store', table 'Orders', column 'CustomerID'. DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_OrderDetails_Orders'. The conflict occurred in database 'Store', table 'OrderDetails', column 'OrderID'. The statement has been terminated. The statement has been terminated