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

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
LVL 1
AZZA-KHAMEESAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Rainer JeschorCommented:
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
 
Naitik GamitSoftware DeveloperCommented:
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
 
DMontgomeryNGCCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.