ADODB.Command.CommandText Issue

  • Thread starter Thread starter Kyle Jedrusiak
  • Start date Start date
K

Kyle Jedrusiak

I'm using an ADODB.Command object to send a programatically built sql
command to the server from and Access .adp project.

The command that I generate is...

select * from vwLBFlagSearchResults where LineID in (select distinct LineID
from vwSearch where RespondentEmail='(e-mail address removed)')

I set the ADODB.CommandType to adCmdText, I set the CommandTimeout to system
wide settings, and I set the CommandText to the above string.

When I call Command.Execute the object interprets the @x.com as a parameter,
puts up a dialog box asking me for a value for that parameter. If I click
OK or Cancel, the search actually works.

What do I need to do so it doesn't interpret the @ to mean a parameter?

Kyle!
 
One thought: the command object is trying to set transform the query into
its canonical form; you may try replacing the single quote with double
quote.

Something that I remember form DAO is that if you display the CommandText
after having set it; it may not be the same string. You can try with ADO
and see if the string is still the same.

Otherwise, you will have to create and add ADO Parameter objects.
 
Here is the code that is getting called...

Private Sub GetData( _
ByVal pReadOnly As Boolean _
)

On Error GoTo GetData_Error

Dim oCommand As ADODB.Command

Set oCommand = New ADODB.Command

With oCommand

Set .ActiveConnection = CurrentProject.Connection

.CommandType = adCmdText
.CommandTimeout = goSystemSettings.CommandTimeout
.commandText = mSearchCommandText
'==================================================================================================
'
' The line below shows the contents of the .CommandText.
' If I execute this line in Query Analyzer it works as expected.
'
' select * from vwLBFlagSearchResults where LineID in (select distinct
LineID > from vwSearch where RespondentEmail='(e-mail address removed)')
'
'==================================================================================================


DoEvents
DoEvents

Set moSearchResultsRS = oCommand.Execute

DoEvents
DoEvents

If (pReadOnly) Then _
Set moSearchResultsRS.ActiveConnection = Nothing

End With

Error_Exit:

Set oCommand = Nothing

Exit Sub

GetData_Error:

Dim errorMsg As String

Select Case Err.Number

Case gkSQLStatementError

errorMsg = _
"The program was unable to build a valid 'Search Command'
based on the information " & _
"entered in the search window." & Chr$(13) & Chr$(13) & _
"Search Command Text:" & Chr$(13) & Chr$(13) & _
SearchCommandText & Chr$(13) & Chr$(13) & _
"Please contact Princeton Information with this information"

Case gkDBTimeoutError

errorMsg = _
"A Time-Out occured before the search could complete." &
Chr$(13) & Chr$(13) & _
"An Administrator can adjust the 'Command Timeout' in the
System Settings maintenance window."

Case Else

errorMsg = _
"The following error occured:" + Chr$(13) & Chr(13) & _
"Error Number: %0" & Chr$(13) & _
"Description : %1"

errorMsg = Replace(errorMsg, "%0", CStr(Err.Number))

errorMsg = Replace(errorMsg, "%1", Err.Description)

End Select

MsgBox errorMsg, vbInformation + vbOKOnly, "Information"

Resume Error_Exit

End Sub
 
Kyle,

where does mSearchCommandText get set. Show us the code! (gee, I hope this
is not a red herring...)

Good luck,

Malcolm
 
Sorry I had replied to Malcom directly...I repeat it here for the group...

The CommandText gets set to a proper search string. If I copy and paste the
sstring into Query Anazyler the query executes properly and returns what
it's supposed to.

It seems to be a bug in the MDAC 2.8 library. I have an standard email
address that is surrounded by single-quotes, the "@" is part of an enclosed
string...yet the library thinks that the "@" is specifying a parameter name.
And thinking it needs a value puts up a dialog box asking the user for that
value.

Just to repeat...here is what is in the command text.

select * from vwLBFlagSearchResults where LineID in (select distinct LineID
from vwSearch where RespondentEmail='(e-mail address removed)')
Here is the "@"
sign------------------------------------------------------------------------------------------------------------^

This string should work as is.

Kyle!
 
If I display the string going into the CommandText and the CommandText after
it has been set are exactly the same.

I tried replacing the single-quotes with double-quotes...but ADO doesn't
like it.

Kyle!
 
Hi Kyle,

I don't know if you ever got anywhere with this, but there's a couple of
possible workarounds I can think of. First off, I'm assuming you're
connecting to SQL Server here...I don't think ADP's can connect to anything
else, but haven't played around all that much, so I'm not positive.

Anyway, first try putting the @ in square brackets...or put a back-slash or
forward-slash before it, or maybe a single quotation? (whatever the default
escape character is for SQL Server)

If you don't get anywhere with that, try replacing your "@" with
CHAR(64)...outside the quotes, obviously. So your string would look like
this:

select * from vwLBFlagSearchResults where LineID in (select distinct
LineID from vwSearch where RespondentEmail='email6' + CHAR(64) + 'x.com')

