Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Problem using OLEDB

Posted on 2003-02-27
8
Medium Priority
?
683 Views
Last Modified: 2012-08-13
Hi Experts,

I have a peculiar problem when i try to execute the following code.

Private Sub Command1_Click()
    Dim mrsMisc As New ADODB.Recordset
    Dim mconConnection As New ADODB.Connection
    Set mrsMisc = New ADODB.Recordset
    Set mconConnection = New ADODB.Connection
    mconConnection.Open "Provider = MSDAORA ; Data source = MRSD; User Id=eqtusr; Password=eqtusr;"
    mconConnection.CursorLocation = adUseClient
    lstrSqlMiscQuery = " (SELECT  TRD_DT as EventDate, EVENT_TM as EventTime," _
     & "NVL(EVENT_TYPE_CD,' ') as EventTypeCode, NVL(ISSUE_SHRT_NM,'') as IssueName," _
     & " NVL(NEW_ISSUE_SYM_ID,'') as NewIssueSymbol, NVL(ITS_FL,'') as ITSFlag, " _
     & " MKT_CLASS_CD as MarketClassCd, NVL(QUAL_ST,' ') as QualSt, '' as MktClsCndCd, '' as EuroHldFl, 'IssueMaint' as TableName, '' as MktOpenTm, '' as MktCloseTm, '' as MktAllowTm FROM ISSUE_MAINT WHERE ISSUE_UNIQUE_ID = 24463 AND (EVENT_TYPE_CD IN ('OTCMAST_CUSIP','OTCSECDELT','OTCSECMAST','OTHER CHG','OTHER-CHG','SYMBOLNAME','UPCFLAG' ,'MSEQ6002' , 'ISSU6702' ,'MSEG6001' ,'MSAW6001') or QUAL_ST ='X' )AND TRD_DT >= TO_DATE('02/26/2003','MM/DD/YYYY') AND TRD_DT <= TO_DATE('02/26/2003','MM/DD/YYYY')AND  MKT_CLASS_CD IN ('C' , 'CQS' , 'ECM' ,'T') UNION ALL SELECT  TRD_DT as EventDate, EVENT_TM as EventTime, EVENT_TYPE_CD as EventTypeCode, '' as IssueName, '' as NewISsueSymbol, '' as ITSFlag, ' ' as MarketClassCd, ' ' as QualSt, MKT_CLS_CNDTN_CD as MktClsCndCd, EURO_HLDY_FL As EuroHldFl, 'MktCntrlTm' as TableName, " _
    & " NVL(US_MKT_OPN_TM,'') as MktOpenTm, NVL(US_MKT_CLS_TM,'') as MktCloseTm, NVL(US_MKT_ALLOW_TM,'') as MktAllowTm  FROM MKT_CNTRL_TM  WHERE   TRD_DT >= TO_DATE('02/26/2003','MM/DD/YYYY')  AND     TRD_DT <= TO_DATE('02/26/2003','MM/DD/YYYY')  AND MKT_SSSN_CD = 'US' AND EVENT_TYPE_CD IN ('MKTTIMES','MKTCLSCOND','BEG-EMC','END-EMC') AND MKT_CD in ('C' , 'CQS' , 'ECM' ,'T') UNION ALL SELECT  TRD_DT as EventDate, EVENT_TM as EventTime, EVENT_TYPE_CD as EventTypeCode, '' as IssueName, '' as NewISsueSymbol, '' as ITSFlag, ' ' as MarketClassCd, ' ' as MktClsCndCd, ' ' as QualSt, '' as EuroHldFl, 'MktCntrlSys' as TableName, '' as MktOpenTm, '' as MktCloseTm, '' as MktAllowTm FROM MKT_CNTRL_SYSTM WHERE   TRD_DT >= TO_DATE('02/26/2003','MM/DD/YYYY') AND     TRD_DT <= TO_DATE('02/26/2003','MM/DD/YYYY') AND MKT_SSSN_CD = 'US' AND MKT_CD = 'CQS') ORDER BY EventDate,EventTime "
    mrsMisc.CursorLocation = adUseClient
    mrsMisc.Open lstrSqlMiscQuery, mconConnection ', adOpenForwardOnly
    MsgBox mrsMisc.RecordCount
End Sub

I am getting the following error: "Operation is not allowed when the object is closed." in mrsMisc.RecordCount
But when i change the Provider from MSDAORA to oraOLEDB it is working fine.
Also if i remove the brackets before SELECT and before ORDER BY (not required for UNION ALL) and execute the about code using MSDAORA, it is working perfectly.

Thanks in advance
0
Comment
Question by:kanithi
8 Comments
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8035968
add:
 mconConnection.Open "Provider = MSDAORA ; Data source = MRSD; User Id=eqtusr; Password=eqtusr;"
   mconConnection.CursorLocation = adUseClient
with
 mconConnection.Open "Provider = MSDAORA ; Data source = MRSD; User Id=eqtusr; Password=eqtusr;"
if mconConnection.state =adstateopen then.....
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8035969
change this:

mrsMisc.CursorLocation = adUseClient
   mrsMisc.Open lstrSqlMiscQuery, mconConnection ', adOpenForwardOnly

to

mrsMisc.CursorLocation = adUseClient
mconConnection.Open
mrsMisc.Open lstrSqlMiscQuery, mconConnection ', adOpenForwardOnly


you must OPEN the connection to the databse BEFORE you can use it to open the recordset.


AW
0
 
LVL 1

Author Comment

by:kanithi
ID: 8036237
Hi,

There is no prblem what so ever in the code or in the query. It was working when i use ODBC.
It is causing the problem when i use OLEDB.

Thanks
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8036920
"There is no prblem what so ever in the code or in the query."
80
0
 
LVL 1

Accepted Solution

by:
kanithi earned 0 total points
ID: 8037199
Hi Experts,

It seems i figured out the problem.
By using OLEDB when you execute any query which is under brackets, it will not run.
Try the following code and you will see the result.


Private Sub Command1_Click()
   Dim mrsMisc As New ADODB.Recordset
   Dim mconConnection As New ADODB.Connection
   Set mrsMisc = New ADODB.Recordset
   Set mconConnection = New ADODB.Connection
   mconConnection.Open "Provider = MSDAORA ; Data source = MRSD; User Id=eqtusr; Password=eqtusr;"
   mconConnection.CursorLocation = adUseClient
 
   lstrSqlMiscQuery = "(select sysdate from dual)"
   mrsMisc.CursorLocation = adUseClient
   mrsMisc.Open lstrSqlMiscQuery, mconConnection ', adOpenForwardOnly
   MsgBox mrsMisc.RecordCount
End Sub
0
 

Expert Comment

by:CleanupPing
ID: 8940439
kanithi:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Experts: Post your closing recommendations!  Who deserves points here?
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 8983755
kanithi, an EE Moderator will handle this for you.
Moderator, my recommended disposition is:

    Refund points and save as a 0-pt PAQ.

DanRollins -- EE database cleanup volunteer
0
 

Expert Comment

by:YensidMod
ID: 9104543
Question is PAQ'd and points refunded.

YensidMod
Community Support Moderator @Experts Exchange
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

571 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