Deleting rows from many tables

What I'm trying to do is delete a user and all their related information within the other tables. I'm not wanting to delete the table, just one column with that user and their related information. So my Primary_Key is UserID within the table [alumni] and my three Foreign_Keys are CommentID, PhotoID, and AlbumID within the tables [comments], [photos], and [albums]. Here is some of the code that I have:

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:SoderquistString %>"DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @.UserID"SelectCommand="SELECT [UserID], [UserName], [FirstName], [LastName], [State] FROM [alumni] WHERE ([State] = @.State)"><DeleteParameters><asp:ParameterName="UserID"Type="Int32"/></DeleteParameters><SelectParameters><asp:ControlParameterControlID="DropDownList1"Name="state"PropertyName="SelectedValue"Type="String"/></SelectParameters></asp:SqlDataSource>

The users are set up in GridView form. Is there some type of DELETE command that I need to be writing that is different than the one above? I have tried adding onto the following DELETE statment:

DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @.UserID

DELETE FROM [photo] WHERE [UserID] = @.UserID;

DELETE FROM [album] WHERE [UserID] = @.UserID;

DELETE FROM [comment] WHERE [UserID] = @.UserID;

...but that doesn't work...and doesn't look right. I would really appreciate anyones suggestions or help that you may be able to provide. Thank you!

Try this. I tested for two tables

DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @.UserID;DELETE FROM [photo] WHERE [UserID] = @.UserID;DELETE FROM [album] WHERE [UserID] = @.UserID;DELETE FROM [comment] WHERE [UserID] = @.UserID"

|||

Thanks for the info. I've tried that already and I still get an error:

Invalid column name 'UserID'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Invalid column name 'UserID'.

Any suggestions?

|||

Do you have UserID column for all tables you are going to delete?

Since I tested the syntax for only two tables, I will test for more tables to confirm whether the syntax works for multiple tables.

|||

This DeleteCommand works on the dummy test tables in SQL Server 2005:

DeleteCommand="DELETE FROM [T1] WHERE [ID1] = @.ID;DELETE FROM [T2] WHERE [ID1] = @.ID;DELETE FROM [T3] WHERE [ID1] = @.ID;DELETE FROM [T4] WHERE [ID1] = @.ID;DELETE FROM [T5] WHERE [ID1] = @.ID"<DeleteParameters><asp:ParameterName="ID"Type="Int32"/></DeleteParameters>

But if you click delete rows too fast, you may get error message for the parameter. You should be able to delete from a stored procedure with error checking from there.

|||

Limno, thanks and I appreciate the help you have given me. I was looking over my tables and noticed that there is a difference in one of them. I'm going to write out the four tables and a few columns that I have within them and hopefully this will help clear a few things up that I'm having trouble with:

alumni - UserID (PK), FirstName, LastName, UserName

photo - PhotoID (FK), AlbumID, PhotoPath

album - AlbumID (FK), UserID, PhotoPath, AlbumName

comments - CommentID (FK), UserID, Comment, UserName, CommentDate

What I have is one primary_key, which is UserID and the other three are foreign_keys (PhotoID, AlbumID, and CommentID). All of the foreign keys have UserID in them except for the photo table because it associates with the album table that has UserID in it. That was the one difference that I forgot about.

So is it possible, with the way I have this set up for me to be able to delete a user from the alumni table along with the other information in the other tables that are associated with that user? I can delete a user if there are no albums, photos or comments connected with that person, but it's when they have something connected to them a problem comes up because of certain constraints.

Thank you in advanced!

|||

Hello:

I tested with the following DELETECommand:

DeleteCommand="DELETE FROM [photo] WHERE ([phtoID] IN (SELECT photoID FROM album WHERE userID = @.userID)); DELETE FROM [comments] WHERE [userID] = @.userID;DELETE FROM [alumni] WHERE [userID] = @.userID;DELETE FROM [album] WHERE [userID] = @.userID"

It seems that the cascading delete through a trigger would be a perfect fit for your case. However, I rearrange the delete action sequence for your case in this DeleteCommand. I tested with success. But you need to test on your data to make sure it works properly. If you want to control the flow, you may need to go with the trigger and/or stored procedure to handle your multiple cascading deletion.

|||

It worked! Thank you for your time, I really appreciate it. It looks like my problem was with the way I was deleting the information from the photo table and how it was related to the albums. This peace of code here...

DELETE FROM [photo] WHERE ([phtoID] IN (SELECT photoID FROM album WHERE userID = @.userID));

...did the trick.

Just for my understanding, what did you mean by "If you want to control the flow, you may need to go with the trigger and/or stored procedure to handle your multiple cascading deletion"? Thanks again!

|||

The code works but you have no way to know if something wrong with it during deletion. I did run into a few times during my test. That is what I mean. If the project becomes critical, you can go with the trigger and stored procedure. Or You can use your own error catch logic from code behind. Just my thought for your situation. I want to know what will happen if there is an error for learning purpose.

I am glad this hack works for you now. If you are interested, you can search for the topic on cascading deleting. The constrains on the foreign keys are very usful to maintain the integrity of your data set.