Number of query values and destination fields are not the same error

Question

I am getting an error while inserting data into a database.

The error is:

"Number of query values and destination fields are not the same".

Insert code:

OleDbConnection vconn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Mutyyba\\Documents\\Database1.accdb");
vconn.Open();

string name = textBox1.Text;
string address = textBox3.Text;
int rollno = Convert.ToInt32(textBox2.Text);

string vquery = "insert into Table1 values(@vname,@vrollno,@vaddress)";

OleDbCommand vcomm = new OleDbCommand(vquery, vconn);
vcomm.Parameters.AddWithValue("@vname", name);
vcomm.Parameters.AddWithValue("@vrollno", rollno);
vcomm.Parameters.AddWithValue("@vaddress", address);

vcomm.ExecuteNonQuery();

MessageBox.Show("your record has been recorded sucessfully!");

vconn.Close();

What am I doing wrong?

Prevision

You have about 16 fields in the query, whatz the number of columns in the database table ? – Penrod

there are 16 columns in my database table – Prevision

Please start using SqlParameter before someone bites you. – Jackscrew

Actually, you have all of one intended field in the query. You have a single quote at the beginning, a single quote at the end, so barring any unescaped quotes in those strings, you'll try to insert exactly one field. Ergo, your error. But don't stop by fixing that, look into parameterized queries so that you do not run into one of those pesky SQL injection issues. – Camenae

then there are some single quotes missing, check the datatypes associated like it should be Name in single quotes values('" + Name + "', – Penrod

Answer 1

I think you just missed some single quotes . I see you have enclosed all the parameters with a starting and end single quotes . See this

One more thing , as you are passing lot of parameter prepare a SqlCommand Object for Parameters. See msdn for more details.

Do something like this :

  SqlCommand comm = new SqlCommand("INSERT INTO table VALUES (@txtsno, @txtdesg, @txtbasic)", connection);

  comm.Parameters.AddWithValue("@txtsno", txtsno.Text.Trim());

  comm.Parameters.AddWithValue("@txtsno", txtdesg.Text.Trim());

  comm.Parameters.AddWithValue("@txtsno", txtbasic.Text.Trim());

This would be more clearer and would not be prone of SQL Injection.

Mcchesney

DO use parameters. DO NOT use .AddWithValue(), as it leaves the .Net runtime to guess at your sql data types. Sometimes the runtime will guess wrong, with tough-to-debug results or odd performance issues. – Frolick

@Joel : Yeah valid point thanks . I found this in the same lines forums.asp.net/t/1200255.aspx/… – Mcchesney

Answer 2

Try to use parameters to build the command

   // Create the InsertCommand.
    command = new OleDbCommand(
        "INSERT INTO Customers (CustomerID, CompanyName) " +
        "VALUES (?, ?)", connection);
   // add parameters like below 
    command.Parameters.Add(
        "CustomerID", OleDbType.Char, 5, "CustomerID");
    command.Parameters.Add(
        "CompanyName", OleDbType.VarChar, 40, "CompanyName");

Plasm

Answer 3

You need to specify the column names in your SQL, or the value sequence should be the exact same (number and order) with the default schema of the table

OleDbCommand cmd = new OleDbCommand("insert into real (name, symbol, date, red, redby, redsell, sbintrabuy, sbtr1, sbtr2, sbtr3, sbintersell, sbtr1, sbtr2, sbtr3, rstl, green) values('" + Name + "','" + Symbol + "','" + Date + "','" + Red + "','" + RedBuy + "','" + RedSell + "','" + SBIntraBuy + "','" + SBTR1 + "','" + SBTR2 + "','" + SBTR3 + "','" + SBIntraSell + "','" + SBTR1 + "','" + SBTR2 + "','" + SBTR3 + "','" + RSTL + "','" + Green + "');", con);

Replace the bold columns with correct names, it's recommended that to specify the column names explictly.

The string values should be around with single quota

Normally, you should write sql like this:

cmd.Parameters.Add("var", System.Data.OleDb.OleDbType.VarChar); cmd.Parameters["var"].Value = 'somevalue';

In your sql should be like: "insert into real(column1) values(@var)".

====

I updated the answer as above, hope it can solve your problem.

Plea

you have all those values enclosed in single quote, it doesn't make sense – Penrod

Answer 4

insert into Main values (28494,1,False,'Buto-asma  Sirop' , 'Buto-asma  Sirop', 3.99 , 'Syrup', 'ispani', ' ', ' ',0, '1',4988 )

solves this problem

Garrek

two table connect but this problem occure how c – Garrek

Please consider actually adding both formatting and some context around your answer. – Notus

Please do not add questions to the answer section. Also here: https://mcmap.net/q/1166473/-number-of-query-values-and-destination-fields-are-not-the-same – Clue

@Clue This is not a question, the intent is clearly to answer. – Nicole

@Nicole if you follow the link in my prior comment you can see (10k+) the whole question. – Clue

@Clue That's a completely different post. Sorry, but I fail to see how is it related in a way that is relevant to this post :) – Nicole

This statement does in no single way relate to the question. It seem to relate to your now deleted answer that you posted elsewhere and asked a new question which you shouldn't do in an answer box. Remember we are not a forum. I suggest you take the tour and revisit How to Ask and maybe take some time to read the other material in the help center. Good luck! – Kelt