working with values returned via SQL

[cmt]

I have an ASP report that returns values from a SQL database and
formats the data in an HTML table.

I am trying to figure out a good way of using CSS to highlight the
table row that contains the highest value returned.

I tried storing the values in an array, and then used a function to
find the highest value. This worked, but then I couldn't really
figure out how to "tell" the report to highlight that highest value.

Could anyone think of an easier way?

Thanks!

 

[Bob Barrows [MVP]]

I have an ASP report that returns values from a SQL database and
formats the data in an HTML table.

I am trying to figure out a good way of using CSS to highlight the
table row that contains the highest value returned.

I tried storing the values in an array, and then used a function to
find the highest value. This worked, but then I couldn't really
figure out how to "tell" the report to highlight that highest value.

Could anyone think of an easier way?

This is supposed to be fairly easy using xml and xslt, but I never
really got a handle around that.

Look at it this way: start with knowing what the html is supposed to
look like. Then use asp to generate a string that looks like what the
intended html is supposed to look like. You sound like you have a good
start. Once you identify the highest value, then use response.write to
output the style attribute string to make it stand out.

Personally, I would use ORDER BY ... DESC in the sql statement so that
the first record in the resultset will always be the one I want to
highlight.

 

[Mike Brind [MVP]]

I have an ASP report that returns values from a SQL database and
formats the data in an HTML table.

I am trying to figure out a good way of using CSS to highlight the
table row that contains the highest value returned.

I tried storing the values in an array, and then used a function to
find the highest value. This worked, but then I couldn't really
figure out how to "tell" the report to highlight that highest value.

Could anyone think of an easier way?

Thanks!

Bob's suggestion is the most straightforward way to approach this, but you
may have reasons for ordering the results according to a different column.
If so, when you are looping through the array to write the <tr>'s out,
simply add an If statement that tests if the current row contains the
highest value. Asuming that your iterator for the for... next loop on the
array is i, and the first column contains the value to test:

Response.Write "<tr "
If arr(0,i) = myHighestValue Then Response.Write "style='background-color:
red;'"
Response.Write ">"

 

[cmt]

Bob's suggestion is the most straightforward way to approach this, but you
may have reasons for ordering the results according to a different column.
If so, when you are looping through the array to write the <tr>'s out,
simply add an If statement that tests if the current row contains the
highest value. Asuming that your iterator for the for... next loop on the
array is i, and the first column contains the value to test:

Response.Write "<tr "
If arr(0,i) = myHighestValue Then Response.Write "style='background-color:
red;'"
Response.Write ">"

Mike, would this work if I am populating the array in the recordset
loop?

Right now, I have objRs in a loop that populates each <td> has it goes
through the loop. As each value is read via ObjRs, I also add that
value to the array. So the array is not fully populated until the
objRs is finished getting all the data.

Here is a sample of what the code structure looks like:

<table border="1">

<%
h = 0 'hitArray counter
objRs.Open strQry, dbLocation, 1
Do While Not objRs.EOF And Not objRs.BOF %>

<tr>
<td><% Response.Write objRs("urlName")%></td>
</tr>
<tr>
<td><% Response.Write objRs("urlDescription")%></td>
</tr>
<% h = h + 1
ReDim Preserve hitArray(h)
hitArray(h) = (objRs("NumberofHits")) %>
<tr>
<td><% Response.Write objRs("NumberofHits")%>
</tr>

Loop

objRs.Close%>

So the array works...it populates with all of the needed data. But it
is not finished populating until the loop is done running its course.
So I don't think there is a way I could test to see if
objRs("NumberofHits") is the maximum value everytime I write it out.

I hope that makes sense!

Thanks!

 

[Mike Brind [MVP]]

Mike, would this work if I am populating the array in the recordset
loop?

Right now, I have objRs in a loop that populates each <td> has it goes
through the loop. As each value is read via ObjRs, I also add that
value to the array. So the array is not fully populated until the
objRs is finished getting all the data.

Here is a sample of what the code structure looks like:

<table border="1">

<%
h = 0 'hitArray counter
objRs.Open strQry, dbLocation, 1
Do While Not objRs.EOF And Not objRs.BOF %>

<tr>
<td><% Response.Write objRs("urlName")%></td>
</tr>
<tr>
<td><% Response.Write objRs("urlDescription")%></td>
</tr>
<% h = h + 1
ReDim Preserve hitArray(h)
hitArray(h) = (objRs("NumberofHits")) %>
<tr>
<td><% Response.Write objRs("NumberofHits")%>
</tr>

Loop

objRs.Close%>

So the array works...it populates with all of the needed data. But it
is not finished populating until the loop is done running its course.
So I don't think there is a way I could test to see if
objRs("NumberofHits") is the maximum value everytime I write it out.

I hope that makes sense!

Thanks!

Right. I assumed that you were already using GetRows() to create an array
from the recordset, then looping that to establish the highest value before
looping it again to write out your html string. That's probably what I
would do.

 

[cmt]

Right. I assumed that you were already using GetRows() to create an array
from the recordset, then looping that to establish the highest value before
looping it again to write out your html string. That's probably what I
would do.

Mike,

So I would setup the array like this...assuming CheckMaxValue is a
function that checks the value against hitArray to see if it is the
maximum value?

h = 0 'hitArray counter
objRs.Open strQry, dbLocation, 1
hitArray = GetRows() 'this array now contains all the rows
from the strQry??

Do While Not objRs.EOF And Not objRs.BOF %>

<tr>
<td><% Response.Write objRs("urlName")%></td>
</tr>
<tr>
<td><% Response.Write objRs("urlDescription")%></td>
</tr>
<tr>
<td><% Response.Write CheckMaxValue(objRs("NumberofHits"))%>
</tr>

Loop

objRs.Close%>

Thanks!

 

[Mike Brind [MVP]]

So I would setup the array like this...assuming CheckMaxValue is a
function that checks the value against hitArray to see if it is the
maximum value?

h = 0 'hitArray counter
objRs.Open strQry, dbLocation, 1
hitArray = GetRows() 'this array now contains all the rows
from the strQry??

Do While Not objRs.EOF And Not objRs.BOF %>

<tr>
<td><% Response.Write objRs("urlName")%></td>
</tr>
<tr>
<td><% Response.Write objRs("urlDescription")%></td>
</tr>
<tr>
<td><% Response.Write CheckMaxValue(objRs("NumberofHits"))%>
</tr>

Loop

objRs.Close%>

Thanks!

hitArray = objRS.GetRows() <-- now hitarray holds the values from objRS.

So, you can now close objRS, because you have no need of it anymore. You
will need to test if there are any records/elements in hitArray, which you
can do with IsArray():

If IsArray(hitArray) Then
'start looping through it as many times as you need.