Problem updating database...pls help!

[reigalz]

hi, i'm doing an ASP project on ecommerce website...
i have a table for my products in the database...
In the products table, there are these fields:
Price, Description, KeyFeatures, SuppliedAccessories, Weight&Dimensions, ProductImage, ProductModel

i'm doing an administrator's page where the admin can update the products' details...
But there's a problem, i can't insert the updated details into the database...i tink it's because the data type for Description, KeyFeatures, SuppliedAccessories, Weight&Dimensions is Memo. As i tried to update the Price field first and it works! why is it that i cannot update the columns with data type as Memo? pls help!

here's my SQL code:
sql="UPDATE Products SET "
sql=sql & "Price='" & price & "',"
sql=sql & "Description='" & description & "',"
sql=sql & "KeyFeatures='" & keyFeatures & "',"
sql=sql & "SuppliedAccessories='" & suppliedAccessories & "',"
sql=sql & "Weight&Dimensions='" & weight_dimensions & "',"
sql=sql & "ProductImage='" & productImageFN & "'"
sql=sql & "WHERE ProductModel='" & productModel & "'"

the error i get is:
Syntax error (missing operator) in query expression ''World's Longest Stamina CD Walkman',KeyFeatures='- G-Protection system<br>- 125 hours playback<br>- Optical digital output<br>- Joint-text capability<br>- CD Text Remote Capability<br>- CD-R/RW Playback',SuppliedAccessories='- Headphones'.
/asp/ASP/Admin_UpdateCD2.asp, line 47

[Mhtml]

Me thinks this is because you have ' in the text you are trying to enter.

Try placing: description = response.htmlEncode(description)

Before your create your sql query, this will convert bad chars into ascii html code and stop problems like that before they occur.

Might be an idea to run it over all input data from forms, using the same syntax as before:
variableName = response.HtmlEncode(variableName)
Replacing variableName with the variable you are converting.:D:)

[reigalz]

when i put this: description = response.htmlEncode(description)
there's an error....
Object doesn't support this property or method: 'htmlEncode'
why?

[raf]

Try placing: description = response.htmlEncode(description)

When inserting into a db ? No way !!!
You've really dropped the ball here.

For starters, its server.HTMLEncode since it's a method of the serverobject.
Further, it should only be used to encode text that is sent to a brower, so that it is displayed as "text" like you see it in the db, and not is parsed.

About the single quotes: there's a sticky on top of this forum about that. Replacing single quotes is the only transformation you need to do on all textfieldvalues you're gonna insert in an sql statement.

with this

variable=replace(variable,"'","''").

After inserting, the single quotes apear just as single quotes (and not as double single quotes like they were after the replace)

For the rest, values for textvariables need to be enclosed by single quotes, values for numerica fields not. dates need to be enclosed by #

[reigalz]

variable=replace(variable,"'","''")

i tried the above method and it works! thanks raf!
but there's a error...
here's my code and the error:

description = Request.Form("description")
description = replace(description,"'","''")

keyFeatures = Request.Form("keyFeatures")
keyFeatures = replace(keyFeatures,"'","''")

suppliedAccessories = Request.Form("suppliedAccessories")
suppliedAccessories = replace(suppliedAccessories,"'","''")

weight_dimensions = Request.Form("weight_dimensions")
weight_dimensions = replace(weight_dimensions,"'","''")

Dim sql

sql="UPDATE Products SET "
sql=sql & "Price='" & price & "',"
sql=sql & "Description='" & description & "',"
sql=sql & "KeyFeatures='" & keyFeatures & "',"
sql=sql & "SuppliedAccessories='" & suppliedAccessories & "',"
sql=sql & "Weight&Dimensions='" & weight_dimensions & "',"
sql=sql & "ProductImage='" & productImageFN & "'"
sql=sql & "WHERE ProductModel='" & productModel & "'"

Error: Syntax error in UPDATE statement.

i tink its the weight_dimensions problem because when i remove it from the sql statement, it managed to update the rest..

[raf]

Weight&Dimensions :confused:
Is this the corect variablename? Try changing that (i know Access uses that as an operator to identify an Access key)

To see the axtual statement that's sent to the RDBM, insert his in your code right beging the sql statement you posted:

response.write sql
response.end

This will print the sql statment to the browser. Easier to debug.

Also, i alwas build my statement like this:

sql="UPDATE Products SET Price='theprice', Description='thedescription', KeyFeatures='thefeatures', SuppliedAccessories='theaccessories', WeightDimensions='thewandd', ProductImage='theimage' WHERE ProductModel='themodel'"

sql=replace(sql,”theprice”,replace(“request.form(“price”),”’”,”’’”))
sql=replace(sql,”thedescription”,replace(“request.form(“description”),”’”,”’’”))
sql=replace(sql,”thefeatures”,replace(“request.form(“features”),”’”,”’’”))
…

(i assmed that all variable are textvariables)

It’s some extra line (though not always since i didn’t need to create variables like in your approach) but it’s mainly coppy and past, is easier to debug, is easier to see if all values are surrounded by the right caracters (none, quotes or #) + is easier to use this approach when you automatically build your sql statement with a “for each loop”