Dreamweaver MM_editCmd.CommandText

How do I Response.Write(MM_editCmd.CommandText) with the (appended?) Parameters
in place of the Question Marks so I can look at it and see what it looks like
before it executes? This would be so helpful for debugging! Especially when
part of my SQL will be Dreamweaver generated, and part of it hand-coded.

Below is a piece of what I'm talking about:

'THE LONG AND UGLY...

MM_editCmd.CommandText = "INSERT INTO MyTable (a bunch of FieldNames....)
VALUES(?,?,?,?....) "

'YOU GET THE POINT....
'HERE'S WHERE THE PARAMETERS COME IN -- and so they go... all of them pretty
much like this...

MM_editCmd.CreateParameter("param44", 5, 1, -1,
MM_IIF(Request.Form("FieldName44"), Request.Form("Week_ID"), null)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param45", 202, 1,
4, Request.Form("FieldName45")) ' adVarWChar

'.... AND SO FORTH

'****** RIGHT HERE IS WHERE I WOULD LIKE TO WRITE THE RESULTING SQL TO SCREEN
TO SEE HOW IT READS with real values inserted where the ?'s are in the
MM_editCmd.CommandText.

karindpowell wrote:

How do I Response.Write(MM_editCmd.CommandText) with the (appended?) Parameters
in place of the Question Marks so I can look at it and see what it looks like
before it executes? This would be so helpful for debugging! Especially when
part of my SQL will be Dreamweaver generated, and part of it hand-coded.

Unfortunately, there isn't an automated way to accomplish what you want to do. As you know the CommandText property contains only the SQL and the ? in place. What you'd have to do is to loop over the parameters array and for each parameter replace the ? with the value of the parameter. You'll also have to check the parameter type so that when a parameter is a string or a date you can replace the ? with the value of the parameter wrapped with single quotes ('). And if you're using an access database, then you'll need to wrap any dates with # instead of a single quote (').

Here's a VBScript function that I wrote a while ago that works with the most common parameter types (number, dates, and strings) that Dreamweaver outputs and substitutes in in single quotes for strings and single quotes or # for date data types, you do have to pass in true/false for isAccess. IN the call to the function, change the name of the command, it'll end with _cmd in the Recordset code.

Function deParameterizeSQL (cmd, isAccess)
dateDelimiter = "'"
SQL = cmd.CommandText

If (isAccess) Then
dateDelimiter = "#"
End If

For Each param In cmd.Parameters
Select Case param.Type
Case 3 ' Numeric
SQL = Replace(SQL, "?", param.Value, 1, 1)
Case 5 ' Numberic
SQL = Replace(SQL, "?", param.Value, 1, 1)
Case 135 ' Date or datetime
SQL = Replace(SQL, "?", dateDelimiter & param.Value & dateDelimiter, 1, 1)
Case 200 ' String
SQL = Replace(SQL, "?", "'" & param.Value & "'", 1, 1)

End Select
Next
deParameterizeSQL = SQL
End Function


isAccess = false
Response.Write(deParameterizeSQL( Recordset1_cmd, isAccess))


This works for me, but I can't say that it'll work for anyone else, and I mostly use PHP now, so any issues you run into, I may not be able to help out with.


--
Danilo Celic

http://blog.extensioneering.com/
WebAssist Extensioneer
Adobe Community Expert