Member Avatar for sharathk60 
OleDbConnection con = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = D:\\incomexp.mdb");
        OleDbCommand com;
             OleDbCommand com = new OleDbCommand("insert  into incomeexp values (@year,@month,@data,@bal,@inc_exp)",con);
                            com.Parameters.Add("@year", OleDbType.Integer);
                            com.Parameters["@year"].Value = yr;
                            com.Parameters.Add("@month", OleDbType.Integer);
                            com.Parameters["@month"].Value = mn;
                            com.Parameters.Add("@data", OleDbType.Char);
                            com.Parameters["@data"].Value = s ;
                            com.Parameters.Add("@bal", OleDbType.Decimal );
                            com.Parameters["@bal"].Value = j ;
                            com.Parameters.Add("@inc_exp", OleDbType.Char);
                            com.Parameters["@inc_exp"].Value = s1 ;

                            con.Open();
                            com.ExecuteNonQuery();
                            con.Close();

------------------------------------------------------------------------------------

getting error message that
 "Number of query values and destination fields are not the same."
Edited by ~s.o.s~ because: Added code tags, please learn to use them.
  • 3 Contributors
  • 2 Replies
  • 280 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by sknake

Dani AI

Generated

This error simply means the VALUES list doesn't match the table's columns. is right: explicitly listing the target columns avoids this class of problem. A few additional gotchas to check that aren't yet mentioned in the thread:

  • OleDb + Jet/ACE uses positional parameter markers (use ? in the SQL). Parameter names are ignored by the provider, so the order you add parameters must match the placeholders.
  • If any column name is a SQL/Jet function or reserved word (for example Year or Month), wrap it in square brackets or rename the column.
  • If the table has an AutoNumber primary key, omit that column from your column list (AutoNumber is populated by Access).

A compact example that shows the positional-parameter style and proper escaping:

cmd.CommandText = "INSERT INTO incomeexp ([Year],[Month],[Description],[Bal],[Inc_Exp]) VALUES (?,?,?,?,?)";
cmd.Parameters.Add("p1", OleDbType.Integer).Value = yr;
cmd.Parameters.Add("p2", OleDbType.Integer).Value = mn;
cmd.Parameters.Add("p3", OleDbType.VarWChar).Value = s;
cmd.Parameters.Add("p4", OleDbType.Decimal).Value = j;
cmd.Parameters.Add("p5", OleDbType.VarWChar).Value = s1;

Troubleshooting checklist: confirm the table column count and names (post the CREATE TABLE if unsure, as suggested), make sure you add exactly as many parameters as there are ? placeholders and in the same order, use appropriate OleDbType for each field (VarWChar for text in Access), and escape reserved names with [ ]. If those are correct the INSERT will succeed.

Recommended Answers

All 2 Replies

Member Avatar for DanielGreen

i dont know, how many columns your table have, but normally you should write sql-statement like this:

insert into incomeexp (year,month,data,bal,inc_exp) values (@year,@month,@data,@bal,@inc_exp)

Otherwise the order and the amount of table-columns and values must be the same.

And the previous-poster already mentioned: the error-message is always helpfull!!

i hope this helps!

Edited by DanielGreen because: n/a
Member Avatar for sknake

sharathk60,

I believe the solution Daniel provided will fix your issue. If it doesn't then please post the CREATE TABLE statement for your table.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.