Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 24th, 2004, 11:28 AM
Authorized User
 
Join Date: Oct 2003
Location: , , .
Posts: 89
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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


 
Old March 24th, 2004, 11:49 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

In the table properties/design from Enterprse manager enable cascade delete. Otherwise you will have to delete the other way, i.e. delete the order details first fro that customer, then the orders for them and finally the customer.



--

Joe




Similar Threads
Thread Thread Starter Forum Replies Last Post
Unable to delete file System.IO.Delete error maricar C# 13 March 14th, 2014 06:50 AM
php/mysql delete button and delete query dungey PHP Databases 17 April 11th, 2009 12:24 PM
Could not delete from specified tables. (Er 3086) Romunda M. Harris-Fonvill Access 0 April 12th, 2006 11:31 AM
how to delete a row when click delete hyperlink naveenkumarg1 Pro JSP 1 August 16th, 2004 01:29 AM
Using Asp to Add/Delete Tables in a SQL database? morpheus Classic ASP Databases 2 April 23rd, 2004 07:59 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.