CodeCharge Studio
search Register Login  

Web Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> ASP

 SQL Update for each row in the Recordset

Print topic Send  topic

Author Message
Damo

Posts: 22
Posted: 07/16/2012, 8:51 AM

Hi,

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

Dim Connection
Dim SQL
Dim RecordSet
Dim ActionsIssued

Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "Lewisham"

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 & "'"
Connection.Execute(SQL)
Recordset.MoveNext
Wend


Set Recordset = Nothing
Connection.Close
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.

Thanks.
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Internet Database

Visually create Web enabled database applications in minutes.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.