by LockwoodTech (3 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Sun 23rd July 2000
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Code for converting the Parameterized ADO Command object into a SQL String that can be executed in Query-Analyzer for better error diagnostics
VBcoders
' that can be executed in Query-Analyzer
Public Function Cmd2SQL(objCmd As ADODB.Command) As String
'-------------------------------------------------------
' Name: Cmd2SQL
' Description: Takes and ADO Command object and translates it into a SQL string
' that you can run in Query-Analyzer to get a better error message or use
' in your application
' Parameters: objCmd As ADODB.Command
' Returns: String - the parameter text with or without quotation wrappers
'-------------------------------------------------------
Dim strSQL As String
Dim n As Integer
' Take out all extra characters in CommandText
strSQL = objCmd.CommandText
strSQL = Replace(strSQL, "?", "")
strSQL = Replace(strSQL, "{", "")
strSQL = Replace(strSQL, "}", "")
strSQL = Replace(strSQL, " ", "")
strSQL = Replace(strSQL, "call", "")
strSQL = Replace(strSQL, "(", "")
strSQL = Replace(strSQL, ")", "")
strSQL = Replace(strSQL, ",", "")
strSQL = Replace(strSQL, "=", "")
' Convert parameter names to SQL @parameters
For n = 0 To objCmd.Parameters.Count - 1
If objCmd.Parameters(n).Name <> "RETURN_VALUE" Then
strSQL = strSQL & " @" & objCmd.Parameters(n).Name & " = " & _
WrapWithApos(objCmd.Parameters(n)) & ", "
End If
Next n
' Take off trailing comma
Cmd2SQL = Left(strSQL, Len(RTrim(strSQL)) - 1)
End Function
Private Function WrapWithApos(prm As ADODB.Parameter) As String
'-------------------------------------------------------
' Name: WrapWithApos
' Description: Interrogates parameter for special cases then calls the Quote
' function to wrap the parameter value with quotes if applicable
' Parameters: prm As ADODB.Parameter
' Returns: String - the parameter text with or without quotation wrappers
'-------------------------------------------------------
Dim strText As String
If IsNull(prm.Value) Then
strText = "NULL"
ElseIf IsDate(prm.Value) Then
strText = "'" & prm.Value & "'"
Else
strText = prm.Value
End If
If prm.Value <> "NULL" Then
If Quote(prm.Type) = True Then
strText = "'" & RTrim(strText) & "'"
End If
End If
WrapWithApos = RTrim(strText)
End Function
Private Function Quote(intPrmType As Integer) As Boolean
'-------------------------------------------------------
' Name: Quote
' Description: This function determines if a ADO Command Object Parameter should
' be wrapped with quotes when it is converted to a SQL string or not
' Parameters: Parameter Type as integer
' Returns: Boolean
' True - this is a string param and should be wrapped with quotes
' False - this is a numeric param and should not
'-------------------------------------------------------
Dim bolVarQuote As Boolean
Select Case intPrmType
Case Is = adNumeric
Case Is = adVarBinary
Case Is = adUnsignedTinyInt
Case Is = adSmallInt
Case Is = adBoolean
Case Is = adSingle
Case Is = adCurrency
Case Is = adInteger
Case Is = adDouble
Case Is = adBinary
Case Is = adVarBinary
Case Is = adLongVarBinary
Case Is = adLongVarWChar
bolVarQuote = True
Case Is = adVarChar
bolVarQuote = True
Case Is = adWChar
bolVarQuote = True
Case Is = adDBTimeStamp
bolVarQuote = True
Case Else
bolVarQuote = True
End Select
Quote = bolVarQuote
End Function
No comments have been posted about Code for converting the Parameterized ADO Command object into a SQL String that can be executed in . Why not be the first to post a comment about Code for converting the Parameterized ADO Command object into a SQL String that can be executed in .
