Exception on inserting into Access 2010 in C Sharp

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,Ser开发者_JS百科viceAddress,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.

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

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

  ''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