0

I'm trying to insert values into an Access database with C#, but only some of the data is being inserted. All of the data types in the database and the C# code are correct.

This code below throws an error:

Syntax ERROR in INSERT INTO statement

Code:

string insertStatement =
             "INSERT INTO Car (Category, Model, CarYear, Mileage, CostPerDay, Availability, Location, Image, Manufacturer, Description) " +
             "VALUES (@Category, @Model, @CarYear, @Mileage, @CostPerDay, @Availability, @Location, @Image, @Manufacturer, @Description)";

OleDbConnection connection = new OleDbConnection(CarRentalDB.ConnectionString);
OleDbDataAdapter adapter = new OleDbDataAdapter(insertStatement, connection);
OleDbCommand command = new OleDbCommand(insertStatement, connection);

command.Parameters.AddWithValue("@Category", car.category.ToString());
command.Parameters.AddWithValue("@Model", car.model);
command.Parameters.AddWithValue("@CarYear", car.year);
command.Parameters.AddWithValue("@Mileage", car.mileage);
command.Parameters.AddWithValue("@CostPerDay", car.costPerDay);
command.Parameters.AddWithValue("@Availability", car.availability);
command.Parameters.AddWithValue("@Location", car.location);
command.Parameters.AddWithValue("@Image", car.image);
command.Parameters.AddWithValue("@Manufacturer", car.manufacturer);
command.Parameters.AddWithValue("@Description", car.description);

connection.Open();

command.ExecuteNonQuery();

However, if I get rid of the Image, Manufacturer, and Description then it executes with no errors. Here is the code that does not return errors and inserts only some of the data:

string insertStatement = 
           "INSERT INTO CAR (Category, Model, CarYear, Mileage, CostPerDay, Availability, Location)" +
           " VALUES (@Category, @Model, @CarYear, @Mileage, @CostPerDay, @Availability, @Location)";

OleDbConnection connection = new OleDbConnection(CarRentalDB.ConnectionString);
OleDbDataAdapter adapter = new OleDbDataAdapter(insertStatement, connection);
OleDbCommand command = new OleDbCommand(insertStatement, connection);

command.Parameters.AddWithValue("@Category", car.category.ToString());
command.Parameters.AddWithValue("@Model", car.model);
command.Parameters.AddWithValue("@Category", car.category.ToString());
command.Parameters.AddWithValue("@Model", car.model);
command.Parameters.AddWithValue("@CarYear", car.year);
command.Parameters.AddWithValue("@Mileage", car.mileage);
command.Parameters.AddWithValue("@CostPerDay", car.costPerDay);
command.Parameters.AddWithValue("@Availability", car.availability);
command.Parameters.AddWithValue("@Location", car.location);

connection.Open();

command.ExecuteNonQuery();

Here is what the data looks like (excuse the multiple entries of the same data)

Thank you for your time!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
los
  • 11
  • 2

2 Answers2

1

If there's a syntax error then that's almost certainly because you are using a reserved word as an identifier. I'm sure you can find a list of Access reserved words if you look but it's likely Image and/or Description, based on your explanation. Wrap those identifiers in brackets

, [Image], Manufacturer, [Description])

and you should be good to go. You can try each one individually to see if it's just one of them - Image is almost certainly a problem but not sure about Description. Note that you can always just wrap all your identifiers in brackets and you'll never have this issue.

jmcilhinney
  • 50,448
  • 5
  • 26
  • 46
0

If there's a syntax error then that's almost certainly because you are using a reserved word as an identifier. I'm sure you can find a list of Access reserved words if you look but it's likely Image and/or Description, based on your explanation. Wrap those identifiers in brackets

, [Image], Manufacturer, [Description]) and you should be good to go. You can try each one individually to see if it's just one of them - Image is almost certainly a problem but not sure about Description. Note that you can always just wrap all your identifiers in brackets and you'll never have this issue.

Chris
  • 11
  • 2