How to delete from more than one table

Question

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

Tapan Patel · Answer

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.

Tapan Patel · Answer

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.

Midhun T P · Answer

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.

Joma Rajab · Answer

thanks but this creates aforeign key and second time I cannot deletebecause the foreignkey already exists as I need to delete continuously.

Pratik Shirse · Answer

try now may it will be helpful.... create procedure SP_Delete as delete from Tbl_StandardMaster delete from Tbl_UserProfile7 return

Bikesh Srivastava · Answer

you create procedure and write two delete query inside procedure..