Returning row count

[snowboardr]

I have a query which returns X rows, how do i get the number X in asp after running the query but before printing out the rows.

I attempted to do a count statement before hand, but there is no non-aggregate columns to do group by so thus the count is always innacurate. (mysql database)

ps. I did attempt to ask my question in MySQL, but I was sent here ( thread436-1203178 )

sql= "SELECT "_
& "COUNT(post.pid) as PostCount,"_
& "post.ptid,"_
& "post.ptid2,"_
& "post.puid,"_
& "post.psubject,"_
& "post.pbody,"_
& "post.ptype,"_
& "post.preplycount,"_
& "post.ptime,"_
& "post.psticky,"_
& "post.ptimereply,"_
& "user.uid,"_
& "user.uname,"_
& "MATCH(psubject,pbody,uname) AGAINST ('+" & qsearch & "' in boolean mode)" _
& " FROM forumpost as post join users user on user.uid=post.puid WHERE MATCH(psubject,pbody,uname) AGAINST ('" & qsearch & "' in boolean mode) group by user.uid"

 

[DreXor]

objRS.RecordCount

you will need to have an attached recordset ( objrs.open objcon, strSQL, options vs using a set objrs=con.execute(sql) )



DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never inside the 'loop' " - DreX 2005

 

[Tarwn]

Another option is the second argument of the ConnectionObj.Execute method.

 

[damber]

or use getrows and ubound, which has bonus performance benefits.

A smile is worth a thousand kind words. So smile, it's easy!