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 6  2669 
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.
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
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
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
"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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Robert Mark Bram |
last post by:
Hi All!
I have the following two methods in an asp/jscript page - my problem is that
without the update statement there is no error, but with the update
statement I get the following error:
...
|
by: Dave |
last post by:
I have 2 tables, one with names, and another with addresses, joined by their
CIVICID number (unique to the ADDRESSINFO table) in Oracle.
I need to update a field in the NAMEINFO table for a...
|
by: S.Patten |
last post by:
Hi,
I have a problem with updating a datetime column,
When I try to change the Column from VB I get "Incorrect syntax near
'942'" returned from
'942' is the unique key column value
...
|
by: Bob Stearns |
last post by:
The statement:
merge into nullid.animals_et_in t1 using is3.animals t2
on t1.sire_assoc=t2.assoc and t1.sire_prefix=t2.prefix and
t1.sire_regnum=t2.regnum
when matched then update set...
|
by: deko |
last post by:
I'm trying to update the address record of an existing record in my mdb with
values from another existing record in the same table. In pseudo code it
might look like this:
UPDATE tblAddress SET...
|
by: amitbadgi |
last post by:
HI i am getting the foll error while conv an asp application to
asp.net
Exception Details: System.Runtime.InteropServices.COMException: Syntax
error in UPDATE statement.
Source Error:
Line...
|
by: Stephen Plotnick |
last post by:
I have three forms and update one table in an Access DB with sucess in one
form.
In the other two forms I'm trying to do an update to a different table in
the Access DB.
The error I'm getting...
|
by: ajos |
last post by:
hi evrybdy,
the problem is:-
i had this running before in jsp but when i changed the jsp page using struts tags there occoured a problem...
when i enter values in the 2 text boxes and click enter...
|
by: phobia1 |
last post by:
Hi once again.
We have just changed our ISP and things that worked fine now do not, Obviously its in the differences of MYSQL and PHP versions.
Have fixed most of the problems but this UPDATE...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
| |
