Order number duplicating and I don't now why??

I have this code adding to the cart. However, the database shows the next order number to be 187, but it started all over at 1 and duplicated the order. Can anyone point me to a clue or something. I cannot figure guy out.


<%@ Language=VBScript %>
<% pageTitle = "Add To Cart" %>
<!-- #include file="db.asp" -->
<!-- #include file="adovbs.inc" -->
<%
Sub CreateNewOrder()
Application.lock
if Application("orderID") = "" then
Application("orderID") = 2000
end if


intOrderID = Application("orderID")
Session("orderID") = intOrderID
Conn.Execute("INSERT INTO orders " _
& " (orderID, status) values " _
& " ("&intOrderID&", 'OPEN')")

Application("orderID") = Application("orderID") + 1
Application.Unlock
End Sub

Sub AddToOrder(nOrderID, nProductID, nQuant, nInstall, nproductCode)
sqlText = "INSERT INTO itemsOrdered " _
& " (orderID, productID, quantity, installationtype, productCode) values " _
& " ("&nOrderID&", "&nProductID&", "&nQuant&", "&nInstall&", '"&nproductCode&"')"
Conn.Execute(sqlText)

End Sub


'Main program
intProdID = Request.form("intProdID")
intQuant = Request.form("intQuant")
strInstall = Request.form("strInstall")
strProductCode = Request.form("strProductCode")

set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open ConString

intOrderID = cstr(Session("orderID"))

if intOrderID = "" then
CreateNewOrder
end if

sqlText = "SELECT * FROM itemsOrdered WHERE orderID =" & intOrderID & "AND productID =" & intProdID
set rsOrder = Conn.Execute(sqlText)

if rsOrder.EOF then
txtInfo = "This item has been added to your order."
AddToOrder intOrderID, intProdID, intQuant, strInstall, strProductCode

else
txtInfo = "<font size=2 face=verana>This item is already in your cart. <br>Click below to change the quantity or to delete the item</font>"

end if


%>


<%
Conn.Close
set Conn = Nothing
%>

I believe u used an application-variable to build up the orderID.
If so, read on. else STOP

this application-variable is probaly defined in your global.asa like this

Sub Application_OnStart
Application("orderID")=0

So if your application was restarted ..... then your in trouble !!! (And by the looks of it, this happened)
Application variables should not be used for this sort of work.

If this was the case, I can help you out, so let me know.

<SCRIPT LANGUAGE=VBScript RUNAT=Server>



Sub Application_OnStart
'==Visual InterDev Generated - startspan==
'-- Project Data Environment
'Set DE = Server.CreateObject("DERuntime.DERuntime")
'Application("DE") = DE.Load(Server.MapPath("Global.ASA"), "_private/DataEnvironment/DataEnvironment.asa")
'==Visual InterDev Generated - endspan==





Dim vPath, pPath, ConString

vPath = "database\mcart2.mdb" 'use this one if database is in root of cart folder
'vPath = "\database\mcart2.mdb" 'use this one if database is in root of your website
pPath = Server.MapPath( vPath )

ConString = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & pPath & ";" & "JET OLEDB:Database Password=foo"

Application("visits") = 0
Application("Active") = 0

set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open ConString

set rsMaxOrder = Conn.Execute("select orderID from" _
& " orders " & "order by orderID desc")
if rsMaxOrder.EOF then
Application("orderID") = 1
else
Application("orderID") = rsMaxOrder("orderID") + 1
end if
rsMaxOrder.Close
set rsMaxOrder = Nothing

Conn.Close
set Conn = Nothing

End Sub

Sub Session_OnStart
Session("Start") = Now
Application.lock
Application("visits") = Application("visits") + 1
intTotal_visitors = Application("visits")
Application.unlock
Session("VisitorID") = intTotal_visitors


Application.lock
Application("Active") = Application("Active") + 1
Application.unlock
End Sub

Sub Session_OnEnd
Application.lock
Application("Active") = Application("Active") - 1
Application.unlock
End Sub

</SCRIPT>

Store your order number in a table within your database.

What Roy Sinclair said - and using the order number as the primary key will prevent duplication.

If the "Primary Key" looks too plain to you, you can always add random characters to the beginning or end of it, or a datestamp, and save that as an "OrderNumber" field... just something to spice it up...

I have an access database with a products table, an itemsorder table (shopping cart) and an orders table for completed orders.

When some one adds to their basket, it writes the orderID, quantity and productID to itemsordered when they checkout, it writes the customer info to the orders table.

The itemsordered and orders are associated by orderID.

So based on your responses, simply making orderID within my itemsordered table the primary key will solve my problem?

sorry I didn't respond quicker. (It's mighty hot in Belgium)

I would do the following:
1. fix your global.asa and make a session-variable. Delete the previous application variable.
Sub Session_OnStart
session("orderID")=0
session("customerID")=0

