Proper syntax to include a paramenter into a SQL string for an Oracle Database

I need to pass a parameter to an Oracle query thru a DSN-less conneection string.  It does not like my syntax, what am I doing wrong?

    adoRS.Open "SELECT * FROM CMIS.UDV_RFS_SR WHERE adoRS!REQUESTOR_ID= gBEMS"


I need to pass the user ID (gBEMS)  to limit the query before the data is used int he rest of my code.

K.
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
OH, now I see there the '*' came from.  I had a typo in a later post.  Sorry.

>>you had the quotes on the wrong object within the sql statement.

Not in the original:  http:#a37046005

I even commented on not knowing what adoRS!REQUESTOR_ID  was and you might need to mess with the syntax but the 'logic' is the same.

If you still do not agree, we can have a moderator decide.
0
 
slightwv (䄆 Netminder) Commented:
adoRS.Open "SELECT * FROM CMIS.UDV_RFS_SR WHERE adoRS!REQUESTOR_ID='" &  gBEMS & "'"

or use bind parameters.  My Access is rusty so I'm not sure about how to use bind variables.
0
 
slightwv (䄆 Netminder) Commented:
well... that is the general idea.

not sure about adoRS!REQUESTOR_ID and what that is.  I might have the syntax mucked.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Karen SchaeferBI ANALYSTAuthor Commented:
I also tried

   adoRS.Open "SELECT * FROM CMIS.UDV_RFS_SR WHERE adoRS!REQUESTOR_ID=" & gBEMS & ""

I am getting a ORA-00927 - missing equal sign in the procedure.

the "adoRS!REQUESTOR_ID" is the user id from the Oracle table.  I have set a DSN-Less ODBC connection string from Access to ORacle.

K
0
 
slightwv (䄆 Netminder) Commented:
I have single quotes in the example I provided if you look real hard.

Not sure that is what is causing that error but...


>>the "adoRS!REQUESTOR_ID" is the user id from the Oracle table

So that is the 'variable' you need to check against the Oracle talbe column called gBEMS?

Then I had it backwards (remember the single quotes):

adoRS.Open "SELECT * FROM CMIS.UDV_RFS_SR WHERE '" & adoRS!REQUESTOR_ID * "' = gBEMS"
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Ok tried your suggestion - Now I get the following error:

Command text was not set for the command object

k
0
 
slightwv (䄆 Netminder) Commented:
My Access is rusty but I'll try:  Can you post more of your code?
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Here is my current code:
Public Sub Update_ProcessInd_SR()

    Dim adoConn As ADODB.Connection
    Dim adoRS As ADODB.Recordset
    Dim sConn As String
    Dim sSql As String
    
   On Error GoTo Update_ProcessInd_SR_Error

     sConn = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY= tcp.world)" & _
             "(PROTOCOL=TCP)(Host=test.ca.abcdb.com)(Port=1521)))(CONNECT_DATA=(SERVICE_NAME=cmisprod.abcdb)));" & _
             "User Id=***;Password=***;"
    Set adoConn = New ADODB.Connection
    adoConn.Open sConn

    Set adoRS = New ADODB.Recordset
        
    adoRS.ActiveConnection = adoConn
    adoRS.CursorType = adOpenKeyset
    adoRS.LockType = adLockOptimistic
    adoRS.CursorLocation = adUseServer
   ' adoRS.Open "SELECT * FROM CMIS.UDV_RFS_SR WHERE adoRS!REQUESTOR_ID=" & gBEMS & ""
    adoRS.Open "SELECT * FROM CMIS.UDV_RFS_SR WHERE '" & adoRS!Requestor_ID * "' = gBEMS"

    Do While adoRS.EOF = False
        sSql = "UPDATE TA_SR SET ta_SR.processed_ind = '" & adoRS!PROCESSED_IND & "' WHERE Ta_SR.Job_group = '" & adoRS!Job_Group & "'"
        CurrentDb.Execute sSql, dbFailOnError
            If adoRS!PROCESSED_IND = "C" Then
                sSql = "UPDATE TA_SR SET TA_SR.SubmittedSR = Yes, Processed_ind = '" & adoRS!PROCESSED_IND & "'" & _
                            " , TA_SR.EDDT = Now()" & _
                            " WHERE Ta_SR.Job_group = '" & adoRS!Job_Group & "'"
            Debug.Print sSql
            
            DoCmd.RunSQL (sSql)
            End If
            
        adoRS.MoveNext
    Loop
    
    adoRS.Close
    Set adoRS = Nothing
    
      'Now call connect
    adoConn.Close
    Set adoConn = Nothing

   On Error GoTo 0
   Exit Sub

Update_ProcessInd_SR_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Update_ProcessInd_SR of Module ModConnectors"
End Sub

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
>>_ID * "'

* not & 
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
changed typo - still getting same error msg.

k
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
thanks for your help I was able to find the solution elsewhere in my other code.

    adoRS.Open "SELECT * FROM CMIS.UDV_RFS_SR WHERE CMIS.UDV_RFS_SR.Requestor_ID = '" & gBEMS & "'"

K
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for kfschaefer1's comment http:/Q_27420795.html#37046280

for the following reason:

found solution elsewhere
0
 
slightwv (䄆 Netminder) Commented:
Is not what I posted pretty much the same thing?  I just didn't have the necessary table information to go on.
0
 
Karen SchaeferConnect With a Mentor BI ANALYSTAuthor Commented:
Sorry to disagree you had the quotes on the wrong object within the sql statement.

adoRS.Open "SELECT * FROM CMIS.UDV_RFS_SR WHERE '" & adoRS!REQUESTOR_ID * "' = gBEMS"


adoRS.Open "SELECT * FROM CMIS.UDV_RFS_SR WHERE CMIS.UDV_RFS_SR.Requestor_ID = '" & gBEMS & "'"
0
 
slightwv (䄆 Netminder) Commented:
Since we could not agree, I'll object and abide by the moderator decision.
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
thanks for your assistance in this matter.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.