For years I have been using in my programs statements like "SELECT * FROM user WHERE user_name=mgr", but I just learned that nowadays I have to use "user_name='mgr'". Now I am trying to verify that I also have to use "'" for Date and Number; the following code is giving me the error
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.4 user lacks privilege or object not found: DATE_CREATED
I am getting the same error if I don't use "'".
Similar code works fine for strings.
Your help will be greatly appreciated,
I just realized that you're using MS-Access. My condolences. Don't use it for web applications.
Yes, you do have to quote dates. Otherwise SQL will usually take something like 2019-05-08 to be an arithmetic expression with a value of 2006. Which isn't at all what you were expecting. Or, if you've defined the column as a date/time object in your schema, it may just yell at you.
For numbers, you don't quote them unless the number is defined in the database schema as a text value. In which case, you do put quotes around them, because they're really just text digits and not literally a number as far as the database is concerned.
When it comes to destroying a civilization, gas chambers cannot hold a candle to echo chambers.
Whether you use a single quote (') or not depends on the column type. Everything that is no a true number is surrounded by single quotes. What I mean by true numbers is that you can have a text column, but if you want to add the value 123 to it, you still need quotes. Said another way, it's not the data being inserted that you concern, but the type of the column.