Thursday, February 9, 2012

ASP.NET / SQL 2k Membership prob

Almost have my custom membershipprovider finsihed, but have ran into a small snag.

Scenario:: 2 methods. CreateUser(blah blah) and ValidateUserName(string username). CreateUserWizard control calls CreateUser(blah blah) which in the start of the method calls ValidateUserName(string username). ValidateUserName runs astored procedure onSQL 2k to check and see if user exists in the db. Returns a bool true or false. If true, then everything works correctly and returns a message displaying that the user exists, please enter a different name.

Now the problem. If it returns false, meaning that the username doesnt exist and is available to use, it calls a stored procedure to create the record in the users db. This is where the problem lies. It does NOTHING but sit there waiting. But it never seems to time out. If I comment out the validating. It will add a record successfully.

Im wondering if it has something to do with calling 2stored procedures consecutivly??

::CODE TO FOLLOW::Heres the code.
------------

public override MembershipUser CreateUser(string username, stringpassword, stringemail, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey, out MembershipCreateStatus status)
{
SqlConnection connect = null;
SqlCommand cmd = null;
MembershipUser newUser = null;

if (!ValidateUsername(username))
{
status = MembershipCreateStatus.DuplicateUserName;
return null;
}

try
{
connect = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnString].ConnectionString);

cmd = new SqlCommand("createuser", connect);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@.UserName", username));
cmd.Parameters.Add(new SqlParameter("@.Pwd", password));
cmd.Parameters.Add(new SqlParameter("@.PwdQuestion", passwordQuestion));
cmd.Parameters.Add(new SqlParameter("@.PwdAnswer", passwordAnswer));
cmd.Parameters.Add(new SqlParameter("@.Email", email));
cmd.Parameters.Add(new SqlParameter("@.Comment", _Name));
cmd.Parameters.Add(new SqlParameter("@.IsApproved", MyMethods.BoolToInt(isApproved)));
cmd.Parameters.Add("@.IsLockedOut", SqlDbType.Bit).Value = 0;
cmd.Parameters.Add(new SqlParameter("@.CreationDate", DateTime.Now));
cmd.Parameters.Add(new SqlParameter("@.LastLoginDate", DateTime.Now));

connect.Open();
cmd.ExecuteNonQuery();

// Right now I am giving default values for DateTime
// in Membership constructor.
newUser = new MembershipUser(
_Name,
username, null, String.Empty, String.Empty,
String.Empty, true, false, DateTime.Now,
DateTime.Now, DateTime.Now, DateTime.Now,
DateTime.Now
);
status = MembershipCreateStatus.Success;
}
catch (Exception ex)
{
status = MembershipCreateStatus.ProviderError;
newUser = null;
throw ex;
}
finally
{
cmd.Dispose();
connect.Close();
}
return newUser;
}

private bool ValidateUsername(string userName)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
bool IsValid = false;

try
{
conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings[ConnString].ConnectionString;

cmd = new SqlCommand("CheckUsername", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@.Username", userName));

conn.Open();
reader = cmd.ExecuteReader();
while (!reader.Read())
{
IsValid = true;
}
return IsValid;
}
catch (Exception ex)
{
throw ex;
}
finally
{
reader.Close();
cmd.Dispose();
conn.Close();
}
}|||

OK! NOT EVERYONE AT ONCE ! <big grin>

just kidding, anyways figured out the problem after pulling all but one last hair out.

The "while(!reader.Read())" was causing problems, just replaced it with "while(reader.HasRows)" and viola, it worked. Beats me.

No comments:

Post a Comment