Проблемы
Ошибка во время выполнения «-2147217900 (80040e14)»: [Microsoft] [драйвер SQL Server ODBC] [SQL Server] в запросе используются операторы внешнего соединения, не относящиеся к ANSI («* =» или «= *»). Чтобы выполнить этот запрос без изменения, установите для свойства уровень совместимости текущей базы данных значение 80 или ниже, используя sp_dbcmptlevel хранимой процедуры. Настоятельно рекомендуется переписать запрос с использованием операторов внешнего соединения ANSI (левое ВНЕШНее соединение, ПРАВОе ВНЕШНее соединение). В будущих версиях SQL Server операторы соединения, не относящиеся к ANSI, не поддерживаются даже в режимах обратной совместимости, эта ошибка возникает в одном из следующих трех экземпляров.
-
FDM 6,0 и 7,0 — ошибка в ФИНАНСОВом масштабе в формате строки для базы данных SQL 2005.
-
FRL13, FDM 6,0 и 7,0 — ошибка при запуске мастера отчетов для базы данных SQL 2005.
-
Отчеты с эталонными кодами, TREF, TPROJ получать ошибки для SQL 2005 DB.
Статус
Этот SMR был исправлен в пакете обновления для R07670 и последующих пакетах обновления, а также на веб-сайте (www.FRxSoftware.com) для обеспечения доступности пакетов обновления для главной книги. Вы также можете зарегистрироваться для автоматического уведомления о службах на нашем веб-сайте.
Обходное решение
Чтобы обойти эту ошибку, выполните указанные ниже действия, чтобы установить уровень совместимости базы данных в 80:
-
В корпоративном диспетчере щелкните правой кнопкой мыши базу данных. Выберите пункт Свойства.
-
Откройте вкладку Параметры.
-
Измените уровень совместимости на 80.
Ссылки
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
I am trying to insert record using for each request.form
And output is:
«INSERT INTO invdat (dispid,disdate,custid) VALUES (‘2, 4, 5, 6, 7, 9’,(‘3/13/2011, 3/13/2011, 3/13/2011, 3/13/2011, 3/14/2011, 3/14/2011’,»)
Microsoft JET Database Engine error ‘80040e14’
Syntax error (comma) in query expression ‘(‘3/13/2011, 3/13/2011, 3/13/2011, 3/13/2011, 3/14/2011, 3/14/2011′,»)’. «
Please help
Joel Coehoorn
397k113 gold badges568 silver badges794 bronze badges
asked Mar 15, 2011 at 7:06
3
«INSERT INTO invdat (dispid,disdate,custid) VALUES (‘2, 4, 5, 6, 7, 9’,(‘3/13/2011, 3/13/2011, 3/13/2011, 3/13/2011, 3/14/2011, 3/14/2011’,») )»
You might have left out the closing bracket, so it was off balance?
answered Mar 15, 2011 at 7:20
1
"INSERT INTO invdat (dispid,disdate,custid) VALUES ('2, 4, 5, 6, 7, 9','3/13/2011, 3/13/2011, 3/13/2011, 3/13/2011, 3/14/2011, 3/14/2011','')«
Try this. You can not use ( within values(....)
Benjamin
11.5k13 gold badges70 silver badges119 bronze badges
answered Mar 15, 2011 at 13:46
GateGate
4954 silver badges11 bronze badges
User1051635487 posted
Hi All ,
I created a database with all text fields. I am using asp to enter the data into access
INSERT INTO Form (fname, lname, sID, fl, email, job, employer, Position, grade, degreename, degreetime, degreePlace ) VALUES (‘chris’,’v’,’12323′,’cv’,’cv@cv.com’,’Yes’,’Uc’,’Developer’,’Yes’,’test’,’2010′,’Uc’)
Above is the sql I am trying to insert [ Response.write(sSql)]
But it is failing and I am not able to insert the data in the table.
I am getting Microsoft JET Database Engine error ‘80040e14’ Syntax error in INSERT INTO statement Error.
Please Advice
What this is referring too, is the JOIN Operation of a Table-Set that I am tring to make.
I am learning this part of ASP, so please understand that rather the code is done incorrectly or not.
I am learning, so give me credit for this if nothing else.
————
The code is joining 4-Tables together.
Can someone please tell me what I have done incorrectly on this issue?
Thank You
Carrzkiss
Starting from the «Sierra.Product_Description FROM
Is where I started editing the original code to add in the new [Table.Fields]
New Table is: BrushStrokes
‘===========================================================
sub db_select_view_Sierra
if do_search = «1» then
request_view_Sierra
view_Sierra_sql = «SELECT » & _
«Cats.Cat, » & _
«Sierra.CatID, » & _
«Sierra.ContentID, » & _
«Sierra.Product_Title, » & _
«Cats.CatTypeID, » & _
«Cats.CatDescription, » & _
«Sierra.Product_Th_Image, » & _
«Sierra.Our_Price, » & _
«Sierra.Aff_Name, » & _
«Sierra.Retail_Price, » & _
«Sierra.Brand_Name, » & _
«Sierra.YAvailable, » & _
«Sierra.Retail_Price, » & _
«Sierra.Percent_Savings, » & _
«Sierra.Unique_Product_ID, » & _
«Sierra.Main_Cat, » & _
«BrushStrokes.BSContentID, » & _
«BrushStrokes.BSProduct_Name, » & _
«BrushStrokes.BSDescription, » & _
«BrushStrokes.BSCol_Cat, » & _
«Sierra.Product_Description FROM (((ContentTypes RIGHT JOIN Sierra ON ContentTypes.contenttypeid = Sierra.contenttypeid)» & _
«LEFT JOIN Cats ON Sierra.catid = Cats.catid) » & _
«LEFT JOIN Cats ON BrushStrokes.catid = Cats.catid)» & _
«WHERE Display=1 AND (Sierra.ContentId LIKE ‘%» & keywords & «%’ OR » & _
«Sierra.Product_Title LIKE ‘%» & keywords & «%’ OR Sierra.Product_Description » & _
«LIKE ‘%» & keywords & «%’ OR Sierra.Main_Cat LIKE ‘%» & keywords & «%’ OR BrushStrokes.BSContentID LIKE ‘%» & keywords & «%'» & _
«OR BrushStrokes.BSProduct_Name LIKE ‘%» & keywords & «%’ OR BrushStrokes.BSDesription LIKE ‘%» & keywords & «%'» & _
«OR BrushStrokes.BSCol_Cat LIKE ‘%» & keywords & «%’ ) ORDER BY Product_Title»
‘===========================================================
01 May 2008 11:06
This short article deals with the following common MS Access-related error messages:
- Syntax error (missing operator) in query expression ‘field=’some_partial_string’
- Syntax error in INSERT INTO statement
- Syntax error in UPDATE statement
- Syntax error in FROM clause
- Syntax error in WHERE clause
There are 4 main causes for these errors: using a Reserved Word for a field name; embedded spaces in field or table names; attempting to insert unescaped single quotes; and incorrectly delimited datatypes. There is a fifth cause, and that is a genuine syntax error resulting from a typo, or otherwise misconstructed SQL statement. Assuming that you are sure this fifth cause is not applicable in your case, here’s how to deal with the other four.
Reserved Words and Embedded Spaces
The most common culprits among reserved words are NAME and PASSWORD, which at first glance appear to be perfectly reasonable choices for field names in, say, a User table. What can be more confounding is that PASSWORD doesn’t appear on this list of reserved words in Access. However, the fact is that in an ASP.NET application, you aren’t dealing with an Access database. It’s actually a Jet 4.0 database, and there is a separate list of reserved words for Jet 4.0 that does indeed include PASSWORD (but not NAME). Best advice is to familiarise yourself with both lists (and the one for Sql Server Reserved Words too) and avoid using any of them as a habit. However, if you can’t change your field names, you will have to srround them with [ ] brackets:
Select [name], [password], emailaddress From Users
The same resolution applies to embedded spaces in field names: change them or surround them in [ ] brackets:
Select [user name], userpassword, [email address] From Users
When using the Query Builder within Visual Studio or Visual Web Developer, you will find that all field names are surrounded by brackets by default.
Unescaped Single Quotes and Incorrect Datatype Delimiters
The third and fourth causes of these errors will be totally resolved if you use parameters in your code. Single quotes act as string delimiters in Jet SQL, so when you try to pass a vlaue that contains a quote, such as a name like O’Brien, or a piece of text like «It’s a lovely day», the Jet engine baulks and throws an error. It thinks that the apostrophe or single quote is telling it that the string value to be passed has ended, and anything afterwards should be treated as legitimate SQL.
In the same way that single quotes delimit string values, other datatypes also have their own delimiters:
| text/string | single quote | ‘ |
| numeric | none | |
| datetime | single quote or octothorpe | ‘ or # |
If you get these wrong, a Syntax error will be thrown. Paramterised queries solve the problem, because the Jet engine infers the datatype from the database field, and treats the value being passed in the correct way. This means that things like «O’Brien» are treated as literal strings. You will see advice from some people recommending that you use some form of string.Replace() method to double any single quotes, which works by escaping them. While this works, it’s a poor idea.