?
Solved

Operation is not allowed when the object is closed. 800A0E78

Posted on 2004-10-26
40
Medium Priority
?
301 Views
Last Modified: 2008-01-09
I am trying to move an export utility that currently resides in Access 2000 to a vbs script.  This code bombs at the "While Not rst.EOF" stating that "Operation is not allowed when the object is closed. 800A0E78".  I have looked on this site and tried many different ways to retrieve the recordset necessary for this operation and keep getting the same error.  The SQL Stored Proc has been tested and is returning the correct information.

Below is the code I am using, any help would be greatly appreciated.

Mike

-------Code--------
Dim WriteString, cn, rst, strConnTriTech, intID
   
    strConnTriTech = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=XXX;pwd=XXX;Initial Catalog=XXXX;Data Source=XXXXXXX"
   
    Set cn = CreateObject("ADODB.Connection")
    cn.Open strConnTriTech

    Set rst = CreateObject("ADODB.RecordSet")
    rst.Open "AMAA_TripImport",cn,0,1,4 'Stored Procedure in the SQL Database
   
    intID = 1
    While Not rst.EOF
        If rst.Fields("ID") = intID Then
            nSequenceNumber = nSequenceNumber + 1
        Else
            nSequenceNumber = 1
        End If
        intID = rst.Fields("ID")
        WriteString = "[import]" & vbCrLf
        WriteString = WriteString & "AtDestDate=" & CStr(Format(rst.Fields("ADDateTime"), "yyyy-mm-dd")) & vbCrLf
        WriteString = WriteString & "AtDestTime=" & CStr(Format(rst.Fields("ADDateTime"), "hh:nn:ss")) & vbCrLf
        WriteString = WriteString & "AtSceneDate=" & CStr(Format(rst.Fields("OSDateTime"), "yyyy-mm-dd")) & vbCrLf
        WriteString = WriteString & "AtSceneTime=" & CStr(Format(rst.Fields("OSDateTime"), "hh:nn:ss")) & vbCrLf
        WriteString = WriteString & "CallType=" & rst.Fields("CallType") & vbCrLf
        WriteString = WriteString & "Caller=" & rst.Fields("Caller_Name") & vbCrLf
        WriteString = WriteString & "CallerPhone=" & rst.Fields("Call_Back_Phone") & vbCrLf
        WriteString = WriteString & "CallReceivedDate=" & CStr(Format(rst.Fields("EntryDate"), "yyyy-mm-dd")) & vbCrLf
        WriteString = WriteString & "CallReceivedTime=" & CStr(Format(rst.Fields("EntryDate"), "hh:nn:ss")) & vbCrLf
        WriteString = WriteString & "ChiefComplaint=" & rst.Fields("Nature/Problem") & vbCrLf
        WriteString = WriteString & "CompleteDate=" & CStr(Format(rst.Fields("CompDateTime"), "yyyy-mm-dd")) & vbCrLf
        WriteString = WriteString & "CompleteTime=" & CStr(Format(rst.Fields("CompDateTime"), "hh:nn:ss")) & vbCrLf
        WriteString = WriteString & "DAddr=" & rst.Fields("DAddr") & vbCrLf
        WriteString = WriteString & "DAddr2=" & rst.Fields("DAddr2") & vbCrLf
        WriteString = WriteString & "DateOfService=" & CStr(Format(rst.Fields("Response_Date"), "yyyy-mm-dd")) & vbCrLf
        WriteString = WriteString & "DCity=" & rst.Fields("DCity") & vbCrLf
        WriteString = WriteString & "DSt=" & rst.Fields("DSt") & vbCrLf
        WriteString = WriteString & "DZip=" & rst.Fields("DZip") & vbCrLf
        WriteString = WriteString & "DispatchDate=" & CStr(Format(rst.Fields("AlertDateTime"), "yyyy-mm-dd")) & vbCrLf
        WriteString = WriteString & "DispatchTime=" & CStr(Format(rst.Fields("AlertDateTime"), "hh:nn:ss")) & vbCrLf
        WriteString = WriteString & "DMapPage=" & rst.Fields("DMapPage") & vbCrLf
        WriteString = WriteString & "EnRouteDate=" & CStr(Format(rst.Fields("RSDateTime"), "yyyy-mm-dd")) & vbCrLf
        WriteString = WriteString & "EnRouteTime=" & CStr(Format(rst.Fields("RSDateTime"), "hh:nn:ss")) & vbCrLf
        WriteString = WriteString & "IncidentNumber=" & rst.Fields("ResponseNumber") & vbCrLf
        WriteString = WriteString & "PartAvailDate=" & CStr(Format(rst.Fields("ExCode5"), "yyyy-mm-dd")) & vbCrLf
        WriteString = WriteString & "PartAvailTime=" & CStr(Format(rst.Fields("ExCode5"), "hh:nn:ss")) & vbCrLf
        If IsNull(rst.Fields("PUDateTime")) Then
            WriteString = WriteString & "PickUpTime=00:00:01" & vbCrLf
        Else
            WriteString = WriteString & "PickUpTime=" & CStr(Format(rst.Fields("PUDateTime"), "hh:nn:ss")) & vbCrLf
        End If
        WriteString = WriteString & "Priority=" & rst.Fields("Priority") & vbCrLf
        WriteString = WriteString & "PUAddr=" & rst.Fields("PUAddr") & vbCrLf
        WriteString = WriteString & "PUAddr2=" & rst.Fields("PUAddr2") & vbCrLf
        WriteString = WriteString & "PUCity=" & rst.Fields("PUCity") & vbCrLf
        WriteString = WriteString & "PUMapPage=" & rst.Fields("PUMapPage") & vbCrLf
        WriteString = WriteString & "PUSt=" & rst.Fields("PUSt") & vbCrLf
        WriteString = WriteString & "PUZip=" & rst.Fields("PUZip") & vbCrLf
        WriteString = WriteString & "TransportDate=" & CStr(Format(rst.Fields("RDDateTime"), "yyyy-mm-dd")) & vbCrLf
        WriteString = WriteString & "TransportTime=" & CStr(Format(rst.Fields("RDDateTime"), "hh:nn:ss")) & vbCrLf
        WriteString = WriteString & "Vehicle=" & rst.Fields("Vehicle") & vbCrLf
        WriteString = WriteString & "TripStatus=Complete" & vbCrLf
        WriteString = WriteString & "Company=" & vbCrLf
       
            'output WriteString to a file
        strFileName = "C:\tripimport\" & CStr(rst.Fields("TicketNumber")) & "_" & CStr(nSequenceNumber) & ".txt"
        'Open (strFileName) For Output As #1
        'Print #1, WriteString
        'Close #1
        rst.MoveNext
    Wend
    rst.Close
    cn.close
