MS Access SQL Insert Query

Question

I'm sure I'm doing something stupid, but I can seem to figure out the problem with this insert query. I have some experience with SQL Server, but unfortunately am forced to use Access for this project (which I am new to). At this point I have tried manually inserting into Access (which worked), and then copying the exact query into Visual Studio and I'm still getting an insert syntax error. I am able to insert into other tables in this same test program, but I have not been able to get this query to work.

The table I am trying to insert into is set up as:

ID - Int Primary Key
time_series_id Int 
open decimal
high decimal
low decimal
close decimal
volume int
observation_date Date/Time

The manual query I tried is:

queryString = "INSERT INTO daily_prices (time_series_id, open, high, low, close, volume, observation_date) VALUES(13, 3036.75, 3045.72, 3023.27, 3027.52, 4894428992, '2013-09-24')";

command = new OleDbCommand(queryString, conn);
command.ExecuteNonQuery();

The query was also originally formulated the following way:

queryString = String.Format(@"INSERT INTO daily_prices (time_series_id, open, high, low, close, volume, observation_date) VALUES ({0}, {1} ,{2} ,{3} ,{4} ,{5} ,'{6}')", newId, open, high, low, close, volume, date);

Any help would be appreciated here. I'm sure it's a dumb mistake, but I am at a bit of a loss since I am able to execute the query in access, and then the same query fails in C#.

An Insert Syntax error, which is why I looked in the wrong place for so long I think. I checked the Access reserved words, but not the SQL reserved words. — Eric Brady
– Eric Brady, Commented Sep 25, 2013 at 22:44

Steve · Accepted Answer · 2013-09-25 22:37:28Z

1

The word OPEN and CLOSE are reserved keywords for Jet-SQL (Jet 4.0). Use square brackets around them (or change the column name if possible)

queryString = "INSERT INTO daily_prices (time_series_id, [open], high, low, " + 
              "[close], volume, observation_date) VALUES " + 
              "(13, 3036.75, 3045.72, 3023.27, 3027.52, 4894428992, '2013-09-24')";

answered Sep 25, 2013 at 22:37

Steve

217k22 gold badges242 silver badges296 bronze badges

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

2 Comments

Eric Brady Over a year ago

I looked up reserved words before posting this but must have been looking at the wrong list. I knew it would be stupid. Thanks Steve.

Steve Over a year ago

This is the list for JET 4.0, notice how these words are still reserved for ACE, but you need to scroll down to see them. To me this is just bad impagination on MS side

Collectives™ on Stack Overflow

MS Access SQL Insert Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related