Re: repeat region and formatting

message from bsamad on 20 Jul 2004

Thanks for your reply ...

The field is numeric in the database. Its is called "USA2000Combo".

A dynamic table using the Recordset 1 and a repeat region shows this field
correctly formated

A dynamic table using Recordset 2 and a repeat region generates the error ONLY
if I format it as a Number - Rounded to Integer.

The problem may be with the SQL or query for the 2nd recordset.

I have two queries on my testing page using the same database field. The first
query is simple to test. The second is more complex.

Recordset 1
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_conn_CensusDatabase_STRING
Recordset1.Source = "SELECT USA2000ComboStateID, USA2000ComboPopID,
USA2000Combo FROM TblUSPopCombo2000 WHERE USA2000ComboStateID ='06' AND
USA2000ComboPopID='012' AND USA2000ComboSUMLEVEL='050'"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>

<%
Dim Recordset2
Dim Recordset2_numRows

Set Recordset2 = Server.CreateObject("ADODB.Recordset")
Recordset2.ActiveConnection = MM_conn_CensusDatabase_STRING
Recordset2.Source = "SELECT TblPopGroups.Population, TblStates.StateName,
TblUSPop1990.USA1990, TblUSPopCombo2000.USA2000Combo,
TblUSPopAlone2000.USA2000Alone, IIf(([USA1990]=0) Or
IsNull([USA1990]),'N/A',(([USA2000Alone]-[USA1990])/([USA1990]))) AS [% Change
From 1990 (Alone)], IIf(([USA1990]=0) Or
IsNull([USA1990]),'N/A',(([USA2000Combo]-[USA1990])/([USA1990]))) AS [% Change
From 1990 (Inclusive)] FROM TblStates RIGHT JOIN (TblPopGroups LEFT JOIN
((TblUSPop1990 LEFT JOIN TblUSPopAlone2000 ON (TblUSPop1990.USA1990SUMLEVEL =
TblUSPopAlone2000.USA2000AloneSUMLEVEL) AND (TblUSPop1990.USA1990StateID =
TblUSPopAlone2000.USA2000AloneStateID) AND (TblUSPop1990.USA1990PopID =
TblUSPopAlone2000.USA2000AlonePopID)) LEFT JOIN TblUSPopCombo2000 ON
(TblUSPopAlone2000.USA2000AlonePopID = TblUSPopCombo2000.USA2000ComboPopID) AND
(TblUSPopAlone2000.USA2000AloneSUMLEVEL =
TblUSPopCombo2000.USA2000ComboSUMLEVEL) AND
(TblUSPopAlone2000.USA2000AloneStateID =
TblUSPopCombo2000.USA2000ComboStateID)) ON TblPopGroups.[Population ID] =
TblUSPop1990.USA1990PopID) ON TblStates.StateID = TblUSPop1990.USA1990StateID
WHERE (((TblUSPop1990.USA1990StateID)='06') AND
((TblUSPop1990.USA1990SUMLEVEL)='040' Or (TblUSPop1990.USA1990SUMLEVEL)='010'))
ORDER BY TblPopGroups.PopPrtOrder;"
Recordset2.CursorType = 0
Recordset2.CursorLocation = 2
Recordset2.LockType = 1
Recordset2.Open()

Recordset2_numRows = 0
%>

The First Table -- NO PROBLEM
<table border="1">
<tr>
<td>USA2000ComboStateID</td>
<td>USA2000ComboPopID</td>
<td>USA2000Combo</td>
</tr>
<% While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) %>
<tr>
<td><%=(Recordset1.Fields.Item("USA2000ComboStateID").Value)%></td>
<td><%=(Recordset1.Fields.Item("USA2000ComboPopID").Value)%></td>
<td><%= FormatNumber((Recordset1.Fields.Item("USA2000Combo").Value), 0,
-2, -2, -2) %></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
</table>

The Second Table -- Error on the said field if I format it as above, otherwise
OK

table border="1">
<tr>
<td>Population</td>
<td>StateName</td>
<td>USA1990</td>
<td>USA2000Alone</td>
<td>USA2000Combo</td>
<td>% Change From 1990 (Alone)</td>
<td>% Change From 1990 (Inclusive)</td>
</tr>
<% While ((Repeat2__numRows <> 0) AND (NOT Recordset2.EOF)) %>
<tr>
<td><%=(Recordset2.Fields.Item("Population").Value)%></td>
<td><%=(Recordset2.Fields.Item("StateName").Value)%></td>
<td><%=(Recordset2.Fields.Item("USA1990").Value)%></td>
<td><%=(Recordset2.Fields.Item("USA2000Alone").Value)%></td>
<td><%=(Recordset2.Fields.Item("USA2000Combo").Value)%></td>
<td><%=(Recordset2.Fields.Item("% Change From 1990 (Alone)").Value)%></td>
<td><%=(Recordset2.Fields.Item("% Change From 1990
(Inclusive)").Value)%></td>
</tr>
<%
Repeat2__index=Repeat2__index+1
Repeat2__numRows=Repeat2__numRows-1
Recordset2.MoveNext()
Wend
%>
</table>

Archived message: Re: repeat region and formatting (Macromedia Dreamweaver Web Design)