Exception on inserting into Access 2010 in C Sharp

Question

I am getting this exception when inserting into a Access 2010 database.

Example, the following :

INSERT INTO CranbrookMain (
       ID,BlockNo,Plot,SubPlot,Code,Type,LastName,FirstName,
       ServiceHome,ServiceAddress,ServiceCity,
       Notes
) VALUES (
       '1','Y','37','DS','C2','O','SMITH','John',
       'Service Inc.','520B SLATER ROAD N.W.','CityName',
       'CityName                          ☺    '
)

Results in the exception:

Ex: System.Data.OleDb.OleDbException (0x80040E14): 
Syntax error in string in query expression ''CityName                          ☺'.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at ReadingData.Program.Main(String[] args) in C:\Users\user\documents\visual studio 2010\Projects\ReadingData\ReadingData\Program.cs:line 238

The code that executes the SQL insert query is:

insertSQL = "INSERT INTO CranbrookMain (ID,BlockNo,Plot,SubPlot,Code,Type,LastName," +
            "FirstName,ServiceHome,ServiceAddress,ServiceCity,Notes) VALUES (" +
            "'"+id+ "','" + blockNo + "','" + plot + "','" + subPlot + "','" + code + 
            "','" + type + "','" + lastname + "','" + firstname + "','" + serviceHome +
            "','" + serviceAddress + "','" + serviceCity + "','" + notes +"')";

OleDbCommand cmd = new OleDbCommand(insertSQL, con); // creating query command
cmd.ExecuteNonQuery();

The error occurs in cmd.ExecuteNonQuery() function call.

The above SQL INSERT statement works fine if I directly execute in the Access 2010 file.

Whats the sad face smiley doing near your city name?

Shoban
– Shoban

2010-12-22 17:42:31 +00:00
Commented Dec 22, 2010 at 17:42 — Shoban
– Shoban, Commented Dec 22, 2010 at 17:42
Thats from the file which has binary data in it.

slao
– slao

2010-12-22 17:44:09 +00:00
Commented Dec 22, 2010 at 17:44 — slao
– slao, Commented Dec 22, 2010 at 17:44
File??? Yo are passing a file for city??

Shoban
– Shoban

2010-12-22 17:57:20 +00:00
Commented Dec 22, 2010 at 17:57 — Shoban
– Shoban, Commented Dec 22, 2010 at 17:57

D'Arcy Rittich · Accepted Answer · 2010-12-22 17:37:17Z

1

Use parameterized queries and you won't have this issue, nor will you be exposed to SQL injection attacks.

answered Dec 22, 2010 at 17:37

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

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

Comments

Renaud Bompuis · Accepted Answer · 2010-12-26 09:16:28Z

As RedFilter said, you should not construct your SQL the way you're doing it.
The issue is that your Notes field contains data that is not properly represented in a way that can be parsed by the database driver.

What do you think will happen if any of your values contains apostrophes? Say for instance, you're trying to save a record for Mr O'Reilly living on 22 Queen's Road)?
You'll either get garbage in your database or you will get exceptions, or, much worse, someone could easily inject some SQL and hack your database.

Instead, do something like:

insertSQL = "INSERT INTO CranbrookMain (ID,BlockNo,Plot,SubPlot,Code,Type,"
          + "LastName,FirstName,ServiceHome,ServiceAddress,ServiceCity,Notes) "
          + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";

OleDbCommand cmd = new OleDbCommand(insertSQL, con);
cmd.Parameters.Add("ID", OleDbType.Integer).Value = id;
cmd.Parameters.Add("BlockNo", OleDbType.Char, 2).Value = blockNo;
cmd.Parameters.Add("Plot", OleDbType.Char, 3).Value = plot;
cmd.Parameters.Add("SubPlot", OleDbType.Char, 3).Value = subPlot;
cmd.Parameters.Add("Code", OleDbType.Char, 3).Value = code;
cmd.Parameters.Add("Type", OleDbType.Char, 3).Value = type;
cmd.Parameters.Add("LastName",OleDbType.Char, 50).Value = lastname;
cmd.Parameters.Add("FirstName", OleDbType.Char, 64).Value = firstname;
cmd.Parameters.Add("ServiceHome", OleDbType.Char, 50).Value = serviceHome;
cmd.Parameters.Add("ServiceAddress", OleDbType.Char, 128).Value = serviceAddress;
cmd.Parameters.Add("ServiceCity", OleDbType.Char, 50).Value = serviceCity;
cmd.Parameters.Add("Notes", OleDbType.Char, 255).Value = notes;

cmd.ExecuteNonQuery();

+1 @RedFilter gave the answer, you provided the code example for it

k3b · Accepted Answer · 2010-12-26 09:17:12Z

0

  ''CityName                          ☺'

It looks like your notes variable contains an apostophe >'< making your SQL invalid something like

  notes = "'CityName                          ☺"

As @RedFilter and @Renaud Bompuis said "Use parameterized queries" instead of concatenating strings

answered Dec 26, 2010 at 9:17

k3b

14.8k7 gold badges56 silver badges88 bronze badges

Collectives™ on Stack Overflow

Exception on inserting into Access 2010 in C Sharp

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related