CODEWITHSUNDEEP
c#ms-accessexception
slao
slao

Reputation: 2076

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,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.

Upvotes: 1

Views: 1171

Answers (3)

k3b
k3b

Reputation: 14755

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

Upvotes: 0

Renaud Bompuis
Renaud Bompuis

Reputation: 16786

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

Upvotes: 1

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171411

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

Upvotes: 1

Related Questions