Solved

aspnet stored proc modification

Posted on 2008-10-08
1
248 Views
Last Modified: 2012-05-05
Hello Experts,

I am currently attempting (with no success) to change the dbo.aspnet_Membership_CreateUser Stored Procedure..

What I am Trying to acheive is; When a new user gets added through my web app the FilterID (non primary key but is uniqueidentifier) field on my customer and staff tables get updated as well, but the only thing these tables have in common is the e-mail as I know who will be using the app..

The problem is I cannot get the stored proc to work and I am not familiar with SQL..

Please help

Cheers Trent

and sorry about the User name for EE as my collegue loged in and made it that way and I cannot change it..
/****** Object:  StoredProcedure [dbo].[aspnet_Membership_CreateUser]    Script Date: 10/09/2008 00:23:05 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

ALTER PROCEDURE [dbo].[aspnet_Membership_CreateUser]

    @ApplicationName                        nvarchar(256),

    @UserName                               nvarchar(256),

    @Password                               nvarchar(128),

    @PasswordSalt                           nvarchar(128),

    @Email                                  nvarchar(256),

    @PasswordQuestion                       nvarchar(256),

    @PasswordAnswer                         nvarchar(128),

    @IsApproved                             bit,

    @CurrentTimeUtc                         datetime,

    @CreateDate                             datetime = NULL,

    @UniqueEmail                            int      = 0,

    @PasswordFormat                         int      = 0,

    @UserId                                 uniqueidentifier OUTPUT

	@FilterID								uniqueidentifier

	@Email_Work								nvarchar(200)

	@Email_Business							nvarchar(200)

AS

BEGIN

    DECLARE @ApplicationId uniqueidentifier

    SELECT  @ApplicationId = NULL

 

    DECLARE @NewUserId uniqueidentifier

    SELECT @NewUserId = NULL

 

    DECLARE @IsLockedOut bit

    SET @IsLockedOut = 0

 

    DECLARE @LastLockoutDate  datetime

    SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )

 

    DECLARE @FailedPasswordAttemptCount int

    SET @FailedPasswordAttemptCount = 0

 

    DECLARE @FailedPasswordAttemptWindowStart  datetime

    SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

 

    DECLARE @FailedPasswordAnswerAttemptCount int

    SET @FailedPasswordAnswerAttemptCount = 0

 

    DECLARE @FailedPasswordAnswerAttemptWindowStart  datetime

    SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

	

 

    DECLARE @NewUserCreated bit

    DECLARE @ReturnValue   int

    SET @ReturnValue = 0

 

    DECLARE @ErrorCode     int

    SET @ErrorCode = 0

 

    DECLARE @TranStarted   bit

    SET @TranStarted = 0

 

    IF( @@TRANCOUNT = 0 )

    BEGIN

	    BEGIN TRANSACTION

	    SET @TranStarted = 1

    END

    ELSE

    	SET @TranStarted = 0

 

    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

 

    IF( @@ERROR <> 0 )

    BEGIN

        SET @ErrorCode = -1

        GOTO Cleanup

    END

 

    SET @CreateDate = @CurrentTimeUtc

 

    SELECT  @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId

    IF ( @NewUserId IS NULL )

    BEGIN

        SET @NewUserId = @UserId

        EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT

        SET @NewUserCreated = 1

    END

    ELSE

    BEGIN

        SET @NewUserCreated = 0

        IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )

        BEGIN

            SET @ErrorCode = 6

            GOTO Cleanup

        END

    END

 

    IF( @@ERROR <> 0 )

    BEGIN

        SET @ErrorCode = -1

        GOTO Cleanup

    END

 

    IF( @ReturnValue = -1 )

    BEGIN

        SET @ErrorCode = 10

        GOTO Cleanup

    END

 

    IF ( EXISTS ( SELECT UserId

                  FROM   dbo.aspnet_Membership

                  WHERE  @NewUserId = UserId ) )

    BEGIN

        SET @ErrorCode = 6

        GOTO Cleanup

    END

 

    SET @UserId = @NewUserId

 

    IF (@UniqueEmail = 1)

    BEGIN

        IF (EXISTS (SELECT *

                    FROM  dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK )

                    WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))

        BEGIN

            SET @ErrorCode = 7

            GOTO Cleanup

        END

    END

 

    IF (@NewUserCreated = 0)

    BEGIN

        UPDATE dbo.aspnet_Users

        SET    LastActivityDate = @CreateDate

        WHERE  @UserId = UserId

        IF( @@ERROR <> 0 )

        BEGIN

            SET @ErrorCode = -1

            GOTO Cleanup

        END

    END

 

 

    INSERT INTO dbo.aspnet_Membership

                ( ApplicationId,

                  UserId,

                  Password,

                  PasswordSalt,

                  Email,

                  LoweredEmail,

                  PasswordQuestion,

                  PasswordAnswer,

                  PasswordFormat,

                  IsApproved,

                  IsLockedOut,

                  CreateDate,

                  LastLoginDate,

                  LastPasswordChangedDate,

                  LastLockoutDate,

                  FailedPasswordAttemptCount,

                  FailedPasswordAttemptWindowStart,

                  FailedPasswordAnswerAttemptCount,

                  FailedPasswordAnswerAttemptWindowStart )

         VALUES ( @ApplicationId,

                  @UserId,

                  @Password,

                  @PasswordSalt,

                  @Email,

                  LOWER(@Email),

                  @PasswordQuestion,

                  @PasswordAnswer,

                  @PasswordFormat,

                  @IsApproved,

                  @IsLockedOut,

                  @CreateDate,

                  @CreateDate,

                  @CreateDate,

                  @LastLockoutDate,

                  @FailedPasswordAttemptCount,

                  @FailedPasswordAttemptWindowStart,

                  @FailedPasswordAnswerAttemptCount,

                  @FailedPasswordAnswerAttemptWindowStart )

 

 

 

    IF( @@ERROR <> 0 )

    BEGIN

        SET @ErrorCode = -1

        GOTO Cleanup

    END

 

    IF( @TranStarted = 1 )

    BEGIN

	    SET @TranStarted = 0

	    COMMIT TRANSACTION

    END

 

    RETURN 0

 

Cleanup:

 

    IF( @TranStarted = 1 )

    BEGIN

        SET @TranStarted = 0

    	ROLLBACK TRANSACTION

    END

 

    RETURN @ErrorCode

 

END

 

DECLARE @Email_Work NVARCHAR(200)

DECLARE @Email_Business NVARCHAR(200)

DECLARE @FilterID uniqueidentifier

DECLARE @NewFilterID uniqueidentifier

 

    SET @FilterID = @NewFilterID

 

BEGIN

		UPDATE CMA_projects.customers_data_0_details

				 FilterID,

				 Email_Work

		VALUES  @FilterID,

				@Email_Work

		WHERE @Email_Work =	Email

IF( @@ERROR <> 0 )

        BEGIN

            SET @ErrorCode = -1

            GOTO Cleanup

        END

END

 

    SET @FilterID = @NewFilterID

 

BEGIN

	UPDATE CMA_projects.staff_data_0_staff_list

				 FilterID,

				 Email_Business

		VALUES  @FilterID,

				@Email_Business

		WHERE @Email_Business =	Email	

 

IF( @@ERROR <> 0 )

        BEGIN

            SET @ErrorCode = -1

            GOTO Cleanup

        END

END

Open in new window

0
Comment
Question by:Small_Balls
1 Comment
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
Comment Utility
I'll make the change right after the INSERT statement.  You'll need to modify it to your particular DB name and field names.

Be sure you keep any modifications to the procedures in a separate script somewhere as updates to the aspnet Membership system could overwrite them.

/****** Object:  StoredProcedure [dbo].[aspnet_Membership_CreateUser]    Script Date: 10/09/2008 00:23:05 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

ALTER PROCEDURE [dbo].[aspnet_Membership_CreateUser]

    @ApplicationName                        nvarchar(256),

    @UserName                               nvarchar(256),

    @Password                               nvarchar(128),

    @PasswordSalt                           nvarchar(128),

    @Email                                  nvarchar(256),

    @PasswordQuestion                       nvarchar(256),

    @PasswordAnswer                         nvarchar(128),

    @IsApproved                             bit,

    @CurrentTimeUtc                         datetime,

    @CreateDate                             datetime = NULL,

    @UniqueEmail                            int      = 0,

    @PasswordFormat                         int      = 0,

    @UserId                                 uniqueidentifier OUTPUT

        @FilterID                                                               uniqueidentifier

        @Email_Work                                                             nvarchar(200)

        @Email_Business                                                 nvarchar(200)

AS

BEGIN

    DECLARE @ApplicationId uniqueidentifier

    SELECT  @ApplicationId = NULL

 

    DECLARE @NewUserId uniqueidentifier

    SELECT @NewUserId = NULL

 

    DECLARE @IsLockedOut bit

    SET @IsLockedOut = 0

 

    DECLARE @LastLockoutDate  datetime

    SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )

 

    DECLARE @FailedPasswordAttemptCount int

    SET @FailedPasswordAttemptCount = 0

 

    DECLARE @FailedPasswordAttemptWindowStart  datetime

    SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

 

    DECLARE @FailedPasswordAnswerAttemptCount int

    SET @FailedPasswordAnswerAttemptCount = 0

 

    DECLARE @FailedPasswordAnswerAttemptWindowStart  datetime

    SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

        

 

    DECLARE @NewUserCreated bit

    DECLARE @ReturnValue   int

    SET @ReturnValue = 0

 

    DECLARE @ErrorCode     int

    SET @ErrorCode = 0

 

    DECLARE @TranStarted   bit

    SET @TranStarted = 0

 

    IF( @@TRANCOUNT = 0 )

    BEGIN

            BEGIN TRANSACTION

            SET @TranStarted = 1

    END

    ELSE

        SET @TranStarted = 0

 

    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

 

    IF( @@ERROR <> 0 )

    BEGIN

        SET @ErrorCode = -1

        GOTO Cleanup

    END

 

    SET @CreateDate = @CurrentTimeUtc

 

    SELECT  @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId

    IF ( @NewUserId IS NULL )

    BEGIN

        SET @NewUserId = @UserId

        EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT

        SET @NewUserCreated = 1

    END

    ELSE

    BEGIN

        SET @NewUserCreated = 0

        IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )

        BEGIN

            SET @ErrorCode = 6

            GOTO Cleanup

        END

    END

 

    IF( @@ERROR <> 0 )

    BEGIN

        SET @ErrorCode = -1

        GOTO Cleanup

    END

 

    IF( @ReturnValue = -1 )

    BEGIN

        SET @ErrorCode = 10

        GOTO Cleanup

    END

 

    IF ( EXISTS ( SELECT UserId

                  FROM   dbo.aspnet_Membership

                  WHERE  @NewUserId = UserId ) )

    BEGIN

        SET @ErrorCode = 6

        GOTO Cleanup

    END

 

    SET @UserId = @NewUserId

 

    IF (@UniqueEmail = 1)

    BEGIN

        IF (EXISTS (SELECT *

                    FROM  dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK )

                    WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))

        BEGIN

            SET @ErrorCode = 7

            GOTO Cleanup

        END

    END

 

    IF (@NewUserCreated = 0)

    BEGIN

        UPDATE dbo.aspnet_Users

        SET    LastActivityDate = @CreateDate

        WHERE  @UserId = UserId

        IF( @@ERROR <> 0 )

        BEGIN

            SET @ErrorCode = -1

            GOTO Cleanup

        END

    END

 

 

    INSERT INTO dbo.aspnet_Membership

                ( ApplicationId,

                  UserId,

                  Password,

                  PasswordSalt,

                  Email,

                  LoweredEmail,

                  PasswordQuestion,

                  PasswordAnswer,

                  PasswordFormat,

                  IsApproved,

                  IsLockedOut,

                  CreateDate,

                  LastLoginDate,

                  LastPasswordChangedDate,

                  LastLockoutDate,

                  FailedPasswordAttemptCount,

                  FailedPasswordAttemptWindowStart,

                  FailedPasswordAnswerAttemptCount,

                  FailedPasswordAnswerAttemptWindowStart )

         VALUES ( @ApplicationId,

                  @UserId,

                  @Password,

                  @PasswordSalt,

                  @Email,

                  LOWER(@Email),

                  @PasswordQuestion,

                  @PasswordAnswer,

                  @PasswordFormat,

                  @IsApproved,

                  @IsLockedOut,

                  @CreateDate,

                  @CreateDate,

                  @CreateDate,

                  @LastLockoutDate,

                  @FailedPasswordAttemptCount,

                  @FailedPasswordAttemptWindowStart,

                  @FailedPasswordAnswerAttemptCount,

                  @FailedPasswordAnswerAttemptWindowStart )

 

 

 

    IF( @@ERROR <> 0 )

    BEGIN

        SET @ErrorCode = -1

        GOTO Cleanup

    END

 /*Begin DanielWilson modification */

    Update MyDB..Customer Set MyField = 'whatever Im trying to do'

    Where Email = @Email
 
 

    Update MyDB..Staff Set MyField = 'whatever Im trying to do'

    Where Email = @Email

 /*End DanielWilson modification */
 
 

    IF( @TranStarted = 1 )

    BEGIN

            SET @TranStarted = 0

            COMMIT TRANSACTION

    END

 

    RETURN 0

 

Cleanup:

 

    IF( @TranStarted = 1 )

    BEGIN

        SET @TranStarted = 0

        ROLLBACK TRANSACTION

    END

 

    RETURN @ErrorCode

 

END

 

DECLARE @Email_Work NVARCHAR(200)

DECLARE @Email_Business NVARCHAR(200)

DECLARE @FilterID uniqueidentifier

DECLARE @NewFilterID uniqueidentifier

 

    SET @FilterID = @NewFilterID

 

BEGIN

                UPDATE CMA_projects.customers_data_0_details

                                 FilterID,

                                 Email_Work

                VALUES  @FilterID,

                                @Email_Work

                WHERE @Email_Work =     Email

IF( @@ERROR <> 0 )

        BEGIN

            SET @ErrorCode = -1

            GOTO Cleanup

        END

END

 

    SET @FilterID = @NewFilterID

 

BEGIN

        UPDATE CMA_projects.staff_data_0_staff_list

                                 FilterID,

                                 Email_Business

                VALUES  @FilterID,

                                @Email_Business

                WHERE @Email_Business = Email   

 

IF( @@ERROR <> 0 )

        BEGIN

            SET @ErrorCode = -1

            GOTO Cleanup

        END

END

Open in new window

0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now