SQL异常：“INSERT语句中的列数多于VALUES中指定的值”。 [英] SQL Exception:"There are more columns in the INSERT statement than values specified in the VALUES"

问题描述

您好。我尝试逐行插入到指定的数据库表。然而，我仍然得到了这个错误，尽管我得到的列和值都是正确的。

Hello. I try to do a row by row insertion to a specified database table. Yet i keep getting that error despite the fact that i got both columns and values count right.

SqlConnection cnn3 = new SqlConnection(cb.ConnectionString);
cnn3.Open();
cnn3.ChangeDatabase(DatabaseInProcess.DbInProcess);
SqlCommand cmd3 = new SqlCommand(
@"INSERT INTO DestinationDatabase.dbo." + table.tableName + " (" + columns + ") VALUES (@ValuesParam)"
, cnn3);
                    
cmd3.Parameters.AddWithValue("@ValuesParam", result);                    
cmd3.ExecuteNonQuery();

是插入的代码。当我用Visual Studio查看器检查sqlCommand cmd3时，我看到：

Is the code that does the insertion. When i check the sqlCommand cmd3 with Visual Studio viewer i see:

"INSERT INTO DestinationDatabase.dbo.QvBranch (BranchPK, BranchCode, BranchName, BranchIsCentral, BranchIsActive, DataState, DataTime, DataUserFK, DataID) VALUES (@ValuesParam)"

加载，当我检查@ValuesParam时，我看到：

loaded and when i check @ValuesParam i see:

{'f8d4848b-e27c-4351-b8a4-6858a1147059', '0000', 'New Department', '0', '0', NULL, NULL, NULL, NULL}

作为SQL值。你可以看到他们似乎匹配，但我仍然得到错误。

感谢您的帮助。

as an SQL value. As you can see they seem to match up but i still get the error.
Thanks for help.

推荐答案

问题在于你只指定了一个参数：@ ValuesParam，它想要九个。你不能只给它一个参数集合，因为系统不知道集合应该是第一列的单个对象，还是九个对象，每列一个 - 所以它假设集合是存储在第一列。



SQL在解析命令字符串时甚至不查看参数内容：它只是查看文本并说不 - 没有足够的参数。



您必须将集合中的每个元素作为单独的命名参数传递。

The problem is that you have only specified one single parameter: "@ValuesParam" and it wants nine. You can't just give it a collection of parameters because the system doesn't know if the collection is supposed to be a single object for the first column, or nine objects, one for each column - and so it assumes the collection is to be stored in the first column.

And SQL doesn't even look at the parameter contents when it parses the command string: it just looks at teh text and says "Nope - not enough parameters".

You will have to pass each of the elements of the collection as a separate named parameter.

这篇关于SQL异常：“INSERT语句中的列数多于VALUES中指定的值”。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！