System.Data.SqlClient.SqlException: 'Invalid column name 'id_or_passportnewusernames'. There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT

[private void btnNextPersonal_Click(object sender, EventArgs e)
        {
            DBAccess objDBAccess = new DBAccess();

            string name = txtNames.Text;
            string surname = txtSurname.Text;
            string Mail = txtEmail.Text;
            string ID = txtID.Text;
            string NoOfRooms = txtNORooms.Text;
            string Pword = txtNewPass.Text;
            string NewUsername = txtNewUsername.Text ;


            if (name.Equals(""))
            {
                MessageBox.Show("Please enter your name.");
            }
           else if (surname.Equals(""))
            {
                MessageBox.Show("Please enter your surname.");
            }
           else if (Mail.Equals(""))
            {
                MessageBox.Show("Please enter your email.");
            }
           
            else if (NoOfRooms.Equals(""))
            {
                MessageBox.Show("Please enter a number of rooms to be booked.");
            }
            else if (NewUsername.Equals(""))
            {
                MessageBox.Show("Please enter a username.");
            }
            else if (Pword.Equals(""))
            {
                MessageBox.Show("Please enter a new password.");
            }
            //Importing data into the database
            else
            {
                SqlCommand insertcommand = new SqlCommand("insert into Users_Names (Names,Surname,Email, no_of_rooms,id_or_passport"  
                    "newusernames, Newpassword) values(@name,@surname,@Mail,@NoOfRooms,@ID,@NewUsername,@Pword)");


                insertcommand.Parameters.AddWithValue("@name", name);
                insertcommand.Parameters.AddWithValue("@surname", surname);
                insertcommand.Parameters.AddWithValue("@Mail", Mail);
                insertcommand.Parameters.AddWithValue("@NoOfRooms", NoOfRooms);
                insertcommand.Parameters.AddWithValue("@ID", ID);
                insertcommand.Parameters.AddWithValue("@NewUsername", NewUsername);
                insertcommand.Parameters.AddWithValue("@Pword", Pword);


                int row = objDBAccess.executeQuery(insertcommand);

                if (row == 1)
                {
                    MessageBox.Show("Account created successfully");
                }
                else
                {
                    MessageBox.Show("Error occured");
                }

            }][1]

CodePudding user response：

If I line up your column names and your parameters, can you see the problem?

insert into Users_Names (
  Names, Surname,  Email, no_of_rooms, id_or_passportnewusernames, Newpassword         ) values(
  @name, @surname, @Mail, @NoOfRooms,  @ID,                        @NewUsername, @Pword)"

Looks like you dropped a comma when you split the string over two lines

Basic technique for debugging sql that doesn't work; use the debugger to pause the code and copy the exact SQL string you're sending to the DB..

Also, consider using verbatim strings. It's a lot easier to check SQL like:

  var sql = @"
SELECT *
FROM t
WHERE x = y
";

Than using the stop-start-concat way

   var sql = @"SELECT *" 
     "FROM t" 
     "WHERE x = y";

Then upgrade to keeping your sql strings in resources..

..then upgrade to entity framework and do away with all this tedious boilerplate entirely