用asp.net2.0做项目,发现membership的用户过段时间就离线了,而我一直在操作啊,郁闷,发现数据表中的最后活动时间字段没有更 新,最后google了一下,发现这是一个bug,看看存储过程,的确啊,@UserID变量没有值啊?!修正了一下,果然工作正常。这个问题到目前都没 有官方的解决。
相关网址:
http://forums.asp.net/thread/1299317.aspx
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=430091&SiteID=1
以下是修正后的存储过程:
CREATE
PROCEDURE
dbo.aspnet_Membership_GetUserByName
@ApplicationName nvarchar ( 256 ),
@UserName nvarchar ( 256 ),
@CurrentTimeUtc datetime ,
@UpdateLastActivity bit = 0
AS
BEGIN
DECLARE @UserId uniqueidentifier
IF ( @UpdateLastActivity = 1 )
BEGIN
-- Dadi, 11.08.2006
-- Am adaugat eu cda 'select' de mai jos. Fara ea nu se poate executa cda 'update' care actualizeaza LastActivityTime.
-- Pb. provine din faptul ca variabila @UserId nu era niciunde initializata
SELECT TOP 1 @userId = u.UserId
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER ( @ApplicationName ) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
LOWER ( @UserName ) = u.LoweredUserName AND u.UserId = m.UserId
-- -----------
SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate, m.LastLoginDate, @CurrentTimeUtc , m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,m.LastLockoutDate
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER ( @ApplicationName ) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
LOWER ( @UserName ) = u.LoweredUserName AND u.UserId = m.UserId
IF ( @@ROWCOUNT = 0 ) -- Username not found
RETURN - 1
UPDATE dbo.aspnet_Users
SET LastActivityDate = @CurrentTimeUtc
WHERE @UserId = UserId
END
ELSE
BEGIN
SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,m.LastLockoutDate
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER ( @ApplicationName ) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
LOWER ( @UserName ) = u.LoweredUserName AND u.UserId = m.UserId
IF ( @@ROWCOUNT = 0 ) -- Username not found
RETURN - 1
END
RETURN 0
END
GO
@ApplicationName nvarchar ( 256 ),
@UserName nvarchar ( 256 ),
@CurrentTimeUtc datetime ,
@UpdateLastActivity bit = 0
AS
BEGIN
DECLARE @UserId uniqueidentifier
IF ( @UpdateLastActivity = 1 )
BEGIN
-- Dadi, 11.08.2006
-- Am adaugat eu cda 'select' de mai jos. Fara ea nu se poate executa cda 'update' care actualizeaza LastActivityTime.
-- Pb. provine din faptul ca variabila @UserId nu era niciunde initializata
SELECT TOP 1 @userId = u.UserId
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER ( @ApplicationName ) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
LOWER ( @UserName ) = u.LoweredUserName AND u.UserId = m.UserId
-- -----------
SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate, m.LastLoginDate, @CurrentTimeUtc , m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,m.LastLockoutDate
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER ( @ApplicationName ) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
LOWER ( @UserName ) = u.LoweredUserName AND u.UserId = m.UserId
IF ( @@ROWCOUNT = 0 ) -- Username not found
RETURN - 1
UPDATE dbo.aspnet_Users
SET LastActivityDate = @CurrentTimeUtc
WHERE @UserId = UserId
END
ELSE
BEGIN
SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,m.LastLockoutDate
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER ( @ApplicationName ) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
LOWER ( @UserName ) = u.LoweredUserName AND u.UserId = m.UserId
IF ( @@ROWCOUNT = 0 ) -- Username not found
RETURN - 1
END
RETURN 0
END
GO