I created a Windows form in Visual Application and connect it to a SQL Server database. But when I running the code shown here, I got an error:

System.Data.SqlClient.SqlException (0x80131904): There are more 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 statement.)

Code:

private void button1_Click (object sender, EventArgs e)
{
    connection.Open();

    SqlCommand cmd = connection.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "INSERT INTO [MyTable] (Name, Surename, Address) VALUES ('"  textBox1.Text  ","  textBox2.Text   ","   textBox3.Text   "')";

    cmd.ExecuteNonQuery();
    connection.Close();

    textBox1.Text = "";
    textBox2.Text = "";
    textBox3.Text = "";
    textBox4.Text = "";
}

I tried to remove some of the values and run it. But it's shows the same error again.

CodePudding user response：

I'd recommend

• Placing data operations into a class, invoke from your form
• Use parameters via Add rather than AddWithValue rather than string concatenation
• Don't use one connection for all operations but that is your choice.

Here is an example that returns the new primary key.

public class DataOperations
{
    private static string _connectionString = "TODO";


    public static int Insert(string name, string surName, string address)
    {
        using (var cn = new SqlConnection(_connectionString))
        {
            using var cmd = new SqlCommand
            {
                Connection = cn,
                CommandText = "INSERT INTO [MyTable] (Name, Surename, Address) VALUES (@Name, @SurName, @Address);"  
                              "SELECT CAST(scope_identity() AS int);"
            };

            cmd.Parameters.Add("@Name", SqlDbType.NChar).Value = name;
            cmd.Parameters.Add("@SurName", SqlDbType.NChar).Value = surName;
            cmd.Parameters.Add("@Address", SqlDbType.NChar).Value = address;

            cn.Open();

            return Convert.ToInt32(cmd.ExecuteScalar());
        }
    }
}

While this example inserts a record similar to what you are doing now.

public class DataOperations
{
    private static string _connectionString = "TODO";


        public static void Insert(string name, string surName, string address)
        {
            using (var cn = new SqlConnection(_connectionString))
            {
                using var cmd = new SqlCommand
                {
                    Connection = cn,
                    CommandText = "INSERT INTO [MyTable] (Name, Surename, Address) VALUES (@Name, @SurName, @Address);"
                };

                cmd.Parameters.Add("@Name", SqlDbType.NChar).Value = name;
                cmd.Parameters.Add("@SurName", SqlDbType.NChar).Value = surName;
                cmd.Parameters.Add("@Address", SqlDbType.NChar).Value = address;

                cn.Open();

                cmd.ExecuteNonQuery();
            }
        }
}

CodePudding user response：

There is the problem of single quotes. That should solve your actual problem.

But you have to use parameterized query not like you have done in your code to avoid SQL injection.

You should use using for the SqlConnection and SqlCommand so that they will be disposed.

You can utilise the return value of ExecuteNonQuery, which will give you idea of count of inserted records.

Use try catch and log the exception.

And I think you can move those lines to a finally block.

textBox1.Text = "";
…