Progress 9.1C + ASP + Insert Queries = Headache for progress newbie

[markpremier]

I've built a shopping cart object in vbscript/asp that stores all relevant item selection and identification data in a progress table.

Once someone selects an item to place in the cart, the form shoots them over to an intermediary page which adds the item to the cart and then returns them to the page they were on.

Here's the problem. I can't seem to commit the transaction via an Execute method using the current connection object. It raises a SQL syntax error every time. The insert appears in the database as verified by SQL Explorer, and by attempting a second insert of the same PK tuple <Session id, item number> raising a key violation error.

Given enough time, but not as long as a session timeout, the transaction posts and the shopping cart works as it should. But it seems bad form to force a coffee break everytime someone adds an item to the cart.

Also troubling is that update queries work instantaneously. I suspect it may be a dirty read.

Long story short, is there a way to commit a transaction on Progress from a VBScript/ASP page?

Things I've tried, and have failed:
connObj.Execute &quot;INSERT INTO table(...) VALUES(...)&quot;
(insert works, but takes maybe 5 minutes to commit)

connObj.Execute &quot;INSERT INTO table(...) VALUES(...)&quot;
connObj.Execute &quot;COMMIT Work&quot; | &quot;Exec SQL COMMIT WORK&quot; | &quot;COMMIT&quot;
(barfs raising Syntax error (7587))

connObj.BeginTrans
... Insert [Commit] ...
connObj.CommitTrans
(behaves as cooresponding transactions w/o Begin and Commit above)

Any help would be greatly appreciated.

Mark

 

[harebrain]

I'm not sure this will completely solve your problem, but by omitting EXECUTE's &quot;optional&quot; third parameter you incur the overhead of creating and returning a closed recordset. Never let ADO do work that it doesn't need to. (See

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdamth02_5.asp)

For your first example, try

connObj.Execute "INSERT INTO table(...) VALUES(...)", , adExecuteNoRecords

It's simple enough to be worth a shot.

Out of curiosity, why do so many people seem to be using ADO to execute SQL statements (according to MSDN, the &quot;alternate&quot; method) rather than using ADO's built-in properties and methods? (Which in this case would be Recordset's .AddNew, .Fields() = &quot;something&quot;, and .Update) Did we all buy the same crappy book whose author didn't really figure out the ADO interface?

--David

 

[markpremier]

I see the point concerning the overhead, but I'd be curious to see how much work is involved in returning essentially a null object.

Adding adExecuteNoRecords didn't do the trick however.

SQL and ADO? I can only speak for myself but coming from a heavy MySQL background we didn't get to play with neat toys like a dynamic recordset so all that's left is SQL and walking up a hill coming and going during a snowstorm to school.

Whether Progress supports the dynamic recordset I have no idea at this point, but I'll give it a shot...