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!
 
I can't reproduce this. The following works for me. Can you see anything
different between what I'm doing and what you're doing that might explain
the different result?

Public Sub TestCommand()

Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "SELECT * FROM tGuardian WHERE " & _
"GuardianID IN (SELECT GuardianID FROM " & _
"tGuardian WHERE EmailAddress = '(e-mail address removed)')"
.CommandType = adCmdText
End With
Set rst = cmd.Execute
Debug.Print rst.Fields(0)

End Sub
 
Here is the subroutine 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

'===================
At this point the .CommandText is equal to "select * from
vwLBFlagSearchResults where LineID in (select distinct LineID from vwSearch
where RespondentEmail='(e-mail address removed)')"

RespondentEmail is a varchar(50)

(e-mail address removed) is an email address
'====================


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
 
Back
Top