C# MS-Access SQL INSERT INTO Error

Question

I wrote a set of codes for a C# program to insert a record into the Access database. Here's the code:

OleDbCommand cmd = new OleDbCommand("INSERT INTO Deployment ([DateTransacted], [ProductType], Brand, Model, SerialNo, Assignment) VALUES ('May 20, 2013', 'LAPTOP', 'ASUS', 'K55V', 'ABCD1234', '10F HRD',)", cnn);
cmd.CommandType = CommandType.Text;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();

The program is executable. However, an error popped up saying:

Syntax Error in INSERT INTO statement

The [DateTransacted] is a string type. What should I do?

Thanks for the edit Soner

James Kevin De Jesus
– James Kevin De Jesus

2013-05-20 06:15:52 +00:00
Commented May 20, 2013 at 6:15 — James Kevin De Jesus
– James Kevin De Jesus, Commented May 20, 2013 at 6:15
Is there any reason to use string to store DateTime?

Damith
– Damith

2013-05-20 06:22:09 +00:00
Commented May 20, 2013 at 6:22 — Damith
– Damith, Commented May 20, 2013 at 6:22

Habib · Accepted Answer · 2013-05-20 06:15:13Z

3

You have an extra comma , at the end of your insert statement.

OleDbCommand cmd = new OleDbCommand("INSERT INTO Deployment
([DateTransacted], [ProductType], Brand, Model, SerialNo, Assignment)
VALUES ('May 20, 2013', 'LAPTOP', 'ASUS', 'K55V', 'ABCD1234', '10F
HRD',)", cnn);
   ^^^

Remove that and you will be good to go, Your statement Should be

OleDbCommand cmd = new OleDbCommand("INSERT INTO Deployment
([DateTransacted], [ProductType], Brand, Model, SerialNo, Assignment)
VALUES ('May 20, 2013', 'LAPTOP', 'ASUS', 'K55V', 'ABCD1234', '10F
HRD')", cnn);

If you are using strinc concatenation to form the query then consider using parameterized query, that will also save you from Sql Injection

answered May 20, 2013 at 6:15

Habib

224k30 gold badges420 silver badges446 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Kevin De Jesus Over a year ago

Thank you, Habib. I didn't see that because I got desperate when the program keeps showing errors.

Damith · Accepted Answer · 2013-05-21 06:22:42Z

remove the , at the end of sql statement

"INSERT INTO Deployment ([DateTransacted], [ProductType], Brand, Model, SerialNo, Assignment) VALUES ('May 20, 2013', 'LAPTOP', 'ASUS', 'K55V', 'ABCD1234', '10F HRD')"

Better to use Parameters and using blocks as below,

using(var con = new OleDbConnection(connectionString))
using (OleDbCommand cmd = 
    new OleDbCommand(@"INSERT INTO Deployment ([DateTransacted], [ProductType], Brand, Model, SerialNo, Assignment) VALUES (?,?,?,?,?,?)", con))
{
    con.Open();
    cmd.Parameters.AddWithValue("@p1", "May 20, 2013"); // if you have date time column give DateTime object as value
    cmd.Parameters.AddWithValue("@p2", "LAPTOP");
    cmd.Parameters.AddWithValue("@p3", "ASUS");
    cmd.Parameters.AddWithValue("@p4", "K55V");
    cmd.Parameters.AddWithValue("@p5", "ABCD1234");
    cmd.Parameters.AddWithValue("@p6", "10F HRD");
    cmd.ExecuteNonQuery();
}

Soner Gönül · Accepted Answer · 2013-05-20 06:25:19Z

1

Delete this unnecessary comma;

OleDbCommand cmd = new OleDbCommand("INSERT INTO Deployment ([DateTransacted], [ProductType], Brand, Model, SerialNo, Assignment) VALUES ('May 20, 2013', 'LAPTOP', 'ASUS', 'K55V', 'ABCD1234', '10F HRD',)", cnn);
                                                                                                                                                                                                        ^^ Delete it

You can use this;

OleDbCommand cmd = new OleDbCommand("INSERT INTO Deployment(DateTransacted, ProductType, Brand, Model, SerialNo, Assignment)
VALUES ('May 20, 2013', 'LAPTOP', 'ASUS', 'K55V', 'ABCD1234', '10FHRD')", cnn);

By the way, DateTransacted and ProductType are not reserved words on ms access. You don't need to use them with square brackets.

And more important, you should always use parameterized queries. This kind of code are open for SQL Injection attacks.

edited May 20, 2013 at 6:25

answered May 20, 2013 at 6:17

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

1 Comment

Soner Gönül Over a year ago

@JamesKevinDeJesus You are welcome ;) Read How to Say Thanks in an Answer

Freelancer · Accepted Answer · 2013-05-20 06:18:06Z

1

Use Parametrized Query to avoid all syntax faults and avoid SQL injections.

Code should be:

OleDbCommand cmd = new OleDbCommand("INSERT INTO Deployment VALUES (@dateOfTran, @prodType, @Brand, @Model, @serNum, @assignment)", cnn); 
cmd.CommandType = CommandType.Text; 
cnn.Open(); 
cmd.parameters.AddWithValue("@dateOfTran",'May 20, 2013');
cmd.parameters.AddWithValue("@prodType",'LAPTOP');
.
.
.
cmd.ExecuteNonQuery(); 
cnn.Close();

answered May 20, 2013 at 6:18

Freelancer

9,1027 gold badges46 silver badges81 bronze badges

1 Comment

James Kevin De Jesus Over a year ago

I'd like to try the codes in another project. Thanks for your help, Freelancer.

Collectives™ on Stack Overflow

C# MS-Access SQL INSERT INTO Error

4 Answers 4

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related