[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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