View Full Version : Microsoft JET Database Engine (0x80040E14)

11-17-2003, 08:23 AM
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''.

11-17-2003, 02:16 PM
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".

11-18-2003, 02:34 AM
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 )
Response.Write "OK Done!"

11-18-2003, 02:47 AM
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.

11-18-2003, 03:15 AM
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.

11-18-2003, 03:41 AM
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.

11-18-2003, 04:01 AM
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.