Solved

Microsoft OLE DB Provider for SQL Server error '80040e14' Incorrect syntax near the keyword 'User'

Posted on 2015-02-02
4
503 Views
Last Modified: 2015-03-25
Hi experts
i am trying to add a new record in the table but i am getting a strange error
Microsoft OLE DB Provider for SQL Server error '80040e14' Incorrect syntax near the keyword 'User'.

my code is

set chqOrs = Server.CreateObject("Adodb.Recordset")
    sql="Select * from personal"
    chqOrs.Open sql,oConn,1,3
    chqOrs.AddNew
    chqOrs("cprno")="00000005"
    chqOrs.Update

Open in new window


i dont know from where 'User' come ?
please help
0
Comment
Question by:AZZA-KHAMEES
4 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40585643
Since I don't see a schema for the table, possibilities are...
There's a trigger on the table that threw a syntax error based on User (likely)
personal is a view and not a table, and either not updatable or requiring User to be populated.

As an aside, you're wasting processing time loading a SELECT * query into a recordset object if all you're doing is inserting a row.
0
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 40585644
Hi,
I guess that you have a column named User which is a reserved SQL word.
As you first run a Select * you will get the column names without square brackets which is normally the eay to escape reserved column names.
Please try to replace the SELECT * with
SELECT [Col1], [User],[Col3], ...
If you just need to add the record and just set one field value, this one column should be enough.
HTH
Rainer
0
 
LVL 8

Expert Comment

by:Naitik Gamit
ID: 40585653
The problem is with sql statements,try another way for exp:
use full path of your table

like,
sql="Select * from dbo.personal"
or
sql="Select * from [user].[dbo].personal" as per your database
0
 

Expert Comment

by:DMontgomeryNGC
ID: 40687138
Sorry for the delays.  Hopefully the network group will get to it today.  I am also meeting with Mgrs. today to discuss the issue.  I will let you know the outcome.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now