Why does it say there's an error in SQL statement Insert into

Question

I am learning to connect to Microsoft Access and write into the database. I created a database in Access and would like to insert data into it.

 OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Amin\Desktop\NamesDatabase1.accdb");

 OleDbCommand cmd = new OleDbCommand();
 string FN = textBox1.Text;
 string LN = textBox2.Text;

 cmd.CommandText = "INSERT INTO Names (FirstName, LastName) Values (FN,LN)";
 cmd.Connection = conn;

 conn.Open();
 cmd.ExecuteNonQuery();

Before I run there seems to be no error. I run the program, update my textBox1.text and textBox2.text and press SAVE, then I get an error:

SYNTAX ERROR in SQL statement Insert into

What did I do wrong??

You need to get into the habit of using parameters like this: http://stackoverflow.com/questions/5893837/using-parameters-inserting-data-into-access-database which will solve this issue and keep you safe from SQL Injection. — Alex K., Apr 06 '17 at 15:35
As it stands the query fails because FN/LN are not interpolated into the string automagically. — Alex K., Apr 06 '17 at 15:35

Pikoh · Answer 1 · 2017-04-06T15:38:36.947

3

In your insert you are trying to add as Values FN and LN. Those values does not exists in the command scope, so it does not know what tu do with it. What you want is instead set those values as parameters and then add them with their value to the command:

OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Amin\Desktop\NamesDatabase1.accdb");
OleDbCommand cmd = new OleDbCommand();
string FN = textBox1.Text;
string LN = textBox2.Text;
cmd.CommandText = "INSERT INTO Names (FirstName, LastName) Values (@FN,@LN)";
cmd.Connection = conn;
cmd.Parameters.Add(new OleDbParameter("@FN", FN));
cmd.Parameters.Add(new OleDbParameter("@LN", LN));
conn.Open();
cmd.ExecuteNonQuery();

edited Apr 06 '17 at 15:38

answered Apr 06 '17 at 15:35

Pikoh

7,582
28
53

1

oledb needs `?` rather than `@IDENTIFIER` placeholders I think – Alex K. Apr 06 '17 at 15:36
No, it works perfectly with @ if you are using Access. The important thing is to keep the adding to the collection in the same order of the placeholders. – Steve Apr 06 '17 at 15:37
@AlexK. it depends on the driver – Scott Chamberlain Apr 06 '17 at 15:38
I think it would work like this as others have said. The important thing is the order – Pikoh Apr 06 '17 at 15:39
1

I would just like to comment for other users: I have seen many posters say that they didn't use parameters because they were harder to use. I think this answer demonstrates that parameters are just as easy as using concatenated strings and much safer. In fact, I think it is easier! – Chris Dunaway Apr 06 '17 at 19:14
I totally agree @ChrisDunaway. Parameters are easier and safer. Lots of questions here about inserting datetimes would be solved just by using parameters. – Pikoh Apr 06 '17 at 19:40

score 0 · Answer 2 · answered Apr 06 '17 at 15:37

You code is breaking because FN and LN are C# variables and you are trying to pass them to query as it is. That does not result in correct syntax in SQL.

You need to pass FN and LN as parameters to the query.

Change you code to following.

OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Amin\Desktop\NamesDatabase1.accdb");
OleDbCommand cmd = new OleDbCommand();
string FN = textBox1.Text;
string LN = textBox2.Text;
cmd.CommandText = "INSERT INTO Names (FirstName, LastName) Values (@FN,@LN)";
cmd.Parameters.Add(new OleDbParameter("@FN", FN));
cmd.Parameters.Add(new OleDbParameter("@LN", LN));
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();

This should resolve your issue.

Why does it say there's an error in SQL statement Insert into

2 Answers2