Solved

Table Cell Formatting Problem

Posted on 2004-08-26
6
144 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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

910 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

19 Experts available now in Live!

Get 1:1 Help Now