|Posted: 07/16/2012, 8:51 AM
I have an SQL statement in the Before_Show that is supposed to look at each row and then return the highest value assigned to it. There are two tables; TblProperty that holds all of the property records (PK UPRN) and TblFire (PK FireNo) which holds all of the associated fire information.
Each property in TblProperty is unique and there will be muliple associated records for each FireUPRN in TblFire. The below code works that it finds the highest number for the first property in the database but it updates all the records for all UPRNs with the same value and does not look at each UPRN and calculates the highest value.
I have been stuck on this for a while now so I would be grateful for any assistance
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")
'Recordset.Open SQL, Connection
SQL = "SELECT FireUPRN, Max(Risk) AS CountOfIssues" & vbLf & _
"FROM TblFire" & vbLf & _
"WHERE Notes Is Not Null" & vbLf & _
"Group By FireUPRN, Risk"
Set RecordSet = Connection.Execute(SQL)
Set ActionsIssued = RecordSet("CountOfIssues")
Response.Write ("Highest Risk Action: " & (ActionsIssued))
While not Recordset.EOF
SQL = "UPDATE TblProperty SET CurrentSiteRiskLevel = '" & ActionsIssued & "'"
Set Recordset = Nothing
Set Connection = Nothing
I do not get an error until I put the line...Recordset.Open SQL, Connection
but then I think that the record set is not open hence it does not move to the next row and update.