Guru Guru - 1 month ago 18
ASP.NET (C#) Question

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'Tester@gmail.com',
@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.

            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;
Comments