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:
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:
<![CDATA[
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!
