When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
User Tips: Preparing SQL Command Text


By Stephen V.

Here's an ASP tip. I've seen it mentioned once or twice, but I cannot stress how useful this practice can be for debugging complicated scripts.

In working with ADO objects, it's almost always useful to compose your sql query text into a string variable and then call whatever method you are using to execute by passing the string variable. Even more important is the practice of wasting space by including the following line in your code:

'Response.Write(sql)

I put this just before the execution of the command. Note the single quote which indicates a comment. This may seem like overkill at first, but once you have worked with a dozen or so pages and frequently changing design specs during the process of creating a web site, you will quickly learn that you will frequently need to uncomment this line and examine your sql text to see where the problem lies. My practice is something like this:

dim cn dim rs dim sql set cn = server.createobject("ADODB.connection") set rs = server.createobject("ADODB.recordset") cn.open Application("MYCNSTRING") {composition of sql and initialization of rs} 'response.write(sql) set rs = cn.execute(sql)

I put these two lines right next to each other so that I can quickly comment out the execution and uncomment out the response.write. Generally I create a page and have the execute commented out and the response.write uncommented out, check the sql to make sure I've composed it right, and if so switch the comments around. I do this because it's sometimes disastrous if you insert malformed data into the database. Even if it's not disastrous, if you don't have easy access to the table and can only manipulate the data with ASP, it's a pain to go in and delete this malformed data.

Even at production time, I leave this commented line in there, just in case I ever need to start debugging again.

Happy Programming!

Return to user tips...


ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article