The absolute simplest thing you can do to save considerable time debugging your database-driven applications is start up SQL Profiler.
I'll admit. I didn't even realize this existed until several years ago at CFUNITED when Charlie Arehart gave a presentation that somehow moved on to showing us the profiler. I can't pick out which one of his many great presentations it was. but what a fortunate turn of events to have been in that room at the time!
I've found this to be most useful in our Ajax applications - situations where we would traditionally use a brute force CFDUMP to see what's going in to the db aren't always possible. Hacking up your code to write the sql to a file or email is nasty and time consuming. Why not just watch the wire instead? Here's how that's done:
Let's start from square one. Start->All Programs->Microsoft SQL Server -> Profiler.
Select "New Trace" from the dropdown menu or the leftmost icon on the toolbar. Connect to your desired database server. In my case. I'm using localhost and windows credentials. YMMV.
The next screen is the most important one: what do you want to see in the log? The names are terrible and don't say enough. Let's over-simplify for a moment and pick the SQLProfilerTSQL_SPs template. To be honest. this is the only one I have used so far.
Don't do anything else - just hit "Run". You'll see some startup messages and what not. Ignore them.
Run a quick template on your site. Not too much - you'll quickly overwhelm yourself (Ctrl-Shift-Del to clear the trace window). You'll see stuff like this:
Here's how you piece that together: the "exec sp_execute..." line is the params being passed in to the SQL statement defined on the following line (UPDATE. SELECT. whatever). At that point it becomes trivially easy to stitch it back together and see what's going on.
Of course. there's FAR more to profiler than this dinky little example. but in terms of getting started without messing with your code it can't be beat. Use it to check security. run performance tests. log a problematic long-running query or simply get around having to touch your code for debugging. If you find a particularly good resource for learning more about profiler. post it here in comments.