Contact US

Question

Cell Colour based on Value

dirtybernard (IS/IT--Management)

(OP)

20 Feb 12 11:56

thread256-1078020: Change cell or font color based on data value

Hi everyone, i have tried to follow the example in the linked thread but i must be stupid.

I am using frontpage 2003 and the database results wizard.
I have a table extracting data which has the following column headings:-

depot customer contact address1 dateofhire destination etc etc

we have 5 depots, LEY WKS BOS ASP HWD, and i'd like the depot cell colour to be in a different colour according to the value 'LEY WLK' of the depot field, to make it easier for people to associate the work for each depot.

Is this possible, please help, i've been struggling on this for ages.

xwb (Programmer) · Answer 1 · 2012-02-21 17:56:17

It will be something like this

CODE

<html>
<body>
<script language="vbscript">
' Set up the test data
dim v(20), depot(5), colour(5)
vcolmax = 20

' Initialize
depot(0) = "LEY": colour(0) = "red"
depot(1) = "WKS": colour(1) = "blue"
depot(2) = "BOS": colour(2) = "green"
depot(3) = "ASP": colour(3) = "cyan"
depot(4) = "HWD": colour(4) = "magenta"
depotmax = 4

' Set up test data
for ii = 0 to vcolmax
   v(ii) = depot(cint(rnd() * 5))
