Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

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

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
purtman
Asked:
purtman
1 Solution
 
plqCommented:
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
 
ftaco96Commented:
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
 
BAlexandrovCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
LlamaJoeCommented:
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
 
sdwilson1Commented:
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
 
LlamaJoeCommented:
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
 
purtmanAuthor Commented:
all set.  thanks!
0
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now