Syntax error in UPDATE statement.

[FayeC]

I really need help figuring this out.
i have a db with mostly text fields but 2.
The user_id field is an autonumber (key) and the user_newsletter is a
number (1 and 0) field meaning 1 yes the person wants to receive a
newsletter and 0 no, don't want to receive it.
Now.....when trying to create an UPDATE statement I am running into
problems writing the code for it.
I had issues before with missing commas but now that the commas are
there I guess I am just stating the userNewsletter the wrong way.
can somebody take a look and tell me what I am doing wrong?
Can I use anything easier than fixQuotes or that is a good safety
measure?

<%
END IF

' Update user
IF updateUser <> "" THEN

sqlString = "UPDATE users SET " &_
"user_username='" & fixQuotes( userUsername ) & "'," &_
"user_password='" & fixQuotes( userPassword ) & "'," &_
"user_fullname='" & fixQuotes( userFullname ) & "'," &_
"user_email='" & fixQuotes( userEmail ) & "', " &_
"user_compname='" & fixQuotes( userCompname ) & "', " &_
"user_dnumber='" & fixQuotes( userDnumber ) & "', " &_
"user_newsletter=" & userNewsletter & ", " &_
"WHERE user_id=" & userID

Con.Execute sqlString

%>
<br>
<table width="500" cellpadding="4"
cellspacing="0" bgcolor="#FFFFFF">
<tr>
<td class="form">
<%=userUsername%> was updated in the database
</td>
</tr>
</table>
Thank you in advance,

FayeC

[Bob Barrows [MVP]]

FayeC wrote:

I really need help figuring this out.
i have a db with mostly text fields but 2.
What database type and version? This is almost always relevant.
The user_id field is an autonumber (key)
Access? I will answer as if that's the correct guess.
and the user_newsletter is a
number (1 and 0) field meaning 1 yes the person wants to receive a
newsletter and 0 no, don't want to receive it.
Now.....when trying to create an UPDATE statement I am running into
problems writing the code for it.
I had issues before with missing commas but now that the commas are
there I guess I am just stating the userNewsletter the wrong way.
can somebody take a look and tell me what I am doing wrong?
Can I use anything easier than fixQuotes or that is a good safety
measure?

<%
END IF

' Update user
IF updateUser <> "" THEN

sqlString = "UPDATE users SET " &_
"user_username='" & fixQuotes( userUsername ) & "'," &_
"user_password='" & fixQuotes( userPassword ) & "'," &_
"user_fullname='" & fixQuotes( userFullname ) & "'," &_
"user_email='" & fixQuotes( userEmail ) & "', " &_
"user_compname='" & fixQuotes( userCompname ) & "', " &_
"user_dnumber='" & fixQuotes( userDnumber ) & "', " &_
"user_newsletter=" & userNewsletter & ", " &_
This last comma does not belong.
"WHERE user_id=" & userID
Look, the only way to debug a sql statement is to look at it. Looking at the
code that generated it is only part of the process. Add

Response.Write sqlString

to your code and look at the result in the browser window. If you've built
the string correctly, you should be able to copy it to the clipboard, open
your database in Access, create a new query in SQL View, paste in the
statement and run it without modification.

Con.Execute sqlString

Yet another problem caused by the attempted use of dynamic sql ... :-)

My preference would be to use a saved parameter query:
http://groups.google.com/groups?hl=e...tngp13.phx.gbl

http://groups.google.com/groups?hl=e...TNGP11.phx.gbl

http://www.google.com/groups?selm=eE...&output=gplain

http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl
However, if you have something against saved queries, at least use parameter
markers and use a Command object to pass your parameter values:
http://groups-beta.google.com/group/...e36562fee7804e

Fixquotes is a good safety measure, but hackers can defeat it. The only sure
way to prevent sql injection* is to use parameters instead of dynamic sql.

*http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/adva..._injection.pdf

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.

[FayeC]

Access db, the db was created on Access XP. I could convert the db to
Access 2003 if necessary.
Another thing is ....I have the same statement working perfectly with
text only fileds as written bellow.
<%
END IF

' Update Product
IF updateProduct <> "" THEN

sqlString = "UPDATE Products SET " &_
"product_name='" & fixQuotes( productName ) & "'," &_
"product_description='" & fixQuotes( productDescription ) & "'," &_
"product_file='" & fixQuotes( productFile ) & "' " &_
"WHERE product_id=" & productID

Con.Execute sqlString

%>

Thank you,

FayeC

