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 Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Conditional Format on result from DB lookup.

Status
Not open for further replies.
kwfrazier

kwfrazier

MIS
Joined
May 9, 2001
Messages
13
Location
US
I am looking to highlight a row(s) of results from a DB lookup based on a returned field. If the field value for any record is 4, then highlight in yellow. If the field value for any record is 5+, then highlight in red. Basically I want to do conditional formatting on each row based on a particular field's value.

I've found quite a bit of working code here, but it requires the user to do something to make it happen. I would like the highlighting to occur when the results are returned to the user.

I'm using FP2000 and already checked the FP Forum, but since this may require a more interesting look from the ASP side, I figured I'd come to the experts!

Thanks,

Kenneth Frazier, MCSE, CCA
Network Engineer
 
Sort by date Sort by votes
simple really

result=rs("resultfield")

if result=4 then
var_backcolor="yellow"
elseif result>=5 then
Var_backcolor = "red"
end if

response.write &quot;<tr bgcolor=&quot; * var_backcolor & &quot;&quot;><td>&quot; & rs(&quot;resultfield&quot;)

and so on for the rest of the rows dataset

hth

Bastien

cat, the other other white meat
 
Upvote 0 Downvote
This looks to be exactly what I'm looking for, however, when you use FrontPage to connect to a DB, the back-end code is a bit confusing. On top of that, it doesn't use response.write to return results.

The interesting piece would be to modify your code to 'work' with the FP junk... I want to test the DaystoClose field and then highlight the row if it meets the above criteria.

Here's a simplified look at the code that FP generates:


Code: 
<html>

<head>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=windows-1252&quot;>
<meta name=&quot;GENERATOR&quot; content=&quot;Microsoft FrontPage 4.0&quot;>
<meta name=&quot;ProgId&quot; content=&quot;FrontPage.Editor.Document&quot;>
<title>YearClosed</title>
<meta name=&quot;Microsoft Theme&quot; content=&quot;sandston 1011, default&quot;>
</head>

<body>

<table width=&quot;100%&quot; border=&quot;1&quot;>
  <thead>
    <tr>
      <td><b>YearClosed</b></td>
      <td><b>MonthClosed</b></td>
      <td><b>Region</b></td>
      <td><b>Marketplace</b></td>
      <td><b>DaysToClose</b></td>
      <td><b>PSN</b></td>
      <td><b>CustID</b></td>
      <td><b>OpenTime</b></td>
      <td><b>ClosedTime</b></td>
    </tr>
  </thead>
  <tbody>
    <!--webbot bot=&quot;DatabaseRegionStart&quot; startspan
    s-columnnames=&quot;YearClosed,MonthClosed,Region,Marketplace,DaysToClose,PSN,CustID,OpenTime,ClosedTime&quot;
    s-columntypes=&quot;2,2,202,202,3,202,202,135,135&quot; s-dataconnection=&quot;respond&quot;
    b-tableformat=&quot;TRUE&quot; b-menuformat=&quot;FALSE&quot; s-menuchoice s-menuvalue
    b-tableborder=&quot;TRUE&quot; b-tableexpand=&quot;TRUE&quot; b-tableheader=&quot;TRUE&quot;
    b-listlabels=&quot;TRUE&quot; b-listseparator=&quot;TRUE&quot; i-ListFormat=&quot;0&quot;
    b-makeform=&quot;TRUE&quot; s-recordsource=&quot;QRY_JC_5DayStatus&quot;
    s-displaycolumns=&quot;YearClosed,MonthClosed,Region,Marketplace,DaysToClose,PSN,CustID,OpenTime,ClosedTime&quot;
    s-criteria s-order s-sql=&quot;SELECT * FROM QRY_JC_5DayStatus&quot;
    b-procedure=&quot;FALSE&quot; clientside SuggestedExt=&quot;asp&quot; s-DefaultFields
    s-NoRecordsFound=&quot;No records returned.&quot; i-MaxRecords=&quot;0&quot; i-GroupSize=&quot;25&quot;
    BOTID=&quot;0&quot; u-dblib=&quot;_fpclass/fpdblib.inc&quot; u-dbrgn1=&quot;_fpclass/fpdbrgn1.inc&quot;
    u-dbrgn2=&quot;_fpclass/fpdbrgn2.inc&quot; tag=&quot;TBODY&quot;
    local_preview=&quot;<tr><td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;><font color=&quot;#000000&quot;>Database Results regions will not preview unless this page is fetched from a Web server with a web browser. The following table row will repeat once for every record returned by the query.</font></td></tr>&quot;
    preview=&quot;<tr><td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;><font color=&quot;#000000&quot;>This is the start of a Database Results region. The region will not work unless the page has a file extension of &quot;.asp&quot;. The page must be fetched from a web server with a web browser to display correctly; the current web is stored on your local disk or network.</font></td></tr>&quot; --><!--#include file=&quot;_fpclass/fpdblib.inc&quot;-->
