Insert data into database error

james james

9y2kLearn iOS Programming

I am getting an error while inserting data.

The error is "Number of query values and destination fields are not the same".

I have read alot about ppl missing some single qoutes but I must be overlooking them.

My code :

private void btn_Insert_Click(object sender, EventArgs e)

{

con.Open();

OleDbCommand cmd = con.CreateCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "insert into Table values('" + txt_Date + "','" + txt_Units + "','" + txt_TimeOrd + "','" + txtTimeRec + "','" + txt_Panel + "', '" + txt_Box +"','" + txt_Name + "', '" + txt_Notes +"')";

cmd.ExecuteNonQuery();

con.Close();

MessageBox.Show("Record inserted Successfully");

}

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ankit Sahu9y ago
Accepted answer
Dear Friend,

Hare Column1,Column1 are Sql Server table Coulum Name you replace these column with table column names like Name ,Notes, Panel

And @Column1,@Column1 are values you give any name like @txt_Date,@txt_Units ...etc.
private void btn_Insert_Click(object sender, EventArgs e)
{
con.Open();
OleDbCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into tableName (column1 , column2) values (@column1 , @column2)" ;
cmd .Parameters.AddWithValue("@column1", txt_Date.Text );
cmd .Parameters.AddWithValue("@column2", txt_Units.Text);
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Record inserted Successfully");
}
For more information check below link ADO.NET tutorial for beginners
https://www.youtube.com/watch?v=aoFDyt8oG0k&list=PL6n9fhu94yhX5dzHunAI2t4kE0kOuv4D7
0
james james9y ago
Ankit

thank you for the example i still get an error:

The INSERT INTO statement contains the following unknown field name: 'column1'. Make sure you have typed the name correctly, and try the operation again.

I am new how would i see the Column number to the name? when i look at the Access file it gives just the name not column number.
0
Ankit Sahu9y ago
Dear Friend,

You use this

cmd.CommandText = "insert into Table values('" + txt_Date.Text + "','" + txt_Units.Text + "','" + txt_TimeOrd.Text + "','" + txtTimeRec.Text + "','" + txt_Panel.Text + "', '" + txt_Box.Text+"','" + txt_Name.Text + "', '" + txt_Notes.Text +"')";

Please avoid this type of query.

always use parameterized query like this....

cmd.CommandText = "insert into tableName (column1 , column2) values (@column1 , @column2)" ;

And Add parameter like this....

cmd .Parameters.AddWithValue("@column1", txt_Date.Text );
cmd .Parameters.AddWithValue("@column2", txt_Units.Text);

For Example :-
private void btn_Insert_Click(object sender, EventArgs e)
{
con.Open();
OleDbCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into tableName (column1 , column2) values (@column1 , @column2)" ;
cmd .Parameters.AddWithValue("@column1", txt_Date.Text );
cmd .Parameters.AddWithValue("@column2", txt_Units.Text);
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Record inserted Successfully");
}
0
Saddam Mohd9y ago
hi james,

Please avoid this type of query.

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.

and second point you missed connectioname to define in query.

ex.

cmd.CommandText = "insert into Table (Name, Salary, address) values('" + txtName + "','" + txtSalary + "','" + txtAddress + "',con)";
0
Amit Gupta9y ago
Hey

Avoid building query using string concatenation, use parameterized query to make your code cleaner and avoid injections.

String concatination always leads to confusion and hard to maintain

Updated:
However, the exception occurs when columns in your database is more than you are passing from the code behind.

Match the number of columns when you are using
Insert into tablename values(..)

If you pass the columnName with values then your query will be executed wiyhout any error like
Insert into tableName (column1, column2) values (column1Value, column2Value).
0