C# SQL Server 插入错误：列名或提供的值数量与表定义不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17403243/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server Insert Error: Column name or number of supplied values does not match table definition
提问by user2514307
I'm trying to add data to a table in SQL Server, using Microsoft Visual Studio 2012. However, it gives me this error:
我正在尝试使用 Microsoft Visual Studio 2012 将数据添加到 SQL Server 中的表中。但是，它给了我这个错误：
SQLException was undandled by user code.
Column name or number of supplied values does not match table definition.
SQLException 由用户代码处理。
列名或提供的值数量与表定义不匹配。
Here's my codes:
这是我的代码：
protected void btnAdd_Click(object sender, EventArgs e)
{
con.Open(); // establish a database connection
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText =
"INSERT INTO Products VALUES (@CatID, @Name, " +
"@Image, @Description, @Price)";
cmd.Parameters.Add("@CatID", SqlDbType.Int).Value =
ddlCategories.SelectedItem.Value;
cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value =
txtName.Text;
cmd.Parameters.Add("@Image", SqlDbType.NVarChar).Value =
"http://localhost:12345/CAPSTONE/images/" + fuImage.FileName;
cmd.Parameters.Add("@Description", SqlDbType.NVarChar).Value =
txtDescription.Text;
cmd.Parameters.Add("@Price", SqlDbType.Decimal).Value =
txtPrice.Text;
// saving the image file to your website folder 'Images'
fuImage.SaveAs(Server.MapPath("~/images/" + fuImage.FileName));
cmd.ExecuteNonQuery();
con.Close();
Response.Redirect("Default.aspx");
}
采纳答案by abramlimpin
Double-check the primary key (I assume if your product ID) of your Products table if it's value is set to auto increment (Identity Specification > Is Identity = true)
仔细检查您的产品表的主键（我假设您的产品 ID），如果它的值设置为自动递增（身份规范 > 是身份 = 真）
回答by JBond
This means that your Productstable has more or less columns than those you've supplied in your VALUESstatement.
这意味着您的Products表的列比您在VALUES语句中提供的列多或少。
(@CatID, @Name, @Image, @Description, @Price)
If you perform the query sp_columns Productsin management studio, it will list all of the columns in the table.
如果您sp_columns Products在 management studio 中执行查询，它将列出表中的所有列。
If however, you only want to insert those values, you would need to perform the following query instead:
但是，如果您只想插入这些值，则需要执行以下查询：
INSERT Products (CatID, Name, Image, Description, Price)
When you specify an INSERTstatement without specifying the column names, it expects all columns to be populated
当您指定一个INSERT语句而不指定列名时，它期望填充所有列
回答by Akrem
you must make all column of your database table in your query when you use this methode (and do not enter a value for Id if it's auto increment) in you case
在这种情况下，当您使用此方法时，您必须在查询中创建数据库表的所有列（并且如果它是自动递增的，则不要输入 Id 的值）
"INSERT INTO Products VALUES (@CatID, @Name, " +
"@Image, @Description, @Price)";
you have only 5 column and CatID is not an auto increment value ! you have to check that if true !
您只有 5 列，并且 CatID 不是自动递增值！你必须检查，如果是真的！
and you can choose the values do you want to make by this query
并且您可以选择要通过此查询生成的值
cmd.CommandText ="INSERT INTO Products (CatID, Name,Image, Description, Price)
VALUES (@CatID, @Name,@Image, @Description, @Price)";
回答by Md. Parvez Alam
Use try catch block , and figure out the exact problem in catch, it will say what you missing, rest most of the possible answers have been given above
使用 try catch block ，并找出 catch 中的确切问题，它会说明您遗漏了什么，其余大部分可能的答案已在上面给出
回答by user2989163
private void btninsert_Click(object sender, EventArgs e)
{
try
{
con.Open();
cmd = new SqlCommand("insert into Empinfo values ('" + textBox1.Text + "''" + textBox2.Text + "''" + textBox3.Text + "''" + textBox4.Text + "')", con);
cmd.ExecuteNonQuery();
MessageBox.Show("Insert the record");
cmd.Dispose();
}
catch(Exception e1)
{
MessageBox.Show("e1");
}
finally
{
con.Close();
}
}
