Delete from 3 tables
Hi,
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:
<b>
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)
CMD.Parameters.Add("@cust_ID", id)
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.
<b>
The tables are as follows:
Customers: Orders: OrderDetails
customerid customerid orderid
orderid
</b>
I tried the following delete:
<b>
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)
objCMD.Parameters.Add("@CUST_id", id)
objCMD.Parameters.Add("@orderID", LBLORDER.Text)
oConn.Open()
objCMD.ExecuteNonQuery()
oConn.Close()
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
|
