?
Solved

Table Cell Formatting Problem

Posted on 2004-08-26
6
Medium Priority
?
176 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

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 tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month14 days, 20 hours left to enroll

771 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