?
Solved

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

Posted on 2011-10-28
17
Medium Priority
?
290 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:Karen Schaefer
  • 8
  • 8
16 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37046005
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37046014
well... that is the general idea.

not sure about adoRS!REQUESTOR_ID and what that is.  I might have the syntax mucked.
0
 

Author Comment

by:Karen Schaefer
ID: 37046030
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37046081
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
 

Author Comment

by:Karen Schaefer
ID: 37046127
Ok tried your suggestion - Now I get the following error:

Command text was not set for the command object

k
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37046137
My Access is rusty but I'll try:  Can you post more of your code?
0
 

Author Comment

by:Karen Schaefer
ID: 37046140
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37046176
>>_ID * "'

* not & 
0
 

Author Comment

by:Karen Schaefer
ID: 37046197
changed typo - still getting same error msg.

k
0
 

Author Comment

by:Karen Schaefer
ID: 37046280
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
 

Author Comment

by:Karen Schaefer
ID: 37047143
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37046408
Is not what I posted pretty much the same thing?  I just didn't have the necessary table information to go on.
0
 

Assisted Solution

by:Karen Schaefer
Karen Schaefer earned 0 total points
ID: 37046437
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
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 37046470
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37047144
Since we could not agree, I'll object and abide by the moderator decision.
0
 

Author Closing Comment

by:Karen Schaefer
ID: 37087279
thanks for your assistance in this matter.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question