I always get this error when I try to register

Question

I'm using Asp.Net MVC and one of the functions of my program is to register and Login. I always get this error when I try to register.

System.Data.SqlClient.SqlException: Column name or number of supplied values does not match table definition.

Model:

public class Users
{
    [Key]
    public int ID { get; set; }

    [Display(Name = "User Type")]
    [Required]
    public int TypeID { get; set; }

    public List<UserType> Types { get; set; }
    public string UserType { get; set; }

    [Display(Name = "Email Address")]
    [MaxLength(80)]
    [Required]
    [DataType(DataType.EmailAddress)]
    public string Email { get; set; }

    [Display(Name = "Password")]
    [MaxLength(50)]
    [Required]
    [DataType(DataType.Password)]
    public string Password { get; set; }

    [Display(Name = "First Name")]
    [MaxLength(80)]
    [Required]
    public string FN { get; set; }

    [Display(Name = "Last Name")]
    [MaxLength(80)]
    [Required]
    public string LN { get; set; }


    [Display(Name = "Phone")]
    [MaxLength(12)]
    [MinLength(12)]
    [Required]
    public string Phone { get; set; }


    [Display(Name = "Status")]
    public string CurrentStatus { get; set; }

    [Display(Name = "Status")]
    public List<SelectListItem> Status { get; set; }
}

Controller:

  [HttpPost]
    public ActionResult Add(Users record)
    {
        if (IsExisting(record.Email))
        {
            ViewBag.Message = "<div class='alert alert-danger'>Email address already existing.</div>"; 
            record.Types = GetUserTypes();
            return View(record);
        }
        else
        {
            using (SqlConnection con = new SqlConnection(Helper.GetConnection()))
            {
                con.Open();
                string query = @"INSERT INTO users VALUES
                (@typeID, @userEmail, @userPassword, @userFirstName,
                @userLastName, @userPhone, @userStatus)";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@typeID", record.TypeID);
                    cmd.Parameters.AddWithValue("@userEmail", record.Email);
                    cmd.Parameters.AddWithValue("@userPW", Helper.Hash(record.Password));
                    cmd.Parameters.AddWithValue("@userFN", record.FN);
                    cmd.Parameters.AddWithValue("@userLN", record.LN);
                    cmd.Parameters.AddWithValue("@userPhone", record.Phone);  
                    cmd.Parameters.AddWithValue("@userStatus", "Active");
                    cmd.ExecuteNonQuery();
                    ViewBag.Message = "<div class='alert alert-success'>Record added.</div>"; // displays alert message when record is successfully added
                    ModelState.Clear(); // removes existing user input

                    record.Types = GetUserTypes();
                    return View(record);
                }
            }
        }
    }

You have both `@userPassword` and `@userPW`, and similar for other values. Got to pick one parameter name! — Andrei, Dec 13 '17 at 11:22

score 3 · Answer 1 · edited Dec 13 '17 at 12:00

3

If you don't specify columns for users table in an insert statement it expects you to pass values for all the columns

If you don't want to pass values for all the columns simply specify the column name for which you are providing values:

INSERT INTO users (typeID, userEmail, userPassword, userFirstName, 
                   userLastName, userPhone, userStatus)
VALUES (@typeID, @userEmail, @userPassword, @userFirstName, 
         @userLastName, @userPhone, @userStatus)

edited Dec 13 '17 at 12:00

Chandan Kumar

4,570
4
42
62

answered Dec 13 '17 at 11:40

Aman B

2,276
1
18
26

I'm still getting the same error. The values of my users table in my database are: userID(primary key), typeID, userEmail, userPW, userFN, userLN, userPhone, userStatus. – Christian Neil Manalang Dec 13 '17 at 14:06
I see. The real problem is your parameter names are different than the query. Try this : ` INSERT INTO users (typeID, userEmail, userPW, userFN, userLN, userPhone, userStatus) VALUES (@typeID, @userEmail, @userPW, @userFN, @userLN, @userPhone, @userStatus) ` – Aman B Dec 13 '17 at 14:53
I got this error, "Cannot insert the value NULL into column 'userID', table 'finalproject.dbo.users'; column does not allow nulls. INSERT fails. The statement has been terminated." – Christian Neil Manalang Dec 13 '17 at 15:11
Seems like userId is not an auto-increment key, If you want to make it an auto increment field see: (https://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column) . Otherwise you have to pass a userId value – Aman B Dec 13 '17 at 15:37

Alexander · Answer 2 · 2017-12-13T20:23:21.377

0

In you query for inserting, before VALUES you need to define the columns...

Like this...

string query = "INSERT INTO table (id,username,password,email) VALUES (@id, @username, @password, @email)";

EDIT:

string query = "INSERT INTO table (typeID, userEmail, userPW, userFN, userLN, userPhone, userStatus) VALUES (@typeID, @userEmail, @userPW, @userFN, @userLN, @userPhone, @userStatus)"

Also, you must set your primary key to auto increment in the sql database.

edited Dec 13 '17 at 20:23

answered Dec 13 '17 at 12:11

Alexander

405
7
17

I'm still getting the same error. The values of my users table in my database are: userID(primary key), typeID, userEmail, userPW, userFN, userLN, userPhone, userStatus. – Christian Neil Manalang Dec 13 '17 at 14:23

I always get this error when I try to register

2 Answers2