It sort of defies the point of ADO, since you're now using SQL
Server-specific commands, but if nothing else works, that'll almost
certainly work around your problem.

For myself, I've set up a function that auto-formats a string for the
server, thus putting all the necessary logic in one place, should the
requirements ever change (new version of SQL Server, port to some other
server, whatever). So my VB code would look something like this...

Public Function ServerString(ByVal strIn As String) As String
'This function makes a broad array of assumptions about the string and
does not try to handle unusual situations
'like pre-existing SQL code in the string, other unusual characters,
etc., which might create
'problems under some circumstances. It's just here to give you an idea
of what the function would look like
'and some of the things it would typically address.

'This is actually a really short function, it's all my comments that
make it long. :P Feel free to remove them,
're-distribute this function, whatever...I don't really care much. :)

'Always do single quotes first, cuz otherwise you might replace single
quotes from lines below.
strIn = Replace(strIn, "'","''")

'Replace @ sign.
strIn = Replace(strIn, "@", "' + CHAR(64) + '")

'Replace Carriage Return/Linefeed combo.
strIn = Replace(strIn, vbCrLf, "' + CHAR(13) + CHAR(10) + '")

'Surround the final string by single quotes.
'I find this convenient to do in a function like this, since it provides
you with server-specific
'string formatting that's completely ready-to-go when it returns from
the function
'(Jet, for example would use double-quotes instead of single).
strIn = "'" & strIn & "'"

'From here, if you wish, you can optimize things like any unecessary
empty strings introduced into the final result by an input
'string such as @ <CR/LF> @, which would produce the following using the
above code:
' '' + CHAR(64) + '' + CHAR(13) + CHAR(10) + '' + CHAR(64) + ''
strIn = Replace(strIn, "+ '' +", "+")
If Left$(strIn, 5) = "'' + " Then strIn = Mid$(strIn, 6)
If Right$(strIn, 5) = " + ''" Then strIn = Left$(strIn, Len(strIn) - 5)

'And finally pass back your modified string.
ServerString = strIn
End Function
 
Robert Morley said:
Hi Kyle,

I don't know if you ever got anywhere with this, but there's a couple of
possible workarounds I can think of. First off, I'm assuming you're
connecting to SQL Server here...I don't think ADP's can connect to anything
else, but haven't played around all that much, so I'm not positive.

Anyway, first try putting the @ in square brackets...or put a back-slash or
forward-slash before it, or maybe a single quotation? (whatever the default
escape character is for SQL Server)

If you don't get anywhere with that, try replacing your "@" with
CHAR(64)...outside the quotes, obviously. So your string would look like
this:

select * from vwLBFlagSearchResults where LineID in (select distinct
LineID from vwSearch where RespondentEmail='email6' + CHAR(64) + 'x.com')

It sort of defies the point of ADO, since you're now using SQL
Server-specific commands, but if nothing else works, that'll almost
certainly work around your problem.

For myself, I've set up a function that auto-formats a string for the
server, thus putting all the necessary logic in one place, should the
requirements ever change (new version of SQL Server, port to some other
server, whatever). So my VB code would look something like this...

Public Function ServerString(ByVal strIn As String) As String
'This function makes a broad array of assumptions about the string and
does not try to handle unusual situations
'like pre-existing SQL code in the string, other unusual characters,
etc., which might create
'problems under some circumstances. It's just here to give you an idea
of what the function would look like
'and some of the things it would typically address.

'This is actually a really short function, it's all my comments that
make it long. :P Feel free to remove them,
're-distribute this function, whatever...I don't really care much. :)

'Always do single quotes first, cuz otherwise you might replace single
quotes from lines below.
strIn = Replace(strIn, "'","''")

'Replace @ sign.
strIn = Replace(strIn, "@", "' + CHAR(64) + '")

'Replace Carriage Return/Linefeed combo.
strIn = Replace(strIn, vbCrLf, "' + CHAR(13) + CHAR(10) + '")

'Surround the final string by single quotes.
'I find this convenient to do in a function like this, since it provides
you with server-specific
'string formatting that's completely ready-to-go when it returns from
the function
'(Jet, for example would use double-quotes instead of single).
strIn = "'" & strIn & "'"

'From here, if you wish, you can optimize things like any unecessary
empty strings introduced into the final result by an input
'string such as @ <CR/LF> @, which would produce the following using the
above code:
' '' + CHAR(64) + '' + CHAR(13) + CHAR(10) + '' + CHAR(64) + ''
strIn = Replace(strIn, "+ '' +", "+")
If Left$(strIn, 5) = "'' + " Then strIn = Mid$(strIn, 6)
If Right$(strIn, 5) = " + ''" Then strIn = Left$(strIn, Len(strIn) - 5)

'And finally pass back your modified string.
ServerString = strIn
End Function




sign------------------------------------------------------------------------
------------------------------------^
Click to expand...
 
Back
Top