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?

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.

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

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.