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

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
dfwroadkingAsked:
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.

Shiju SasidharanAssoc Project ManagerCommented:
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
Shiju SasidharanAssoc Project ManagerCommented:
Hi

>>'   Code to exit  if no records Found

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

;-)
Shiju

 
0
ampapaCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Shiju SasidharanAssoc Project ManagerCommented:
Hi
if it doesnt work then try this before opening the recordset
'-------------------------------------------------------
   rst.CursorLocation= 3
  ' or with
  ' rst.CursorLocation= 2
'--------------------------------------------------------

 ;-)
Shiju

 
0
dfwroadkingAuthor Commented:
Shijusn,

It gives me the same error.

Mike
0
dfwroadkingAuthor Commented:
ampapa,

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

Thanks,
Mike
0
ampapaCommented:
Did it ever run on the production machine? Is the DB open?
0
dfwroadkingAuthor Commented:
ampapa,

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

Mike
0
ampapaCommented:
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
dfwroadkingAuthor Commented:
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
ampapaCommented:
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
ampapaCommented:
0
dfwroadkingAuthor Commented:
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
AjithJoseCommented:

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


0
dfwroadkingAuthor Commented:
AjithJose

It always returns a recordset.

Mike
0
leonstrykerCommented:
Try:

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

MsgBox rst.RecordCount

Lets see if there is anything here,

Leon
0
dfwroadkingAuthor Commented:
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
leonstrykerCommented:
Assuming your procedure is named AMAA_TripImport

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

Leon
0
dfwroadkingAuthor Commented:
leonstryker,

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

Mike
0
leonstrykerCommented:
What is the header on your store procedure?  Could you show it to the "AS" part please.

Leon
0
dfwroadkingAuthor Commented:

CREATE     PROCEDURE AMAA_TripImport AS
-- AMAA Trip Import       26 October 2004
-- Michael A. Smith
--
0
leonstrykerCommented:
Check permissions on the store procedure please.

Leon
0
dfwroadkingAuthor Commented:
I am executing the SP as the SA user.

Mike
0
leonstrykerCommented:
Mike have you tried it with other store procedures?

Leon
0
dfwroadkingAuthor Commented:
No, I will try that.
0
dfwroadkingAuthor Commented:
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
leonstrykerCommented:
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
dfwroadkingAuthor Commented:
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
dfwroadkingAuthor Commented:
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
leonstrykerCommented:
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
leonstrykerCommented:
I would also try to commenting out the:

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

portion.

Leon
0
dfwroadkingAuthor Commented:
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
dfwroadkingAuthor Commented:
But it still makes no sense why I shouldn't be able to create the recordset without creating a table.

Thanks,
Mike
0
leonstrykerCommented:
>>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
dfwroadkingAuthor Commented:
LeonStryker,

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

Mike
0
leonstrykerCommented:
How long does it take this procedure to run and how much does it return?  Maybe you are timming out.

Leon
0
dfwroadkingAuthor Commented:
8 seconds to run and it returns approximately 200-250 rows.

Mike
0
leonstrykerCommented:
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

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
dfwroadkingAuthor Commented:
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
leonstrykerCommented:
Np.  i wish i could have been of more help,

Leon
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
Visual Basic Classic

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.