Click here to Skip to main content
15,900,589 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Messdatabase.mdb");
st = "insert into Member_Datails(Name,Gender,Address,Contactno,Paid_Amount,Count_Remaining,Joining_date) values('" + name + "','" + gender + "','" + address + "','" + ContactNo + "','" + PaidAmount + "','" + SetThali + "','" + "','" + joiningdate + "')";
                    //SqlCommand cmd1 = new SqlCommand(st, con);
                    OleDbCommand cmd = new OleDbCommand(st, con);

                    cmd.ExecuteNonQuery();



want insert only selected field in access database
Posted

This is why you should use parametrized queries, and not concat string, which is an overhead in c#. Look here: "','" + "','". Do you see the problem? You have 7 parameters inside the parentheses, and 8 in the values section, because of this one.

Have a good look at this tutorial: https://derekreynolds.wordpress.com/2011/05/16/using-sqlcommand-and-addwithvalue-parameters-to-execute-sql-insert/[^], and rewrite your code before more you get more headacks...
 
Share this answer
 
v2
Oh dear...

The error message is pretty explicit:
Number of query values and destination fields are not the same.

So the number of fields you list in teh columns:
SQL
INSERT INTO (col1, col2, col3) ...
Does not match teh number of values you are passing through:
SQL
... VALUES 1,2,3,4

And if you cut out the "packaging" from your query it's pretty obvious:
C#
st = "insert into Member_Datails(Name,Gender,Address,Contactno,Paid_Amount,Count_Remaining,Joining_date) values('name','gender','address','ContactNo','PaidAmount','SetThali','','joiningdate')";
You have 7 columns named, and 8 values.

But...don't do it like that! Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
 
Share this answer
 
use parameterized query, it is more safe and you can avoid issues like incorrect data formats etc..
C#
insert into Member_Datails(Name,Gender,Address,Contactno,Paid_Amount,Count_Remaining,Joining_date) values(?,?,?,?,?,?,?)

change the sql statement as above and set the parameter values as the sequence given in the insert statement

C#
OleDbCommand cmd = new OleDbCommand("insert into Member_Datails(Name,Gender,Address,Contactno,Paid_Amount,Count_Remaining,Joining_date) values(?,?,?,?,?,?,?)", con);
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@Gender", gender);
cmd.Parameters.AddWithValue("@Address", address);
cmd.Parameters.AddWithValue("@Contactno", ContactNo );
cmd.Parameters.AddWithValue("@PaidAmount", PaidAmount);
cmd.Parameters.AddWithValue("@Count_Remaining", CountRemaining);
cmd.Parameters.AddWithValue("@Joining_date", joiningdate);
con.Open();
cmd.ExecuteNonQuery();
 
Share this answer
 
v3
Comments
Zoltán Zörgő 17-Jan-15 6:55am    
? and @...?
DamithSL 17-Jan-15 6:59am    
yes, this is OleDbCommand, please check remarks section of http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters%28v=vs.110%29.aspx[^]
parameter name does not matter when set the parameter values, need to add parameters in same order in the sql statement
Zoltán Zörgő 17-Jan-15 8:01am    
I don't really get the use of names in this case, but you'r right. I skipped OleDb :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900