How to update page background color based on returned query value from SQL

S

Stefan B Rusynko

You could have saved everyone in this thread a lot of time if you started off by saying you were using the DataBase Result Wizard
(DBRW) on the page to get the value for the field named Cenvalue

The DBRW has is Not a query string
- it just happens to use a query string to generated recordsets (in a very specific way)

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


| > If CenValue is actually being passed by a parameter as in from
| > somepagelink.asp?CenValue=155
| >
| > <%
| > If Request.Querystring("CenValue")<>"" THEN CenValue = Request.Querystring("CenValue")
| > If IsNumeric(CenValue) THEN
| > IF CenValue <140 THEN
| > strColor ="green"
| > ELSEIF CenValue >=140 AND CenValue <160 THEN
| > strColor ="yellow"
| > ELSE
| > strColor ="red"
| > END IF
| > ELSE
| > strColor ="black" 'bad value passed
| > END IF
| > %>
| >
| > --
| >
| > _____________________________________________
| > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > "Warning - Using the F1 Key will not break anything!" (-;
| > _____________________________________________
| >
| >
| > | >
| > | >
| > | >
| > | >
| > | >
| > | > > IMHO
| > | > > It is dangerous coding practice to run a Cint on any value that you are not 100% certain will always be convertable to an
| > integer
| > | > > (not empty or null or text that can't be converted to an integer - which will all generate errors)
| > | >
| > | > > Better to always check it first using
| > | >
| > | > > IF IsNumeric(request.Querystring("parameterName")) THEN Cenvalue = Cint(request.Querystring("parameterName"))
| > | >
| > | > > --
| > | >
| > | > > _____________________________________________
| > | > > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > | > > "Warning - Using the F1 Key will not break anything!" (-;
| > | > > _____________________________________________
| > | >
| > | >
| > | > > || > | > > |
| > | > > | > >> Select the table w/ the DBRW value and Before it in code view add
| > | > > | > >>
| > | > > | > >> <%
| > | > > | > >> TestValue = FieldVal(fp_rs,"name")
| > | > > | > >> IF IsNumeric(TestValue) THEN 'Make sure it is a number
| > | > > | > >> IF TestValue <100 THEN
| > | > > | > >> strColor ="blue" 'Default if lower than special colors
| > | > > | > >> ELSEIF TestValue <=150 AND THEN
| > | > > | > >> strColor ="green"
| > | > > | > >> ELSEIF TestValue <=200 THEN
| > | > > | > >> strColor ="red"
| > | > > | > >> ' Additiontal values ranges here as more ELSEIF
| > | > > | > >> ELSE
| > | > > | > >> strColor ="purple" 'Default if none of above
| > | > > | > >> END IF
| > | > > | > >> END IF
| > | > > | > >> % >
| > | > > | > >>
| > | > > | > >> Then find your table cell w/ the DBRW value and change it to
| > | > > | > >> From
| > | > > | > >> <td><%=FP_FieldVal(fp_rs,"name")%></td>
| > | > > | > >> To
| > | > > | > >> <td background="<%=strColor%>"><%=FP_FieldVal(fp_rs,"name")%></td>
| > | > > | > >>
| > | > > | > >> --
| > | > > | > >>
| > | > > | > >> _____________________________________________
| > | > > | > >> SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > | > > | > >> "Warning - Using the F1 Key will not break anything!" (-;
| > | > > | > >> _____________________________________________
| > | > > | > >>
| > | > > | > >>
| > | > > | > >> |I need to change my background color based on the results of a query.
| > | > > | > >> | For example, if the value returned by my query is within 100 - 150, my
| > | > > | > >> | background color should be green. If it's 150-200, it would be red,
| > | > > | > >> | etc. I am using Frontpage Database componets to attach to this
| > | > > | > >> | database.
| > | > > | > >> |
| > | > > | > >> | Thanks,
| > | > > | > >> | Brian
| > | > > | > >> |
| > | > > | > >
| > | > > | > > I am trying to use this code:
| > | > > | > >
| > | > > | > > <%
| > | > > | > > CenValue = request.QueryString("SELECT * FROM Census")
| > | > > | > > IF IsNumeric(CenValue) THEN
| > | > > | > > IF CenValue <140 THEN
| > | > > | > > strColor ="green"
| > | > > | > > ELSEIF CenValue <=160 THEN
| > | > > | > > strColor ="yellow"
| > | > > | > > ELSEIF CenValue >=160 THEN
| > | > > | > > strColor ="red"
| > | > > | > > END IF
| > | > > | > > END IF
| > | > > | > > %>
| > | > > | > >
| > | > > | > > <body bgcolor="<%=strColor%>">
| > | > > | > >
| > | > > | > > My value for "cenvalue" is 146 but my color is still coming back as
| > | > > | > > green. For some reason it see's the cenvalue as less than 140 but it
| > | > > | > > is not. Any ideas?
| > | > > | >
| > | > > | > Because IF CenValue <140 and ElseIf CenValue <=160 are both true so the first condition still applies.
| > | > > | > Try testing for CenValue to be greater than 140 but less than 160 using AndIf
| > | > > | >
| > | > > | > --
| > | > > | > Steve Easton
| > | > > | > Microsoft MVP FrontPage
| > | > > | > FP Cleaner
| > | > > | >http://www.95isalive.com/fixes/fpclean.htm
| > | > > | > Hit Me FP
| > | > > | >http://www.95isalive.com/fixes/HitMeFP.htm
| > | > > | > Coming Soon: Function5 Auto Refresh
| > | > > |
| > | > > |
| > | > > | 146 < 140 ?
| > | > > |
| > | > > | I don't think a SQL statement is valid when used as a querystring
| > | > > | parameter name
| > | > > | CenValue = request.QueryString("SELECT * FROM Census")
| > | > > | Also, the CenValue retrived from request.Querystring("parameter") will
| > | > > | be a string, which when compared to a number will probably have a
| > | > > | numeric value of 0 - this is less than 140.
| > | > > |
| > | > > | I am not sure if CenValue is supposed to come from a parameter in a link
| > | > > | from another page, or from a database query.
| > | > > | If from the parameter
| > | > > | Then
| > | > > | Cenvalue = Cint(request.Querystring("parameterName"))
| > | > > | Would be better (if integer values are involved.
| > | > > | --
| > | > > | Ron Symonds - Microsoft MVP (FrontPage)
| > | > > | Reply only to group - emails will be deleted unread.
| > | > > | FrontPage Support: http://www.frontpagemvps.com/
| > | > > |http://www.rxs-enterprises.org/fp
| > | > > |
| > | >
| > | > OK, I am taking a step back and trying to get my comparison operators
| > | > right.
| > | >
| > | > I changed my code to the following:
| > | >
| > | > <%
| > | > CenValue = 155
| > | > IF CenValue <140 THEN
| > | > strColor ="green"
| > | > ELSEIF CenValue >=140 ANDIF CenValue <160 THEN
| > | > strColor ="yellow"
| > | > ELSEIF CenValue >=160 THEN
| > | > strColor ="red"
| > | > END IF
| > | > %>
| > | >
| > | > This time I am explicitly setting the CenValue. However, if I use
| > | > ANDIF, I get an error expecting another THEN. I am not worried about
| > | > my request.querystring value at this time, but I just need to get my
| > | > operators syntax correct.
| > | >
| > | > -Brian- Hide quoted text -
| > | >
| > | > - Show quoted text -
| > |
| > | Nevermind, I just dropped the "ANDIF" and used "AND". Works fine now.
| > | All I need now is to figure out my querystring so it returns the
| > | correct value. Thanks!
| > |
|
| Nope, Cenvalue is being taken from SQL
|
| <%
| fp_sQry="SELECT * FROM TotalCensus"
| fp_sDefault=""
| fp_sNoRecords="<tr><td colspan=1 align=""LEFT"" width=""100%"">No
| records returned.</td></tr>"
| fp_sDataConn="Census"
| fp_iMaxRecords=256
| fp_iCommandType=1
| fp_iPageSize=0
| fp_fTableFormat=True
| fp_fMenuFormat=False
| fp_sMenuChoice=""
| fp_sMenuValue=""
| fp_sColTypes="&TotalCensus=3&"
| fp_iDisplayCols=1
| fp_fCustomQuery=False
| BOTID=15
| fp_iRegion=BOTID
| %>
|
 
T

Techhead

You could have saved everyone in this thread a lot of time if you started off by saying you were using the DataBase Result Wizard
(DBRW) on the page to get the value for the field named Cenvalue

The DBRW has is Not a query string
- it just happens to use a query string to generated recordsets (in a very specific way)

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


| > If CenValue is actually being passed by a parameter as in from
| > somepagelink.asp?CenValue=155
| >
| > <%
| > If Request.Querystring("CenValue")<>"" THEN CenValue = Request.Querystring("CenValue")
| > If IsNumeric(CenValue) THEN
| > IF CenValue <140 THEN
| > strColor ="green"
| > ELSEIF CenValue >=140 AND CenValue <160 THEN
| > strColor ="yellow"
| > ELSE
| > strColor ="red"
| > END IF
| > ELSE
| > strColor ="black" 'bad value passed
| > END IF
| > %>
| >
| > --
| >
| > _____________________________________________
| > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > "Warning - Using the F1 Key will not break anything!" (-;
| > _____________________________________________
| >
| >
| > | >
| > | >
| > | >
| > | >
| > | >
| > | > > IMHO
| > | > > It is dangerous coding practice to run a Cint on any value that you are not 100% certain will always be convertable to an
| > integer
| > | > > (not empty or null or text that can't be converted to an integer - which will all generate errors)
| > | >
| > | > > Better to always check it first using
| > | >
| > | > > IF IsNumeric(request.Querystring("parameterName")) THEN Cenvalue = Cint(request.Querystring("parameterName"))
| > | >
| > | > > --
| > | >
| > | > > _____________________________________________
| > | > > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > | > > "Warning - Using the F1 Key will not break anything!" (-;
| > | > > _____________________________________________
| > | >
| > | >
| > | > > || > | > > |
| > | > > | > >> Select the table w/ the DBRW value and Before it in code view add
| > | > > | > >>
| > | > > | > >> <%
| > | > > | > >> TestValue = FieldVal(fp_rs,"name")
| > | > > | > >> IF IsNumeric(TestValue) THEN 'Make sure it is a number
| > | > > | > >> IF TestValue <100 THEN
| > | > > | > >> strColor ="blue" 'Default if lower than special colors
| > | > > | > >> ELSEIF TestValue <=150 AND THEN
| > | > > | > >> strColor ="green"
| > | > > | > >> ELSEIF TestValue <=200 THEN
| > | > > | > >> strColor ="red"
| > | > > | > >> ' Additiontal values ranges here as more ELSEIF
| > | > > | > >> ELSE
| > | > > | > >> strColor ="purple" 'Default if none of above
| > | > > | > >> END IF
| > | > > | > >> END IF
| > | > > | > >> % >
| > | > > | > >>
| > | > > | > >> Then find your table cell w/ the DBRW value and change it to
| > | > > | > >> From
| > | > > | > >> <td><%=FP_FieldVal(fp_rs,"name")%></td>
| > | > > | > >> To
| > | > > | > >> <td background="<%=strColor%>"><%=FP_FieldVal(fp_rs,"name")%></td>
| > | > > | > >>
| > | > > | > >> --
| > | > > | > >>
| > | > > | > >> _____________________________________________
| > | > > | > >> SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > | > > | > >> "Warning - Using the F1 Key will not break anything!" (-;
| > | > > | > >> _____________________________________________
| > | > > | > >>
| > | > > | > >>
| > | > > | > >> |I need to change my background color based on the results of a query.
| > | > > | > >> | For example, if the value returned by my query is within 100 - 150, my
| > | > > | > >> | background color should be green. If it's 150-200, it would be red,
| > | > > | > >> | etc. I am using Frontpage Database componets to attach to this
| > | > > | > >> | database.
| > | > > | > >> |
| > | > > | > >> | Thanks,
| > | > > | > >> | Brian
| > | > > | > >> |
| > | > > | > >
| > | > > | > > I am trying to use this code:
| > | > > | > >
| > | > > | > > <%
| > | > > | > > CenValue = request.QueryString("SELECT * FROM Census")
| > | > > | > > IF IsNumeric(CenValue) THEN
| > | > > | > > IF CenValue <140 THEN
| > | > > | > > strColor ="green"
| > | > > | > > ELSEIF CenValue <=160 THEN
| > | > > | > > strColor ="yellow"
| > | > > | > > ELSEIF CenValue >=160 THEN
| > | > > | > > strColor ="red"
| > | > > | > > END IF
| > | > > | > > END IF
| > | > > | > > %>
| > | > > | > >
| > | > > | > > <body bgcolor="<%=strColor%>">
| > | > > | > >
| > | > > | > > My value for "cenvalue" is 146 but my color is still coming back as
| > | > > | > > green. For some reason it see's the cenvalue as less than 140 but it
| > | > > | > > is not. Any ideas?
| > | > > | >
| > | > > | > Because IF CenValue <140 and ElseIf CenValue <=160 are both true so the first condition still applies.
| > | > > | > Try testing for CenValue to be greater than 140 but less than 160 using AndIf
| > | > > | >
| > | > > | > --
| > | > > | > Steve Easton
| > | > > | > Microsoft MVP FrontPage
| > | > > | > FP Cleaner
| > | > > | >http://www.95isalive.com/fixes/fpclean.htm
| > | > > | > Hit Me FP
| > | > > | >http://www.95isalive.com/fixes/HitMeFP.htm
| > | > > | > Coming Soon: Function5 Auto Refresh
| > | > > |
| > | > > |
| > | > > | 146 < 140 ?
| > | > > |
| > | > > | I don't think a SQL statement is valid when used as a querystring
| > | > > | parameter name
| > | > > | CenValue = request.QueryString("SELECT * FROM Census")
| > | > > | Also, the CenValue retrived from request.Querystring("parameter") will
| > | > > | be a string, which when compared to a number will probably have a
| > | > > | numeric value of 0 - this is less than 140.
| > | > > |
| > | > > | I am not sure if CenValue is supposed to come from a parameter in a link
| > | > > | from another page, or from a database query.
| > | > > | If from the parameter
| > | > > | Then
| > | > > | Cenvalue = Cint(request.Querystring("parameterName"))
| > | > > | Would be better (if integer values are involved.
| > | > > | --
| > | > > | Ron Symonds - Microsoft MVP (FrontPage)
| > | > > | Reply only to group - emails will be deleted unread.
| > | > > | FrontPage Support: http://www.frontpagemvps.com/
| > | > > |http://www.rxs-enterprises.org/fp
| > | > > |
| > | >
| > | > OK, I am taking a step back and trying to get my comparison operators
| > | > right.
| > | >
| > | > I changed my code to the following:
| > | >
| > | > <%
| > | > CenValue = 155
| > | > IF CenValue <140 THEN
| > | > strColor ="green"
| > | > ELSEIF CenValue >=140 ANDIF CenValue <160 THEN
| > | > strColor ="yellow"
| > | > ELSEIF CenValue >=160 THEN
| > | > strColor ="red"
| > | > END IF
| > | > %>
| > | >
| > | > This time I am explicitly setting the CenValue. However, if I use
| > | > ANDIF, I get an error expecting another THEN. I am not worried about
| > | > my request.querystring value at this time, but I just need to get my
| > | > operators syntax correct.
| > | >
| > | > -Brian- Hide quoted text -
| > | >
| > | > - Show quoted text -
| > |
| > | Nevermind, I just dropped the "ANDIF" and used "AND". Works fine now.
| > | All I need now is to figure out my querystring so it returns the
| > | correct value. Thanks!
| > |
|
| Nope, Cenvalue is being taken from SQL
|
| <%
| fp_sQry="SELECT * FROM TotalCensus"
| fp_sDefault=""
| fp_sNoRecords="<tr><td colspan=1 align=""LEFT"" width=""100%"">No
| records returned.</td></tr>"
| fp_sDataConn="Census"
| fp_iMaxRecords=256
| fp_iCommandType=1
| fp_iPageSize=0
| fp_fTableFormat=True
| fp_fMenuFormat=False
| fp_sMenuChoice=""
| fp_sMenuValue=""
| fp_sColTypes="&TotalCensus=3&"
| fp_iDisplayCols=1
| fp_fCustomQuery=False
| BOTID=15
| fp_iRegion=BOTID
| %>
|
Click to expand...

I did mention I was using FrontPage Database components at the very
beginning of my post but sorry I did not refer to it as DBRW.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Change text box background color depending on value in Label Reports 0
How to color cells based upon a value in another cell 1
Change cell background color based on content that results from li 4
background Color 0
Conditional Sum Based on Cell Background Color 6
Change background color of record 3
How do I change color attributes? 0
change the color of cell background based on a result 2

Top