next
</script>
<table border="1" cellspacing="1" width="100%" id="AutoNumber1">
<tr>
<th>Depot</th>
<th>Customer</th>
<th>Contact</th>
<th>Address</th>
<th>Date Of Hire</th>
<th>Destination</th>
</tr>
<script language="vbscript">
   for i = 1 to vcolmax
      document.write  "<tr>"
      for dd = 0 to depotmax
         if v(i) = depot(dd) then
            document.write "<td width=""50%"" bgcolor=""" & colour(dd) & """>" & depot(dd) & "</td>"
            exit for
         end if
      next
      document.write "<td></td>"
      document.write "<td></td>"
      document.write "<td></td>"
      document.write "<td></td>"
      document.write "<td></td>"
      document.write "</tr>"
   next
</script>
</table>
</script>
</body>
</html>

xwb (Programmer) · Answer 2 · 2012-02-22 00:56:15

If this doesn't fix your problem I will have to setup a database to see what sort of code the wizard is generating.  It could be that you are putting in code where the wizard expects to write code and it is clobbering it.  I find that spinning my own database code is faster than using the wizard.

Like in all games, fighting the wizard is difficult.  It is doable but you will fail many times.  All the code is crammed on one line and there are no comments to help you.  You have to see what it is doing, modify what it is going to generate and then it will do what you want.

Alternatively, instead of fighting the wizard, you could use a dsnless connection and do the code by yourself.  Have a look at
http://www.haneng.com/lessons_20.asp.  If you like stories, try http://www.powerasp.net/content/database/dsn_vs_dnsless.asp.

dirtybernard (IS/IT--Management) · Answer 3 · 2012-02-23 06:52:35

Hi, here is my code, can you try and add yours to it to make it work?

CODE

<html>

<head>
<% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do not Edit.
FP_LCID = 2057 %>
<meta http-equiv="Content-Language" content="en-gb">
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do not Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Diary</title>
<meta name="Microsoft Border" content="b, default">
</head>

<body>

<p align="center">TesT New with Colour</p>
<table width="100%" border="1">
  <thead>
    <tr>
      <th ALIGN="LEFT"><b>entryid</b></th>
      <th ALIGN="LEFT"><b>depot</b></th>
      <th ALIGN="LEFT"><b>customerid</b></th>
      <th ALIGN="LEFT"><b>customername</b></th>
      <th ALIGN="LEFT"><b>contact</b></th>
      <th ALIGN="LEFT"><b>address1</b></th>
      <th ALIGN="LEFT"><b>dateofhire</b></th>
      <th ALIGN="LEFT"><b>destination</b></th>
      <th ALIGN="LEFT"><b>date</b></th>
    </tr>

  </thead>
  <tbody>
    
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Diary"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=9 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Diary"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&entryid=3&depot=202&customerid=2&customername=202&contact=202&address1=202&address2=202&town=202&county=202&postcode=202&telephone=202&fax=202&dateofhire=135&destination=202&pickup1=202&pickup2=202&pickup3=202&starttime=202&returntime=202&typeofcoach=202&sizeofcoach=202&specials=202&price=6&drivername=202&regno=202&returndate=135&deposit=6&comments=202&km=202&invno=202&am=202&pm=202&date=135&"
fp_iDisplayCols=9
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

<tr>
      <td>
      <%=FP_FieldVal(fp_rs,"entryid")%></td>
      <td>
      <%=FP_FieldVal(fp_rs,"depot")%></td>
      <td>
      <%=FP_FieldVal(fp_rs,"customerid")%></td>
      <td>
      <%=FP_FieldVal(fp_rs,"customername")%></td>
      <td>
      <%=FP_FieldVal(fp_rs,"contact")%></td>
      <td>
      <%=FP_FieldVal(fp_rs,"address1")%></td>
      <td>
      <%=FP_FieldVal(fp_rs,"dateofhire")%></td>
      <td>
      <%=FP_FieldVal(fp_rs,"destination")%></td>
      <td>
      <%=FP_FieldVal(fp_rs,"date")%></td>
    </tr>
    
</tbody>

</table>
<p align="center">

</body>

</html>

xwb (Programmer) · Answer 4 · 2012-02-24 17:53:58

It is easier to rewrite everyting without using the wizard. Wizard fighting is best left to the masochists. It will look something like this

CODE

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>Select using DSN</title>
</head>

<body>
<p align="center">TesT New with Colour</p>

<%
' Set up the lookup table
dim depot, colour
depot = array ("LEY", "WKS", "BOS", "ASP", "HWD")
colour = array("red", "blue", "green", "cyan", "magenta")
depotmin = lbound(depot)
depotmax = ubound(depot)

dim conn, mdbpath, connstr, sqlQuery, field, dvalue, dcolour, dd
connstr = "Diary"
sqlQuery = "select * from Diary"
' These are the fields we want to print
field = array ("entryid","depot", "customerid", "customername","contact","address1","dateofhire","destination","date")

set conn = Server.CreateObject("ADODB.Connection")
conn.Open connstr
set rs = conn.Execute (sqlQuery)
%>
<table width="100%" border="1">
  <thead>
    <tr>
<%
' Print the header
for each f in field
   Response.Write("<th>" & f & "</th>")
next%>
    </tr>
  </thead>
  <tbody>
<%
if rs.eof then
   ' No data
    Response.Write ("<tr><td colspan=<" & UBound(field) & "align=left width=""100%"">No records returned.</td></tr>")
else
   do while not rs.eof
      Response.Write ("<tr>")
      for each f in field
         if f = "depot" then
            ' extract the value
            dvalue = rs(f)
            ' find the matching colour
            dcolour = depot(depotmin)
            for dd = depotmin to depotmax
               if dvalue = depot(dd) then
                  dcolour = colour(dd)
                  exit for
               end if
            next
            Response.Write ("<td width=""50%"" bgcolor=""" & dcolour & """>" & dvalue & "</td>")
          else
              Response.Write ("<td>" & rs(f) & "</td>")
           end if
      next
      Response.Write ("</tr>")
      rs.MoveNext
   loop
end if
%>
  </tbody>
</body>

dirtybernard (IS/IT--Management) · Answer 5 · 2012-02-27 09:18:21

That just comes up with an ODBC error, is there not a way to format the wizard generated table using Javascript? If 'depot' = LEY then colour = blue etc ???

xwb (Programmer) · Answer 6 · 2012-02-28 14:38:01

You used to be able to do that before they added in their checksums. The checksums work out whether you have modified the code or not and just go bezerk.

You could try the following in place of conn.open

CODE

mdbpath = Server.MapPath("Diary")
connstr = "Driver={Microsoft Access Driver (*.mdb)};"
connstr = connstr & "DBQ=" & mdbpath & ";"
conn.Open connstr

I am assuming you are using an Access DB. The connection string is different for different database engines.

Come Join Us!

Posting Guidelines

Cell Colour based on Value

Cell Colour based on Value

Cell Colour based on Value

RE: Cell Colour based on Value

CODE

RE: Cell Colour based on Value

RE: Cell Colour based on Value

CODE

RE: Cell Colour based on Value

CODE

RE: Cell Colour based on Value

RE: Cell Colour based on Value

CODE

Red Flag This Post

Red Flag Submitted

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Join Tek-Tips^® Today!

Contact US

Come Join Us!

Posting Guidelines

Cell Colour based on Value

Cell Colour based on Value

Cell Colour based on Value

RE: Cell Colour based on Value

CODE

RE: Cell Colour based on Value

RE: Cell Colour based on Value

CODE

RE: Cell Colour based on Value

CODE

RE: Cell Colour based on Value

RE: Cell Colour based on Value

CODE

Red Flag This Post

Red Flag Submitted

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Join Tek-Tips® Today!

Join Tek-Tips^® Today!