Count (*)
-
I wish to count the number of records in my table and then display the number on the screen So i done:
SQLQuery = "SELECT COUNT(*) FROM Log" Set RS = OBJdbConnection.Execute(SQLQuery) Response.Write(RS.RecordCount)But i'm getting into a muddle on how to do it. Can someone please show me the light.!! --- Peter M -
I wish to count the number of records in my table and then display the number on the screen So i done:
SQLQuery = "SELECT COUNT(*) FROM Log" Set RS = OBJdbConnection.Execute(SQLQuery) Response.Write(RS.RecordCount)But i'm getting into a muddle on how to do it. Can someone please show me the light.!! --- Peter MFirst of all, does the Log table have an primary key index, preferable an autoincrementing ID. It would be much faster as the log file table size increases to do a 'select count(ID) from log' than doing a count(*). Executing a count(*) may induce a table scan while count(ID) will only scan the primary key index. 2nd, you should probably execute a OBJdbConnection.ExecuteScalar rather than a execute. The executescalar is designed to return a single value which is what you are looking for.
Dim rowCount as object
SQLQuery = "SELECT COUNT(ID) FROM Log"
value = OBJdbConnection.ExecuteScalar(SQLQuery)
if not value is dbnull.value then
'Make sure you didn't receive a Null from the database.
'If not it should be an int or int32. Can't remember which one is actually returned.
Response.Write(value.tostring)
end ifThe above is not tested but you should get the idea. Michael I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious. Vince Lombardi (1913-1970)
-
First of all, does the Log table have an primary key index, preferable an autoincrementing ID. It would be much faster as the log file table size increases to do a 'select count(ID) from log' than doing a count(*). Executing a count(*) may induce a table scan while count(ID) will only scan the primary key index. 2nd, you should probably execute a OBJdbConnection.ExecuteScalar rather than a execute. The executescalar is designed to return a single value which is what you are looking for.
Dim rowCount as object
SQLQuery = "SELECT COUNT(ID) FROM Log"
value = OBJdbConnection.ExecuteScalar(SQLQuery)
if not value is dbnull.value then
'Make sure you didn't receive a Null from the database.
'If not it should be an int or int32. Can't remember which one is actually returned.
Response.Write(value.tostring)
end ifThe above is not tested but you should get the idea. Michael I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious. Vince Lombardi (1913-1970)
Yes it does have a primary index, which is just a auto number. Before I recieved your message, I managed to get things working by doing the following
SQLQuery = "SELECT * FROM Log" Set RS = OBJdbConnection.Execute(SQLQuery) While (Not RS.EOF) sTotalpages = sTotalpages + 1 RS.MoveNext WendMaybe its a crude way to count the pages. I'll give your code a try now --- Peter M -
Yes it does have a primary index, which is just a auto number. Before I recieved your message, I managed to get things working by doing the following
SQLQuery = "SELECT * FROM Log" Set RS = OBJdbConnection.Execute(SQLQuery) While (Not RS.EOF) sTotalpages = sTotalpages + 1 RS.MoveNext WendMaybe its a crude way to count the pages. I'll give your code a try now --- Peter MWell I tried your code but I get the error "ADODB.Connection error '800a0bb9' Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. /Includes/paging.asp, line 12 " I couldnt' get the tostring to work, so i took it out as well as the error checking, as they will not be blank fields (we are checking the primary after all) So the code ended up
dim sTotalpages SQLQuery = "SELECT COUNT(ID) FROM Log" sTotalpages = OBJdbConnection.ExecuteScalar(SQLQuery) 'Line 12 - the error point Response.Write(sTotalpages)I'm rather confused to why I am having problems, i mean it looks easy enough, there is obviously something I am doing wrong. Well I'll go back to my old method for now, and have a break, and come back to it later --- Peter M -
Yes it does have a primary index, which is just a auto number. Before I recieved your message, I managed to get things working by doing the following
SQLQuery = "SELECT * FROM Log" Set RS = OBJdbConnection.Execute(SQLQuery) While (Not RS.EOF) sTotalpages = sTotalpages + 1 RS.MoveNext WendMaybe its a crude way to count the pages. I'll give your code a try now --- Peter MDefinitely give it a try. It probably has bugs since it was written on-the-fly but looping through the recordset is definitely an insufficient way of doing it. ExecuteScalar with the count being on the primary key is probably the most efficient way of obtaining the count. Michael I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious. Vince Lombardi (1913-1970)
-
Well I tried your code but I get the error "ADODB.Connection error '800a0bb9' Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. /Includes/paging.asp, line 12 " I couldnt' get the tostring to work, so i took it out as well as the error checking, as they will not be blank fields (we are checking the primary after all) So the code ended up
dim sTotalpages SQLQuery = "SELECT COUNT(ID) FROM Log" sTotalpages = OBJdbConnection.ExecuteScalar(SQLQuery) 'Line 12 - the error point Response.Write(sTotalpages)I'm rather confused to why I am having problems, i mean it looks easy enough, there is obviously something I am doing wrong. Well I'll go back to my old method for now, and have a break, and come back to it later --- Peter MHere this works:
Dim cn As New System.Data.SqlClient.SqlConnection(_connectString)
Dim value As Object
Dim cmd As New System.Data.SqlClient.SqlCommand("Select count(SampleID) from tblSamplesSample")
cmd.Connection = cn
cn.Open()
value = cmd.ExecuteScalar
Response.Write(value)Change the select statement and add your connect string. Michael I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious. Vince Lombardi (1913-1970)
-
I wish to count the number of records in my table and then display the number on the screen So i done:
SQLQuery = "SELECT COUNT(*) FROM Log" Set RS = OBJdbConnection.Execute(SQLQuery) Response.Write(RS.RecordCount)But i'm getting into a muddle on how to do it. Can someone please show me the light.!! --- Peter M -
Definitely give it a try. It probably has bugs since it was written on-the-fly but looping through the recordset is definitely an insufficient way of doing it. ExecuteScalar with the count being on the primary key is probably the most efficient way of obtaining the count. Michael I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious. Vince Lombardi (1913-1970)
Tried again, but could not get it to work, I had a feeling that the code you submitted was in ASP.NET, which my friend David Wulff confirmed for me. My host (oneandone) does not allow me to use .NET, which explains things, i've managed to get things to work now with the following code, incase anyone else reads this in the future
set rs = OBJdbConnection.execute("SELECT count(ID) as total from Log") sTotalpages = rs("total")Thanks for your time, its appeciated! --- Peter M -
Could not get this to work with executeQuery, as mentioned with Dr X, this might be because the code is ADO.NET? Your all using space age technology compared to me, i'm not on the .NET train yet :eek: Thanks for your time otherwise --- Peter M
-
I wish to count the number of records in my table and then display the number on the screen So i done:
SQLQuery = "SELECT COUNT(*) FROM Log" Set RS = OBJdbConnection.Execute(SQLQuery) Response.Write(RS.RecordCount)But i'm getting into a muddle on how to do it. Can someone please show me the light.!! --- Peter MOK, now i have working code to count all the records thanks for all your suggestions, that’s all fine now. The other thing that I wanted to clear up was this, and i'll do my best at explaining! I wanted to select all the data in a table, then sort in descending order (the primary being a Autonumber called ID), and then select a range of records for paging. Now i can do the paging fine, the the first record is shown first, and wanted everything to be in reverse order. So my first bit of code works, in ascending order. (ignore the maths bit its used to select ie records 1-5 for page 1 and 6-10 for page 2 ...)
SQLQuery = "SELECT * FROM Log WHERE Log.ID BETWEEN " & (((sCurrentpage-1)*5)+1) & " AND " & (sCurrentpage*5)This works fine, but I wanted it to have all the records in reverse order, so I thought ah ha, i'll just put in order by:SQLQuery = "SELECT * FROM Log ORDER BY ID DESC WHERE Log.ID BETWEEN " & (((sCurrentpage-1)*5)+1) & " AND " & (sCurrentpage*5)But this doesn't work. If I were to putORDER BY ID DESCat the end of the SQL statement it will work, but it will only reverse order of the selected records. But i wanted to reverse order of everything and then select..understand the problem?? Suggestions on a post card.. Please note, I am not able to use anything to do with .NET, so i need .NETless code :-O ..cheers! --- Peter M -
OK, now i have working code to count all the records thanks for all your suggestions, that’s all fine now. The other thing that I wanted to clear up was this, and i'll do my best at explaining! I wanted to select all the data in a table, then sort in descending order (the primary being a Autonumber called ID), and then select a range of records for paging. Now i can do the paging fine, the the first record is shown first, and wanted everything to be in reverse order. So my first bit of code works, in ascending order. (ignore the maths bit its used to select ie records 1-5 for page 1 and 6-10 for page 2 ...)
SQLQuery = "SELECT * FROM Log WHERE Log.ID BETWEEN " & (((sCurrentpage-1)*5)+1) & " AND " & (sCurrentpage*5)This works fine, but I wanted it to have all the records in reverse order, so I thought ah ha, i'll just put in order by:SQLQuery = "SELECT * FROM Log ORDER BY ID DESC WHERE Log.ID BETWEEN " & (((sCurrentpage-1)*5)+1) & " AND " & (sCurrentpage*5)But this doesn't work. If I were to putORDER BY ID DESCat the end of the SQL statement it will work, but it will only reverse order of the selected records. But i wanted to reverse order of everything and then select..understand the problem?? Suggestions on a post card.. Please note, I am not able to use anything to do with .NET, so i need .NETless code :-O ..cheers! --- Peter MOrder by goes at the end of the statement.
SQLQuery = "SELECT * FROM Log WHERE Log.ID BETWEEN " & (((sCurrentpage-1)*5)+1) & " AND " & (sCurrentpage*5) & " ORDER BY ID DESC"
Michael I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious. Vince Lombardi (1913-1970)