Solved

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

Posted on 2015-02-02
4
661 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

813 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

10 Experts available now in Live!

Get 1:1 Help Now