syntax error while inserting data into ms access table

Question

I have a following code:

OleDbConnection aConnection = new
                    OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
                                 Data Source=storage_db.accdb");
string sql = "INSERT INTO Client cname,phone,password)
                           VALUES('"+textBox1.Text+"','"+textBox2.Text+"',
                                  '"+textBox3.Text+"')";
aConnection.Open();
OleDbCommand insert = new OleDbCommand(sql, aConnection);
insert.ExecuteNonQuery();
aConnection.Close();

But when I try to insert data I am getting an exception syntax error in insert query. What am I doing wrong?

Use parameter queries. The one that you are using is more insecure. — Dhrumil, May 21 '15 at 09:59

score 2 · Answer 1 · answered May 21 '15 at 11:54

Password is a reserved word in Access so you need to wrap the password column in square brackets in your query. You also need to wrap the columns you're inserting data into in parentheses:

INSERT INTO (Client cname,phone,[password]) VALUES(...

As @HarveySpecter points out in the comments you should also use a parameterized query rather than concatenating user input otherwise you're opening yourself up to SQL injection attacks.

Ella S. · Answer 2 · 2015-05-22T07:55:29.173

1

Try this:

string sql = "INSERT INTO Client (cname, phone, [password])
VALUES('"+textBox1.Text+"','"+textBox2.Text+"','"+textBox3.Text+"')";

You forget parentheses. But better use parameter queries.

edited May 22 '15 at 07:55

answered May 21 '15 at 09:58

Ella S.

138
1
1
7

And write here the result sql query from stack trace, for example `INSERT INTO Client (cname, phone, password) VALUES('text1','text2','text3'). Maybe error in your text data... – Ella S. May 21 '15 at 10:08
uncoat exception type "System.Data.OleDb.OleDbException" in System.Data.dll additional information: syntax error in INSERT INTO instruction. – user3340565 May 21 '15 at 10:11
What is Client table structure? All textboxes has values? – Ella S. May 21 '15 at 10:18
Yes texboxes all feeled/ Table client has 4 culumns id, cname, phone and password. id is autoincrement all other are type shorttext. – user3340565 May 21 '15 at 10:34

score 1 · Answer 3 · answered May 21 '15 at 12:09

Your insert syntax is incorrect... you need to () around both the fields you are inserting AND the values clause...

insert into Client
   ( cname, phone, [password] )
   values
   ( ?, ?, ? )

The "?" are place-holders for the parameters and the parameter statements must be in the same order as the "?" represent. Also, note... if the values are expected to be numeric, date, etc, make sure the values you are setting in the parameter are the correct data type too. But in your case, these are all string-based.

OleDbCommand insert = new OleDbCommand(sql, aConnection);
insert.Parameters.Add( "parmName", textBox1.Text );
insert.Parameters.Add( "parmPhone", textBox2.Text );
insert.Parameters.Add( "parmPwd", textBox3.Text );

syntax error while inserting data into ms access table

3 Answers3

Linked

Related