On Mon, 19 Dec 2005 10:54:53 -0500, "Bob Barrows [MVP]"
<re******@NOyahoo.SPAMcom> wrote:

FayeC wrote:

I really need help figuring this out.
i have a db with mostly text fields but 2.

What database type and version? This is almost always relevant.

The user_id field is an autonumber (key)

Access? I will answer as if that's the correct guess.

and the user_newsletter is a
number (1 and 0) field meaning 1 yes the person wants to receive a
newsletter and 0 no, don't want to receive it.
Now.....when trying to create an UPDATE statement I am running into
problems writing the code for it.
I had issues before with missing commas but now that the commas are
there I guess I am just stating the userNewsletter the wrong way.
can somebody take a look and tell me what I am doing wrong?
Can I use anything easier than fixQuotes or that is a good safety
measure?

<%
END IF

' Update user
IF updateUser <> "" THEN

sqlString = "UPDATE users SET " &_
"user_username='" & fixQuotes( userUsername ) & "'," &_
"user_password='" & fixQuotes( userPassword ) & "'," &_
"user_fullname='" & fixQuotes( userFullname ) & "'," &_
"user_email='" & fixQuotes( userEmail ) & "', " &_
"user_compname='" & fixQuotes( userCompname ) & "', " &_
"user_dnumber='" & fixQuotes( userDnumber ) & "', " &_
"user_newsletter=" & userNewsletter & ", " &_

This last comma does not belong.

"WHERE user_id=" & userID

Look, the only way to debug a sql statement is to look at it. Looking at the
code that generated it is only part of the process. Add

Response.Write sqlString

to your code and look at the result in the browser window. If you've built
the string correctly, you should be able to copy it to the clipboard, open
your database in Access, create a new query in SQL View, paste in the
statement and run it without modification.

Con.Execute sqlString

Yet another problem caused by the attempted use of dynamic sql ... :-)

My preference would be to use a saved parameter query:
http://groups.google.com/groups?hl=e...tngp13.phx.gbl

http://groups.google.com/groups?hl=e...TNGP11.phx.gbl

http://www.google.com/groups?selm=eE...&output=gplain

http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl
However, if you have something against saved queries, at least use parameter
markers and use a Command object to pass your parameter values:
http://groups-beta.google.com/group/...e36562fee7804e

Fixquotes is a good safety measure, but hackers can defeat it. The only sure
way to prevent sql injection* is to use parameters instead of dynamic sql.

*http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/adva..._injection.pdf

Bob Barrows

[Paxton]

FayeC wrote:

Access db, the db was created on Access XP. I could convert the db to
Access 2003 if necessary.
Another thing is ....I have the same statement working perfectly with
text only fileds as written bellow.
<%
END IF

' Update Product
IF updateProduct <> "" THEN

sqlString = "UPDATE Products SET " &_
"product_name='" & fixQuotes( productName ) & "'," &_
"product_description='" & fixQuotes( productDescription ) & "'," &_
"product_file='" & fixQuotes( productFile ) & "' " &_
"WHERE product_id=" & productID

Con.Execute sqlString

%>

Thank you,

FayeC

But it's not the same statement, and it works perfectly because your
syntax is correct in the above example, whereas, as Bob pointed out, it
was incorrect in the previous sample.

The WHERE cause should not be separated from the rest of the statement
by a comma. Your first sample basically said:

"UPDATE table set field1 = "value1", field2 = "value2", WHERE......"
<-- incorrect

whereas your second sample says:

"UPDATE table set field1 = "value1", field2 = "value2" WHERE......"
<-- correct

Oh, and please try to avoid top-posting. The whole thread gets out of
kilter :-)

/P.

On Mon, 19 Dec 2005 10:54:53 -0500, "Bob Barrows [MVP]"
<re******@NOyahoo.SPAMcom> wrote:

FayeC wrote:

I really need help figuring this out.
i have a db with mostly text fields but 2.

What database type and version? This is almost always relevant.

The user_id field is an autonumber (key)

Access? I will answer as if that's the correct guess.

and the user_newsletter is a
number (1 and 0) field meaning 1 yes the person wants to receive a
newsletter and 0 no, don't want to receive it.
Now.....when trying to create an UPDATE statement I am running into
problems writing the code for it.
I had issues before with missing commas but now that the commas are
there I guess I am just stating the userNewsletter the wrong way.
can somebody take a look and tell me what I am doing wrong?
Can I use anything easier than fixQuotes or that is a good safety
measure?

<%
END IF

' Update user
IF updateUser <> "" THEN