0
Comment
Question by:dfwroadking
  • 19
  • 12
  • 5
  • +2
40 Comments
 
LVL 14

Expert Comment

by:Shiju Sasidharan
ID: 12413050
Hi
Change the following statement and try
>> rst.Open "AMAA_TripImport",cn,0,1,4 'Stored Procedure in the SQL Database
'-------------------------------------------------------
    rst.Open "AMAA_TripImport",cn,3,3,4 'Stored Procedure in the SQL Database
    If rst.EOF Or rst.BOF Then

             '   Code to exit  if no records Found

     End If
  'Now Continue ur remaining Code....
    intID = 1
    While Not rst.EOF
        If rst.Fields("ID") = intID Then
            nSequenceNumber = nSequenceNumber + 1
        Else
            nSequenceNumber = 1

       
'------------------------------------------------------------------------

;-)
Shiju

0
 
LVL 14

Expert Comment

by:Shiju Sasidharan
ID: 12413090
Hi

>>'   Code to exit  if no records Found

     i mean , u can put the Code to handle when no records found

;-)
Shiju

 
0
 
LVL 8

Expert Comment

by:ampapa
ID: 12413114
I had a similar error the other day and it turned out that the DB wasn't opening when I thought it was which I why I got the error "...object is closed"..

The ultimate solution was that I needed to update to the latest MDAC, 2.8 I believe. My development machine was newer than my production machine, go figure...
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 14

Expert Comment

by:Shiju Sasidharan
ID: 12413195
Hi
if it doesnt work then try this before opening the recordset
'-------------------------------------------------------
   rst.CursorLocation= 3
  ' or with
  ' rst.CursorLocation= 2
'--------------------------------------------------------

 ;-)
Shiju

 
0
 

Author Comment

by:dfwroadking
ID: 12413228
Shijusn,

It gives me the same error.

