Insert issue

[insert into MS Access issue]

I have the following code in an ASP page that is processing a form to create a new user. I keep getting the following error

Microsoft JET Database Engine (0x80040E14
Syntax error in INSERT INTO statement

The database is MS Access, the latest, I am able to perform select statements on it and do everything except write to the database. The insert statement created is as follows

insert into UserTable ( UserName, Password, EmployeeID, AccessRight, EmailAddress, ProjectID ) values ('why' ,'why' ,'121212' , 0, 'why@c.com' ,1

Where everything is text except, AccessRight and ProjectID which are numbers (long integer).

I have taken care of the permissions issue, I am not sure what I am doing wrong, any help would be appreciated ! Code follow

thank
Car

set dbConn = server.CreateObject("adodb.connection"
set recLgn = server.CreateObject("adodb.RecordSet"

' open the connectio
dbConn.Mode =
dbConn.Open Application("Database"
strDB = Application("Database"
strSQL = "insert into UserTable ( UserName, Password, EmployeeID, AccessRight, EmailAddress, ProjectID )values"
strSQL = strSQL & " ('" & user & "' ,'" & pass & "' ,'" & CSTR(employeeID) & "' , " & adminPriv & ", '" & eAddress & "' ," & nProjID & ")
dbConn.Execute strSQ

[McKirahan]

"insert into MS Access issue" <anonymous@discussions.microsoft.com> wrote in
message news:661A086D-17D4-455E-9BEB-65222D71C09C@microsoft.com...

> I have the following code in an ASP page that is processing a form to

create a new user. I keep getting the following error:

>
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in INSERT INTO statement.
>
> The database is MS Access, the latest, I am able to perform select

statements on it and do everything except write to the database. The insert
statement created is as follows:

>
> insert into UserTable ( UserName, Password, EmployeeID, AccessRight,

EmailAddress, ProjectID ) values ('why' ,'why' ,'121212' , 0, 'why@c.com'
,1)

>
> Where everything is text except, AccessRight and ProjectID which are

numbers (long integer).

>
> I have taken care of the permissions issue, I am not sure what I am doing

wrong, any help would be appreciated ! Code follows

>
> thanks
> Carl
>
>
> set dbConn = server.CreateObject("adodb.connection")
> set recLgn = server.CreateObject("adodb.RecordSet")
>
> ' open the connection
> dbConn.Mode = 3
> dbConn.Open Application("Database")
> strDB = Application("Database")
> strSQL = "insert into UserTable ( UserName, Password, EmployeeID,

AccessRight, EmailAddress, ProjectID )values"

> strSQL = strSQL & " ('" & user & "' ,'" & pass & "' ,'" & CSTR(employeeID)

& "' , " & adminPriv & ", '" & eAddress & "' ," & nProjID & ")"

> dbConn.Execute strSQL

Have you tried inserting a Response.Write(strSQL) before the dbConn.Execute
statement and then cut-and-paste it into an MS-Access query to see if it
works that way?

[Aaron Bertrand [MVP]]

Password is a reserved word and should not be used as a column name. If you
can't change it, you'll have to make the following edit to all SQL
statements:

INSERT ... (... , [Password], ...)

The square brackets tell the engine to pretend it's not a reserved word,
sort of...

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]


"insert into MS Access issue" <anonymous@discussions.microsoft.com> wrote in
message news:661A086D-17D4-455E-9BEB-65222D71C09C@microsoft.com...

>I have the following code in an ASP page that is processing a form to
>create a new user. I keep getting the following error:
>
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in INSERT INTO statement.
>
> The database is MS Access, the latest, I am able to perform select
> statements on it and do everything except write to the database. The
> insert statement created is as follows:
>
> insert into UserTable ( UserName, Password, EmployeeID, AccessRight,
> EmailAddress, ProjectID ) values ('why' ,'why' ,'121212' , 0, 'why@c.com'
> ,1)
>
> Where everything is text except, AccessRight and ProjectID which are
> numbers (long integer).
>
> I have taken care of the permissions issue, I am not sure what I am doing
> wrong, any help would be appreciated ! Code follows
>
> thanks
> Carl
>
>
> set dbConn = server.CreateObject("adodb.connection")
> set recLgn = server.CreateObject("adodb.RecordSet")
>
> ' open the connection
> dbConn.Mode = 3
> dbConn.Open Application("Database")
> strDB = Application("Database")
> strSQL = "insert into UserTable ( UserName, Password, EmployeeID,
> AccessRight, EmailAddress, ProjectID )values"
> strSQL = strSQL & " ('" & user & "' ,'" & pass & "' ,'" & CSTR(employeeID)
> & "' , " & adminPriv & ", '" & eAddress & "' ," & nProjID & ")"
> dbConn.Execute strSQL
>
>

[Bob Barrows [MVP]]

insert into MS Access issue wrote:

> I have the following code in an ASP page that is processing a form to
> create a new user. I keep getting the following error:
>
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in INSERT INTO statement.
>
> The database is MS Access, the latest, I am able to perform select
> statements on it and do everything except write to the database. The
> insert statement created is as follows:
>
> insert into UserTable ( UserName, Password, EmployeeID, AccessRight,
> EmailAddress, ProjectID ) values ('why' ,'why' ,'121212' , 0,
> 'why@c.com' ,1)
>

Password is a reserved keyword and should not be used for a database object
name. My recommendation is to change the name of the field, but, if you
can't for some reason, then you will need to remember to surround it with
brackets when using it in a query called via ADO:
insert into UserTable ( UserName, [Password], ...

HTH,
bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Carl]

The Password in the table column was the issue thank you I would have never found this it was driving me crazy!

THANKS

Carl