Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2004-04-22
7
Medium Priority
?
234 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

When deciding to adopt any help desk solutions many factors should be explored before taking decisions. This will change from business to another but in general there are some kind of rule of thumb. Here are some quick tips: Do we need only ticket…
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 reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…

772 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