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.
|
|
|
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)
|
|
|
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
|
|
|
TheunisP
Posts: 342
|
Posted: 06/16/2012, 5:14 PM |
|
@andrewi - sorry didn;t see you reply - you are right
|
|
|
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.
|
|
|
|