[Webinar] Streamline your web hosting managementRegister Today

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

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>
0
jlcannon
Asked:
jlcannon
  • 6
  • 4
1 Solution
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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