Solved

Table Cell Formatting Problem

Posted on 2004-08-26
6
161 Views
Last Modified: 2010-04-25
I have pulled a recordset from an Excel Spreadsheet into a Dynamic Table and when I attempt to format some of the values as percentages, the web page will not load and the Error Type is Microsoft VBScript Runtime (0x800A000d), type mismatch: 'FormatPercent'. This occurs on line 71.

Here is the code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/conHAWD.asp" -->
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_conHAWD_STRING
Recordset1.Source = "SELECT * FROM HAWDData"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim rsHAWD
Dim rsHAWD_numRows

Set rsHAWD = Server.CreateObject("ADODB.Recordset")
rsHAWD.ActiveConnection = MM_conHAWD_STRING
rsHAWD.Source = "SELECT * FROM HAWDData"
rsHAWD.CursorType = 0
rsHAWD.CursorLocation = 2
rsHAWD.LockType = 1
rsHAWD.Open()

rsHAWD_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
rsHAWD_numRows = rsHAWD_numRows + Repeat1__numRows
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
<style type="text/css">
<!--
.style2 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: x-small; }
body,td,th {
      font-family: Verdana, Arial, Helvetica, sans-serif;
}
.style3 {font-size: x-small}
-->
</style>
</head>

<body>
<table border="1" cellpadding="0" cellspacing="0">
  <tr class="style3">
    <td width="77"><pre class="style2">WWCO</pre></td>
    <td width="77"><pre class="style2">SPB</pre></td>
    <td width="77"><pre class="style2">WWCO</pre></td>
    <td width="77"><pre class="style2">SPB</pre></td>
    <td width="77"><pre class="style2">WWCO</pre></td>
    <td width="77"><pre class="style2">SPB</pre></td>
  </tr>
  <% While ((Repeat1__numRows <> 0) AND (NOT rsHAWD.EOF)) %>
  <tr class="style3">
    <td><pre class="style3"><%= FormatCurrency((rsHAWD.Fields.Item("F1").Value), 2, -2, -2, -2) %></pre></td>
    <td><pre class="style3"><%= FormatCurrency((rsHAWD.Fields.Item("F2").Value), 2, -2, -2, -2) %></pre></td>
    <td><pre class="style3"><%= FormatCurrency((rsHAWD.Fields.Item("F3").Value), 2, -2, -2, -2) %></pre></td>
    <td><pre class="style3"><%= FormatCurrency((rsHAWD.Fields.Item("F4").Value), 2, -2, -2, -2) %></pre></td>
    <td><pre class="style3"><%= FormatPercent((rsHAWD.Fields.Item("F5").Value), 2, -2, -2, -2) %></pre></td>  <-- This is the 1st problem line
    <td><pre class="style3"><%= FormatPercent((rsHAWD.Fields.Item("F6").Value), 2, -2, -2, -2) %></pre></td>  <-- This is the 2nd problem line
  </tr>
  <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rsHAWD.MoveNext()
Wend
%>
</table>

</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
<%
rsHAWD.Close()
Set rsHAWD = Nothing
%>


If I remove the FormatPercent from these lines the table loads properly albeit without the formating I require.

Any help would be appreciated.
0
Comment
Question by:BHHanley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 8

Expert Comment

by:alexhogan
ID: 11909627
What is your data type on field F6?

The error you are getting is one that you get when you cross data types.
0
 

Author Comment

by:BHHanley
ID: 11913980
The data from the spreadsheet is a calculated field (one cell value divided by another) which then displays as a percentage amount on the spreadsheet.

When the data pulls into the recordset and no formatting is applied to the value it will displays as follows: eg: Spreadsheet (32.5%)  Recordset Value (.3254258793)

0
 

Author Comment

by:BHHanley
ID: 11939066
I have solved the problem. There were cell values in the spreadsheet which were displaying divide by zero errors. This would cause the recordset formatting to crash. Once I changed these values to zero values, the applied formatting worked fine.

Please close this question and refund the points.

Thank-You.
0
 
LVL 2

Expert Comment

by:cheyennejk
ID: 11975193


sometime Excel data may be interpreted as text... Try to type cast all number data to be safe:


cdbl(rsHAWD.Fields.Item("F5").Value)
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 12268021
PAQed, with points refunded (500)

Computer101
E-E Admin
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Resume template for HTML/Dreamweaver website 9 528
How to distribute wireless to 4 other PCs. 3 220
Lock Form Controls 3 309
xml to xml using xslt and php 4 558
For those who don't know, Adobe Dreamweaver is a popular commercial web editor that enables you to design, build and manage complex websites. The editor is a WYSIWYG (What You See Is What You Get) web editor, which means that you can create your web…
I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question