Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Table Cell Formatting Problem

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
BHHanley
Asked:
BHHanley
1 Solution
 
alexhoganCommented:
What is your data type on field F6?

The error you are getting is one that you get when you cross data types.
0
 
BHHanleyAuthor Commented:
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
 
BHHanleyAuthor Commented:
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
 
cheyennejkCommented:


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

Computer101
E-E Admin
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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