View Single Post
  #1 (permalink)  
Old March 3rd, 2006, 11:17 PM
tropical tropical is offline
Registered User
 
Join Date: Mar 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default data mismatch while updating record

Hi there,

I'm a novice in ASP, I'm stuck in this issue:

I've an existing table "Item" with columns of "Qty" and "AveragePrice". When users add some more items on it, the code supposes to update the AveragePrice based on the newly added quantity and price.

Qty is Integer and AveragePrice is Money with lenght 8. But when I was trying to do "update" in following code, it tells me to use CAST or CONVERT to explicitly convert the data type. But I've tried with CAST(newAverage AS money) or CONVERT(money, newAverage), both got error messages. Actually the piece of code was existing before, but the AveragePrice column is what I'm trying to add.

================================
Dim Item2
set Item2 = Server.CreateObject ("ADODB.Recordset")
Item2.Open SQL, conn

Dim newQTY, oldQTY, oldAverage, newAverage
if not Item2.EOF then
  oldQTY = Item2("Qty")
  oldAverage = Item2("AveragePrice")
  newQTY = oldQTY + CLng(qty) 'qty given some time before
  newAverage = ( oldQTY * oldAverage + CLng(qty) * cost ) / ( oldQTY + CLng(qty)))

  SQL = "update Item " &_
    "set Qty = '" & newQTY & "', AveragePrice = '" & newAverage & "' " &_
    "WHERE ItemID = '" & itemID & "'"
End if
============================

I've tried to change the newAverage in the line "update Item..." to CAST(newAverage AS money) or CONVERT(money, newAverage), seems both not working.

Looking forward eagerly to some help

Thanks a lot
Tropical

Reply With Quote