To read the article online, visit http://www.4GuysFromRolla.com/webtech/tips/t112899-2.shtml

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...


Article Information
Article Title: User Tips: Preparing SQL Command Text
Article Author: Stephen V.
Article URL: http://www.4GuysFromRolla.com/webtech/tips/t112899-2.shtml


Copyright 2017 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers