• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

Recordcount

What I am trying to do is get a recordcount of a query but avoiding the limit by 100.This is what I thought of but its returning a -1 and I cannot understand why.

ok what I did is did a same query for example

sql = "select * from table1 where match(column1) against('products') order by id limit by 100"

sql2 = "select * from table1 where match(column1) against('products') order by id"


RS.open sql,conn,3,1
RS2.open sql2,conn,3,1

and then where I want the count to be is repsone.write"RS2.recordcount"

this will do the same query but without the limit to get the true couint but for some reason I get a -1.Why would that be?
0
thenone
Asked:
thenone
  • 32
  • 14
  • 4
  • +3
2 Solutions
 
thenoneAuthor Commented:
ok I have read all three and I have it locked as static.Could it be that I am running the same query twice and its getting confused.By the way kev did you get a chance to look at the rss function? Thanks.
0
 
thenoneAuthor Commented:
also it is not closed.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
thenoneAuthor Commented:
maybe its because to get the true count I have to close the first recordset first?
0
 
kevp75Commented:
i wouldn't think so, because they are 2 seperate quries.

:)
i'm having trouble with a regex funtion to find the code for the style sheet and replacing it with nothing
0
 
thenoneAuthor Commented:
ok well its weird becasue the second recordset is exactly the same as the first.When I do a rs.recordcount on the first it shows the records but when i do it on the second it doesn't.
0
 
thenoneAuthor Commented:
maybe its the way im locking it im doing static with read only
0
 
RiverLightDesignCommented:
open it this way,

RS.open sql,conn,3,3

response.write(rs.recordcount)
0
 
thenoneAuthor Commented:
Didn't work. I wonder if its becasue I only have one index of full text in the table.I wonder if I created two instances that would work.
0
 
thenoneAuthor Commented:
by the way i am using mysql 5.0
0
 
thenoneAuthor Commented:
Ive tried locking the recordsets and tried not locking the recordsets either way it doesn't work.
0
 
thenoneAuthor Commented:
What I am trying to do is get a total recordcount of the query of records returned even though I have a limit of 100.

I have tried sub queries etc. an example select * from table1(select count(*) from table1) as thecount from table1 where <<<<<< etc.

that query just returns the total records in the table.

I did read you can use count(query) something like that????

I am really puzzled....
0
 
JohnModigCommented:
Hi thenone.
First of all - are you getting anything out of the table using your original sql? :

 sql = "select * from table1 where match(column1) against('products') order by id limit by 100"

Second, is counting records all you want to do with this? Then using the sql count function would be the way to go. Just pick a field to count, something like:

 sql = "select count(column1) as myCount from table1"

...and then later using: myRecordset("myCount") to retrieve the value. There are also other methods to use depending in what you want. However, first we have to make sure that your recordset works without the count function. So please answer my first question.

Regards,
John
0
 
Anthony PerkinsCommented:
A record count of -1 is just the provider's way of saying that it does not ssupport that property for that particular type of recordset.  In general it is best to avoid its use always.  However, if you insist try setting the CursorLocation to adUseClient, prior to the Open as in:

rs.CursorLocation = 3 'adUseClient
rs.Open
0
 
thenoneAuthor Commented:
Hi thenone.
First of all - are you getting anything out of the table using your original sql? :

 sql = "select * from table1 where match(column1) against('products') order by id limit by 100"

Second, is counting records all you want to do with this? Then using the sql count function would be the way to go. Just pick a field to count, something like:

 sql = "select count(column1) as myCount from table1"

...and then later using: myRecordset("myCount") to retrieve the value. There are also other methods to use depending in what you want. However, first we have to make sure that your recordset works without the count function. So please answer my first question.

Regards,
John<<<<<< If I do this I just get the total count for that column,I would like to get the total count of records found...

rs.CursorLocation = 3 'adUseClient<<<< I have my cursor location set at this..

As far as select * I did that last night just to make sure I have the proper connections at it works fine.

So how would I do the sql count to retrieve the records found in the original query.This sounds to me the best way and also the easiest.
0
 
kevp75Commented:
sql = "select count(column1) as myCount from table1"


this will give the total count of all records in that table, regardless of what column you choose, a recordset is the entire row(unless you specify the columns, in which case it will only be those columns of the rows)

you can also do this
sql = "select count(*) as myCount from table1"

this just means that you are counting all columns of all rows, whatever you put in between the parens will not matter as it will still only give the count of the rows
0
 
thenoneAuthor Commented:
Good morning kev, So how would I do it to imitate recordsets found? Or is this possible.
0
 
WMIFCommented:
you can use a subquery and then you access the count like a regular column.

select * ,
(select count(*) from table1 where match(column1) against('products')) as reccount
from table1
where match(column1) against('products')
order by id
limit by 100

RS.open sql,conn,3,1


then you get the record count like this:

response.write rs("reccount")
0
 
thenoneAuthor Commented:
you can use a subquery and then you access the count like a regular column.

select * ,
(select count(*) from table1 where match(column1) against('products')) as reccount
from table1
where match(column1) against('products')
order by id
limit by 100

RS.open sql,conn,3,1


then you get the record count like this:

response.write rs("reccount")

ok with this its getting a count past the 100 limit but its almost as though its multiplying by 10 or something its not accurate.
0
 
Anthony PerkinsCommented:
Even if you do get the right results, you are executing the subquery, select count(*) from table1 where match(column1) against('products')) 100 times in addition to the main query. So wth all due respect to WMIF, may I suggest a better alternative.

Set rs = Server.CreateObject("ADODB.Recordset")
With rs
    .Source = "select count(*) as reccount from table1 where match(column1) against('products'); " & _
          "select * from table1 where match(column1) against('products') order by id limit by 100"
    .ActiveConnection = "Your connection goes here"
    .CursorType = 3      ' adOpenStatic
    .LockType = 1         ' adLockReadOnly
    .Open , , , , 1          ' adCmdText
    Response.Write rs.Fields("reccount").Value & "<br>"
   Set rs = rs.NextRecordset

   ' Rest of your code to manipulate/display the values in the main query

   .Close
End With
Set rs = Nothing
   
0
 
thenoneAuthor Commented:
so what you are suggestion is to have two seperate queires.
0
 
thenoneAuthor Commented:
also I am getting a mysql server error with that query.
0
 
thenoneAuthor Commented:
couldn't I just take reccount and divide it by 10 into a new variable.
0
 
thenoneAuthor Commented:
wmif

multiplies the resulting records by 5.125
0
 
Anthony PerkinsCommented:
>>so what you are suggestion is to have two seperate queires.<<
It is better than 101 queries.  But whatever works for you.

>>also I am getting a mysql server error with that query.<<
I have no idea.  I have never used MySQL.  Until you post your code and the error generated, I cannot even speculate.
0
 
thenoneAuthor Commented:
sql ="Select Distinct max(p1) as p1,p2,id,(select count(*) from products where match(products) against('" & searchstrr & "')) as reccount from products1 where match(products) against ('"

Dim against_str
against_str = ""
for i=lbound(arrKeyWords) to ubound(arrKeyWords)
  sql=sql & " +" & replace(arrKeyWords(i) , "'", "")
  against_str=against_str & " +" & replace(arrKeyWords(i) , "'", "")
next



sql = sql & "' in Boolean mode) group by p2 order by max(id) limit 100"


ok this is my sql statement.
0
 
Anthony PerkinsCommented:
I have no idea.

Good luck.
0
 
thenoneAuthor Commented:
Thanks alot acperkins :)

0
 
JohnModigCommented:
theone,
Are you still getting an error? Or is it working for you? Just to explain my previous post a bit further - counting rows from one column usually works better than counting rows from all columns (*) and is faster. The result will be the same as long as you are counting a column that has no null values in it (id or something) - since you are counting the ROWS of that column = number of records = number of posts. Anyway, for even faster performance you can also do:

 count(1)

This will be even faster, as the database engine will not have to fetch back the data fields. Now, the count function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.

Anyway, give this a try and then post you full sql.
0
 
kevp75Commented:
thenone

the problem at hand is you are still counting a limited recordset.  Take the count out of that part of your query.  We've been telling you this all along.

this part here:
select count(*) from products where match(products) against('" & searchstrr & "')) as reccount from products1 where match(products) against ('"

Dim against_str
against_str = ""
for i=lbound(arrKeyWords) to ubound(arrKeyWords)
  sql=sql & " +" & replace(arrKeyWords(i) , "'", "")
  against_str=against_str & " +" & replace(arrKeyWords(i) , "'", "")
next



sql = sql & "' in Boolean mode) group by p2 order by max(id) limit 100

see the limit 100, you are limiting the entire query to 100 records, thus you will only get a count of 100.  You NEED to do a seperate query to get what you want.  If you use the function I suggested in my other post, it will work for you.

get rid of the COUNT part of your query, but.....add this in (with the function)

strRecCount = getCount(cnn,"products1"," WHERE match(products) against('" & searchstrr & "'))

then to display it all you have to do is response.write(strRecCount)
0
 
thenoneAuthor Commented:
Thanks kev can you post the function again thanks.
0
 
WMIFCommented:
why not just grab it from your other question?
0
 
thenoneAuthor Commented:
ok kev here is the function you proposed and Im still confused on the filter?

Function getCount(cnn,table,filt)
     set connn=createobject("adodb.connection")
          connn.open cnn
               set rc = connn.execute("SELECT Count(*) as cField FROM " & table & filt &"")
               if rc.eof then
                    getCount = 0
               else
                    getCount = rc("cField")
               end if
          connn.close
     set connn=nothing
End Function

0
 
kevp75Commented:
filter = your WHERE part of the query, as I've shown you in both questions
0
 
thenoneAuthor Commented:
so the filter would be WHERE match(products) against('" & searchstrr & "'))
0
 
thenoneAuthor Commented:
Function getCount(cnn,table,filt)
     set connn=createobject("adodb.connection")
          connn.open cnn
               set rc = connn.execute"Select Count(*) as cField from table1 WHERE match(products) against('" & searchstrr & "'))

               if rc.eof then
                    getCount = 0
               else
                    getCount = rc("cField")
               end if
          connn.close
     set connn=nothing
End Function


is this correct? and to use!!
0
 
kevp75Commented:
yes....yes
0
 
thenoneAuthor Commented:
and to use it I just call it like this <%=getCount%> Correct?
0
 
kevp75Commented:
no
use the original function that I posted, without changing it!

and, like I said in that question use it like this:
<%=getCount(connectionString,"tableName","  WHERE match(products) against('" & searchstrr & "'))%>

(which also happens to be posted above...)

if you aren't going to do this, exactly how I have shown you in both questions, then there is no hope.  If this method does not work, and you will not post your entire code, than please stop posting to these questions, as no-one else will be able to help you unless you do.

Please!  For the sake of everyone, READ through both the questions, the answer you need is in both questions, you just need to follow the directions.
0
 
WMIFCommented:
and then delete one since this has becoming an exact duplicate.
0
 
thenoneAuthor Commented:
Function getCount(cnn,table,filt)
     set connn=createobject("adodb.connection")
          connn.open cnn
               set rc = connn.execute("SELECT Count(*) as cField FROM " & table & filt &"")
               if rc.eof then
                    getCount = 0
               else
                    getCount = rc("cField")
               end if
          connn.close
     set connn=nothing
End Function

<%=getCount(connectionString,"tableName","  WHERE match(products) against('" & searchstrr & "'))%>

Just like this!!!! Thanks for your help!!!
0
 
thenoneAuthor Commented:
I will give points after I test it.Thanks kev for your help and also wmif.
0
 
kevp75Commented:
no dude...not like that, you are modifying the function...USE THE ORIGINAL FUNCTION

you even copied it here on this question!

Comment from thenone
Date: 06/06/2006 09:16AM PDT
 Author Comment  


ok kev here is the function you proposed and Im still confused on the filter?

Function getCount(cnn,table,filt)
     set connn=createobject("adodb.connection")
          connn.open cnn
               set rc = connn.execute("SELECT Count(*) as cField FROM " & table & filt &"")
               if rc.eof then
                    getCount = 0
               else
                    getCount = rc("cField")
               end if
          connn.close
     set connn=nothing
End Function

and then use:
<%=getCount(connectionString,"tableName","  WHERE match(products) against('" & searchstrr & "'))%><<<-----right here is the WHERE (the filter)

do you see the filter, (your query...from the WHERE on)?

 you can modify the function, but you would have to modify your response (the displayed count)

try this out:
Function getCount(cnn)
     set connn=createobject("adodb.connection")
          connn.open cnn
               set rc = connn.execute("SELECT Count(*) as cField FROM " & table & filt &"")
               if rc.eof then
                    getCount = 0
               else
                    getCount = rc("cField")
               end if
          connn.close
     set connn=nothing
End Function

<%=getCount(connectionString)%>
0
 
kevp75Commented:
damn tab....
typo:::::

try this instead:
Function getCount(cnn)
     set connn=createobject("adodb.connection")
          connn.open cnn
               set rc = connn.execute"Select Count(*) as cField from table1 WHERE match(products) against('" & searchstrr & "'))

               if rc.eof then
                    getCount = 0
               else
                    getCount = rc("cField")
               end if
          connn.close
     set connn=nothing
End Function

<%=getCount(connectionString)%>
0
 
kevp75Commented:
p.s.  in case you are not doing it already, that function should be at the top of your page under your searchstr

and

<%=getCount(connectionString)%>
shoud go where ever you need to display the number




p.s.s.
if you still don't get it, please post the entire code for the page
0
 
thenoneAuthor Commented:
ok kev <%=getCount(connectionString)%>


connectionstring? Should be?
0
 
thenoneAuthor Commented:
should be cnn or whatever I have named the connection string now I get it!!
0
 
thenoneAuthor Commented:
What I am confused about maybe thats why I was so stubborned is I created two recordsets with similar queries except one didn't have the limit and it didn't work.The only difference that I see the function that it will do is it will only be called once quicker etc.
0
 
kevp75Commented:
correct it supposed to be your connection string...

it's the way you are trying to write your query:

sql ="Select Distinct max(p1) as p1,p2,id,(select count(*) from products where match(products) against('" & searchstrr & "')) as reccount from products1 where match(products) against ('"

Dim against_str
against_str = ""
for i=lbound(arrKeyWords) to ubound(arrKeyWords)
  sql=sql & " +" & replace(arrKeyWords(i) , "'", "")
  against_str=against_str & " +" & replace(arrKeyWords(i) , "'", "")
next

sql = sql & "' in Boolean mode) group by p2 order by max(id) limit 100"


won't work because you are limiting your total execution on the database to 100 records, thus your total record count would be 100
0
 
thenoneAuthor Commented:
I know but what I was initially confused about is I had two recordsets.

example:

sql = "select * from products where match(products) against ('" & searchstrr & "')"

sql2 = "select count(*) as reccount from products where match(products) against ('" & searchstrr & "')"

rs.open cnn,sql,3,3
rs2.open cnn,sql2,3,3

<<<<< and this did not work it returned -1

so hopefully with the function it will work!!!!! I will try tonight.
0
 
kevp75Commented:
<<<<< and this did not work it returned -1      <--the reason for this is in those links in my first post
0
 
thenoneAuthor Commented:
set rc = Connn.execute "Select Count(*) as cField from Table1 WHERE match(products) against('" & searchstrr & "')"



I get expected end of statement
0
 
thenoneAuthor Commented:
ok it works really well thank you except how do I get distinct count(*) of all records returned?
0
 
kevp75Commented:
i believe by doing just that
SELECT DISTINCT COUNT(*) FROM tableName
0
 
kevp75Commented:
ty for the grade
0
 
thenoneAuthor Commented:
no problem you deserved it.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 32
  • 14
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now