asp w/ access SQL error... help please

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?

Re: asp w/ access SQL error... help please

"Matt" wrote in message
news:1180653937.506908.263000@w5g2000hsg.googlegroups.com...
> 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-connecti on-string-look-like.html

You should not be creating a recordset to perform an UPDATE operation.
http://databases.aspfaq.com/database/when-should-i-use-creat eobject-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.

--
Mike Brind