ASP session on end - HELP!

whiteshadow

I am scripting up a shopping cart, i store the CartID and the items in a database, i have created the following in the global.asa file.

To stop the database getting too big i want to delete the cart items from the database when the session ends..

here's my code but im having no joy.. is there another option.
Would a subroutine in MySQL maybe be an option..
to delete all sessions/entries that are over a day old..
is these even possible?


<SCRIPT LANGUAGE=VBScript RUNAT=Server>

Sub Application_OnStart
End Sub

Sub Application_OnEnd
End Sub

Sub Session_OnStart
Dim orders, i, r1, intOrderID
Randomize Timer
r1 = Rnd
r1 = r1 * 1000000
intOrderID = 1000000 + Int (r1)
Session("CartID") = intOrderID
End Sub

Sub Session_OnEnd
clearorder
End Sub


Sub clearorder
db_server = "sql3.xxx.ie"
db_name = "xxx"
db_username = "xxxx"
db_userpassword = "xxx"

connectstr = "Driver={MySQL ODBC 3.51 Driver};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open connectstr
Conn.execute("delete * from cart where cart ="&Session("CartID"))
Conn.Close
End Sub

</SCRIPT>

ron_darrell

Hi,

Firstly as you are already storing the data in a session variable why are you also storing it on the database? If you only store it in the session variable then it will simple disappear once the session expires. If you were not using the session variable I could see an argument for saving on server resources but seeing as you are not doing this I can't see why you need to save the variables to the database also. However that your design so not my prob.

There seem to be a few small things in the on_start and clearorder sub that might be causing the problem. Try replacing :

1. Int(r1) with Cint(r1)
2. Conn.execute("delete * from cart where cart ="&Session("CartID")) with
Conn.execute("delete * from cart where cart ="&Session("CartID")&";")

as SQL commands general prefer to have a closing ';' to let them know that the statement is over.

The random number generator is a good idea I suppose but couldn't you just have an incrementing autonumber on the row. There may be problems with you generating the [cart] number, especially if there are a large number of hits on the site (the vb random number does a reasonable job of randomising however it is not without possiblity that 2 users may end up with the same [cart] id which could cause you some interesting issues for your users. It's late & I'm tired so I may be missing something else. I'll take another look at it in the morning. Hope it was some help.

-RD

Draupnir

Session OnEnd is largely unreliable and will not always be fired within your application. In fact, a lot of the time it will not be fired and the code will never run.

Examples would be users who close their browser window or users who shut down their PC mid visit to your site can all cause this event not to fire. There are more examples.

I would advise that you avoid trying to use Session OnEnd for the kind of functionality you describe above, otherwise you will be left with many orphan records in your database.

As described above, if this data is only needed for the duration of the session, is there a need to ever put it into the DB?

whiteshadow

Hi thanks for the replys

what im storing in the database is items in a shopping cart. so for my CartID i have an itemID and the quantity...

The above code worked when i changed the session timeout to be 1 minute and fixed this schoolboy error

Conn.execute("delete * from cart where cart ="&Session("CartID"))

changed to
Connexecute("delete from cart where cart ="&Session("CartID"))
duh!

but i think i need to just store the cartID in the session and then use the database for all the rest of the functions, be it adding/editing or deleting items from the cart.

Maybe i should assign a cartDate to it. and then delete items that are maybe more than 4 hours old or something...

Are stored procedures possible in mySQL?

ron_darrell

They are. Seriously though for the functionality you seem to be looking for it would make more sense just to keep the variables as session variables until such time as the order is put through. You're taking a double resource hit by storing them in both the session variable and the db and then a further resource hit each time you want to reference/edit them as the db will need to be interrogated each time. I really would suggest abandoning the storing temp vars to the db idea.

-RD

Seth Brundle

If the items are stored as session variables then if the session times out or if the user leaves and subsequently returns they (from what I gather) can't continue.
If it were me I would store the session number as a cookie on the users machine. This number would reference the items in their cart - stored in a DB.
Storing items in a DB would also allow you to track users who selected items and then didn't go to the checkout - it may reveal some info about your customers.

ron_darrell

Aren't there ethical problems with storing that kind of info ?? Also isn't a bad idea to allow the user to go and come back and continue as before ?? How can you guarantee it is the same user ?? If that is what you looking for then yes perhaps storing it on the db would be a good idea but there would need to be significant security in place to prevent abuse (i.e. a registered user can only store that kind of info and would need to log back on when they come back at the very least) However I would still suggest not placing this data on the db until the last possible moment (i.e. provide a save to 'my items' link that saves this data to the db rather than saving it as we go along) to minimise the impact on resources and speed.

-RD