Deleting Data From DB

I have edited the aspnet_Users_CreateUser stored procedure so that the UserId that is created when a new user is created is copied to a UserId field in another table. However, when I use the website administration tool to delete users that have been created, it gives me an error saying the delete statement conflicted with the reference constraint. I then added the following code in the aspnet_Users_DeleteUser procedure...

IF((@.TablesToDeleteFrom & 16) <> 0AND

(

EXISTS(SELECT name FROMsysobjectsWHERE(name= N'vw_tt')AND(type ='V'))))BEGINDELETE FROMdbo.userclasssetWHERE@.UserId = UserIdSELECT@.ErrorCode = @.@.ERROR,

@.RowCount = @.@.ROWCOUNT

IF( @.ErrorCode <> 0 )GOTOCleanupIF(@.RowCount <> 0)SELECT@.NumTablesDeletedFrom = @.NumTablesDeletedFrom + 1 hdhd

END

This code was then added to the function at the end which deletes the data from the aspnet_Users table when everything else has been removed

(@.TablesToDeleteFrom & 16) <> 0

AND

Now when I delete a user in the website admin tool, it "deletes" (with no error) the user from the list but doesnt actually physically delete it from the database.

Any ideas?

Problem fixed. The following code seemed to do the trick. I just needed to place it in the right point in the code which was just before the deletion of the UserId information in the membership table. Sorry to waste peoples time!

DELETE FROM dbo.userclassset WHERE @.UserId = UserId

Delete records not matching Top25 in GroupBy

I have a table (tblA) that records the RecordID, UserID and
LastViewedDate (DateTime) of each record opened in tblB where RecordID
is the PK in tblB. I want to construct a query that groups all records
in tblA by RecordID, filters by UserID and keeps only the most recent
25 RecordIDs and deletes the rest.

This gets me a recordset of all RecordIDs filtered by UserID in tblA
but I can't figure out how to sort it by LastViewedDate DESC and to
eliminate those not in the Top25:

SELECT RecordID
FROM dbo.tblA
WHERE (UserID = 1234)
GROUP BY RecordID

Any help is appreciated!
lqYou'll need an extra IN clause to get a single ID out, but it's pretty
straightforward (don't forget the NOT!):

delete Record
whererecordID NOT in
(
select recordID
from
(
SELECT top 25
RecordID
, max(LastViewedDate) as lastViewed
FROM dbo.tblA
WHERE (UserID = 1234)
GROUP BY RecordID
order by lastViewed desc
) as whaa
)

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/|||You'll need an extra IN clause to get a single ID out, but it's pretty
straightforward (don't forget the NOT!):

delete Record
whererecordID NOT in
(
select recordID
from
(
SELECT top 25
RecordID
, max(LastViewedDate) as lastViewed
FROM dbo.tblA
WHERE (UserID = 1234)
GROUP BY RecordID
order by lastViewed desc
) as whaa
)

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/|||You'll need an extra IN clause to get a single ID out, but it's pretty
straightforward (don't forget the NOT!):

delete Record
whererecordID NOT in
(
select recordID
from
(
SELECT top 25
RecordID
, max(LastViewedDate) as lastViewed
FROM dbo.tblA
WHERE (UserID = 1234)
GROUP BY RecordID
order by lastViewed desc
) as whaa
)

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/|||Jason,
Thanks for that solution.
I have to add a small fix to it though for anyone stumbling across this
who might require a similar solution.

delete dbo.tblA
where recordID NOT in
(
select recordID
from
(
SELECT top 25
RecordID
, max(LastViewedDate) as lastViewed
FROM dbo.tblA
WHERE (UserID = 1234)
GROUP BY RecordID
order by lastViewed desc
) as whaa
)
AND (UserID = 1234)

Without the "AND (UserID = 1234)" all records regardless of user are
deleted.

Also, users may have to play with the PK of tblA instead of refering to
RecordID which is the PK of tblB, as I had to do to get the results I
wanted:

delete dbo.tblA
where ViewedID NOT in
(
select MaxViewedID
from
(
SELECT top 25
RecordID
, max(LastViewedDate) as lastViewed,
max(ViewedID) as MaxViewedID
FROM dbo.tblA
WHERE (UserID = 1234)
GROUP BY RecordID
order by lastViewed desc
) as whaa
)
AND (UserID = 1234)