CodeCharge Studio
search Register Login  

Visual PHP Web Development

Visually Create Internationalized Web Applications, Web Reports, Calendars, and more.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> ASP

 Distinct RecordCount always returns -1

Print topic Send  topic

Author Message
Damo

Posts: 22
Posted: 06/11/2012, 6:38 AM

Hi,

I am trying to find the unique record count for my query. The query uses two tables and uses a join hence the record count within the builder gives me the total number of records of the join. However, I only want the record count for the total amount distinct values.

I have amended the before show total record code to the following:

Dim Connection
Dim SQL
Dim RecordSet
Dim NoOfRecords

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

SQL = "SELECT COUNT(DISTINCT PropertyID) FROM TblProperty"

Set RecordSet = Connection.Execute(SQL)

NoOfRecords = RecordSet.RecordCount
TblAsbestos_TblProperty1.TblAsbestos_TblProperty1_TotalRecords.Value = NoOfRecords

The above code always returns -1. I have tried 'SELECT COUNT(*) ' and it still returns -1

Could someone please advise me what is wrong with my code.

Thanks in advance.
View profile  Send private message
andrewi

Posts: 162
Posted: 06/16/2012, 3:37 PM

Well, you're selecting a COUNT of record, so you're only going to get a single row with a single value in it. You're then attempting to count the number or records returned (RecordCount), not read the single value.

Consider this alternative -I've given a name to the Count column that you're selecting to make it clearer:

SQL = "SELECT COUNT(*) AS CountOfProperties FROM TblProperty"  
  
...  
  
NoOfRecords = Recordset("CountOfProperties").value


Hope that helps.

(The -1 is possibly because the RecordCount property is not available until you've performed a "MoveLast" operation. But you don't want to count the number of records returned - you just want to read the value returned by the COUNT() statement)
View profile  Send private message
TheunisP

Posts: 342
Posted: 06/16/2012, 5:13 PM

your code is counter productive - you do a select to count the records and at the end turn around and ask the record set's count property not the returned value


SQL = "SELECT COUNT(*) AS CountOfProperties FROM TblProperty"
Set RecordSet = Connection.Execute(SQL)
NoOfRecords = RecordSet("CountOfProperties")

should solve your problem




View profile  Send private message
TheunisP

Posts: 342
Posted: 06/16/2012, 5:14 PM

@andrewi - sorry didn;t see you reply - you are right ;-)

View profile  Send private message
Damo

Posts: 22
Posted: 07/04/2012, 8:14 AM

Hi,

Thank you for your solutions it worked!

Unfortunately I still require a little more help...

Now that I have the Count Value I want to Insert that into a table:

So far I have this...

Dim Connection
Dim SQL
Dim PutSQL
Dim RecordSet
Dim NoOfRecords

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

SQL = "SELECT COUNT(PropertyID) AS CountOfProperties FROM TblProperty Where Result = 'Issue'"
Set RecordSet = Connection.Execute(SQL)
NoOfRecords = RecordSet("CountOfProperties")

The code above works as I have tested it with a response.write

However, I need the result of the SQL statement to be shown in a grid. I created a new column and added a label and assigned the label value to 'CountOfProperties' but this did not work inside a grid only outside.

So I then decided to create the column (CountOfProperties) in the SQL table and then try and insert the value of the SQL statement to that column...

PutSQL = "INSERT INTO TblProperty(PropertyID, CountOfProperties) VALUES ("& Connection.ToSQL(PropertyID, ccsText)&","& Connection.ToSQL(CountOfProperties, ccsInteger)")"

Connection.Execute(PutSQL)

Could someone please advise me where I have gone wrong?

Thanks in advance.
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.

PHP Reports

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

Home   |    Search   |    Members   |    Register   |    Login


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