Damo
Posts: 22
|
Posted: 07/03/2012, 9:17 AM |
|
Hi,
I am trying to calculate the highest value from a table and assign it to a varaible so I can use the variable result in an IF Then Else statement that will assign a string dependent on the results.
Eg. If the highestvalue is between 0 - 5 then it will result in "Poor", 6 - 10 = "Average", etc.
I have two tables: TblProperty and TblResults.
The two tables are linked together by an id 'PropertyID and 'ResultsID'. Each property will have a number of values associated with it (approx. 68 per property)
Within TblResults I have a column called 'Score' and there will be 68 values that are associated to each individual property.
I am trying to find out the highest value from the 'Score' column ( range: 0 - 25)
Hopefully I am trying to calculate this in the correct place:
I have created custom code in the "Before Build Select" event:
Dim SQL
Dim Connection
Dim highestvalue
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "TestServer"
SQL = "SELECT MAX(Score) as 'highestvalue' FROM TblResults"
Connection.Execute(SQL)
Set highestvalue = Connection.Execute(SQL)
Connection.Close
Set Connection = Nothing
I have created a label called 'highestvalue' and nothing is diaplayed.
I have even put a response.write ("Highest Risk is " & highestvalue) and I do not get a value.
I have tried different ways of assigning the results of my SQL statement to a variable but no luck, can someone please tell me where I am going wrong?
The If then else statement I am using is in the main ASP code, so please advise me if my variable will be read from there or if I need to write the highest value to the SQL database and then read the result in the if then else statement.
Thanks in advance.
Damo
|
|
|
Damo
Posts: 22
|
Posted: 07/04/2012, 8:56 AM |
|
Hi,
I have fixed my SQL statement to:
SQL = "SELECT MAX(Score) AS maxvalue FROM TblResults WHERE PropertyID = 'B25015'"
Set RecordSet = Connection.Execute(SQL)
highestvalue = RecordSet("maxvalue")
I get the correct result and it displays in a Response.Write
How do I read the variable value in the main ASP code?
Do I create a new column in the SQL database and do a INERT INTO VALUES or is there another way of reading the variable value?
Please advise.
Thanks in advance
|
|
|
|