sqlString = "UPDATE users SET " &_
"user_username='" & fixQuotes( userUsername ) & "'," &_
"user_password='" & fixQuotes( userPassword ) & "'," &_
"user_fullname='" & fixQuotes( userFullname ) & "'," &_
"user_email='" & fixQuotes( userEmail ) & "', " &_
"user_compname='" & fixQuotes( userCompname ) & "', " &_
"user_dnumber='" & fixQuotes( userDnumber ) & "', " &_
"user_newsletter=" & userNewsletter & ", " &_

This last comma does not belong.

"WHERE user_id=" & userID

Look, the only way to debug a sql statement is to look at it. Looking at the
code that generated it is only part of the process. Add

Response.Write sqlString

to your code and look at the result in the browser window. If you've built
the string correctly, you should be able to copy it to the clipboard, open
your database in Access, create a new query in SQL View, paste in the
statement and run it without modification.

Con.Execute sqlString

Yet another problem caused by the attempted use of dynamic sql ... :-)

My preference would be to use a saved parameter query:
http://groups.google.com/groups?hl=e...tngp13.phx.gbl

http://groups.google.com/groups?hl=e...TNGP11.phx.gbl

http://www.google.com/groups?selm=eE...&output=gplain

http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl
However, if you have something against saved queries, at least use parameter
markers and use a Command object to pass your parameter values:
http://groups-beta.google.com/group/...e36562fee7804e

Fixquotes is a good safety measure, but hackers can defeat it. The only sure
way to prevent sql injection* is to use parameters instead of dynamic sql.

*http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/adva..._injection.pdf

Bob Barrows

[FayeC]

What I meant was that I did fix the commas and it still won't work.
<%
END IF

' Update user
IF updateUser <> "" THEN

sqlString = "UPDATE users SET " &_
"user_username='" & fixQuotes( userUsername ) & "'," &_
"user_password='" & fixQuotes( userPassword ) & "'," &_
"user_fullname='" & fixQuotes( userFullname ) & "'," &_
"user_email='" & fixQuotes( userEmail ) & "', " &_
"user_compname='" & fixQuotes( userCompname ) & "', " &_
"user_dnumber='" & fixQuotes( userDnumber ) & "', " &_
"user_newsletter=" & fixQuotes( userNewsletter ) & "' " &_
"WHERE user_id=" & userID

Con.Execute sqlString

%>
<br>
<table width="500" cellpadding="4"
cellspacing="0" bgcolor="#FFFFFF">
<tr>
<td class="form">
<%=userUsername%> was updated in the database
</td>
</tr>
</table>

I tried it this way and
<%
END IF

' Update user
IF updateUser <> "" THEN

sqlString = "UPDATE users SET " &_
"user_username='" & fixQuotes( userUsername ) & "'," &_
"user_password='" & fixQuotes( userPassword ) & "'," &_
"user_fullname='" & fixQuotes( userFullname ) & "'," &_
"user_email='" & fixQuotes( userEmail ) & "', " &_
"user_compname='" & fixQuotes( userCompname ) & "', " &_
"user_dnumber='" & fixQuotes( userDnumber ) & "', " &_
"user_newsletter=" & userNewsletter &_
"WHERE user_id=" & userID

Con.Execute sqlString

%>
<br>
<table width="500" cellpadding="4"
cellspacing="0" bgcolor="#FFFFFF">
<tr>
<td class="form">
<%=userUsername%> was updated in the database
</td>
</tr>
</table>

Any help?

FayeC

On 20 Dec 2005 06:04:07 -0800, "Paxton" <pa*******@hotmail.com> wrote:

FayeC wrote:

Access db, the db was created on Access XP. I could convert the db to
Access 2003 if necessary.
Another thing is ....I have the same statement working perfectly with
text only fileds as written bellow.
<%
END IF

' Update Product
IF updateProduct <> "" THEN

sqlString = "UPDATE Products SET " &_
"product_name='" & fixQuotes( productName ) & "'," &_
"product_description='" & fixQuotes( productDescription ) & "'," &_
"product_file='" & fixQuotes( productFile ) & "' " &_
"WHERE product_id=" & productID

Con.Execute sqlString

%>

Thank you,

FayeC

But it's not the same statement, and it works perfectly because your
syntax is correct in the above example, whereas, as Bob pointed out, it
was incorrect in the previous sample.

The WHERE cause should not be separated from the rest of the statement
by a comma. Your first sample basically said:

"UPDATE table set field1 = "value1", field2 = "value2", WHERE......"
<-- incorrect

whereas your second sample says:

"UPDATE table set field1 = "value1", field2 = "value2" WHERE......"
<-- correct