2. In your itemsorder table, add a new variable (lets say "product" ), with datatype AutoNumber. By "indexed", choose "Yes (No Duplicates)"

This wil fix your initial problem.
Buth I think this wount be the whole sollution for you because your data isn't written to your database in one movement.


3. Do step 2 for the order-table buth on the OrderID variable
4. Start by writing to your order-table when a customer logs in. Puth in his info and Access will enter a new unique value in the OrderID table.
5. Now you need to get this number to use the "inner joint" with the itemorder table.
Do this with a select query on the order table and use all the info you have as conditions. something like
select orderID from orders where customerID = login and date = #datestart# and time = #timestart#

(If you don't have enough info to make a unique selection, then add a date and time like above. The datestart end timestart variables are filled in at the top of the page, and are written down in the database with the insert-query.)


Then puth this orderID in the sessionvariable with something like this

session("orderID")=rsOrderID.Fields("OrderID")

6. When you now write to your itemsorder table, you also insert this session("orderID")


So you wille have a 1 on many link between the two tables (based on the OrderID) and unique indexed, autonumbered variables in both tables (orderID in orders and product in itemorders)

It's (chronologically) the reverse of what you do, buth you know this makes sense.


Hope this helps you. if not, let us know.

I am not sure about step 1. What do you mean? What do I delete and do I insert the Sub Session_OnStart below?


. fix your global.asa and make a session-variable. Delete the previous application variable.



Sub Session_OnStart
session("orderID")=0
session("customerID")=0

I guess I don't understand why this is happening? Why would it reset when it is at 2076 and go back to 1

Originally posted by details
I guess I don't understand why this is happening? Why would it reset when it is at 2076 and go back to 1

Your number was stored in an application variable. When your application is started the variable is initialized and then incremented as the application runs. As your application ran you got orders until the variable reached the value of 2076. After that point some event happened to cause your application to stop running on the server (system reboot, a change to some page, a deliberate stopping of the application or even the application timed out for inactivity and was shut down).

When that happened, the next time your application was started the variable was again initialized back to the value which the code set it to every time the application starts.

If you want to keep unique values, you must store and update the variable outside the application. Since you already have a database it's a simple matter storing that variable in a table within that same database.

Does that make it clearer?

OK.

Sit back and relex (it's gona be a long one)

I think Mr Sinclair cleared up your last question (the resetting). You should realize that an application variable isn't a very permanent/secure thing, and you shouldn't store anything in it that you want to use over a long period.

Only store data in it that is ment to be shared with your users. Like the neath feature on this forum that shows you whitch user are currently logged in. The application variables contain data that is simmilar for every user (at that time) Examples : logged in users, highest bidding etc.

Your
session variable contains data for one user (if somebody connects to your application, a session is started and you can use session variables to store data for that specific user.

Now, data you need to tore permanently should be in your DB.
The orderIDs need to be permanently stored. The details for the orders also.
To avoid a lot of connections (and traffic), it's usually a good idee to create a session variable where you store data in that you pulled out of your DB, or that you want to insert in it later.

So far the lesson.

No, one more thing.

In your global.asa, you have 4 events in 4 subs:
Sub Session_OnStart
Sub Session_OnEnd
Sub Application_OnStart
Sub Application_OnEnd

What you should do (step 1) is to insert te following in your global.asa (If the sub allready exists, skip the first line)
Sub Session_OnStart
session("orderID")=0
session("customerID")=0

This will 'create' the variables for that session + sets them to 0. Think of them as waiting memoryspace.
If your not sure about editing the global.asa, then just don't delete the application-variable and simply don't use it anymore in your asp's.


Now, I think step 2 - 3 where cleare (just preparing the tables so they can store the data)

When a new session starts, I would use the login-data to insert a new record in the orders-table. Your autonumer variable will generate a new unique value (previous+1, is standard)

In step 5 you pick that value up whith a select query, and you store it in the session-variable.

When the user want's to order a specific product, you insert that value with the other data in your itemsorder table.


It's not that difficult (once you have used it once) and it's worthe the efford to fully understand this proces because it's the core of any databasedriven application.


So, don't give this up and let us know if you have any problems.

Ok so I have added the following the the global.asa below:
session("orderID")=0
session("customerID")=0
see below...to make sure I did it right.

K, now, where does customerID come into play?

Also, in step 2 with the itemsordered table, how can I add an autonumber, when the orderID is duplicated because of more then one product in the shopping cart?



<SCRIPT LANGUAGE=VBScript RUNAT=Server>



Sub Application_OnStart
'==Visual InterDev Generated - startspan==
'-- Project Data Environment
'Set DE = Server.CreateObject("DERuntime.DERuntime")
'Application("DE") = DE.Load(Server.MapPath("Global.ASA"), "_private/DataEnvironment/DataEnvironment.asa")
'==Visual InterDev Generated - endspan==




Dim vPath, pPath, ConString

vPath = "database\mcart2.mdb"

pPath = Server.MapPath( vPath )

ConString = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & pPath & ";" & "JET OLEDB:Database Password=foo"

Application("visits") = 0
Application("Active") = 0

set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open ConString

set rsMaxOrder = Conn.Execute("select orderID from" _
& " orders " & "order by orderID desc")
if rsMaxOrder.EOF then
Application("orderID") = 1
else
Application("orderID") = rsMaxOrder("orderID") + 1
end if
rsMaxOrder.Close
set rsMaxOrder = Nothing

Conn.Close
set Conn = Nothing

End Sub

Sub Session_OnStart
session("orderID")=0
session("customerID")=0
Session("Start") = Now
Application.lock
Application("visits") = Application("visits") + 1
intTotal_visitors = Application("visits")
Application.unlock
Session("VisitorID") = intTotal_visitors


Application.lock
Application("Active") = Application("Active") + 1
Application.unlock
End Sub

Sub Session_OnEnd
Application.lock
Application("Active") = Application("Active") - 1
Application.unlock
End Sub

</SCRIPT>

About your first question (the global.asa) : this should work. (I must say that my global.asa 's look a little less elaborated)

About the customerID session-variable : I alway use a session variable if I want to display some data on 'every' page, or if I need to insert that data on more then one occasion. So, If a new user opens a session (logs in), I validate his username and pasword against my DB, and if everything is OK, I write the username in a session variable (the session("customerID") )

I assume your users don't allways have to go straight to the "ordering" part of your site (Maybe they first want to look up their ordere product, the state of their order, read about new product, etc etc). Buth when they do want to start a new order, you would be in trouble whitout the session-variable, because you can only pick up data out of a form if it was on the previous page, so you wouldn't have their username (I know you could store it in the querystring and drag it along, buth that has some serious disadvantiges).

THE FOLLOWING PARAGRAPH DOES NOT CONTAIN USESFULL TIPS
The quickest and easyest way to solve all this is to open a new order each time somebody connects and to assign, automatically, a new unique number to it. and surely, the easyest way to do all this is by creating an application variable that does this just the way you tried it.
BUTH YOU NOW KNOW WHERE THIS IS LEADING TO ... (The number is raised by 'non-succesfull logins' (everybody that browses your startpage starts a session and ...), the application can be restarted (and you start at 0), ...


Buth this is a better way to do it :
- a user logs in --> in a tabel that contains the customer-info, you look up his pasword and compair it to the one on the login-form (use the username as condition in your selectquery)
- login succesfull --> store his username in the session("customeID")
- when he want to place a new order --> insert a new record in your orders-table. (each record should at least contain the username so you can link it to the customer-ifo table , and a unique orderID so you can link it to the itemsordered table) The orderID is an AutoNumber variable (see previous messages)

- pick up the data in this new records orderID-field (see previous message)

and so on (see previous messages)

Now about your last question (the AutoNumer in the itemsordered table)

This is no problem. You can have more then one record with the orderID, in the itemsordered table. (In the orders-table, the orderID is unique because this should be your primary key)
In the itemsordered table, the primary key is the productorderedID (or an itemorderedID, or whatever you want to call it) variable. And this is an AutoNumber variable. Buth you have to include the orderID in every record, to have the relation with the orders-table (which in turn has a relation to the customer-info-table) and you obviously also need to include a productcode (for the relation etc etc)

Like this, you have a relational database with primary key's in every table, and where you can pull out the data you need (even spread over 10 tables) in one select query.


Hope this gets you a bit further. I think you are allmost there. Give us a sign if you still have questions.

I just created a function and replaced all code requesting the application variable. This works fine. Thanks for all the help!!!

<%

function GetNextOrderID()
dim rsMaxOrder


set rsMaxOrder = Server.CreateObject("ADODB.Recordset")
rsMaxOrder.Open "SELECT MAX(orderID) AS LastID FROM orders", Conn, adOpenStatic, adLockReadOnly, adCmdText

GetNextOrderID = cint(rsMaxOrder("LastID")) + 1

rsMaxOrder.Close
set rsMaxOrder = Nothing

end function

sub CreateNewOrder()

intOrderID = cstr(GetNextOrderID())

Session("orderID") = intOrderID
Conn.Execute("INSERT INTO orders (orderID, status) values (" & intOrderID & ", 'OPEN')")

End Sub

You might want to add a lock around your order code:

sub CreateNewOrder()

Application.Lock
intOrderID = cstr(GetNextOrderID())

Session("orderID") = intOrderID
Conn.Execute("INSERT INTO orders (orderID, status) values (" & intOrderID & ", 'OPEN')")
Application.Unlock

End Sub

Just to ensure two separate requests don't overlap calls to GetNextOrderID and the database insert.

I got it. I just took out the application variable and made a function to get the MaxOrderID. Works greats. Thanks for all the help.