error on webpage

I have built a webpage that the user chooses a category and below that there is a table that is supposed to become populated with the data the corresponds to their category choice. this information is read from an excel file that I have defined a name of tblData. I am getting an error in the table that is :

Microsoft VBScript runtime error '800a01a8'

Object required: ''

/energysystc/Technology/businessSigTech.asp, line 156


and the code for the page looks like this:

<%@ Language=VBScript %>
<!--#include file="../includes/adovbs.inc"-->
<%

            vXlsFile = "/virtual/documents/energysystc/TechnologyNotice.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


      session("Category") = Request.QueryString("Category")
      CatArray = Array("ASU","Boiler","Cooling Tower","Demin Water","Electrical","Fire Water -- Engines","Fire Water","Gas Turbines","Heat Recovery Unit(HRU)","Potable Water","Process Water","Raw Water(River/Sea)","Steam Turbine","Utilities Distribution","Waste Water/Sewer")












%>

<html>

<head>

<title>Specific Equipment Instructions</title>
</head>
<script LANGUAGE="vbscript">
<!--
      sub Myformsubmit(index)
            select case index
                  case 1
                  myform.Category.value = ""
                  MyForm.submit
                  case 2
                  MyForm.submit
            end select
      end sub
-->
</script>
<body onload="">


<table width="100%" cellspacing="0" cellpadding="0">
  <tr>
  </tr>
  <tr>
    <td><div align="left"><table border="0" cellpadding="0" cellspacing="0" width="100%">
     
      <tr>
        <td width="81%" colspan="2"><div align="left"><table name="ContentTable" id="ContentTable" border="0" cellpadding="0" cellspacing="0" width="100%">
          <tr>
            <td class="Content" name="ContentCell" id="ContentCell" valign="top" width="100%">
            <p class="BC HiddenForPrinting">&lt;&lt; <a href="/technology/technology.htm">
            Technology</a></p>

<form method="get" name="myform">

<table border="0" width="100%">
<tr>
<td width="20%"><b>Equipment Group</b></strong></td>
<td width="80%"><select name="Category" id="Category" style="Height: 22px; Width: 600px;" onChange="Myformsubmit(1)">
            <option></option>
<%
      for each item in catArray
            if item = session("Category") then
                  response.write "<option selected>" & item & "</option>" & vbcrlf
            sql = "SELECT * FROM tblData WHERE tblData.[Equipment]='" & Session("Category") & "';"
                  
                  set RS = server.createobject("adodb.recordset")
                  RS.open sql,Conn,adOpenForwardOnly,adLockReadOnly
            else
                  response.write "<option >" & item & "</option>" & vbcrlf
            end if
      next
%>
</select>
</td>
</tr>

</table>
<p>&nbsp;</p>
<%
'if session("EquipmentGroup") <> "" then
      'sql = "SELECT * FROM tblData WHERE tblData.[Equipment]='" & Session("Category") & "';"
      'Response.Write sql
      'response.end
      'set RS = server.createobject("adodb.recordset")
      'RS.open sql,Conn,adOpenForwardOnly,adLockReadOnly

%>
<table border="1" width="100%">
<tr class="cellcolorlighter">
<td width="9%" align="center" valign="top" class="menuheadcell">Change ID</td>
<td width=25% align="center" valign="top" class="menuheadcell">Equipment Group</td>
<td width="23%" align="center" valign="top" class="menuheadcell">Summary of
Change</td>
<td width="13%" align="center" valign="top" class="menuheadcell">Actions</td>
<td width=20% align="center" valign="top" class="menuheadcell">Supporting
Documentation</td>
</tr>
<tr>
<td width="11%" valign=top ><%=rs.fields("Change ID")%>&nbsp;</td>
<td width="26%" valign=top><%=rs.fields("Equipment")%>&nbsp;</td>
<td width="25%" valign=top><%=rs.fields("Summary of Change")%>&nbsp;</td>
<td width="15%" valign=top><%=rs.fields("Actions")%>&nbsp;</td>
<td width=20% valign=top><%=rs.fields("Supporting Documentation")%></a>&nbsp;</td>
</tr>
'<%
      'rs.movenext
'loop
'rs.close
'set rs = nothing
'%>
</table>

</form>
<%

'end if


      conn.Close
      set conn = nothing
%>


            </td>
           
          </tr>
        </table>
        </div></td>
      </tr>
    </table>
    </div></td>
  </tr>
</table>
</body>
</html>
jlcannonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hieloCommented:
I am guessing line 156 is:
sql = "SELECT * FROM tblData WHERE tblData.[Equipment]='" & Session("Category") & "';"

If so change it to:
sql = "SELECT * FROM [tblData] WHERE [Equipment]='" & Session("Category") & "';"

AND make sure your excell sheet id named tblData
0
jlcannonAuthor Commented:
made the change and still get
Microsoft VBScript runtime error '800a01a8'

Object required: ''

/energysystc/Technology/businessSigTech.asp, line 156
0
hieloCommented:
You have a #include file. So your line #156 will not match the code you posted. What exactly is line 156.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

jlcannonAuthor Commented:
how would i determine the line exactly. I had wondered about that and how to determine it.
0
jlcannonAuthor Commented:
ok i think it is referring to
%>
<tr>
<td width="11%" valign=top ><%=rs.fields("Change ID")%>&nbsp;</td>
<td width="26%" valign=top><%=rs.fields("Equipment")%>&nbsp;</td>
<td width="25%" valign=top><%=rs.fields("Summary of Change")%>&nbsp;</td>
<td width="15%" valign=top><%=rs.fields("Actions")%>&nbsp;</td>
<td width=20% valign=top><%=rs.fields("Supporting Documentation")%></a>&nbsp;</td>
</tr>
<%
and so I think it is somehow not reading the excel file.. maybe i need to just start over.
0
hieloCommented:
Here is a complete sample code that has worked for me. Hopefully it will serve to guide you. I describe my setup so you understand it better. OK. My Excel file looks as follows:
| State | Abbreviation |
|--------|-----------------|
| Ohio  | OH                |
| Iowa  | IA                 |