Oh, and please try to avoid top-posting. The whole thread gets out of
kilter :-)

/P.

On Mon, 19 Dec 2005 10:54:53 -0500, "Bob Barrows [MVP]"
<re******@NOyahoo.SPAMcom> wrote:

>FayeC wrote:
>> I really need help figuring this out.
>> i have a db with mostly text fields but 2.
>
>What database type and version? This is almost always relevant.
>
>> The user_id field is an autonumber (key)
>
>Access? I will answer as if that's the correct guess.
>
>> and the user_newsletter is a
>> number (1 and 0) field meaning 1 yes the person wants to receive a
>> newsletter and 0 no, don't want to receive it.
>> Now.....when trying to create an UPDATE statement I am running into
>> problems writing the code for it.
>> I had issues before with missing commas but now that the commas are
>> there I guess I am just stating the userNewsletter the wrong way.
>> can somebody take a look and tell me what I am doing wrong?
>> Can I use anything easier than fixQuotes or that is a good safety
>> measure?
>>
>> <%
>> END IF
>>
>>
>>
>> ' Update user
>> IF updateUser <> "" THEN
>>
>> sqlString = "UPDATE users SET " &_
>> "user_username='" & fixQuotes( userUsername ) & "'," &_
>> "user_password='" & fixQuotes( userPassword ) & "'," &_
>> "user_fullname='" & fixQuotes( userFullname ) & "'," &_
>> "user_email='" & fixQuotes( userEmail ) & "', " &_
>> "user_compname='" & fixQuotes( userCompname ) & "', " &_
>> "user_dnumber='" & fixQuotes( userDnumber ) & "', " &_
>> "user_newsletter=" & userNewsletter & ", " &_
>
>This last comma does not belong.
>
>> "WHERE user_id=" & userID
>
>Look, the only way to debug a sql statement is to look at it. Looking at the
>code that generated it is only part of the process. Add
>
>Response.Write sqlString
>
>to your code and look at the result in the browser window. If you've built
>the string correctly, you should be able to copy it to the clipboard, open
>your database in Access, create a new query in SQL View, paste in the
>statement and run it without modification.
>
>>
>> Con.Execute sqlString
>>
>>
>Yet another problem caused by the attempted use of dynamic sql ... :-)
>
>My preference would be to use a saved parameter query:
>http://groups.google.com/groups?hl=e...tngp13.phx.gbl
>
>http://groups.google.com/groups?hl=e...TNGP11.phx.gbl
>
>http://www.google.com/groups?selm=eE...&output=gplain
>
>http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl
>
>
>However, if you have something against saved queries, at least use parameter
>markers and use a Command object to pass your parameter values:
>http://groups-beta.google.com/group/...e36562fee7804e
>
>Fixquotes is a good safety measure, but hackers can defeat it. The only sure
>way to prevent sql injection* is to use parameters instead of dynamic sql.
>
>*http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
>http://www.nextgenss.com/papers/adva..._injection.pdf
>
>Bob Barrows

[Giles]

"FayeC" <fa*******@hotmail.com> wrote

What I meant was that I did fix the commas and it still won't work.

sqlString = "UPDATE users SET " &_
"user_username='" & fixQuotes( userUsername ) & "'," &_
"user_password='" & fixQuotes( userPassword ) & "'," &_
"user_fullname='" & fixQuotes( userFullname ) & "'," &_
"user_email='" & fixQuotes( userEmail ) & "', " &_
"user_compname='" & fixQuotes( userCompname ) & "', " &_
"user_dnumber='" & fixQuotes( userDnumber ) & "', " &_
"user_newsletter=" & fixQuotes( userNewsletter ) & "' " &_
"WHERE user_id=" & userID

Con.Execute sqlString
I think you said user_newsletter is a numeric, so you can't put it in quotes "user_newsletter=" & userNewsletter & " " &_

Do remember to keep the trailing space.
And do use response.write sqlString as has been suggested ++, just rem out
the execute until you have stared really hard at the string and are sure
there are no typos!
Giles

[FayeC]

On Wed, 21 Dec 2005 13:54:12 -0000, "Giles" <gi***@nospam.com> wrote:

I think you said user_newsletter is a numeric, so you can't put it in quotes

"user_newsletter=" & userNewsletter & " " &_

Do remember to keep the trailing space.
And do use response.write sqlString as has been suggested ++, just rem out
the execute until you have stared really hard at the string and are sure
there are no typos!
Giles

Thank you :) I got it to work and the other files too.

FayeC