Need help on asp/database query

retrieve and update a counter in a table

[Meryl_Smith]

I have been using a single record table in a database to keep track of an orderid number for a shopping cart application. I am having problems that surfaced only on the server which hosts the website ( My test machine works ok, which drives me nuts!).

I have a 10 character field which I select, save into a session variable, increment, and put back. I then use the session variable to link my user information to the items in the cart, all saved in the database for reference. I use the same order id number in the receipt.

I am having a problem with my script and need to try another way of doing this. My script gives me an error ONLY when the same user tries to create another order after completing the first.

Can someone provide some examples of how I can create a unique order id for this purpose?

For reference, here is the script that bombs the second time around on the server:

<%
OrderIdSessionVar = "OrderID"
' Get a unique OrderID number and save to session.

'instantiate connection object
Set oConn = Server.CreateObject("ADODB.Connection")

'create connection string (this is for a Visual FoxPro database)
sconnString = Session("svDBname")
'open connection
oConn.Open(sConnString)

'create query text

sql = "select NextOrderID from NextOrderID"

'instantiate recordset object

set rsId = Server.CreateObject("ADODB.Recordset")
rsId.Source = sql
rsId.CursorType = 2
rsId.CursorLocation = 2
rsId.LockType = 2
'open the recordset
rsId.open sql, oConn

Session(OrderIdSessionVar) = rsId.Fields("NextOrderID").value
' increment
rsId.Fields("NextOrderID").value = Session(OrderIdSessionVar) + 1

rsId.Update
rsId.Close
set rsId = Nothing
oconn.close
set oconn = nothing

%>

This is the error I am getting:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

Query-based update failed because the row to update could not be found.

/websrc/user_profile.asp, line 53

Thanks for the help. I have a customer waiting for this on Wednesday.

Meryl

[txbakers]

Couldn't you use an auto-increment field? That way you would always have a unique record to post to.

If you needed that new number, you could read the last record of the file and increment it by 1 in your code for use internally.

[duckhunter]

Use the Identity field for sure and insert a record immediately when assigned to a customer.

This is SQL, foxpro should allow for something similar:

--Preferably this is a Stored Procedure
BEGIN TRANSACTION
INSERT INTO NEXTORDERID (InsertDate) values (getdate())
SELECT @@IDENTITY
COMMIT TRANSACTION

The recordset you get back has a Unique (Identity) OrderID.

NOTE: Watch when using triggers and this method. If an Insert trigger is set, the @@IDENTITY you get back is the last inserted Identity, even if it's from the insert that occurred to another table due to the trigger.

[tomasz]

duckhunter:

I don't think @@Identity variable is supported on foxpro it is only supported in TSQL on SQL server. I thought that is flat file database .DBF type. Well I could be wrong.

Meryl:
I would try lock optimistic instead of pessimistic setting and change to open keyset cursor and see what happens :)

rsId.CursorType = 1
rsId.CursorLocation = 2
rsId.LockType = 3

[duckhunter]

If you don't lock exclusively, you run the risk of two users having the same Orderid since it is stored in a user sesion. The auto-increment field should be used and the insert wrapped in a transaction.

You could do this:

Begin
Insert into.....
Select max(id).....
commit

[tomasz]

duckhunter;
I agree if TSQL is available that would be the best way to approach this problem. Unfortunately it is not.
On the locking issue. In typical client server environment that would be a case, but since web server is using only one account which is controlled by system, there is no way you can run 2 processes at the same time. The second process is waiting for first one to finish.

Remeber,In this post he mentioned this is single row table so auto-increment field is not a option (works with insert only).

Maybe something like this on the same connection instead of rs.Open

get NextOrderID ..
sSQL=select NextOrderID from table..

rs=cn.execute(sSQL..
set new id
update set NextOrderID =NextOrderID +1
dim intResult
cn.execute(sSQL,intResult)' update ID
if intResult=1 then ' updated correctly