Hi,
I'm working on my college project, where part of it involves updating user 
profile. It works fine until I want to update "password" field.


SQL statement without updating password that works fine:
UPDATE Users SET firstname = 'Jenny', lastname = 'Munro', email 
= 'pkysela@s...', address1 = '27 Mapperley road', address2 
= 'Mapperley', city = 'London', postcode = 'NG5 6DE', country = 'UK' WHERE 
userID = 14


The same SQL statement attemting to update password:
UPDATE Users SET firstname = 'Jenny', lastname = 'Munro', email 
= 'pkysela@s...', password = 'rich', address1 = '27 Mapperley road', 
address2 = 'Mapperley', city = 'London', postcode = 'NG5 6DE', country 
= 'UK' WHERE userID = 14


Error message:
Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.


The SQL statement is generated using a loop:
for each strField in objrsUsers.fields
	strName = strField.name
	strValue = request.form(strName)
	if not strValue = "" then
		strSQL = strSQL & strName & " = " & "'" & strValue & "', "
	end if
next
intLength = Len(strSQL)
strSQL = Left(strSQL, intLength - 2) 'getting rid off the last comma
strSQL = "UPDATE Users SET " & strSQL & " WHERE userID = " & 
request.cookies("nad")("userID")

I have absolutely no idea why it doesn't like it, cause I tried it in 
Access and it didn't give any errors. Please help!!!!!!!!!!!!!!!!!!

"password" may be a reserved word in SQL Server.


> Hi,
I> 'm working on my college project, where part of it involves updating 
user 
p> rofile. It works fine until I want to update "password" field.

> 
S> QL statement without updating password that works fine:
U> PDATE Users SET firstname = 'Jenny', lastname = 'Munro', email 
=>  'pkysela@s...', address1 = '27 Mapperley road', address2 
=>  'Mapperley', city = 'London', postcode = 'NG5 6DE', country = 'UK' 
WHERE 
u> serID = 14

> 
T> he same SQL statement attemting to update password:
U> PDATE Users SET firstname = 'Jenny', lastname = 'Munro', email 
=>  'pkysela@s...', password = 'rich', address1 = '27 Mapperley 
road', 
a> ddress2 = 'Mapperley', city = 'London', postcode = 'NG5 6DE', country 
=>  'UK' WHERE userID = 14

> 
E> rror message:
M> icrosoft JET Database Engine (0x80040E14)
S> yntax error in UPDATE statement.

> 
T> he SQL statement is generated using a loop:
f> or each strField in objrsUsers.fields
	> strName = strField.name
	> strValue = request.form(strName)
	> if not strValue = "" then
	> 	strSQL = strSQL & strName & " = " & "'" & strValue & "', "
	> end if
n> ext
i> ntLength = Len(strSQL)
s> trSQL = Left(strSQL, intLength - 2) 'getting rid off the last comma
s> trSQL = "UPDATE Users SET " & strSQL & " WHERE userID = " & 
r> equest.cookies("nad")("userID")

> I have absolutely no idea why it doesn't like it, cause I tried it in 
A> ccess and it didn't give any errors. Please help!!!!!!!!!!!!!!!!!!

check the column name are correct or not,if it is correct then check the
datatype for password column

appaji
----- Original Message -----
From: "Petr Kysela" <pkysela@y...>
To: "ASPToday Discuss" <asptoday_discuss@p...>
Sent: Sunday, May 19, 2002 4:55 PM
Subject: [asptoday_discuss] Syntax error in UPDATE statement


> Hi,
> I'm working on my college project, where part of it involves updating user
> profile. It works fine until I want to update "password" field.
>
>
> SQL statement without updating password that works fine:
> UPDATE Users SET firstname = 'Jenny', lastname = 'Munro', email
> = 'pkysela@s...', address1 = '27 Mapperley road', address2
> = 'Mapperley', city = 'London', postcode = 'NG5 6DE', country = 'UK' WHERE
> userID = 14
>
>
> The same SQL statement attemting to update password:
> UPDATE Users SET firstname = 'Jenny', lastname = 'Munro', email
> = 'pkysela@s...', password = 'rich', address1 = '27 Mapperley road',
> address2 = 'Mapperley', city = 'London', postcode = 'NG5 6DE', country
> = 'UK' WHERE userID = 14
>
>
> Error message:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in UPDATE statement.
>
>
> The SQL statement is generated using a loop:
> for each strField in objrsUsers.fields
> strName = strField.name
> strValue = request.form(strName)
> if not strValue = "" then
> strSQL = strSQL & strName & " = " & "'" & strValue & "', "
> end if
> next
> intLength = Len(strSQL)
> strSQL = Left(strSQL, intLength - 2) 'getting rid off the last comma
> strSQL = "UPDATE Users SET " & strSQL & " WHERE userID = " &
> request.cookies("nad")("userID")
>
> I have absolutely no idea why it doesn't like it, cause I tried it in
> Access and it didn't give any errors. Please help!!!!!!!!!!!!!!!!!!
>
>

Put square backets around your field name:

[password]=3D ''

Because there is an issue with reserved words in access...

Gr,
Jeroen Diderik

-----Original Message-----
From: Appaji [mailto:svappaji@h...]
Sent: Tuesday, May 21, 2002 6:21 AM
To: ASPToday Discuss
Subject: [asptoday_discuss] Re: Syntax error in UPDATE statement


check the column name are correct or not,if it is correct then check the
datatype for password column

appaji
----- Original Message -----
From: "Petr Kysela" <pkysela@y...>
To: "ASPToday Discuss" <asptoday_discuss@p...>
Sent: Sunday, May 19, 2002 4:55 PM
Subject: [asptoday_discuss] Syntax error in UPDATE statement


> Hi,
> I'm working on my college project, where part of it involves updating
> user profile. It works fine until I want to update "password" field.
>
>
> SQL statement without updating password that works fine: UPDATE Users
> SET firstname =3D 'Jenny', lastname =3D 'Munro', email =3D
> 'pkysela@s...', address1 =3D '27 Mapperley road', address2 =3D
> 'Mapperley', city =3D 'London', postcode =3D 'NG5 6DE', country =3D 
'UK'
> WHERE userID =3D 14
>
>
> The same SQL statement attemting to update password:
> UPDATE Users SET firstname =3D 'Jenny', lastname =3D 'Munro', email 
=3D
> 'pkysela@s...', password =3D 'rich', address1 =3D '27 Mapperley
> road', address2 =3D 'Mapperley', city =3D 'London', postcode =3D 'NG5 
6DE',
> country =3D 'UK' WHERE userID =3D 14
>
>
> Error message:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in UPDATE statement.
>
>
> The SQL statement is generated using a loop:
> for each strField in objrsUsers.fields
> strName =3D strField.name
> strValue =3D request.form(strName)
> if not strValue =3D "" then
> strSQL =3D strSQL & strName & " =3D " & "'" & strValue & "', " end if
> next
> intLength =3D Len(strSQL)
> strSQL =3D Left(strSQL, intLength - 2) 'getting rid off the last comma
> strSQL =3D "UPDATE Users SET " & strSQL & " WHERE userID =3D " &
> request.cookies("nad")("userID")
>
> I have absolutely no idea why it doesn't like it, cause I tried it in
> Access and it didn't give any errors. Please help!!!!!!!!!!!!!!!!!!
>
>

What it was: it didn't like the name "password" in the sql statement, 
changed it to userpassword and everything worked fine.
Cheers for tips.