View Full Version : One Record Only Glitch

10-09-2007, 06:02 PM
Hey folks,

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.

Any help?

10-09-2007, 06:52 PM
You can replicate this?

If so, go into SQL Server Profiler and start a Trace. Run through whatever sequence of steps on your site that produces the error, and look at the Trace output to see which SQL statements are actually being run against the database.

If you can't replicate it... your world just got a lot more painful :( I'd start by suggesting things like:
1. Get your ASP page to write out the SQL statements it's generating. Like a Trace, you need to see what's actually being run against the database.
2. Post your code.

10-09-2007, 10:11 PM
Sadly, I have to VPN into a specifically set network machine, and that machine doesn't have SQL Profiler installed for reasons that are beyond me. And I'm just a contractor, so it's not like they're going to be helpful... *rolls eyes*

Anyways, I think I've found the culprit, and it's something I didn't expect. The client has this include file which, when I don't include it, fixes the problem. Even if I don't run any of the functions that are in the include file. So now I get to run through this include file to check and see just what kind of insanity it's pulling that can cause such a weird error.

I'm not sure if I should just close this as resolved (since I have, essentially, found the error), or wait until I pin down the exact thing... If for no other reason than to allow somebody who comes after me with the same problem to be able to search and find my solution.