Solved

While running a stored procedure, my connection keeps closing.  How do I prevent it?

Posted on 2004-04-22
7
227 Views
Last Modified: 2013-12-24
I am writing an ASP page and am trying to access information from a stored procedure.  Unfortunately, right after I run this stored procedure, I try to access the records and it says that the stored procedure is closed.  It gives me the following message:

               ADODB.Recordset error '800a0e78'
               Operation is not allowed when the object is closed.


Down below is my code.  Any ideas or suggestions?  This stored procedure does have a cursor in it, but it is only selecting rows from multiple tables, not updating or deleting anything.      
                       Thanks

IF request("whichname") <> "" then
      dim rst, comm, connstr, intCounter
      dim strStoredProcedure, strVariance
      intCounter = 0
      server.ScriptTimeout = 3600
      set comm = Server.CreateObject ("ADODB.Command")
      set rst = Server.CreateObject("ADODB.Recordset")
      Response.write "<tr><td>JOB = " & request("whichJob") & "</td></tr>"
      Response.write "<tr><td>Fname = " &  Request("FName") & "</td></tr>"
      Response.write "<tr><td>Lname = " & Request("LName") & "</td></tr>"
      with comm
            .CommandTimeout = 0
              .ActiveConnection = ConnStr
            .CommandText =       "sp_showNamesForNoCall"
            .CommandType = 4
            .Parameters.refresh
            .CreateParameter "@whichJob", adVarChar, , 255, Request("whichJob")
            .CreateParameter "@FnameToNotCall", adVarChar, , 255, Request("FName")
            .CreateParameter "@LnameToNotCall", adVarChar, , 255, Request("LName")
            .CreateParameter "@rowsUpdated", adVarChar,  , 255, null
            .CreateParameter "@rowsDeleted",adVarChar, , 255, null
            .parameters("@whichJob").value = Request("whichJob")
            .parameters("@FnameToNotCall").value = Request("FName")
            .Parameters("@LnameToNotCall").value = Request("LName")  
            .Parameters("@rowsUpdated").Value = 0
            .Parameters("@rowsDeleted").Value = 0
            set rst = .Execute
      end with

' Build the table
%>      
      </tr>
      <table width="90%" border="0" align=center  cellpadding=2 >
      <tr height=7pt  >
      <td  bgcolor=red width=5% align=center valign=bottom><font size=2 face="arial" color=white><b>Selection</b></FONT></td>
      <td  bgcolor=red width=5% align=center valign=bottom><font size=2 face="arial" color=white><b>I3_RowID</b></FONT></td>
      <td  bgcolor=red width=6% align=center valign=bottom><FONT size=2 face="arial"  color=white><b>PhoneNumber</b></FONT></td>
      <td  bgcolor=red width=7% align=center valign=bottom><FONT size=2 face="arial"  color=white><b>Status</b></FONT></td>
      <td  bgcolor=red width=6% align=center valign=bottom><FONT size=2 face="arial"  color=white><b>Company_subid</b></FONT></td>
      <td  bgcolor=red width=8% align=center valign=bottom><FONT size=2 face="arial"  color=white><b>Name</b></FONT></td>
      <td  bgcolor=red width=8% align=center valign=bottom><FONT size=2 face="arial"  color=white><b>Title</b></FONT></td>
      <td  bgcolor=red width=8% align=center valign=bottom><FONT size=2 face="arial"  color=white><b>Company</b></FONT></td>
      <td  bgcolor=red width=5% align=center valign=bottom><font size=2 face="arial"  color=white><b>City</b></FONT></td>
      <td  bgcolor=red width=6% align=center valign=bottom><FONT size=2 face="arial"  color=white><b>State</b></FONT></td>
      <td  bgcolor=red width=7% align=center valign=bottom><FONT size=2 face="arial"  color=white><b>Zip</b></FONT></td>
      </tr>      
                <%
      do until rst.eof
          intCounter = intCounter + 1
      %>
