?
Solved

Problem using OLEDB

Posted on 2003-02-27
8
Medium Priority
?
675 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: 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

Independent Software Vendors: 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!

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses
Course of the Month13 days, 20 hours left to enroll

801 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