and the sheet name is Sheet1. The ASP code is:
<%
' Set Connection Params
Set oConn = Server.CreateObject("ADODB.connection")
oConn.Open "Driver={Microsoft Excel Driver (*.xls)};DBQ=" &  Server.Mappath("States.xls") & ";"
 
Set RS=Server.CreateObject("ADODB.Recordset")
 
' Write the SQL Query
RS.open "SELECT * FROM [Sheet1$]", oConn
 
do until RS.EOF
      Response.Write ( RS("State") & " -- " & RS("Abbreviation") & "")
      RS.movenext
Loop
 
'Close the recordset/connection
 
RS.Close
oConn.Close
Set RS = Nothing
Set oConn = Nothing
%>

If you are having problems, make sure the folder where the excel file exists is readable by your script.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jlcannonAuthor Commented:
ok now I get

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/energysystc/Technology/businessSigTech1.asp, line 164
0
hieloCommented:
>>name or ordinal.
Using my example as reference, that means that you are doing something like:
Response.Write ( RS("State") & " -- " & RS("Abbreviation") & "")

Basically, It's not acknowledging State and Abbreviation as headers. Instead of the Header names, you can use Numbers:
Response.Write ( RS(0) & " -- " & RS(1) & "")
 
The first column is 0, the second is 1, etc.
0
jlcannonAuthor Commented:
ok here is my current code:

<%@ Language=VBScript %>
<!--#include file="../includes/adovbs.inc"-->
<%

            vXlsFile = "/virtual/documents/energysystc/TechnologyNotice.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
            
            session("Category") = Request.QueryString("Category")
      CatArray = Array("ASU","Boiler","Cooling Tower","Demin Water","Electrical","Fire Water -- Engines","Fire Water","Gas Turbines","Heat Recovery Unit(HRU)","Potable Water","Process Water","Raw Water(River/Sea)","Steam Turbine","Utilities Distribution","Waste Water/Sewer")












%>

<html>

<head>
<title>Significant Technology Change Notification</title>
</head>
<script LANGUAGE="vbscript">
<!--
      sub Myformsubmit(index)
            select case index
                  case 1
                  myform.Category.value = ""
                  MyForm.submit
                  case 2
                  MyForm.submit
            end select
      end sub
-->
</script>
<body onload="">



<form method="get" name="myform">

<table border="0" width="100%">
<tr>
<td width="20%"><b>Equipment Group</b></strong></td>
<td width="80%"><select name="Category" id="Category" style="Height: 22px; Width: 600px;" onChange="Myformsubmit(1)">
            <option></option>
<%
      for each item in catArray
            if item = session("Category") then
                  response.write "<option selected>" & item & "</option>" & vbcrlf
                  else
                  response.write "<option >" & item & "</option>" & vbcrlf
            end if

            sql = "SELECT * FROM [tblData] WHERE [equipment]='" & Session("Category") & "';"
                  
                  set RS = server.createobject("adodb.recordset")
                  RS.open sql,Conn,adOpenForwardOnly,adLockReadOnly
            
      next
%>
</select>
</td>
</tr>

</table>
<p>&nbsp;</p>
<%

%>
<table border="1" width="100%">
<tr class="cellcolorlighter">
<td width="9%" align="center" valign="top" class="menuheadcell">Change ID</td>
<td width=25% align="center" valign="top" class="menuheadcell">Equipment Group</td>
<td width="23%" align="center" valign="top" class="menuheadcell">Summary of Change</td>
<td width="13%" align="center" valign="top" class="menuheadcell">Actions</td>
<td width=20% align="center" valign="top" class="menuheadcell">Supporting Documentation</td>
</tr>
<%
'do while not rs.eof
%>
<tr>
<td width="11%" valign=top ><%=rs.fields("equipment")%>&nbsp;</td>
<td width="26%" valign=top><%=rs.fields("Actions")%>&nbsp;</td>
<td width="25%" valign=top><%=rs.fields("Summary of Change")%>&nbsp;</td>
<td width="15%" valign=top><%=rs.fields("Actions")%>&nbsp;</td>
<td width=20% valign=top><%=rs.fields("Supporting Documentation")%></a>&nbsp;</td>
</tr>
<%
      rs.movenext
'loop
rs.close
set rs = nothing
%>
</table>

</form>
<%

'end if


      conn.Close
      set conn = nothing
%>


            </td>
                      </tr>
        </table>
        </div></td>
      </tr>
      <tr>
        <td width="81%" colspan="2">
        <!--webbot bot="Include" U-include="../includes/footer_gabba.htm" TAG="BODY" --></td>
      </tr>
      <tr>
        <td name="SpacerCell" id="SpacerCell"><img src="http://usmdlcdoww002.intranet.dow.com/common/images/topspacer.gif" width="640" height="1"></td>
      </tr>
    </table>
    </div></td>
  </tr>
</table>
</body>
</html>

Here is my corrent error:
ADODB.Field error '80020009'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/energysystc/Technology/BusinessSigTech.asp, line 0
0
jlcannonAuthor Commented:
ok well I can get it to dispaly all records in the excel sheet just i am having trouble when I want to add the selection box to allow the user to select a certain piece of equipment then only show the records pertaining to that equipment.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.