asp w/ access SQL error... help please

[Matt]

I'm using classic ASP with an Access 2003 database, running on Win
2003 Server (IIS 6), trying to update a table and get the following
error:

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
statement. - Err.Number:-2147217900 - SQL:UPDATE shipments SET
shipmentDescription='$0 - $

Here's my script:

dim SQL, connTemp, rsTemp

pIdShipment = request.Form("idShipment")
pShipmentDescription = request.form("shipmentDescription")
pPriceToAdd = request.form("priceToAdd")
pPercentageToAdd = request.form("percentageToAdd")
pShipmentTime = request.form("shipmentTime")

pQuantityFrom = request.form("quantityFrom")
pQuantityTo = request.form("quantityTo")

pWeightFrom = request.form("weightFrom")
pWeightTo = request.form("weightTo")

pPriceFrom = request.form("priceFrom")
pPriceTo = request.form("priceTo")

pIdZone = request.form("shippingZone")
pIdCustomerType = request.form("customerType")

' validate
if pShipmentDescription="" then
response.redirect "modifyShipmentForm.asp?
idShipment="&pIdShipment&"&message="& Server.Urlencode("Please enter a
valid shipment description")
end if

' update shipment in to db

SQL="UPDATE shipments SET shipmentDescription='"
&pShipmentDescription& "', priceToAdd=" &pPriceToAdd& ",
percentageToAdd=" &pPercentageToAdd& ", shipmentTime='"
&pShipmentTime& "', quantityFrom=" &pQuantityFrom& ", quantityTo="
&pQuantityTo& ", weightFrom=" &pWeightFrom& ", weightTo=" &pWeightTo&
", priceFrom=" &pPriceFrom& ", priceTo=" &pPriceTo& ",
idShippingZone=" &pIdZone& ", idCustomerType=" &pIdCustomerType& "
WHERE idShipment=" &pIdShipment

set connTemp = server.createObject("adodb.connection")

connTemp.Open DSN=MSC

set rsTemp=connTemp.execute(SQL)


The shipmentDescription and ShipmentTime columns are text fields,
everything else is number fields in Access.

Any ideas on what I'm doing wrong?

 

[Mike Brind]

I'm using classic ASP with an Access 2003 database, running on Win
2003 Server (IIS 6), trying to update a table and get the following
error:

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
statement. - Err.Number:-2147217900 - SQL:UPDATE shipments SET
shipmentDescription='$0 - $

Here's my script:

dim SQL, connTemp, rsTemp

pIdShipment = request.Form("idShipment")
pShipmentDescription = request.form("shipmentDescription")
pPriceToAdd = request.form("priceToAdd")
pPercentageToAdd = request.form("percentageToAdd")
pShipmentTime = request.form("shipmentTime")

pQuantityFrom = request.form("quantityFrom")
pQuantityTo = request.form("quantityTo")

pWeightFrom = request.form("weightFrom")
pWeightTo = request.form("weightTo")

pPriceFrom = request.form("priceFrom")
pPriceTo = request.form("priceTo")

pIdZone = request.form("shippingZone")
pIdCustomerType = request.form("customerType")

' validate
if pShipmentDescription="" then
response.redirect "modifyShipmentForm.asp?
idShipment="&pIdShipment&"&message="& Server.Urlencode("Please enter a
valid shipment description")
end if

' update shipment in to db

SQL="UPDATE shipments SET shipmentDescription='"
&pShipmentDescription& "', priceToAdd=" &pPriceToAdd& ",
percentageToAdd=" &pPercentageToAdd& ", shipmentTime='"
&pShipmentTime& "', quantityFrom=" &pQuantityFrom& ", quantityTo="
&pQuantityTo& ", weightFrom=" &pWeightFrom& ", weightTo=" &pWeightTo&
", priceFrom=" &pPriceFrom& ", priceTo=" &pPriceTo& ",
idShippingZone=" &pIdZone& ", idCustomerType=" &pIdCustomerType& "
WHERE idShipment=" &pIdShipment

set connTemp = server.createObject("adodb.connection")

connTemp.Open DSN=MSC

set rsTemp=connTemp.execute(SQL)


The shipmentDescription and ShipmentTime columns are text fields,
everything else is number fields in Access.

Any ideas on what I'm doing wrong?

Do a Response.Write SQL and try to run the result against the database
directly, unless just looking at the result shows an obvious error in your
SQL.

Couple of other things: ODBC is a bad idea with Access. You should use the
native OLEDB provider:
http://databases.aspfaq.com/database/what-should-my-connection-string-look-like.html

You should not be creating a recordset to perform an UPDATE operation.
http://databases.aspfaq.com/databas...ateobject-to-create-my-recordset-objects.html

Also, you should not plonk form values directly into SQL. You should use
parameters. They will protect you from SQL Injection, and remove the need
to delimit datatypes.