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

Cell Colour based on Value

Status
Not open for further replies.
dirtybernard

dirtybernard

IS-IT--Management
Feb 20, 2012
3
GB
thread256-1078020

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.
 
Sort by date Sort by votes
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>
 
Upvote 0 Downvote
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
If you like stories, try
 
Upvote 0 Downvote
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>
    <!--webbot bot="DatabaseRegionStart" s-columnnames="entryid,depot,customerid,customername,contact,address1,address2,town,county,postcode,telephone,fax,dateofhire,destination,pickup1,pickup2,pickup3,starttime,returntime,typeofcoach,sizeofcoach,specials,price,drivername,regno,returndate,deposit,comments,km,invno,am,pm,date" s-columntypes="3,202,2,202,202,202,202,202,202,202,202,202,135,202,202,202,202,202,202,202,202,202,6,202,202,135,6,202,202,202,202,202,135" s-dataconnection="Diary" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource="Diary" s-displaycolumns="entryid,depot,customerid,customername,contact,address1,dateofhire,destination,date" s-criteria s-order s-sql="SELECT * FROM Diary" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="No records returned." i-maxrecords="256" i-groupsize="0" botid="0" u-dblib="../_fpclass/fpdblib.inc" u-dbrgn1="../_fpclass/fpdbrgn1.inc" u-dbrgn2="../_fpclass/fpdbrgn2.inc" tag="TBODY" preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region. 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.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" startspan --><!--#include file="../_fpclass/fpdblib.inc"-->
<% 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
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="47516" --><tr>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="entryid,depot,customerid,customername,contact,address1,address2,town,county,postcode,telephone,fax,dateofhire,destination,pickup1,pickup2,pickup3,starttime,returntime,typeofcoach,sizeofcoach,specials,price,drivername,regno,returndate,deposit,comments,km,invno,am,pm,date" s-column="entryid" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;entryid&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"entryid")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="15463" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="entryid,depot,customerid,customername,contact,address1,address2,town,county,postcode,telephone,fax,dateofhire,destination,pickup1,pickup2,pickup3,starttime,returntime,typeofcoach,sizeofcoach,specials,price,drivername,regno,returndate,deposit,comments,km,invno,am,pm,date" s-column="depot" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;depot&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"depot")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="9347" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="entryid,depot,customerid,customername,contact,address1,address2,town,county,postcode,telephone,fax,dateofhire,destination,pickup1,pickup2,pickup3,starttime,returntime,typeofcoach,sizeofcoach,specials,price,drivername,regno,returndate,deposit,comments,km,invno,am,pm,date" s-column="customerid" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;customerid&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"customerid")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="31660" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="entryid,depot,customerid,customername,contact,address1,address2,town,county,postcode,telephone,fax,dateofhire,destination,pickup1,pickup2,pickup3,starttime,returntime,typeofcoach,sizeofcoach,specials,price,drivername,regno,returndate,deposit,comments,km,invno,am,pm,date" s-column="customername" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;customername&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"customername")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="34187" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="entryid,depot,customerid,customername,contact,address1,address2,town,county,postcode,telephone,fax,dateofhire,destination,pickup1,pickup2,pickup3,starttime,returntime,typeofcoach,sizeofcoach,specials,price,drivername,regno,returndate,deposit,comments,km,invno,am,pm,date" s-column="contact" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;contact&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"contact")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="15573" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="entryid,depot,customerid,customername,contact,address1,address2,town,county,postcode,telephone,fax,dateofhire,destination,pickup1,pickup2,pickup3,starttime,returntime,typeofcoach,sizeofcoach,specials,price,drivername,regno,returndate,deposit,comments,km,invno,am,pm,date" s-column="address1" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;address1&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"address1")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="14794" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="entryid,depot,customerid,customername,contact,address1,address2,town,county,postcode,telephone,fax,dateofhire,destination,pickup1,pickup2,pickup3,starttime,returntime,typeofcoach,sizeofcoach,specials,price,drivername,regno,returndate,deposit,comments,km,invno,am,pm,date" s-column="dateofhire" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;dateofhire&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"dateofhire")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="29543" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="entryid,depot,customerid,customername,contact,address1,address2,town,county,postcode,telephone,fax,dateofhire,destination,pickup1,pickup2,pickup3,starttime,returntime,typeofcoach,sizeofcoach,specials,price,drivername,regno,returndate,deposit,comments,km,invno,am,pm,date" s-column="destination" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;destination&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"destination")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="31938" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="entryid,depot,customerid,customername,contact,address1,address2,town,county,postcode,telephone,fax,dateofhire,destination,pickup1,pickup2,pickup3,starttime,returntime,typeofcoach,sizeofcoach,specials,price,drivername,regno,returndate,deposit,comments,km,invno,am,pm,date" s-column="date" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;date&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"date")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="5315" --></td>
    </tr>
    <!--webbot bot="DatabaseRegionEnd" b-tableformat="TRUE" b-menuformat="FALSE" u-dbrgn2="../_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside tag="TBODY" preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the end of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" startspan --><!--#include file="../_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="56926" --></tbody>

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

</body>

</html>
 
Upvote 0 Downvote
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>
 
Upvote 0 Downvote
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 ???
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Viandit
  • Locked
  • Question
mp4 on FrontPage
Replies
0
Views
452
Viandit
Viandit
yeboyerp
  • Locked
  • Question
MS Front Page 2003 - Cell Height changing
Replies
0
Views
223
yeboyerp
yeboyerp
NewGirl999
  • Locked
  • Question
Is it possible to show database record number on confirmation 2
Replies
3
Views
213
NewGirl999
NewGirl999
foalmom
  • Locked
  • Question
This is a new one for me
Replies
3
Views
314
xwb
xwb
deanbri75
  • Locked
  • Question
database results based on user login
Replies
0
Views
126
deanbri75
deanbri75

Part and Inventory Search

Sponsor

Back
Top