<%
fp_sQry=&quot;SELECT * FROM QRY_JC_5DayStatus&quot;
fp_sDefault=&quot;&quot;
fp_sNoRecords=&quot;<tr><td colspan=9 align=left width=&quot;&quot;100%&quot;&quot;>No records returned.</td></tr>&quot;
fp_sDataConn=&quot;respond&quot;
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=25
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=&quot;&quot;
fp_sMenuValue=&quot;&quot;
fp_iDisplayCols=9
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file=&quot;_fpclass/fpdbrgn1.inc&quot;-->
<!--webbot bot=&quot;DatabaseRegionStart&quot; I-CheckSum=&quot;14016&quot; endspan -->
    <tr>
      <td><!--webbot bot=&quot;DatabaseResultColumn&quot; startspan
        s-columnnames=&quot;YearClosed,MonthClosed,Region,Marketplace,DaysToClose,PSN,CustID,OpenTime,ClosedTime&quot;
        s-column=&quot;YearClosed&quot; b-tableformat=&quot;TRUE&quot; b-hasHTML=&quot;FALSE&quot; clientside
        local_preview=&quot;<font size=&quot;-1&quot;><<</font>YearClosed<font size=&quot;-1&quot;>>></font>&quot;
        preview=&quot;<font size=&quot;-1&quot;><<</font>YearClosed<font size=&quot;-1&quot;>>></font>&quot; --><%=FP_FieldVal(fp_rs,&quot;YearClosed&quot;)%><!--webbot
        bot=&quot;DatabaseResultColumn&quot; I-CheckSum=&quot;28702&quot; endspan -->
      </td>
      <td><!--webbot bot=&quot;DatabaseResultColumn&quot; startspan
        s-columnnames=&quot;YearClosed,MonthClosed,Region,Marketplace,DaysToClose,PSN,CustID,OpenTime,ClosedTime&quot;
        s-column=&quot;MonthClosed&quot; b-tableformat=&quot;TRUE&quot; b-hasHTML=&quot;FALSE&quot; clientside
        local_preview=&quot;<font size=&quot;-1&quot;><<</font>MonthClosed<font size=&quot;-1&quot;>>></font>&quot;
        preview=&quot;<font size=&quot;-1&quot;><<</font>MonthClosed<font size=&quot;-1&quot;>>></font>&quot; --><%=FP_FieldVal(fp_rs,&quot;MonthClosed&quot;)%><!--webbot
        bot=&quot;DatabaseResultColumn&quot; I-CheckSum=&quot;30759&quot; endspan -->
      </td>
      <td><!--webbot bot=&quot;DatabaseResultColumn&quot; startspan
        s-columnnames=&quot;YearClosed,MonthClosed,Region,Marketplace,DaysToClose,PSN,CustID,OpenTime,ClosedTime&quot;
        s-column=&quot;Region&quot; b-tableformat=&quot;TRUE&quot; b-hasHTML=&quot;FALSE&quot; clientside
        local_preview=&quot;<font size=&quot;-1&quot;><<</font>Region<font size=&quot;-1&quot;>>></font>&quot;
        preview=&quot;<font size=&quot;-1&quot;><<</font>Region<font size=&quot;-1&quot;>>></font>&quot; --><%=FP_FieldVal(fp_rs,&quot;Region&quot;)%><!--webbot
        bot=&quot;DatabaseResultColumn&quot; I-CheckSum=&quot;15114&quot; endspan -->
      </td>
      <td><!--webbot bot=&quot;DatabaseResultColumn&quot; startspan
        s-columnnames=&quot;YearClosed,MonthClosed,Region,Marketplace,DaysToClose,PSN,CustID,OpenTime,ClosedTime&quot;
        s-column=&quot;Marketplace&quot; b-tableformat=&quot;TRUE&quot; b-hasHTML=&quot;FALSE&quot; clientside
        local_preview=&quot;<font size=&quot;-1&quot;><<</font>Marketplace<font size=&quot;-1&quot;>>></font>&quot;
        preview=&quot;<font size=&quot;-1&quot;><<</font>Marketplace<font size=&quot;-1&quot;>>></font>&quot; --><%=FP_FieldVal(fp_rs,&quot;Marketplace&quot;)%><!--webbot
        bot=&quot;DatabaseResultColumn&quot; I-CheckSum=&quot;27894&quot; endspan -->
      </td>
      <td><!--webbot bot=&quot;DatabaseResultColumn&quot; startspan
        s-columnnames=&quot;YearClosed,MonthClosed,Region,Marketplace,DaysToClose,PSN,CustID,OpenTime,ClosedTime&quot;
        s-column=&quot;DaysToClose&quot; b-tableformat=&quot;TRUE&quot; b-hasHTML=&quot;FALSE&quot; clientside
        local_preview=&quot;<font size=&quot;-1&quot;><<</font>DaysToClose<font size=&quot;-1&quot;>>></font>&quot;
        preview=&quot;<font size=&quot;-1&quot;><<</font>DaysToClose<font size=&quot;-1&quot;>>></font>&quot; --><%=FP_FieldVal(fp_rs,&quot;DaysToClose&quot;)%><!--webbot
        bot=&quot;DatabaseResultColumn&quot; I-CheckSum=&quot;29457&quot; endspan -->
      </td>
      <td><!--webbot bot=&quot;DatabaseResultColumn&quot; startspan
        s-columnnames=&quot;YearClosed,MonthClosed,Region,Marketplace,DaysToClose,PSN,CustID,OpenTime,ClosedTime&quot;
        s-column=&quot;PSN&quot; b-tableformat=&quot;TRUE&quot; b-hasHTML=&quot;FALSE&quot; clientside
        local_preview=&quot;<font size=&quot;-1&quot;><<</font>PSN<font size=&quot;-1&quot;>>></font>&quot;
        preview=&quot;<font size=&quot;-1&quot;><<</font>PSN<font size=&quot;-1&quot;>>></font>&quot; --><%=FP_FieldVal(fp_rs,&quot;PSN&quot;)%><!--webbot
        bot=&quot;DatabaseResultColumn&quot; I-CheckSum=&quot;483&quot; endspan -->
      </td>
      <td><!--webbot bot=&quot;DatabaseResultColumn&quot; startspan
        s-columnnames=&quot;YearClosed,MonthClosed,Region,Marketplace,DaysToClose,PSN,CustID,OpenTime,ClosedTime&quot;
        s-column=&quot;CustID&quot; b-tableformat=&quot;TRUE&quot; b-hasHTML=&quot;FALSE&quot; clientside
        local_preview=&quot;<font size=&quot;-1&quot;><<</font>CustID<font size=&quot;-1&quot;>>></font>&quot;
        preview=&quot;<font size=&quot;-1&quot;><<</font>CustID<font size=&quot;-1&quot;>>></font>&quot; --><%=FP_FieldVal(fp_rs,&quot;CustID&quot;)%><!--webbot
        bot=&quot;DatabaseResultColumn&quot; I-CheckSum=&quot;15748&quot; endspan -->
      </td>
      <td><!--webbot bot=&quot;DatabaseResultColumn&quot; startspan
        s-columnnames=&quot;YearClosed,MonthClosed,Region,Marketplace,DaysToClose,PSN,CustID,OpenTime,ClosedTime&quot;
        s-column=&quot;OpenTime&quot; b-tableformat=&quot;TRUE&quot; b-hasHTML=&quot;FALSE&quot; clientside
        local_preview=&quot;<font size=&quot;-1&quot;><<</font>OpenTime<font size=&quot;-1&quot;>>></font>&quot;
        preview=&quot;<font size=&quot;-1&quot;><<</font>OpenTime<font size=&quot;-1&quot;>>></font>&quot; --><%=FP_FieldVal(fp_rs,&quot;OpenTime&quot;)%><!--webbot
        bot=&quot;DatabaseResultColumn&quot; I-CheckSum=&quot;16591&quot; endspan -->
      </td>
      <td><!--webbot bot=&quot;DatabaseResultColumn&quot; startspan
        s-columnnames=&quot;YearClosed,MonthClosed,Region,Marketplace,DaysToClose,PSN,CustID,OpenTime,ClosedTime&quot;
        s-column=&quot;ClosedTime&quot; b-tableformat=&quot;TRUE&quot; b-hasHTML=&quot;FALSE&quot; clientside
        local_preview=&quot;<font size=&quot;-1&quot;><<</font>ClosedTime<font size=&quot;-1&quot;>>></font>&quot;
        preview=&quot;<font size=&quot;-1&quot;><<</font>ClosedTime<font size=&quot;-1&quot;>>></font>&quot; --><%=FP_FieldVal(fp_rs,&quot;ClosedTime&quot;)%><!--webbot
        bot=&quot;DatabaseResultColumn&quot; I-CheckSum=&quot;28166&quot; endspan -->
      </td>
    </tr>
    <!--webbot bot=&quot;DatabaseRegionEnd&quot; startspan b-tableformat=&quot;TRUE&quot;
    b-menuformat=&quot;FALSE&quot; u-dbrgn2=&quot;_fpclass/fpdbrgn2.inc&quot; i-groupsize=&quot;25&quot;
    clientside tag=&quot;TBODY&quot;
    local_preview=&quot;<tr><td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;><font color=&quot;#000000&quot;>This is the end of a Database Results region.</font></td></tr><TR><TD ALIGN=LEFT VALIGN=MIDDLE COLSPAN=64><FORM><NOBR><INPUT TYPE=Button VALUE=&quot;  |<  &quot;><INPUT TYPE=Button VALUE=&quot;   <  &quot;><INPUT TYPE=Button VALUE=&quot;  >   &quot;><INPUT TYPE=Button VALUE=&quot;  >|  &quot;>  [1/25]</NOBR></FORM></td></tr>&quot;
    preview=&quot;<tr><td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;><font color=&quot;#000000&quot;>This is the end of a Database Results region.</font></td></tr><TR><TD ALIGN=LEFT VALIGN=MIDDLE COLSPAN=64><NOBR><INPUT TYPE=Button VALUE=&quot;  |<  &quot;><INPUT TYPE=Button VALUE=&quot;   <  &quot;><INPUT TYPE=Button VALUE=&quot;  >   &quot;><INPUT TYPE=Button VALUE=&quot;  >|  &quot;>  [1/25]</NOBR><BR></td></tr>&quot; --><!--#include file=&quot;_fpclass/fpdbrgn2.inc&quot;-->
