CODEWITHSUNDEEP
c#ms-accessoledb
James Kevin De Jesus
James Kevin De Jesus

Reputation: 73

C# MS-Access SQL INSERT INTO Error

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?

Upvotes: 1

Views: 1201

Answers (4)

Damith
Damith

Reputation: 63065

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();
}

Upvotes: 2

Soner Gönül
Soner Gönül

Reputation: 98750

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.

Upvotes: 1

Freelancer
Freelancer

Reputation: 9074

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();

Upvotes: 1

Habib
Habib

Reputation: 223257

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

Upvotes: 3

Related Questions