SimpleMembership - Add email to UserProfile - System.Data.SqlClient.SqlException: Invalid column name "Email" error

Question

I'm fairly sure I have followed all the steps, but seem to have missed something. Using simplemembership in MVC4 app. Add Email to UserProfile table and in the Register and UserProfile models, added it to the Register method, but still getting the error. Here is some code:

Models:

public class UserProfile
{
    public int UserId { get; set; }
    public string UserName { get; set; }
    public string Email { get; set; }
}

public class RegisterModel
{
    [Display(Name = "Email Address")]
    [StringLength(20)]
    // [Required]
    public string Email { get; set; }

    [Display(Name = "Date of Birth")]
    //   [Required]
    public DateTime DOB { get; set; }

    [Required]
    [System.Web.Mvc.Remote("VerifyUserExists", "Account", ErrorMessage="That Username is already taken.")]
    [Display(Name = "User name")]
    public string UserName { get; set; }

    [Required]
    [StringLength(100, ErrorMessage = "The {0} must be at least {2} characters long.", MinimumLength = 6)]
    [DataType(DataType.Password)]
    [Display(Name = "Password")]
    public string Password { get; set; }

    [DataType(DataType.Password)]
    [Display(Name = "Confirm password")]
    [Compare("Password", ErrorMessage = "The password and confirmation password do not match.")]
    public string ConfirmPassword { get; set; }
}

Controller:

    public ActionResult Register(RegisterModel model)
    {
        if (ModelState.IsValid)
        {
            // Attempt to register the user
            try
            {
                WebSecurity.CreateUserAndAccount(model.UserName, model.Password, new { Email = model.Email });
                WebSecurity.Login(model.UserName, model.Password);

                return RedirectToAction("Index", "Home");
            }
            catch (MembershipCreateUserException e)
            {
                ModelState.AddModelError("", ErrorCodeToString(e.StatusCode));
            }


        }

I am not trying to use the email address as the login, just want to grab it during the registration step so I can send an auto confirm email.

I have tried with the UserProfile table included in the EF model, and with it out, no difference. I have confirmed the table in the DB has a Email column.

Jehanna

What you have here looks good. Maybe you have more than one database that you're not aware of (like a file). At what point in execution do you receive the error? – Marrowbone

Error on this line: WebSecurity.CreateUserAndAccount(model.UserName, model.Password, new { Email = model.Email }); The AppData folder is empty and the only connection strings go to my localdb instance in SQL Server 2012, where the db is. I have been researching the CreateUserAndAccount method to find out where it is trying to save to, but have come up with nothing so far. – Jehanna

When you call WebSecurity.InitializeDatabaseConnection, which connection string are you using (and is that the right database)? – Counterproposal

If I remove the section , new{ Email etc ' from the Register method in the controller, it pass fine. So it is saving to somewhere i am not aware of. The connection string specifies the localdb and the database, so not sure how it could be going anywhere else: data source=(localdb)\v11.0;initial catalog=MyDatabase – Jehanna

@teahou. How did you create the column? – Counterproposal

Yes. I found the issue, it is in the database. The app was saving to an old aspnet membership table. I removed it and it seems fine now. Not sure how it was saving to there though. – Jehanna

I would be more restrictive on accepting emails. Instead of using [StringLength(20)] I would replace with: [Display(Name = "Email address")] [Required(ErrorMessage = "The email address is required")] [EmailAddress(ErrorMessage = "Invalid Email Address")] public string Email { get; set; } - Just a suggestion. – Delegate

Answer 1

If you're using the default connection, open the database by clicking View -> Server Explorer then expand the DefaultConnection. Under Tables you will see the UserProfile table. Add your columns to the table first and update the database, then add your extra fields to the class.

Fraternal

Answer 2

I'm guessing that the Email property (in your UserProfile class) was somthing you added after you already executed the application for the first time , so if the table already exist befor you changed your model and added the Email property, It might be the cause for the exception.

as you mentioned yourself in one of your comments:

If I remove the section , new{ Email etc ' from the Register method in the controller, it pass fine

To fix that, I think you need to do somthing like that in your DbContext deriven class.(assuming you use code first approach):

     public class myDbContext: DbContext
{
    public myDbContext()
        : base("DefaultConnection")
    {
        Database.SetInitializer<myDbContext>(new DropCreateDatabaseIfModelChanges<myDbContext>());
    }

    public DbSet<UserProfile> UserProfiles { get; set; }

}

The default setting is CreateDatabaseIfNotExists ,so if your UserProfile table was already exist, it didn't create it again and didn't find your new Email property (It was in your model but not in the database table);

Ader