Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Format Excel Column in ASP

Status
Not open for further replies.
jennuhw

jennuhw

MIS
Apr 18, 2001
426
US
I have a page that brings up an excel spreadsheet in the browser. The information is coming from a SQL statement. I am having problems getting my number formatting. I have tried different things, but it always comes out crazy. Can someone lead me in the right direction? Thanks.

<%
Response.ContentType = &quot;application/vnd.ms-excel&quot;
%>

<html>

<head>
<meta name=&quot;GENERATOR&quot; content=&quot;Microsoft FrontPage 5.0&quot;>
<meta name=&quot;ProgId&quot; content=&quot;FrontPage.Editor.Document&quot;>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=windows-1252&quot;>
<title>Sales Forecasting Search Results</title>

<%
Dim myConnString
Dim myConnection
Dim mySQL

myConnString = Application(&quot;WBW_ConnectionString&quot;)
Set myConnection = Server.CreateObject(&quot;ADODB.Connection&quot;)
myConnection.Open myConnString

mySQL= &quot;SELECT WBW.cust_code, WBW.item_code, Cust_Item.Part_Number, Item.desc1, Item.prod_group, Sum((WBW!inv_qty/WBW!sales_size)*WBW!ut_price) AS [2002 Sales], &quot;
mySQL= mySQL & &quot;Sum(WBW!inv_qty) AS [2002 Qty], Sum(([WBW]![inv_qty2]/[WBW]![sales_size2])*[WBW]![ut_price2]) AS [2003 Sales], Sum([WBW]![inv_qty2]) &quot;
mySQL= mySQL & &quot;AS [2003 Qty] FROM (Item INNER JOIN WBW ON Item.item_code = WBW.item_code) INNER JOIN Cust_Item ON (Cust_Item.cust_code = WBW.cust_code) &quot;
mySQL= mySQL & &quot;AND (WBW.item_code = Cust_Item.item_code) WHERE (((WBW.inv_date)>=#&quot;&Request.Form(&quot;inv_date&quot;)&&quot;/2002# And &quot;
mySQL= mySQL & &quot;(WBW.inv_date)<=#&quot;&Request.Form(&quot;inv_date2&quot;)&&quot;/2002#)) OR (((WBW.inv_date2)>=#&quot;&Request.Form(&quot;inv_date&quot;)&&quot;/2003# And (WBW.inv_date2)<=#&quot;&Request.Form(&quot;inv_date2&quot;)&&quot;/2003#)) &quot;
mySQL= mySQL & &quot;GROUP BY WBW.cust_code, WBW.item_code, Cust_Item.Part_Number, Item.desc1, Item.prod_group HAVING (((WBW.cust_code)='&quot;&Request.Form(&quot;cust_code&quot;)&&quot;'))&quot;

'response.write mysql
'response.end

set RS=myConnection.execute (mySQL)
'myConnection.Execute mySQL
'Close Connection
%>

<TABLE BORDER=1>
<COL SPAN=&quot;1&quot; ALIGN=&quot;LEFT&quot;></COL>
<COL SPAN=&quot;1&quot; ALIGN=&quot;LEFT&quot;></COL>
<COL SPAN=&quot;1&quot; ALIGN=&quot;LEFT&quot;></COL>
<COL SPAN=&quot;1&quot; ALIGN=&quot;LEFT&quot;></COL>
<COL SPAN=&quot;1&quot; ALIGN=&quot;LEFT&quot;></COL>
<COL SPAN=&quot;1&quot; ALIGN=&quot;RIGHT&quot;></COL>
<COL SPAN=&quot;1&quot; ALIGN=&quot;RIGHT&quot;></COL>
<COL SPAN=&quot;1&quot; ALIGN=&quot;RIGHT&quot;></COL>
<COL SPAN=&quot;1&quot; ALIGN=&quot;RIGHT&quot;></COL>


<TR>

<%
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Loop through Fields Names and print out the Field Names
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
j = 2 'row counter
For i = 0 to RS.Fields.Count - 1
%>
<TD><B><% = RS(i).Name %></B>&nbsp;</TD>
<% Next %>

</TR>
<%
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Loop through rows, displaying each field
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Do While Not RS.EOF
%>
<TR>

<% For i = 0 to RS.Fields.Count - 1 %>
<TD VALIGN=TOP>
<% RESPONSE.WRITE RS(i).VALUE %>&nbsp;</TD>

<% Next %>
</TR>

<%
RS.MoveNext
j = j + 1
Loop
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Make sure to close the Result Set and the Connection object
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%




myConnection.Close
Set myConnection = Nothing
%>



Thanks!
 
Sort by date Sort by votes
the way you're going about it looks like you're trying to do it as RS_Obj.GetRows, your for/next statements can be changed around to :

For Each Field in RS.Fields
<td>Field.Name</td>
Next

then to populate the data :

Do While not RS.EOF
For each Field in RS.Fields
<td>RS(Field.Name)</td>
Next
Loop
 
Upvote 0 Downvote
Please excuse my ignorance, but isn't that what I am doing, but a different way? I am pretty new at this, sorry. What tags do I need for my number formatting? (#,###,###.##)
 
Upvote 0 Downvote
In ASP you can use FormatNumber function to format your numbers, as below:

<% For i = 0 to RS.Fields.Count - 1 %>
<TD VALIGN=TOP>
<% RESPONSE.WRITE FormatNumber(RS(i).VALUE) %> </TD>

<% Next %>

The rest should be okay. In general the formatting will be as below:

FormatNumber(1234567) --> 1,234,567.00
FormatNumber(.1) --> 0.10

Hope this will serve your purpose.

Shihab
 
Upvote 0 Downvote
I fixed this by doing the format in my SQL statement as follows:

mySQL= mySQL & &quot;Format(Sum((WBW!inv_qty/WBW!sales_size)*WBW!ut_price), '$##00.00') AS [2002 Sales], &quot;
mySQL= mySQL & &quot;Format(Sum(WBW!inv_qty), '#,###') AS [2002 Qty], &quot;
mySQL= mySQL & &quot;Format(Sum(([WBW]![inv_qty2]/[WBW]![sales_size2])*[WBW]![ut_price2]), '$##00.00') AS [2003 Sales], Format(Sum([WBW]![inv_qty2]), '#,###') &quot;

Thanks for all of your inputs.

[ponytails]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
743
xwb
xwb
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
769
leifoet
leifoet
leifoet
  • Locked
  • Question
Code does not run in Microsoft Edge - it does in Google Chrome
Replies
3
Views
933
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
671
penguinspeaks
penguinspeaks
how2trainyourdragon
  • Locked
  • Question
Request.Server variables blank for classic ASP
Replies
12
Views
1K
GriffMG
GriffMG

Part and Inventory Search

Sponsor

Back
Top