Membership stored procedure aspnet_Membership_CreateUser not working

I have been trying to make the build in stored procedure to work with Register.aspx. I’m not using the wizard part. Hence writing my own code. I don’t understand where the error is. If I use exec stored procedure and send the same value it will execute in Management Studio. But in code it fails.

Here is the code that does the execution of the stored procedure:

using (SqlConnection myConnection = new SqlConnection  (ConfigurationManager.ConnectionStrings["connection"].ToString()))
{
    using (SqlCommand insertNewUsers = new SqlCommand("aspnet_Membership_CreateUser", myConnection))
    {
        insertNewUsers.CommandType = CommandType.StoredProcedure;

        insertNewUsers.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = AppName;
        insertNewUsers.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = UserName.Text;
        insertNewUsers.Parameters.Add("@Password ", SqlDbType.NVarChar, 128).Value = goHashPassword;
        insertNewUsers.Parameters.Add("@PasswordSalt", SqlDbType.NVarChar, 128).Value = newSalt;
        insertNewUsers.Parameters.Add("@Email", SqlDbType.NVarChar, 256).Value = Email.Text;
        //  insertNewUsers.Parameters.Add(("@LoweredEmail"), SqlDbType.NVarChar, 256).Value = (Email.Text).ToLower();
        insertNewUsers.Parameters.Add("@PasswordQuestion", SqlDbType.NVarChar, 256).Value = DBNull.Value ;
        insertNewUsers.Parameters.Add("@PasswordAnswer", SqlDbType.NVarChar, 128).Value = DBNull.Value;              
        insertNewUsers.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = 1;
        // insertNewUsers.Parameters.Add("@IsLockedOut", SqlDbType.NVarChar, 1).Value = 0;
        insertNewUsers.Parameters.Add("@CurrentTimeUtc", SqlDbType.DateTime).Value = DateTime.UtcNow.Date;
        insertNewUsers.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = DateTime.Today.ToLocalTime();
        insertNewUsers.Parameters.Add("@UniqueEmail", SqlDbType.Int).Value = 0;
        insertNewUsers.Parameters.Add("@PasswordFormat", SqlDbType.Int).Value = 0;

        pID = insertNewUsers.Parameters.Add("@return_value", SqlDbType.Int);
        pID.Direction = ParameterDirection.ReturnValue;

        try
        {
            if (insertNewUsers.Connection.State == ConnectionState.Closed)
            {
                insertNewUsers.Connection.Open();
            }

            insertNewUsers.ExecuteNonQuery();  // HERE IS WHERE IT FAILS

            rowCount = (Int32)insertNewUsers.Parameters["@return_value"].Value;
         } // END TRY
         catch (Exception ex)
         {
             ErrorMessage.Text = ex.StackTrace.ToString();
         }
         finally
         {
             myConnection.Dispose();
         }
     }
}

Now if I run build in stored procedure with following parameters it runs and adds a user and creates a membership

DECLARE @return_value int,
        @UserId uniqueidentifier

 EXEC @return_value = [dbo].[aspnet_Membership_CreateUser]
        @ApplicationName = N'/',
        @UserName = N'Tester',
        @Password = N'cUW3NXKEEjiNkxOjhLXwhHFQApQ=',
        @PasswordSalt = N'GYkMYFzkDn0vm4Li',
        @Email = N'[e-mail]',
        @PasswordQuestion = NULL,
        @PasswordAnswer = NULL,
        @IsApproved = 1,
        @CurrentTimeUtc =  N'20131027',
        @CreateDate =   N'20131027' ,
        @UniqueEmail = 0,
        @PasswordFormat = 0,
        @UserId = @UserId OUTPUT

  SELECT @UserId as N'@UserId'

  SELECT 'Return Value' = @return_value
  GO

I have checked datatype with the table and is fine. I’m not sure about the datetime stamp. on manual execution, i just have to give the ‘yyyymmdd’ like i have specified but in c# i have specified to be as seen in code. Hope to find some help on this

Answer

~Technically, you’re missing a parameter in the DotNet code. (outputUserIdParam seen below)

~Practically, you should use the MembershipProvider API. You are re-inventing the wheel with your implementation.

            insertNewUsers.CommandType = CommandType.StoredProcedure;

            insertNewUsers.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = AppName;
            insertNewUsers.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = UserName;
            insertNewUsers.Parameters.Add("@Password ", SqlDbType.NVarChar, 128).Value = pwd;
            insertNewUsers.Parameters.Add("@PasswordSalt", SqlDbType.NVarChar, 128).Value = string.Empty;
            insertNewUsers.Parameters.Add("@Email", SqlDbType.NVarChar, 256).Value = Email;
            //  insertNewUsers.Parameters.Add(("@LoweredEmail"), SqlDbType.NVarChar, 256).Value = (Email.Text).ToLower();
            insertNewUsers.Parameters.Add("@PasswordQuestion", SqlDbType.NVarChar, 256).Value = DBNull.Value;
            insertNewUsers.Parameters.Add("@PasswordAnswer", SqlDbType.NVarChar, 128).Value = DBNull.Value;
            insertNewUsers.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = 1;
            // insertNewUsers.Parameters.Add("@IsLockedOut", SqlDbType.NVarChar, 1).Value = 0;
            insertNewUsers.Parameters.Add("@CurrentTimeUtc", SqlDbType.DateTime).Value = DateTime.UtcNow.Date;
            insertNewUsers.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = DateTime.Today.ToLocalTime();
            insertNewUsers.Parameters.Add("@UniqueEmail", SqlDbType.Int).Value = 0;
            insertNewUsers.Parameters.Add("@PasswordFormat", SqlDbType.Int).Value = 0;

            // Create parameter with Direction as Output (and correct name and type)
            SqlParameter outputUserIdParam = new SqlParameter("@UserId", SqlDbType.UniqueIdentifier)
            {
                Direction = ParameterDirection.Output
            };

            insertNewUsers.Parameters.Add(outputUserIdParam);


            pID = insertNewUsers.Parameters.Add("@return_value", SqlDbType.Int);
            pID.Direction = ParameterDirection.ReturnValue;