SQL Exception:"There are more columns in the INSERT statement than values specified in the VALUES"

Question

0.00/5 (No votes)

See more:

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.

C#

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();

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

SQL

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

loaded and when i check @ValuesParam i see:

SQL

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

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

Posted 17-Sep-14 19:38pm

YourAverageCoder

Updated 17-Sep-14 19:52pm

George Jonsson

v3

Add a Solution

Comments

Ajith K Gatty 18-Sep-14 1:47am

Hi,
The value counts are matching. But check the values you are passing is proper or not.
Ex:You have a column Named DataID and the corresponding value to that is NULL.
Check it once.

YourAverageCoder 18-Sep-14 1:51am

Those values are from the database table i try to import to DestinationDatabase. When i enter the same command in SQL Server Studio, the Destination Database accepts it:

INSERT INTO DestinationDatabase.dbo.QvBranch ( BranchPK, BranchCode, BranchName, BranchIsCentral, BranchIsActive, DataState, DataTime, DataUserFK, DataID ) VALUES ( 'F8D4848B-E27C-4351-B8A4-6858A1147059','0000','New Department','0','0',NULL,NULL,NULL,NULL )

Sinisa Hajnal 18-Sep-14 2:08am

Install RedGate Profiler and see what comes into the database. There is 30-day trial for free. But its worth every penny.

YourAverageCoder 18-Sep-14 2:11am

Will do. Thanks.

Abdul Samad KP 18-Sep-14 2:11am

You have to add parameters one by one

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2014-09-17T20:13:00

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.