value addition while executing an update statement

Hi,

I have a small problem in sql here. I have three sql statements. My table "OrderBooks" contains fields 'Sno','Title','Author',
'Publisher','CopiesOrdered', 'CopiesRecd','OrderDate' etc.

My module in asp aims at updating the stock level everytime i receive a batch of books ordered. The problem i'm facing is something unique. Every time i try to update the stocklevel the value gets added twice. Duplication occurs only in the copiesrecd field and not the records.

My problem is discussed below:

Suppose i have ordered 50 copies of a book. And assume i have already recieved 10 copies against that order and now if i receive another set of 10 copies my table should show 20 units after the execution of the update statement

But what happens is, even before the execution of the update statement my query1 returns the value 20 and again as per the update command another 10 is added and the final query returns the value 30

I do not understand how my select query returns 20 instead of 10.

If i comment the second query then my select statements returns the correct value.


Pls refer my sql statment below
---------------------------------------------------------------
sql = "Select * from orderbooks where orderno = " & ordno
Response.Write sql
rs.Open sql,cn,3,3
Response.Write "No of copies. in table before the execution of update statement = " & rs("Copiesrecd")
Response.Write "<br>"
sql2 = "UPDATE ORDERBOOKS SET COPIESRECD =(SELECT COPIESRECD FROM ORDERBOOKS WHERE ORDERNO = " & ordno & ")+ " & qtyrecdnow & " WHERE (ORDERNO = " & ordno & ")"
rs2.Open sql2,cn,3,3
Response.Write "Update Query executed"
Response.Write "<br>"

sql3 = "Select * from orderbooks where orderno = " & ordno
Response.Write sql3
rs3.Open sql3,cn,3,3
Response.Write "No of copies. in table before the execution of update statement = " & rs3("Copiesrecd")
Response.Write "<br>"
---------------------------------------------------------------

Has anybody faced a similar problem before. If so what was the reason for the same

rgds

Ragesh

Hopefully you've already solved this problem (since you posted it on the 17th of Jan) but just in case:



From the looks of your code, the problem appears to be in the variable "qtyrecdnow". What exactly does this variable contain?
Sticking with your example, this variable should contain 10. You've already recieved 10 books, and you're now recieving 10 more.

The problem you're having (as well as your variable name) leads me to believe that you're storing the total you have recieved in "qtyrecdnow". So you've already recieved 10, and you're telling it that you've recieved 20. Your insert statement takes the 10 you already had, adds the 20 that you gave it, and gives you 30.