Mike
0
 

Author Comment

by:dfwroadking
ID: 12413438
ampapa,

I get the same error running the script on the production machine.

Thanks,
Mike
0
 
LVL 8

Expert Comment

by:ampapa
ID: 12413485
Did it ever run on the production machine? Is the DB open?
0
 

Author Comment

by:dfwroadking
ID: 12413528
ampapa,

It is a new process, so no, it has never run on the production machine.  The DB is open.

Mike
0
 
LVL 8

Expert Comment

by:ampapa
ID: 12413584
This error I'm fairly sure relates to the DB not being open and not to the recordset itself.

What reference are you using for ADO?
0
 

Author Comment

by:dfwroadking
ID: 12413626
ampapa,

I am not using this code in a vb app.  I am scripting this for use by WSH.  No reference is being used.

Mike
0
 
LVL 8

Expert Comment

by:ampapa
ID: 12413714
Script or VB I'm still sure the error is in the connection to the DB not the recordset, I'll see if I can dig something up.
0
 
LVL 8

Expert Comment

by:ampapa
ID: 12413792
0
 

Author Comment

by:dfwroadking
ID: 12413841
ampapa,

The connection string is working.  I am able to pull a recordset directly from a table, but am unable to from the stored proc.

Mike
0
 
LVL 4

Expert Comment

by:AjithJose
ID: 12413936

Check the output of your stored procedure using TSQL debugger, and confirm that It allways returns a recordset.


0
 

Author Comment

by:dfwroadking
ID: 12414003
AjithJose

It always returns a recordset.

Mike
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 12414278
Try:

rst.Open "Exec AMAA_TripImport",cn,0,1,4

MsgBox rst.RecordCount

Lets see if there is anything here,

Leon
0
 

Author Comment

by:dfwroadking
ID: 12414453
leonstryker,

That didn't work, but I modified my SP to create a new table called TripImport with the data.  I then use the following code to retrieve the recordset.  

    Set rst = CreateObject("ADODB.RecordSet")
    rst.Open "SELECT * FROM TripImport",cn,0,1,1
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 12414598
Assuming your procedure is named AMAA_TripImport

This should work:
rst.Open "Exec AMAA_TripImport",cn,0,1

Leon
0
 

Author Comment

by:dfwroadking
ID: 12414652
leonstryker,

Thats still prompts the "Operation is not allowed when the object is closed" error.

Mike
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 12414714
What is the header on your store procedure?  Could you show it to the "AS" part please.

Leon
0
 

Author Comment

by:dfwroadking
ID: 12414806

CREATE     PROCEDURE AMAA_TripImport AS
-- AMAA Trip Import       26 October 2004
-- Michael A. Smith
--
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 12415015
Check permissions on the store procedure please.

Leon
0
 

Author Comment

by:dfwroadking
ID: 12415102
I am executing the SP as the SA user.

Mike
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 12415180
Mike have you tried it with other store procedures?

Leon
0
 

Author Comment

by:dfwroadking
ID: 12415232
No, I will try that.
0
 

Author Comment

by:dfwroadking
ID: 12423504
Leonstryker

I created another SP and successfully created the recordset using the following code.

    Set rst = CreateObject("ADODB.RecordSet")
    rst.Open "AMAA_Test",cn,0,1,4 'Stored Procedure in the SQL Database
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 12423570
In that case, I would have to say there is something in this sp which the driver does not like.  Is it very long?  Lets take a look at it.

Leon
0
 

Author Comment

by:dfwroadking
ID: 12423667
It cut off the last part of my message.

The test recordset us considerably smaller than the original, but I am still confused as to why one will work and the other will not.

Thanks,
Mike
0
 

Author Comment

by:dfwroadking
ID: 12423702
ALTER      PROCEDURE AMAA_TripImport AS
-- AMAA Trip Import       26 October 2004
-- Michael A. Smith
--

DECLARE @DateValue varChar(10)
DECLARE @StartDate      varChar(19)
DECLARE @EndDate varChar(19)
DECLARE @intID int


SELECT @DateValue = convert(varchar(10),getDate()-1,101)
SELECT @StartDate      = @DateValue + ' 00:00:00'
SELECT @EndDate = @DateValue + ' 23:59:59'

RAISERROR (@StartDate,-1,-1) WITH NOWAIT
RAISERROR (@EndDate,-1,-1) WITH NOWAIT

