Number of query values and destination fields are not the same./Syntax error in INSERT INTO statement

Question

1.00/5 (1 vote)

See more:

When Iam inserting into tables...i get some errors.
Tried many method....but it did not work...please help!
I did the same code for another table and it gets added... i can not find the error.

For Following code it gives "Number of query values and destination fields are not the same"

C#

ConnectionToDB();
string sql = "INSERT INTO TW100NEL VALUES('"+pl+"','"+desc+"',"+grp+","+qty+",'"+unit+"')";
comm.CommandType = CommandType.Text;
comm.CommandText = sql;
comm.Connection = conn;
conn.Open();
comm.ExecuteNonQuery();
MessageBox.Show("Successfully Added!");
conn.Close();

When i write like following, it give error :"Syntax error in INSERT INTO statement"

C#

string sql ="INSERT INTO TW100NEL(ComponentNumber,ObjectDescription,Group,Quantity,Unit) VALUES(@pl,@desc,@grp,@qty,@unit)";
comm.Parameters.AddWithValue("@pl", pl);
comm.Parameters.AddWithValue("@desc", desc);
comm.Parameters.AddWithValue("@grp", grp);
comm.Parameters.AddWithValue("@qty", qty);
comm.Parameters.AddWithValue("@unit", unit);
comm.CommandType = CommandType.Text;
comm.CommandText = sql;
comm.Connection = conn;
conn.Open();
comm.ExecuteNonQuery();
MessageBox.Show("Successfully Added!");
conn.Close();

I have checked all rows..it is correct and whatever value i am entering goes to rows which are editable.
I am working on accdb.
Following query works well in accdb query design :

INSERT INTO TW100NEL VALUES('1234','zzzzzzz',4,2,'PC');

I did not show code of connections because it is working well for other sql.

Thank you in advance.

Posted 14-Sep-14 19:12pm

Member 11082315

Updated 14-Sep-14 19:20pm

v2

Add a Solution

Comments

Prasad Avunoori 15-Sep-14 1:18am

The Erro message clearly says that "Number of query values and destination fields are not the same". How many columns are there in table "TW100NEL" ?

Member 11082315 15-Sep-14 1:26am

this is my table with 'field Name' and 'data Type' :
ID : AutoNumber
ComponentNumber : Text
ObjectDescription : Text
Group : Number
Quantity : Number
Unit : Text

Prasad Avunoori 15-Sep-14 1:27am

Find my solution below.

Member 11082315 15-Sep-14 2:15am

thank you it worked :)

Abdul Samad KP 15-Sep-14 1:27am

Did you try
INSERT INTO TW100NEL(ComponentNumber,ObjectDescription,Group,Quantity,Unit) VALUES(?,?,?,?,?)

ChintanShukla 15-Sep-14 1:28am

Did you check with the data types?

3 solutions

Solution 3

If it is a MySQL database you need to wrap keywords in a ` character. (ASCII = 0x60)

SQL

INSERT INTO TW100NEL
    (ComponentNumber, ObjectDescription, `Group`, Quantity, Unit) 
VALUES 
    (@pl, @desc, @grp, @qty, @unit)

Posted 14-Sep-14 19:39pm

George Jonsson

Comments

[no name] 15-Sep-14 6:42am

it's a sql database as the command below from the example
comm.CommandText = sql;

George Jonsson 15-Sep-14 10:03am

eh. So you mean that if you declare a variable like this string sql; it clearly identifies which type of database it is???
Different databases have different syntax and in this case it was not clear which one the user meant.

[no name] 16-Sep-14 1:16am

you are right ! but as you know while declaring a variable it should be clear and meaningful , the efficient variable name is this one make it's purpose clear.
so if you are using Sql connection but you declare your variable as Oracle!!!!! this is absolutely misleading

George Jonsson 16-Sep-14 1:31am

They all use SQL queries, so naming a variable like string sql; would in most cases refer to an SQL string or command and not specify the database.
And what made you think I meant the variable should be called Oracle?
The question should be tagged with the proper designation, SQL Server not only c#.

[no name] 16-Sep-14 2:09am

i'm talking from the code view , as you say they all using sql string so to distingue for the reader you can rename it to Sql or MySql Or Oracle (for example).

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)

Prasad Avunoori · Accepted Answer · 2014-09-14T19:27:00

Solution 1

Group is a keyword in SQL server. It must be wrapped by square brackets.

Try below code.

SQL

INSERT INTO TW100NEL
(ComponentNumber,ObjectDescription,[Group],Quantity,Unit) VALUES(@pl,@desc,@grp,@qty,@unit)

Posted 14-Sep-14 19:27pm

Prasad Avunoori

Gihan Liyanage · Accepted Answer · 2014-09-14T19:32:00

Solution 2

Check with this

C#

ConnectionToDB();
string sql = "INSERT INTO table_name (ComponentNumber,ObjectDescription,[Group],Quantity,Unit) VALUES('"+pl+"','"+desc+"',"+grp+","+qty+",'"+unit+"')";
comm.CommandType = CommandType.Text;
comm.CommandText = sql;
comm.Connection = conn;
conn.Open();
comm.ExecuteNonQuery();
MessageBox.Show("Successfully Added!");
conn.Close();

Posted 14-Sep-14 19:32pm

Gihan Liyanage

Comments

Richard Deeming 23-Sep-14 7:38am

Well done - you've taken a properly parameterized query, and introduced a SQL Injection[^] vulnerability! :doh:

NEVER use string concatenation to build a SQL query.