10

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?

Alexander Leonov
  • 4,694
  • 1
  • 17
  • 25
Sharrok G
  • 447
  • 3
  • 13
  • 27
  • You have about 16 fields in the query, whatz the number of columns in the database table ? – V4Vendetta Nov 08 '11 at 05:18
  • there are 16 columns in my database table – Sharrok G Nov 08 '11 at 05:20
  • 7
    Please start using [`SqlParameter`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx) before someone bites you. – Albin Sunnanbo Nov 08 '11 at 05:20
  • 3
    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. – Anthony Pegram Nov 08 '11 at 05:21
  • 1
    then there are some single quotes missing, check the datatypes associated like it should be Name in single quotes `values('" + Name + "',` – V4Vendetta Nov 08 '11 at 05:22

4 Answers4

6

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.

Sandeep Pathak
  • 10,567
  • 8
  • 45
  • 57
  • 1
    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. – Joel Coehoorn Nov 08 '11 at 05:31
  • @Joel : Yeah valid point thanks . I found this in the same lines http://forums.asp.net/t/1200255.aspx/1?Parameters+AddWithValue+vs+Parameters+Add+ – Sandeep Pathak Nov 08 '11 at 05:51
3

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");
Damith
  • 62,401
  • 13
  • 102
  • 153
2

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.

夏至夕陽
  • 352
  • 1
  • 5
-2
insert into Main values (28494,1,False,'Buto-asma  Sirop' , 'Buto-asma  Sirop', 3.99 , 'Syrup', 'ispani', ' ', ' ',0, '1',4988 )

solves this problem

BartoszKP
  • 34,786
  • 15
  • 102
  • 130
ismail
  • 21
  • two table connect but this problem occure how c – ismail Mar 05 '15 at 11:36
  • 1
    Please consider actually adding both formatting and some context around your answer. – Yannick Meeus Mar 05 '15 at 11:44
  • Please do not add questions to the answer section. Also here: http://stackoverflow.com/a/28897538/1699210 – bummi Mar 06 '15 at 12:30
  • @bummi This is not a question, the intent is clearly to answer. – BartoszKP Mar 06 '15 at 12:53
  • @BartoszKP if you follow the link in my prior comment you can see (10k+) the whole question. – bummi Mar 06 '15 at 13:01
  • @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 :) – BartoszKP Mar 06 '15 at 13:13
  • 1
    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 [ask] and maybe take some time to read the other material in the [help]. Good luck! – rene Mar 06 '15 at 18:30