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?

Solution 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.

Solution 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");

Solution 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.

Comments

[V4Vendetta]

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

[Sharrok G]

there are 16 columns in my database table

[Albin Sunnanbo]

Please start using SqlParameter before someone bites you.

[Anthony Pegram]

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.

[V4Vendetta]

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

[Joel Coehoorn]

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.

[V4Vendetta]

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

[Sharrok G]

Thanks For Your All Help Thanks

[Sandeep Pathak]

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

[ismail]

two table connect but this problem occure how c

[Yannick Meeus]

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

[bummi]

Please do not add questions to the answer section. Also here: stackoverflow.com/a/28897538/1699210

[BartoszKP]

@bummi This is not a question, the intent is clearly to answer.

[bummi]

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

[BartoszKP]

@bummi 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 :)

[rene]

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!