SELECT Response_Master_Incident.ID,
                  Response_Master_Incident.Response_Date as Response_Date,
                  convert(varchar(10),Response_Master_Incident.Response_Date,120) as DateOfService,
                  convert(varchar(8),Response_Master_Incident.Response_Date,108) as TimeOfService,
                  Response_Master_Incident.Incident_Type AS CallType, Response_Master_Incident.Caller_Name,
                  Response_Master_Incident.Call_Back_Phone, Response_Master_Incident.Caller_Type,
                  Response_Master_Incident.Master_Incident_Number AS TicketNumber,
                  Response_Vehicles_Assigned.Response_Number AS ResponseNumber,
                  Response_Master_Incident.Time_CallTakingComplete AS EntryDate,
                  convert(varchar(10),Response_Master_Incident.Time_CallTakingComplete,120) AS CallReceivedDate,
                  convert(varchar(8),Response_Master_Incident.Time_CallTakingComplete,108) AS CallReceivedTime,
                  Response_PreScheduled_Info.Time_PickupPromised AS PUDateTime,
                  convert(varchar(10),Response_PreScheduled_Info.Time_PickupPromised,120) AS PickUpDate,
                  convert(varchar(8),Response_PreScheduled_Info.Time_PickupPromised,108) AS PickUpTime,
                  Response_Master_Incident.Time_CallEnteredQueue AS TimeofCall2Que,
                  Response_Vehicles_Assigned.Time_Assigned AS AlertDateTime,
                  convert(varchar(10),Response_Vehicles_Assigned.Time_Assigned,120) AS DispatchDate,
                  convert(varchar(8),Response_Vehicles_Assigned.Time_Assigned,108) AS DispatchTime,
                  Response_Vehicles_Assigned.Time_Enroute AS RSDateTime,
                  convert(varchar(10),Response_Vehicles_Assigned.Time_Enroute,120) AS EnRouteDate,
                  convert(varchar(8),Response_Vehicles_Assigned.Time_Enroute,108) AS EnRouteTime,
                  Response_Vehicles_Assigned.Time_ArrivedAtScene AS OSDateTime,
                  convert(varchar(10),Response_Vehicles_Assigned.Time_ArrivedAtScene,120) AS AtSceneDate,
                  convert(varchar(8),Response_Vehicles_Assigned.Time_ArrivedAtScene,108) AS AtSceneTime,
                  Response_Transports.Time_Depart_Scene AS RDDateTime,
                  convert(varchar(10),Response_Transports.Time_Depart_Scene,120) AS TransportDate,
                  convert(varchar(8),Response_Transports.Time_Depart_Scene,108) AS TransportTime,
                  Response_Transports.Time_Arrive_Destination AS ADDateTime,
                  convert(varchar(10),Response_Transports.Time_Arrive_Destination,120) AS AtDestDate,
                  convert(varchar(8),Response_Transports.Time_Arrive_Destination,108) AS AtDestTime,
                  Response_Transports.Time_Delayed_Availability AS ExCode5,
                  convert(varchar(10),Response_Transports.Time_Delayed_Availability,120) AS PartAvailDate,
                  convert(varchar(8),Response_Transports.Time_Delayed_Availability,108) AS PartAvailTime,
                  Response_Vehicles_Assigned.Time_Call_Cleared AS CompDateTime,
                  convert(varchar(10),Response_Vehicles_Assigned.Time_Call_Cleared,120) AS CompleteDate,
                  convert(varchar(8),Response_Vehicles_Assigned.Time_Call_Cleared,108) AS CompleteTime,
                  Response_Master_Incident.Priority_Number AS Priority,
                  Response_Vehicles_Assigned.Radio_Name AS UnitNumber, Problem.Code AS ChiefComplaint,
                  CASE WHEN Response_Vehicles_Assigned.Call_Disposition IS NOT NULL THEN
                                    Response_Vehicles_Assigned.Call_Disposition ELSE
                  CASE WHEN Response_Transports.Transport_Protocol IS NOT NULL THEN
                                    Response_Transports.Transport_Protocol ELSE
                        Response_Master_Incident.Call_Disposition
                  END
                  END AS Disposition, Response_Master_Incident.Division,
                  Response_Master_Incident.Address AS PUAddr, Response_Master_Incident.Apartment AS PUAddr2,
                  Response_Master_Incident.City AS PUCity, Response_Master_Incident.State AS PUSt,
                  Response_Master_Incident.Postal_Code AS PUZip, Response_Master_Incident.Building AS PUMapPage,
                  Response_Vehicles_Assigned.Radio_Name AS Vehicle, Response_Transports.Address AS DAddr,
                  Response_Transports.Apartment AS DAddr2, Response_Transports.City AS DCity,
                  Response_Transports.State AS DSt, Response_Transports.Postal_Code AS DZip,
                  Response_Transports.Building AS DMapPage
