VB6 RDO error 40088 No open cursor closed.

Posted on 2011-10-11
Last Modified: 2012-05-12
I have an old VB6 program that connects to an Oracle database. We are moving our system over to a SQL server database and I'm n process of converting code.
I have has some success in some of my VB6 programs and many of them work with minor changes. I came accross one function that error's out with Run-time error '40088' No open cursor or cursor closed.

Here is the function it fails at:
Public Function FillORNull(ByVal FieldName) As String
'procedure to take care of NULLs in the DB
    If IsNull(FieldName) Then
        FillORNull = ""
        FillORNull = Trim$(FieldName)
    End If
End Function

This is written in VB6 using RDO. The entire project worked on a Oracle database, prior to some minor conversions, I only had to change the sysdate fucntion to GetDate() so far.
 I understand that RDO is obsolete as is VB6. In the next phase of this project, I will have time to rewrite all of it in .net, however this is easily a year long project I cannot do now. Any help on getting this to work on a SQL server 2008 R2 server would be appreciated. Please let me know if anyone needs more info.
Question by:mossmis
    LVL 4

    Expert Comment

    This may or may not be the problem.

    I have problems at time with the IsNull() so I would write such a function as follows:

    Public Function FillORNull(ByVal FieldName) As String
    'procedure to take care of NULLs in the DB
        FillORNull = Trim$("" & FieldName & "")
    End Function

    Open in new window

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    the error you get does not look like a problem in that function, actually.
    can you show some relevant code? aka the sql/procedure that is called?

    Author Comment

    Essentially, the program interacts with a user and inserts data in the the database. Once the data is entered, the program then proceeds to print a label. I

    I run the program line by line noticed all records get entered in the database, then the print portion fails.

    Right after the the data entry is processed, I noticed this line:

    Then the program proceeds to the printing portion and fails
    here when it got to the FillOrNull procedure      
     Select Case FillORNull(PkDataSet.rdoColumns(PkData_CompanyCode))
            Case "01"

    The FillORNull is called dozens of times before the  DBCon.CommitTrans and it works.

    Something tells me that DBCon.CommitTrans closed the connection and When FillORNull was called, it errored out. What puzzles me is why this worked in an Oracle DB.
    LVL 142

    Accepted Solution

    >DBCon.CommitTrans closed the connection
    it should not, actually...
    but it might close the cursor (recordset).
    so, either you need to move the CommitTrans after that line, or reopen the cursor, or take the info from the RDO object to some local variable(s), and use that one instead.

    Author Closing Comment

    I ended up reopening the DB connection when the label wants to print. The error stopped and my label finally printed. Our system consists of approx 10 VB6 apps and most of them have this FillORNull Function I will need to reopen connections for. This is a good quick fix to hold us over until we eventually convert to .net with ADO (That will be a big project). Thanks again "angellll" for the guidance!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    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…
    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…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now