FAHNZ

MODE

Jan 7 2011

Transact SQL LastIndexOf Function

I can’t help but think there may be a more efficient way to do this, but I’m posting here anyway. I welcome updates to make this better…

CREATE FUNCTION LastIndexOf
(
@substring nvarchar(max),
@stringToSearch nvarchar(max)
)
RETURNS int
AS
BEGIN

RETURN
LEN(@stringToSearch)
- LEN(@substring)
+ 2
- CHARINDEX(REVERSE(@substring), REVERSE(@stringToSearch))

END
GO

I still can’t believe I didn’t find much on this (beyond a single character search) on Google. Go figure.


Jul 6 2009

ASP.NET Membership Woes

The problem:

I kept on getting foreign key constraint errors when trying to use ASP.NET (2.0) DeleteUser method:

Membership.DeleteUser(<username>);

All my research on the internet pointed to it being a permissions problem. Like many of the posts I found, this worked fine in my local development database, but in production it would yield this error:

The DELETE statement conflicted with the REFERENCE  constraint
"FK__aspnet_Me__UserI__15502E78". The conflict occurred in  database
"dbname", table "dbo.aspnet_Membership", column 'UserId'.
The  statement has been terminated.

After hours of experimentation with user permissions in the database and research on the problem, the support team at the hosting company I was working with found the solution of setting the references foreign key constraints to cascade upon deletion. I went into SQL Server Management Studio and updated the FKs that were referenced in the errors to cascade upon deletion and voila! Problem solved. Good job CrystalTech support and especially Geoff.

Update: I looked into my local database (that has been working properly) and saw that the foreign keys are not set to cascade deletions, so it still may be some strange permissions issue. Regardless, this seems like an acceptable workaround and it did get those deletions to work (and the usernames freed up).