Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Nov 2003
    Location
    Malaysia (Boleh!)
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Microsoft JET Database Engine (0x80040E14)

    Can someone identify syntax error of below? Thanks.

    SQL = "UPDATE reservationdetails INNER JOIN reservation ON ReservationDetails.ReservationID = Reservation.ReservationID "
    SQL = SQL & "SET Quantity = " & qty & " WHERE AND status = 'temp' "

    Error mesg:
    Microsoft JET Database Engine (0x80040E14)
    Syntax error (missing operator) in query expression 'AND status = 'temp''.

  • #2
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Well I didn't think you could do a JOIN in an UPDATE but my SQL is iffy at the best of times. But anyway, the error is because you have "WHERE AND" - delete the "AND".

  • #3
    New to the CF scene
    Join Date
    Nov 2003
    Location
    Malaysia (Boleh!)
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update problem

    I used the below code to update the reservation list. But the problem is these code got problem. See attachment. After I put 2, 5, 10 under quantity column, all quantity value will become 10. I think it takes the last value and update to all records. So, how do I fix it? Thanks.

    <%
    qty = Request("quantity_fr")
    ids = Split( strProductID, ", " ) ' that is COMMA-SPACE
    qtys = Split( qty, ", " ) ' that is COMMA-SPACE

    For ix = 0 To UBound(ids)
    id = ids(ix)
    qty = qtys(ix)
    SQL = "UPDATE reservationdetails INNER JOIN reservation ON ReservationDetails.ReservationID = Reservation.ReservationID "
    SQL = SQL & "SET Quantity = " & qty & " WHERE status = 'temp' "
    conn.Execute( SQL )
    Next
    Response.Write "OK Done!"
    response.end
    %>
    Attached Thumbnails Attached Thumbnails Microsoft JET Database Engine (0x80040E14)-update_list.gif  

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hmm. That's what you're doing.

    This seems like it should be a fairly simple update, but can you explain in words (not code) what you're trying to do?

    I don't see the significance of "2, 5, 10" or what you're trying to do there.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #5
    New to the CF scene
    Join Date
    Nov 2003
    Location
    Malaysia (Boleh!)
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Basically, this is revervation system. The user will select the product he wants (using checkbox) and there is a link to view the selected items. When he clicks the link, the revervation list page will come out (previous attachment). All quantity is by default set to zero and only at this point of time, user will edit the quantity column and after that click update button and the system will update the whole list of item in batch.

  • #6
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hmm... ok then if I were you I'd sit down and plan on paper how you want to update the records. Right now you're looping through a comma delimited string, and updating everything in a way I don't think makes any sense (but I could be wrong).

    But don't you keep track of the inventory as well? Therefore you're going to have to subtract the number of items ordered once someone completes an order - and you'll have to do that individually.

    One way to do it would be to keep track of the item AND value like:

    <input type="text" name="item" value="3|10" />

    ...or something like that... so you know that the item's ID is 3 and the user ordered 10 of them. That's just one way, as I said, maybe it will give you some ideas. Without knowing more details about your reservation system I'm kind of stuck.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #7
    New to the CF scene
    Join Date
    Nov 2003
    Location
    Malaysia (Boleh!)
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Actually, what you said is true because this is a quite a complex system but at this point of time, I just need to do something simple assigned by my superior. He wants to see if I can develop something simple before gives me the green light to develop the whole system. I have done inventory system before but just once and also it is not efficient enough because I am stuck mainly in the area of update multiple, insert multiple.

    Back the my initial problem, perhaps I should let you know my database structure a little.

    I use two tables:

    1. Reservation (ReservationID, CustomerID, Reserved_Date, Discount)

    2. ReservationDetails (ReservationDetailsID, ReservationID, ProductID, Quantity, Status)

    When I add the items to list, the code will insert those items into these 2 tables and set quantity = 0 and status = temp.

    Then, when I click view reservation list page, it will display all items added into list under that customerID (session).

    So, all quantity at this time is 0 and user will update that quantity and lastly update as batch (which is my initial problem).

    Hope this is much clearer. So, how do update as batch here? I will appreciate all help.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •