July 23, 2008 at 1:18 am
Hi there,
I am running a ASP/IIS website with SQL2005 as database.
Is it possible to determine how many rows a recordset has?
For example:
SET rs = conn.Execute ("SELECT name FROM customers WHERE age<30")
count = rs.RecordCount
Response.Write "We have " & count & " customers younger than 30, this is the list :"
While Not rs.Eof
Response.Write rs.Fields("name").Value "
rs.MoveNext
Wend
Obviously the count = rs.RecordCount doesn't work in this example. What should I do to get it working?
Thanks,
Ray
July 23, 2008 at 1:36 am
Do it like this:
SET rs = conn.Execute ("SELECT name, Count(*) Over(Partition By 1) as [Count] FROM customers WHERE age<30")
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 23, 2008 at 2:05 am
Hi,
Thanks for your reply.
When I try your solution in the Server Manager Studio I get a popup with this message:
query:
SELECT name,Count(*) OVER (Partition BY 1) AS [Count] FROM customers
message:
The OVER SQL construct or statement is not supported.
Strangely enough, I do get the results (with rowcount) I want:
name count
-------------------------
John 3
Peter 3
Mary 3
Will try now from my ASP code...
July 23, 2008 at 6:14 am
Just a guess, you're running against a SQL Server 2000 instance. This is the 2005 forum and the solution offered was one that works there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 23, 2008 at 7:17 am
Nope... it's SQL2005.... SQL Server 9.0.1399 (free Express Edition) to be exact.
The behaviour is pretty strange: the first time I run the query I get the message. After that, I can run it as many times as I like again without the message.
After I change the query slightly (for example, add another row to the resultset), again only the first time I run the query I get the message.
Hmmmmpfff
July 23, 2008 at 7:45 am
It's ADO.net or the ADO.net provider that is having the problem. Two ways around it that I can think of,:
1) Write a View or Stored Proc, then call that, or...
2) See if there is a "Passthrough" setting or flag in ADO.net. I know that there was one in ADO, RDO, DAO, and ODBC, but I've never checked ADO.net for it
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 24, 2008 at 12:53 am
Hi there,
Solution 1) does the trick; as a stored procedure the error doesn't occur.
Thanks to everyone for the help,
Ray
July 24, 2008 at 6:05 am
Great! Glad it worked out.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 25, 2008 at 4:10 pm
For some objects, you have to use methods like a MoveLast before total counts are updated, after moving to the last record in the set a count properity is usually correct. There is typically a method like MoveFirst that restores the obejct to the starting point for the next statement.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply