Hi all, I am connecting to a Perva

[saraw]

Hi all,
I am connecting to a Pervasive DB from VB and am calling a stored procedure and trying to put the results into a recordset. This is VERY standard code and I use it all the time with MSSQL with no problems. But here, when I try to later reference the recordset, I keep getting the error :
"operation is not allowed when the object is closed"

Below is my VB code. My stored proc is a BASIC select statement:

Dim cn as adodb.connection
Dim cmdBill As ADODB.Command
Dim rsBill As ADODB.Recordset

Set cn = New ADODB.Connection
cn.Open "Provider=PervasiveOLEDB;Data Source=MyPath"
'connection is fine

Set cmdBill = New ADODB.Command
With cmdBill
    .ActiveConnection = cn
    .CommandText = "spGetBills"
    .CommandType = adCmdStoredProc
    Set rsBill = .Execute
End With

Thanks for any help,
Sara

 

[TomKane]

Does it work if you just do inline SQL as opposed to a stored procedure? Mabe if you begin with a simple sql statement and build your up?

 

[saraw]

Hi, thanks for the reply.
Yes, regular SQL statements work, as in:

Dim cn as adodb.connection
Dim rsBill As ADODB.Recordset

Set cn = New ADODB.Connection
cn.Open "Provider=PervasiveOLEDB;Data Source=MyPath"
'connection is fine

Set rsBill = new ADODB.recordset
rsBill.open "Select * ....", cn  
'Using same SQL statement as in stored proc

The problem seems to be with calling the recordset to receive the results of the command object (which as I said in the last post never gives probs in MSSQL).

Thanks for any further help,
Sara

 

[TomKane]

We do our stored procedure like this:

strSQL = "USP_GET_ACCOUNT_FOR_ACTION" _
& " @INFIXEDDATE='" & dtFixedEndDate & "'," _
& " @INDATE='" & dtLocalWorkDate & "'," _
& " @INTIMEPERIODCODE=" & intLocalTimePeriodId & "," _
& " @INFROMWORKPROCESSCODE=" & lngLocalSelectedWorkProcessFromCode & "," _
& " @INTOWORKPROCESSCODE=" & lngLocalSelectedWorkProcessToCode & "," _
& " @INACCTYPE='" & strAccountTermType & "'," _
& " @INJOBLIST='" & strLocalListJobRoles & "'," _
& " @INFROMJOBTYPECODE='" & strLocalSelectedJobTypeFromCode & "'," _
& " @INTOJOBTYPECODE='" & strLocalSelectedJobTypeToCode & "'"
Set adorsLocal = AccessADO.Execute(strSQL)

I don't know if that will make any difference to you...

 

[saraw]

Thanks.
I know this is an alternative method and I suppose I could use it. I just wonder why on earth the other way doesn't work and specifically why it gives that error.

Oh well, I'll have to live with this!

Thanks again,
Sara

 

[saraw]

TomKane,
I tried your way now, and I get the same error - operation is not allowed when the object is closed.

Any suggestions?

Sara

 

[TomKane]

You could try tracing the file activity. What I mean is we use P.SQL 7 and we have a thing called "Monitor" which shows us who is accessing which files, etc. Can you open this up to see if there is any activity and what it is - this might give you an idea as to what is happening.

 

[mirtheil]

WHat version of Pervasive are you using? I tried your code on one of my test machines and after changing the Procedure name to one defined in my database and changing "MyPath" to the path to my database (or to the DBName) and it worked for me if the Stored Procedure didn't return any values. If the Stored Procedure returned values, I got a message about arguments. If I changed the code to:

With cmdBill
    .ActiveConnection = cn
    .CommandText = "call proc1()"
    .CommandType = adCmdText
    Set rsBill = .Execute

Everything worked. I'm using Pervasive.SQL V8. What does your Stored Procedure do? info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[saraw]

Hi, thanks for the reply.
I'm using v2000. Running the code I originally posted does not give an error. However, if I try to access the returned recordset afterwards I get the above error. i.e. if after

set rsBill = .execute

I then do:

Do while not rsBill.eof

OR

If not rsBill.eof then

then it gives an error on this line that recordset is closed or whatever ( as mentioned above).

Thanks,
Sara

 

[Trygve]

Hi Sara!

I have the same problem as you, and I wonder if you have found a solution?

Thanks,
Trygve

 

[BenF]

I have the same problem as well. Who has the answer!

 

[mirtheil]

Are you calling a Stored Procedure? If so, what does the SP do? Does the query work in the PCC? If not, what error does it return?

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician