?
Solved

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

Posted on 2004-04-22
7
Medium Priority
?
230 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Turn A Profile Picture Into A Cartoon Using Photoshop And Illustrator This tutorial will teach you how to make a cartoon style image out of a regular picture. I have tried to keep the tutorial as simple as possible. I used Adobe CS4 for this tuto…
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 properly insert a Vimeo Video into a WordPress site or Blog. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp…
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…
Suggested Courses

801 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