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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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