Link to home
Create AccountLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

ASP.net TableAdapters - can you have multiple SQL statements in one command

Hi

I am new to ASP.net and built a new command in a table adapter to delete a product from the Products table in the Northwind database. The only problem was that products with that Product_ID
cannot be deleted because of table relationships with Orders and then Order Details tables. An expert was kind enough to give me the SQL code to do this below. I copied it into the SQL area of a new command but it didn't work. Can you have multiple SQL statements in a new command in a table adapter or should they be in three separate commands. Also, if my table dapter relates specifically to the Products table is this a problem?


DELETE FROM Orders
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
WHERE ProductID = @Original_ProductID

DELETE FROM [Order Details]
WHERE ProductID = @Original_ProductID

DELETE FROM Products
WHERE        (ProductID = @Original_ProductID)

Open in new window

Avatar of Wouter Boevink
Wouter Boevink
Flag of Netherlands image

What didn't work, did you get an error?
Avatar of Murray Brown

ASKER

I get the error
There was an error: Incorrect syntax near the keyword 'INNER'.
Yeah got that one too, sorry about that:

The correct syntax is:

DELETE Orders FROM Orders
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
WHERE ProductID = @Original_ProductID

DELETE FROM [Order Details]
WHERE ProductID = @Original_ProductID

DELETE FROM Products
WHERE        (ProductID = @Original_ProductID)

But there's another problem with the database design, apparently you can't delete an order when it still has 'order details' and you can't delete an 'Order Detail' when it still has an Order.

I'll get back to you with a decent solution
ok thanks. I look forward to it
ASKER CERTIFIED SOLUTION
Avatar of Wouter Boevink
Wouter Boevink
Flag of Netherlands image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks very much for the extensive help