CSEngine CSEngine -4 years ago 239
C# Question

column count does match value count at row 1 C#

I do not know why I am getting this error:

C# Code:

using (MySqlConnection connection = new MySqlConnection("datasource=localhost;port=3306;database=project;username=***;password=***;"))
{
MySqlCommand cmd = new MySqlCommand("INSERT INTO student (studentID, studentFirstName, studentLastName, studentUserName, studentPassword) VALUES (@userID, @, @FirstName, @LastName, @Username, @Password);");
cmd.CommandType = CommandType.Text;
cmd.Connection = connection;
cmd.Parameters.AddWithValue("userID", Convert.ToInt32(textBoxUserID.Text));
cmd.Parameters.AddWithValue("@FirstName", textBoxFirstName.Text);
cmd.Parameters.AddWithValue("@LastName", textBoxLastName.Text);
cmd.Parameters.AddWithValue("@UserName", textBoxUsername.Text);
cmd.Parameters.AddWithValue("@Password", textBoxPassword.Text);
connection.Open();
cmd.Connection = connection;
cmd.ExecuteNonQuery();
MessageBox.Show("Saved");
connection.Close();
}


It may due to me overlooking something.

Error:


An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data

Additional information: Column count doesn't match value count at row 1

Answer Source

Format out your code and you'll see all the syntactic problems clearly:

string connectionString = 
  "datasource=localhost;port=3306;database=project;username=***;password=***;";

using (MySqlConnection connection = new MySqlConnection(connectionString)) {
  connection.Open();

  //DONE: keep sql readable
  string sql = 
    @"INSERT INTO student (
         studentID, 
         studentFirstName, 
         studentLastName, 
         studentUserName, 
         studentPassword) 
      VALUES (
         @userID, 
         @FirstName, -- wrong @ param 
         @LastName, 
         @Username, 
         @Password);";

  //DONE: wrap IDisposable into using
  using (MySqlCommand cmd = new MySqlCommand(sql)) {
    cmd.CommandType = CommandType.Text; // redundant
    cmd.Connection = connection;

    //DONE: separate code with new lines
    // wrong parameter name
    cmd.Parameters.AddWithValue("@userID", Convert.ToInt32(textBoxUserID.Text));
    cmd.Parameters.AddWithValue("@FirstName", textBoxFirstName.Text);
    cmd.Parameters.AddWithValue("@LastName", textBoxLastName.Text);
    cmd.Parameters.AddWithValue("@UserName", textBoxUsername.Text);
    cmd.Parameters.AddWithValue("@Password", textBoxPassword.Text);

    cmd.ExecuteNonQuery();
  } 
}

MessageBox.Show("Saved");
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download