</table>
<table width="100%" border="0" align=center  cellpadding=2>
      <tr height=7pt  >
      <td  width=5% align=center valign=bottom><Input type=checkbox id=Name_<%=intCounter%> ></td>
      <td  width=5% align=center valign=bottom><Input type=hidden id=HoldID_<%=intCounter%> value ="<%=rst("i3_row_id")%>"></td>
      <td  width=5% align=center valign=bottom><font size=2 face="arial" color=white><b><%=rst("I3_RowID")%></b></FONT></td>
      <td  width=6% align=center valign=bottom><FONT size=2 face="arial"  color=white><b><%=rst("PhoneNumber")%></b></FONT></td>
      <td  width=7% align=center valign=bottom><FONT size=2 face="arial"  color=white><b><%=rst("Status")%></b></FONT></td>
      <td  width=6% align=center valign=bottom><FONT size=2 face="arial"  color=white><b><%=rst("Company_subid")%></b></FONT></td>
0
Comment
Question by:purtman
7 Comments
 
LVL 8

Expert Comment

by:plq
ID: 10911660
This error message means that the recordset is not open. That happens in my experience when the recordset is opened on an action query.

Try putting select * from sometable at the end of your sp, and then see if rs contains records.
0
 
LVL 9

Expert Comment

by:ftaco96
ID: 11059188
I've gotten this error before using ASP with SQL Server. Check to see if your st. proc has any print statements in it (perhaps left over from debugging). If it does, they could cause weird things to happen with your recordsets. Comment them or take them out and see if it works.
0
 
LVL 6

Expert Comment

by:BAlexandrov
ID: 11243153
Put
"Set NoCount ON" at the begining of the stored procedure.
Check if you have forgoten print statements also.
If still have same error, please post the procedure.

Bojidar Alexandrov
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 5

Expert Comment

by:LlamaJoe
ID: 11422832
first of all, don't use the .refresh method if you are going to create the parameters - the refresh method does this for you...

now it's time for old fashion ado debugging lesson.

change your Connection to an actual connection object

set conn = server.createobject("adodb.connection")
conn.connectionString = strConnectionString
conn.open

then check that you have a connection
response.write conn.state  ' should equal 1 if you are connected

you can also check the errors collection in conn.errors  (e.g., you've got the wrong password, etc...)

now after every step above, use the state property for the command and the recordset objects. and response.write the results so that you can see "1" being printed.

other arcane possibilities -
1) you should change your cursor to a client side forward only cursor (see connection and recordset objects)
2) you are trying to print text/blob/binary data -> they must be the last fields in your query and you must use the getChunk methods to read this type of data (when it is over 4k)
3) there have to be rows in the recordset in order for you to do until rs.eof
I always do this line first...
if not rs.bof and not rs.eof then
  do until rs.eof

    rs.moveNext
  loop
end if



0
 
LVL 1

Accepted Solution

by:
sdwilson1 earned 500 total points
ID: 11444653
two things that i always check for. make sure you have this at the very top of your page:

<!-- METADATA TYPE="typelib" FILE="c:\program files\common files\system\ado\msado15.dll"-->

No stored procedure of mine run without it

also might want to check that you have set the permissions on the stored procedure. basic stuff but I always forget ;)
0
 
LVL 5

Expert Comment

by:LlamaJoe
ID: 11452083
fyi, you only need the typelib if you are going to reference ado constants by their name, i.e., adVarChar

if you look at the ado reference, you can find the enum value for all of these constants.

e.g., adVarChar for a parameter object is a DataTypeEnum and advarchar = 200 so your statement
.CreateParameter "@FnameToNotCall", adVarChar, , 255, Request("FName")  could be written
.CreateParameter "@FnameToNotCall", 200, , 255, Request("FName")

see the ado reference at
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadoapireference.asp 
for more info.
0
 

Author Comment

by:purtman
ID: 11491112
all set.  thanks!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
The purpose of this video is to demonstrate how to manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be IPage.com Log into your Hosting account. IPage will be used for demonstration : Locat…
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now