jlcannon
asked on
HTTP 500 error
I have an asp page that I am connecting to an excel spreadsheet to try to allow folks to update some cells withihin this sheet without having to let them open the actual excel sheet. Everytime I try to navigate to the page it gives me the http: 500 error. I have pasted the asp code below.
<%@ Language=VBScript %>
<!--#include file="../includes/adovbs.inc"-->
<!--#include file="../script/ServerFunction.inc"-->
<!--#include file="Tools/userinfo.inc"-->
<%
Session("Category") = Request("Category")
Session("Year") = Request("Year")
Session("ID") = Request("ID")
'Response.Write "servername = " & servername
UID = Request.ServerVariables("LOGON_USER")
Uname = ucase(right(UID,7))
vXlsFile = "\virtual\documents\energysysvc\EHS List.xls"
vXlsFile = Server.Mappath(vXlsFile)
ExcelConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & chr(34) & vXlsFile & chr(34) & ";" & _
"Extended Properties=" & chr(34) & "Excel 8.0;"& chr(34)
'Open Database Connection
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open ExcelConnString
set OpRS = server.createobject("adodb.recordset")
Name = vFullName
sql = "SELECT * FROM tbl4Key WHERE ID=" & Request("ID") &";"
OpRS.open sql,Conn,adopenkeyset,adlockoptimistic
dim Site,Year,FDate,Business,Title,Category,Comments,MakeitPersonal,Askopenendedquestions,CreateaCulture,ID
'Server Side form validation to keep our database clean
FDate = OpRs("FDate")
Business = OpRs("Business")
Site = OpRs("Site")
Title = OpRs("Title")
Category = OpRs("Category")
Comments = OpRs("Comments")
MakeitPersonal = OpRs("MakeitPersonal")
Askopenendedquestions = OpRs("Askopenendedquestions")
CreateaCulture = OpRs("CreateaCulture")
Escalation = OpRs("Escalation")
ID = OpRs("ID")
if Category = "" then
error = "You have not entered a category."
Response.Write error
Response.End
end if
'If we pass through validation then store the information in the db
%>
<html>
<head>
<title></title>
</head>
<body onload="SetMenu()">
<table width="100%" cellspacing="0" cellpadding="0">
<tr>
<td><div align="left"><table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td><div align="left"><table name="ContentTable" id="ContentTable" border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td name="MenuCell" id="MenuCell" valign="top" bgcolor="#dddddd">
<!--webbot bot="Include" U-include="../includes/menu.htm" TAG="BODY" --></td>
<td class="Content" name="ContentCell" id="ContentCell" valign="top" width="80%">
<p class="BC HiddenForPrinting"><< <a href="../4Key/4Keymessage.htm">
TIH Home</a><br>
<center><b>Welcome, Choose which entry to edit.</b><br>
<br>
</center><form method="post" action="../4Key/4KeyEdithandler.asp">
<table border="1" width="59%" id="table1">
<tr>
<td><b>Record ID</b><br>
<input type="text" name="ID" Value='<%=ID%>' size="22"></td>
<td><b>Business</b><br>
<input type="text" name="Business" Value='<%=Business%>' size="22"></td>
</tr><tr>
<td><b>Date</b><br>
<input type="text" name="FDate" Value='<%=FDate%>' size="22"></td>
<td><b>Site:</b><br>
<input type="text" name="Site" Value="<%=Site%>" size="22"></td>
</tr><tr>
<td><b>Title</b><br>
<input type="text" name="Title" Value="<%=Title%>" size="22"></td>
<td><b>Category</b><br>
<input type="text" name="Category" Value="<%=Category%>" size="22"></td>
</tr><tr>
<td><b>Comments</b><br>
<input type="text" name="Comments" Value="<%=Comments%>" size="22"></td>
<td><b>Make it Personal</b><br>
<input type="text" name="MakeitPersonal" Value="<%=MakeitPersonal%>" size="22"></td>
</tr><tr>
<td><b>Ask open ended questions</b><br>
<input type="text" name="Askopenendedquestions" Value="<%=Askopenendedquestions%>"size="22"></td>
<td><b>Create a Culture</b><br>
<input type="text" name="CreateaCulture" Value="<%=CreateaCulture%>"size="22"></td>
</tr><tr>
<td><b>Escalation</b><br>
<input type="text" name="Escalation" Value="<%=Escalation%>"size="22"></td>
</tr>
</table>
<input type="submit" value="UpDate"><input type="reset" value="Reset">
</form>
<%
'set rs=nothing
'set conn=nothing
'rs.close
'Conn.close
%>
</table>
<a href="../4key/4Keymessage.htm">Return to Main Page
</a>
</td>
</tr>
</table>
</div></td>
</tr>
</table>
</div></td>
</tr>
</table>
</body>
</html>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
can you post the table definition for tbl4Key? also, can you response.write the sql after you build it and make sure its what you expect it to be, with the correct id and all?
ASKER
excellent, once I did this and actually got the error I was able to troubleshoot it and see a typo. I knew this code worked as I have another page, connecting to access, using the same code.
ASKER
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/energysysvc/4key/4KeyrecE
but i know the record exists becasue how i get to this page is by actually clicking on the record ID from another page that is displaying the list of records and i choose the one I want to update.