Solved

Table Cell Formatting Problem

Posted on 2004-08-26
6
137 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
6 Comments
 
LVL 8

Expert Comment

by:alexhogan
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility


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
Comment Utility
PAQed, with points refunded (500)

Computer101
E-E Admin
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

763 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now