I've got a real doozy of an SQL problem, if anybody out there can help me. This is for a business website with the standard shopping cart system and so forth, on an ASP page with a SQL Server 2005 backend. For whatever reason, after it goes through the checkout process, the two order related tables (order_info and order_details) start acting really strange. When the user goes in to view his past orders, sometimes that query will only return the very first row. What makes it even more strange, is that by hitting F5 and reloading the page, it will fix itself then break then fix itself, over and over. This only happens for the first 5 minutes or so after the user has submitted an order, after which, he doesn't have any problems.
It's using the default settings for the ADO Recordset.Open - nothing special there - and I've tried messing with different settings to get it to behave. I thought it was some kind of locking issue, but that doesn't appear to be the case (I can't say I'm aware of a case where a locking conflict would cause something to return 1 row and then halt without throwing an error). I know that its not a problem with the data in the table, both because it will sometimes work right and also because if I go and view the table data through SQL Server Query Analyser it always works, even while the web page is fritzing out.
I'm thinking there's something in the ADO Recordset or Connection, but I don't know what. I've done so much poking and prodding that I'm sure it isn't the SQL (I mean, what could go wrong with "SELECT * FROM order_details WHERE order_id = '" & order_id & "' AND is_submitted = 1") that's causing it to only return one row sometimes.
have you run profiler to ensure that the same queries are getting run on every loading of the page? Have you viewed the page source to ensure there are no hidden errors that are getting thrown? Could you post the asp code that is giving you a problem?