<!--webbot bot=&quot;DatabaseRegionEnd&quot; I-CheckSum=&quot;62730&quot; endspan -->
  </tbody>
</table>

</body>

</html>

Thanks,

Kenneth Frazier, MCSE, CCA
Network Engineer
 
Upvote 0 Downvote
honestly, the best thing is to dump FrontPage and get a real editor, Visual Studio or Visual Interdev if you want to stick to MS products. Or there are a host of others.

the above 'code' is why i get jobs fixing other people's code...because they can't make heads or tails out of it...the last time I did this I took six pages of code and reduced it down to 15 lines...

if you can handle the dns connections statements I will rewrite this to somethig that actually makes sense for ya...not this garbage, and by the way, MM Ultradev does the same thing, way to much code for what are essentially simple things to to do

I assume that i can use the columns headers to represent the data fields and you can adpat that to fit what your needs are?

Let me know

Bastien

cat, the other other white meat
 
Upvote 0 Downvote
All help is greatly appreciated - send it on! You can use the column headers and I'll modify as needed. Thanks again!

Thanks,

Kenneth Frazier, MCSE, CCA
Network Engineer
 
Upvote 0 Downvote
<html>

<head>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=windows-1252&quot;>
<meta name=&quot;GENERATOR&quot; content=&quot;Microsoft FrontPage 4.0&quot;>
<meta name=&quot;ProgId&quot; content=&quot;FrontPage.Editor.Document&quot;>
<title>YearClosed</title>
<meta name=&quot;Microsoft Theme&quot; content=&quot;sandston 1011, default&quot;>
</head>

<body>

<table width=&quot;100%&quot; border=&quot;1&quot;>
<thead>
<tr>
<td><b>YearClosed</b></td>
<td><b>MonthClosed</b></td>
<td><b>Region</b></td>
<td><b>Marketplace</b></td>
<td><b>DaysToClose</b></td>
<td><b>PSN</b></td>
<td><b>CustID</b></td>
<td><b>OpenTime</b></td>
<td><b>ClosedTime</b></td>
</tr>
</thead>
<tbody>
<%
<script language=&quot;vbscript&quot;>
option explicit

Dim oConn, oRS, sSQL
Dim result,var_backcolor

Set oConn = Server.CreateObject(&quot;ADODB.Connection&quot;)
oConn.Open(&quot;DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=&quot; & Server.MapPath(&quot;\mdb-database\airaid.mdb&quot;))
DSNtest=dsntest & &quot;DBQ=&quot; & Server.MapPath(&quot;yourdbhere.mdb&quot;)

sSQL = &quot;SELECT * FROM QRY_JC_5DayStatus&quot;

Set oRS = oConn.Execute(sSQL)

if oRS.EOF and oRS.BOF then
response.write &quot;<tr><td colspan=&quot;&quot;9&quot;&quot; align=&quot;&quot;center&quot;&quot;><b>No records found</b></center></td></tr>&quot;
'any other code you want here to handle errors and exceptions - like links to previous pages etc
else

Do while not oRS.EOF
'assign the value we want to check to the value result
result=oRS(&quot;DaysToClose&quot;)

'check the value and change the background colors for the row
'you might want to change the color values to HEX values
if result=4 then
var_backcolor=&quot;yellow&quot;
elseif result>=5 then
var_backcolor=&quot;red&quot;
else
'any other value (ie 0-3) the row color will be white
var_backcolor=&quot;white&quot;
end if

response.write &quot;<tr bgcolor=&quot; * var_backcolor & &quot;&quot;>&quot;

Response.Write &quot;<td>&quot; & oRS(&quot;YearClosed&quot;).Value & &quot;</td>&quot;
Response.Write &quot;<td>&quot; & oRS(&quot;MonthClosed&quot;).Value & &quot;</td>&quot;
Response.Write &quot;<td>&quot; & oRS(&quot;Region&quot;).Value & &quot;</td>&quot;
Response.Write &quot;<td>&quot; & oRS(&quot;MarketPlace&quot;).Value & &quot;</td>&quot;
Response.Write &quot;<td>&quot; & oRS(&quot;DaysToClose&quot;).Value & &quot;</td>&quot;
Response.Write &quot;<td>&quot; & oRS(&quot;PSN&quot;).Value & &quot;</td>&quot;
Response.Write &quot;<td>&quot; & oRS(&quot;CustID&quot;).Value & &quot;</td>&quot;
Response.Write &quot;<td>&quot; & oRS(&quot;OpenTime&quot;).Value & &quot;</td>&quot;
Response.Write &quot;<td>&quot; & oRS(&quot;ClosedTime&quot;).Value & &quot;</td>&quot;

Response.write &quot;</tr>&quot;
Loop
'add what ever code you have here to move to other pages or whatever

end if

%>


Bastien

cat, the other other white meat
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question Question
READ TEXT FILE BASED ON VARIABLE
Replies
2
Views
899
guitarzan
guitarzan
penguinspeaks
  • Locked
  • Question Question
Return to current location on page after asp processes.
Replies
2
Views
1K
penguinspeaks
penguinspeaks
JScannell
  • Locked
  • Question Question
Border issue with Google Chrome on an Android phone
Replies
0
Views
739
JScannell
JScannell
penguinspeaks
  • Locked
  • Question Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
718
penguinspeaks
penguinspeaks
penguinspeaks
  • Locked
  • Question Question
Start loop from a specified point
Replies
0
Views
641
penguinspeaks
penguinspeaks

Part and Inventory Search

Sponsor

Back
Top