Ask your question here : ASP.NET or MVC | C# | Windows Phone
Microsoft Technology Journals by Abhimanyu K Vatsa
HOME ABOUT RAZOR BOOK ASP.NET JQUERY C# VIDEOS MVC EBOOK ARCHIVE

5 Nov 2012

Adding Email, Details field in UserProfile DB Table in MVC

In this post you will learn how to add Email and Details columns to an existing database. We get this database when running an application trying to create a new user account. As you know, we don’t see an email field in a user registration form as well as in the membership database. But we can enable this using some quick changes in the application. Read on.

In MVC we also have the opportunity to create a separate database table for users. In other words, we can add a new database table like ‘NewUsers’ and we can hook this database table to membership from here (in the 'Filter' folder>InitializeSimpleMembershipAttribute.cs file):

WebSecurity.InitializeDatabaseConnection("DefaultConnection", "NewUsers", "Id", "Username", autoCreateTables: true);

Note: Remember to match ‘UserId’ data in both tables ‘NewUsers’ and ‘webpages_Membership’, otherwise this will create a very big security hole in your application. Well this is not the subject of this post.

Now, let’s go ahead and follow the steps to add Email and Details field to the database and update the Model, View and Controller to accept that data from the user.


Step 1: Update Database

Open the database and add the following fields.


Step 2: Update Data Model

Open the Data Model ‘AccountModels.cs’, and add the following things in ‘RegisterModel’.


Step 3: Update Controller

Open ‘AccountController.cs’ and add the following code snippets.


Step 4: Update View

Now, open ‘Register.cshtml’ view and add the following code snippets.


Now, when you run the application you will see all in action.

What if one wants to let the user login using an email id?

Here it is, one quick change in the application will enable this feature.


I’m just instructing my application to use the ‘EmailId’ field instead of ‘UserName’ field which is the default and I’m done.


Hope this helps.

Comment using Google Services (17 comments):

  1. Nice article, but notice that "What if one wants to let the user login using an email id?" doesn't work well.

    It is throwing error: Cannot insert the value NULL into column 'UserName', table 'LoginSys.dbo.UserProfile'; column does not allow nulls. INSERT fails.
    The statement has been terminated.

    Do you know what could be cause of this error?

    ReplyDelete
    Replies
    1. Patel: for this you need to re-design the registration page & login page. you are getting this error because in 'UserProfile' database table 'username' column is NOT NULL field.

      Delete
    2. really helpful your notes but you didnt mention about the database coonection to this application ...

      Delete
  2. Nice tip Abhimanyu!!. I have one suggestion for you. Use syntax highlighter instead of putting code pictures over there. So Please can copy and use your code directly.

    ReplyDelete
    Replies
    1. Thank you Jalpesh :) nice suggestion, i'll add this feature very soon.

      Delete
  3. Hi Abhimanyu.
    I discovered your blog because I was doing exactly what you are doing here but when I run for the first time the website, for some reason my new fields are not created on the table.

    I have tried a lot of things and nothing works, and I do exactly what you are doing.
    I have Windows 8, VS2012 professional and also MSSQL server 2012 with all the updates.

    Any idea why is not working for me?

    Thanks

    ReplyDelete
    Replies
    1. You maybe missing a step. Create a new project first and then try creating a new user account that will generate the simple membership database and then add new fields as given in step 1.

      Delete
    2. I also found that any custom fields are not generated from the objects and must be pre-authored into the simple membership tables.

      Delete
    3. I found this also. Any custom fields added to the models are not added to the UserProfile table on create. They need to be pre-authored (as you did in your first step). You might want to delete your tables and see what happens on your example.

      Delete
  4. Hello I tried to add the Column Email for userProfiles. When i try to register a user with a new email i get this error.
    Cannot insert the value NULL into column 'UserId', table 'DB.DB.UserProfile'; column does not allow nulls. INSERT fails.

    ReplyDelete
  5. hello sir,
    i'm trying to register at run-time(register view) in mvc4, but it gives error as-

    Cannot insert explicit value for identity column in table 'webpages_Membership' when IDENTITY_INSERT is set to OFF.

    at-

    WebSecurity.CreateUserAndAccount(model.UserName, model.Password, new {Address = model.Address, City = model.City, State = model.State, Country = model.Country});

    ReplyDelete
  6. hello sir
    nice post ...but i want to display different column name e.g
    when user has successfully logged in it should display user Firstname instead of username or(any other column which is added manually), ....
    can you suggest something thanks in advance

    ReplyDelete
  7. you didn't explain what to do that email login in would work, you just said you have to change the variable from UserName to EmailID, which doesn't work..

    Cannot insert the value NULL into column 'UserName', table 'db.dbo.UserProfile'; column does not allow nulls. INSERT fails.
    The statement has been terminated.

    ReplyDelete
  8. Ignoring the simple NULL issues, if you use WebSecurity.InitializeDatabaseConnection("DefaultConnection", "UserProfile", "UserId", "EmailId", autoCreateTables: true) as suggested you simply wind up with the username replacing the emaiIId field in the database. This results in a null userName (unless you add that back in the CreateUserAndAccount call) so the system breaks when you add a second user this way (duplicate null user id).

    ReplyDelete