FROM (((Response_Master_Incident LEFT JOIN Response_Vehicles_Assigned ON Response_Master_Incident.ID = Response_Vehicles_Assigned.Master_Incident_ID) LEFT JOIN Response_Transports ON Response_Vehicles_Assigned.ID = Response_Transports.Vehicle_Assigned_ID) LEFT JOIN Problem ON Response_Master_Incident.Problem = Problem.Description) LEFT JOIN Response_PreScheduled_Info ON Response_Master_Incident.ID = Response_PreScheduled_Info.Master_Incident_ID
WHERE (((Problem.Code) Not Like '800' And (Problem.Code) Not Like '650' And (Problem.Code) Not Like '510'
                  And (Problem.Code) Not Like '511' And (Problem.Code) Not Like '40') AND
                  ((CASE WHEN Response_Vehicles_Assigned.Call_Disposition IS NOT NULL THEN
                              Response_Vehicles_Assigned.Call_Disposition ELSE
                              CASE WHEN Response_Transports.Transport_Protocol IS NOT NULL THEN
                                    Response_Transports.Transport_Protocol ELSE
                              Response_Master_Incident.Call_Disposition
                              END END) Not Like 'Test%') AND
                  ((Response_Master_Incident.Division) Not Like 'Dallas%') AND
                  ((Response_Master_Incident.Time_First_Unit_Assigned) Between @StartDate And @EndDate))
ORDER BY Response_Master_Incident.ID

0
 
LVL 29

Expert Comment

by:leonstryker
ID: 12423748
As a test, modify the procedure to slect the data into a temp table and then select the result out of the temp table.  Lets see if that works,

Leon
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 12423778
I would also try to commenting out the:

RAISERROR (@StartDate,-1,-1) WITH NOWAIT
RAISERROR (@EndDate,-1,-1) WITH NOWAIT

portion.

Leon
0
 

Author Comment

by:dfwroadking
ID: 12423853
leonstryker,

Thats still prompts the "Operation is not allowed when the object is closed" error.  I have completed the project by having the SP create a table called TripImport and then can create a recordset using the following code.

    Set rst = CreateObject("ADODB.RecordSet")
    rst.Open "SELECT * FROM TripImport",cn,0,1,1 'Open Records from the TripImport Tab
0
 

Author Comment

by:dfwroadking
ID: 12423857
But it still makes no sense why I shouldn't be able to create the recordset without creating a table.

Thanks,
Mike
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 12423964
>>I have completed the project by having the SP create a table called TripImport and then can create a recordset using the following code.

I know, but I would like you to try creating a temp table inside the store procedure and have the store procedure select out of that.  So basically do a Select Into #table1 and then at the end Select * From #table1,

Leon
0
 

Author Comment

by:dfwroadking
ID: 12424021
LeonStryker,

I did that and removed the RAISERROR portion and it gives me the same error.

Mike
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 12424091
How long does it take this procedure to run and how much does it return?  Maybe you are timming out.

Leon
0
 

Author Comment

by:dfwroadking
ID: 12424251
8 seconds to run and it returns approximately 200-250 rows.

Mike
0
 
LVL 29

Accepted Solution

by:
leonstryker earned 750 total points
ID: 12424704
That is not much.  It should be working.  If you want to track down the source what you should do is start taking things out untill it works and look for the thing which makes it break.

Leon
0
 

Author Comment

by:dfwroadking
ID: 12424788
Thanks for your help Leon.  Since I have a working solution at the moment, I will try your suggestions and see what I can find that breaks it.

Thanks for your efforts and suggestions.

Mike

0
 
LVL 29

Expert Comment

by:leonstryker
ID: 12424797
Np.  i wish i could have been of more help,

Leon
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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

750 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