asked on

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.

GeoffHarper

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

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?

mossmis

ASKER

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.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mossmis

ASKER

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!