Stock update SQL - overzealous updating

dohdohdoh
dohdohdoh used Ask the Experts™
on
I've written the update statement to subtract stock quantities after a sale (Line 56), but its going overboard and subtracting past sales again and again, so in my test environment I now have negative numbers.
How should my update SQL go to update the bookID of that sale and that sale only?
<%
If (CStr(Request("MM_insert")) = "checkout_PP_LI_Hid") Then
  If (Not MM_abortEdit) Then
    ' execute the insert
    Dim MM_editCmd

    Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_conn_STRING
    MM_editCmd.CommandText = "INSERT INTO Customers ([first], [last], address1, address2, suburb, [state], pcode, country, phone, email) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
    MM_editCmd.Prepared = true
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202, 1, 255, Request.Form("first_name")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202, 1, 255, Request.Form("last_name")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 202, 1, 255, Request.Form("address1")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 202, 1, 255, Request.Form("address2")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5", 202, 1, 255, Request.Form("city")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param6", 202, 1, 255, Request.Form("state")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param7", 202, 1, 255, Request.Form("zip")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param8", 202, 1, 255, Request.Form("country")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param9", 202, 1, 255, Request.Form("phone")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param10", 202, 1, 255, Request.Form("email")) ' adVarWChar
    MM_editCmd.Execute 
	'MM_editCmd.ActiveConnection.Close
	
  ' MM_editCmd.Execute 'leave the connection open
MM_editCmd.CommandText = "SELECT @@IDENTITY;"
Dim rs, identityValue
set rs=MM_editCmd.execute
'rs.Open
If NOT(rs.eof AND rs.bof) then
    identityValue = rs(0).Value
End if
MM_editCmd.ActiveConnection.Close
'rs.Close

%>
<%
'Then

Dim MM_editCmd2
    Set MM_editCmd2 = Server.CreateObject ("ADODB.Command")
    MM_editCmd2.ActiveConnection = MM_conn_STRING
    MM_editCmd2.CommandText = "INSERT INTO Sales (CustID, title, bookID, sales_amnt) VALUES (?, ?, ?, ?)"
    MM_editCmd2.Prepared = true
    MM_editCmd2.Parameters.Append MM_editCmd2.CreateParameter("param1", 3, 1, 4, identityValue) ' adInteger
    MM_editCmd2.Parameters.Append MM_editCmd2.CreateParameter("param2", 202, 1, 255, Request.Form("Name")) ' adVarWChar
    MM_editCmd2.Parameters.Append MM_editCmd2.CreateParameter("param3", 202, 1, 255, Request.Form("ID")) ' adVarWChar
    MM_editCmd2.Parameters.Append MM_editCmd2.CreateParameter("param4", 202, 1, 255, Request.Form("Price")) ' adVarWChar
	
    MM_editCmd2.Execute
    MM_editCmd2.ActiveConnection.Close
%>
<%'update stock 
Dim MM_updateStock
    Set MM_updateStock = Server.CreateObject ("ADODB.Command")
    MM_updateStock.ActiveConnection = MM_conn_STRING
    MM_updateStock.CommandText = "UPDATE Stock SET quantity = (quantity - 1) WHERE EXISTS (SELECT bookID FROM Sales WHERe Sales.bookID = Stock.bookID)"
    MM_updateStock.Prepared = true
    MM_updateStock.Execute
    MM_updateStock.ActiveConnection.Close
%>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
the update statement should be like that

UPDATE Stock SET quantity = (quantity - 1) WHERE bookID = @bookID

here the @bookID is the ID value of the book sold.

Author

Commented:
But I still need to tell it the bookID that was just sold, which is why I had a where exists match. What's the best way for me to pass the correct bookIDs only?
Hi,

I'm not a SQL expert but I think we can get this going together. Let me go step by step;

I think you must give the ID of the book you are selling to the WHERE clause. For example, assume that the book you sold has an ID=100 (sales.bookID=100), then something like this:

MM_updateStock.CommandText = "UPDATE Stock SET quantity = (quantity - 1) WHERE EXISTS (SELECT bookID FROM Sales WHERe Sales.bookID = 100 AND Sales.bookID = Stock.bookID)"

I also think that you already get the bookID from Request.Form("ID"). So, you will actually be getting the bookID=100 from bookID= Request.Form("ID"). The final query will be:
 
MM_updateStock.CommandText = "UPDATE Stock SET quantity = (quantity - 1) WHERE EXISTS (SELECT bookID FROM Sales WHERe Sales.bookID =  "+ Request.Form("ID")   + " AND Sales.bookID = Stock.bookID)"

You must be sure to get an ID from Request.Form("ID"), or you will get a SQL error.
Commented:
try

<%'update stock
Dim MM_updateStock
    Set MM_updateStock = Server.CreateObject ("ADODB.Command")
    MM_updateStock.ActiveConnection = MM_conn_STRING
    MM_updateStock.CommandText = "UPDATE Stock SET quantity = (quantity - 1) WHERE bookID = ?"
      MM_updateStock.Parameters.Append MM_editCmd2.CreateParameter("param1", 202, 1, 255, Request.Form("ID")) ' adVarWChar    
    MM_updateStock.Prepared = true
    MM_updateStock.Execute
    MM_updateStock.ActiveConnection.Close
%>

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial