We help IT Professionals succeed at work.
Get Started

SELECT CASE query won't select

UOC
UOC asked
on
17 Comments
1 Solution
401 Views
Last Modified: 2021-04-21
This question is an extension of question ID: 24633744 .

Essentially, I have done a SELECT CASE statement but, as per the code below, it is not selecting the relevent field and displaying it, instead saying -

ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/inventory/view_ars_1.asp, line 139

Why?
No matter what the Category is, it will not select it so it is not specific to oneline of code but whatever the category the select case statement is based upon. In the above error it was based on the category being 'Procurement' but the same thing occurs if it is 'Web' or 'Support 'etc.

Can anyone help?
I can supply more information if required.
<!--  #include file="utils.asp" -->
<%
id = Session("uid")
%>
 
<script type="text/javascript">
function toggle(o,e){
	e =document.getElementById(e);
	e.style.display=(e.style.display=="none" ? "" : "none");
	o.innerHTML = (e.style.display=="none" ? "ALL": "HIDE");
}
  </script>
 
<%
Set rs = Server.CreateObject("ADODB.Recordset")
strsql = "select * from ars where id = '" & bt_escape(Session("uid")) & "'"
rs.Open strsql, strcn
%>
 
    <p><script>
function doit(btn) {
  xx = window.top.document.getElementsByTagName("frameset")[1];
  if (xx.cols == "10,45,45")
    {xx.cols = "10,90,0";
     btn.value = "Show";}
  else
    {xx.cols = "10,45,45";
     btn.value = "Hide";}
}
</script>
<input id="b1" type="button" value="Hide" onclick="doit(this)"></p>
    <p><font color="#999999" size="4" face="Rockwell"><strong>View Helpdesk Job Request for Job Number <%=Session("uid")%></strong></font></p>
    <p>
      <input name="button2" type="button" onClick="history.go(-1)" value="<< Back">
      <font size="2" face="Verdana, Arial, Helvetica, sans-serif">
      <input name="button3" type="button" onClick="window.open('ars_ass_software_1.asp','mywindow','width=700,height=700,scrollbars=yes')" value="Associated Software">
      <input name="button4" type="button" onClick="window.open('ars_rel_jobs_1.asp','mywindow','width=700,height=700,scrollbars=yes')" scrolling="YES" value="Related Jobs">
	  <input name="button5" type="button" onClick="window.open('ars_inv_details_1.asp','mywindow','width=900,height=900,scrollbars=yes')" value="Inventory Details">
</font> </p>
 
<form name="form1" method="post" action="<%=currentscript%>" onsubmit="return check()">
<div align="left">
<table width="90%" border="0">
<tr bgcolor="#CCCCCC">
  <td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">&nbsp;</font></td>
  <td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">&nbsp;</font></td>
  <td valign="top">&nbsp;</td>
  <td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">&nbsp;</font></td>
  <td valign="top">&nbsp;</td>
  <td><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><a href="update_ars1.asp">Update</a></font></td>
</tr>
<tr bgcolor="#CCCCCC">
<td width="14%" valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Job Number:</font></td>
<td width="22%" valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
  <input name="id" DISABLED type="text" id="id" value="<%=Session("uid")%>">
</font></td>
<td width="8%" valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Computer ID:</font></td>
<td width="22%" valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
  <input name="Hostname" DISABLED type="text" value="<%=rs("Hostname")%>">
</font></td>
<td width="10%" valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Department:</font></td>
<td width="24%"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
  <input type="text" name="Department" DISABLED value="<%=rs("Department")%>">
  </font></td>
</tr>
<tr bgcolor="#CCCCCC">
<td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Phone:</font></td>
<td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
  <input name="Phone" DISABLED type="text" value="<%=rs("Phone")%>">
</font></td>
<td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Username:</font></td>
<td valign="top"><input name="Username" DISABLED type="text" id="Username" value="<%=rs("Username")%>"></td>
<td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Email:</font></td>
<td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
  <input name="Email" DISABLED type="text" value="<%=rs("Email")%>">
</font></td>
</tr>
<tr bgcolor="#999999">
  <td colspan="6" valign="top"><hr></td>
  </tr>
<tr bgcolor="#CCCCCC">
<td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Summary:</font></td>
<td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
 <textarea name="Summary" value="<%=rs("Summary")%>" DISABLED></TEXTAREA>
 <input name="button6" type="button" onClick="window.open('ars_summary_1.asp','mywindow','width=700,height=700')" value="Summary">
</font></td>
<td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Request Log:</font></td>
<td valign="top">
  <textarea name="textarea" value="<%=rs("RequestLog")%>" DISABLED></textarea>
  <input name="button" type="button" onClick="window.open('ars_requestlog_1.asp','mywindow','width=700,height=700')" value="History"></td>
<td valign="top"><p><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Priority:</font></p>
  <p><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Status:</font></p></td>
<td valign="top"><p><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
  <input type="text" name="Priority" value="<%=rs("Priority")%>" DISABLED>
      
</font></p>
  <p><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
    <input type = "text" name="Statuss" value="<%=rs("Statuss")%>" DISABLED>
      
    </font></p></td>
</tr>
<tr bgcolor="#CCCCCC">
  <td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Request Summary:</font></td>
  <td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
    <input name="RequestSummary" type="text" value="<%=rs("RequestSummary")%>" DISABLED>
  </font></td>
  <td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Hardware:</font></td>
  <td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
    <input type="text" name="Hardware" value="<%=rs("Hardware")%>" DISABLED>
    </font></td>
  <td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Operating System:</font></td>
  <td><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
    <input name="OS" type="text" id="OS" value="<%=rs("OS")%>" DISABLED>
  </font></td>
  </tr>
<tr bgcolor="#CCCCCC">
  <td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Category:</font></td>
  <td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
    <input type="text" name="Category" value="<%=rs("Category")%>" DISABLED>
  </font></td>
  <td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Total
      Time Taken:</font></td>
  <td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
    <input type="text" name="Times" value="<%=rs("Times")%>" DISABLED>
  </font></td>
  <td valign="top">&nbsp;</td>
  <td>&nbsp;</td>
</tr>
<tr bgcolor="#CCCCCC">
  <td valign="top">&nbsp;</td>
  <td valign="top">
<%
  c = rs("Category")
Select Case c
Case "Web / Intranet"
   strsql = "select * from ars_Web where uid = '" & bt_escape(Session("uid")) & "'"
   response.Write rs("Web_subject")
Case "Procurement"
    strsql = "select * from ars_Proc where uid = '" & bt_escape(Session("uid")) & "'"
    response.Write rs("Proc_purp")
Case "Networking"
    strsql = "select * from ars_Network where uid = '" & bt_escape(Session("uid")) & "'"
    response.Write rs("Network_Sum")
Case "Software Licencing"
    strsql = "select * from ars_SL where uid = '" & bt_escape(Session("uid")) & "'"
    response.Write rs("SL_Platform")
Case "Mac Support"
    strsql = "select * from ars_Mac where uid = '" & bt_escape(Session("uid")) & "'"
    response.Write rs("Mac_Sum")
Case "Software Problems"
    strsql = "select * from ars_SP where uid = '" & bt_escape(Session("uid")) & "'"
    response.Write rs("SP_Sum")
Case "Sys Admin" 
    strsql = "select * from ars_SA where uid = '" & bt_escape(Session("uid")) & "'"
    response.Write rs("SA_Sum")
Case "Students"
    strsql = "select * from ars_Student where uid = '" & bt_escape(Session("uid")) & "'"
    response.Write rs("Student_Sum")
Case "Electrical Testing"
    strsql = "select * from ars_ET where uid = '" & bt_escape(Session("uid")) & "'"
    response.Write rs("ET_Sum")
Case "Computer Problem" 
    strsql = "select * from ars_CP where uid = '" & bt_escape(Session("uid")) & "'"
    response.Write rs("CP_Sum")
Case "Wireless" 
    strsql = "select * from ars_Wireless where uid = '" & bt_escape(Session("uid")) & "'"
    response.Write rs("Wireless_Sum")
Case "Database"
    strsql = "select * from ars_DB where uid = '" & bt_escape(Session("uid")) & "'"
    response.Write rs("DB_sig")
Case "New Staff Member" 
    strsql = "select * from ars_NSM where uid = '" & bt_escape(Session("uid")) & "'"
    response.Write rs("NSM_fname")
Case "Leaving Staff Member" 
    strsql = "select * from ars_LSM where uid = '" & bt_escape(Session("uid")) & "'"
    response.Write rs("LSM_Sum")
Case Else
   response.write("No data")
End Select
%>
 
  </td>
  <td valign="top">&nbsp;</td>
  <td valign="top">&nbsp;</td>
  <td valign="top">&nbsp;</td>
  <td>&nbsp;</td>
</tr>
<tr bgcolor="#CCCCCC">
  <td valign="top">&nbsp;</td>
  <td valign="top">&nbsp;</td>
  <td valign="top">&nbsp;</td>
  <td valign="top">&nbsp;</td>
  <td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">&nbsp;</font></td>
  <td><font size="2" face="Verdana, Arial, Helvetica, sans-serif">&nbsp;</font></td>
</tr>
<tr bgcolor="#999999">
  <td colspan="6" valign="top"><hr></td>
  </tr>
<tr bgcolor="#CCCCCC">
  <td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Assigned To:</font></td>
  <td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
    <input type="text" name="Tech" value="<%=rs("Tech")%>" DISABLED>
   </font></td>
  <td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Assigned By:</font></td>
  <td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
    <input name="SubmittedBy" type="text" value="<%=rs("SubmittedBy")%>" DISABLED>
  </font></td>
  <td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Date
      Assigned:</font></td>
  <td valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><%=rs("DateTime")%></font></td>
  </tr>
  
</table>
</div>
 
 
 
</body>
</html>
 
<%
rs.Close
Set rs = Nothing
%>

Open in new window

Comment
Watch Question
IT Manager
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 17 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE