VB6 RDO error 40088 No open cursor closed.

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 = ""
    Else
        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.
mossmisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GeoffHarperCommented:
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

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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?
0
mossmisAuthor Commented:
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:
**************************
 DBCon.CommitTrans
**************************

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.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mossmisAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.