How to delete from more than one table

Joma Rajab

10y968

I need to delete from multiple tables which are linked to each other and the delete is based on a selected value from a drop-down list and this drop-down list contains id of the first table

the query below works fine for deleting from Material table where there is no row for that material id in other tables. However, when id is in other tables I could not delete

SqlCommand cmd = new SqlCommand("Delete From dbo.Material Where MaId='" + DropDownList5.SelectedItem.Value.ToString() + "'; Delete from Quiz where MaID = '" + DropDownList5.DataValueField + "';Delete from Lastviewed where MaID = '" + DropDownList5.DataValueField + "'; Delete from Comments where MaID = '" + DropDownList5.DataValueField + "'; ", conn);

please help

Thank you

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pratik Shirse10y ago
try now may it will be helpful....
create procedure SP_Delete
as
delete from Tbl_StandardMaster,
delete from Tbl_UserProfile7
return
0
Midhun Tp10y ago
Hi Joma,
You have to first perform delete operation for tables having foreign key. And then delete data from table having primary key. So change your order of deletion in above code.
+1
Bikesh Srivastava10y ago
you create procedure and write two delete query inside procedure..
0
Tapan Patel10y ago
If I am not wrong, you have converted the selected value to ToString in first query but rest of the queries doesn't have that conversion. Try changing that and let me know.
Also, show me the query cmd value after executing the above command, that would give better idea towards resolution.
+1
Joma Rajab10y ago
thanks but this creates aforeign key and second time I cannot deletebecause the foreignkey already exists as I need to delete continuously.
+1
Tapan Patel10y ago
Why don't you try cascade delete?Please visit below link.

http://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server

Please mark it as an answer if it addresses the issue.
+1