Trying To Return Total Record Count with Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trying To Return Total Record Count with Query

Author

Topic

dzach
Starting Member

38 Posts

Posted - 2007-02-26 : 15:50:01

I'm trying to return the total records with my query, but I'm getting the following error:

"Item cannot be found in the collection corresponding to the requested name or ordinal."

Here's my query:


set rsFind = conn.Execute ("Select Count(Incident_ID) as TotalCount, Incident_ID, ProblemDescriptionTrunc, Action_Summary, RootCause, Problem_Solution002, " _
& " AssignedTechnician, DATEADD(s, dbo.TTS_Main.DateClosed,  '1/1/1970') AS DateClosed, DATEADD(s, dbo.TTS_Main.Date_Opened, '1/1/1970') AS DateOpened, AssignedGroup From tts_main Where ProblemDescriptionTrunc LIKE '%" & prob & "%' And Last_Name LIKE '%" & l_name & "%' " _
& " AND AssignedTechnician LIKE '%" & assigned_tech & "%' And Incident_ID LIKE '%" & ticketnum & "%' AND assignedgroup LIKE '%" & assigned_group & "%' " _
& " Order By DateClosed DESC ")

<%response.write rsfind("TotalCount")%>

Thanks for any help!
Dale

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-26 : 15:53:18

Does the select statement return the correct result set when you run it in Query Analyzer or Management Studio?

Tara Kizer

sshelper
Posting Yak Master

216 Posts

Posted - 2007-02-26 : 15:55:56

Looking at your code, I believe you are missing a GROUP BY clause in your SELECT statement because you are doing a COUNT with other columns. Try adding the GROUP BY clause and test the query in Query Analyzer and see if this solves your problem.

SQL Server Helper
http://www.sql-server-helper.com

dzach
Starting Member

38 Posts

Posted - 2007-02-26 : 16:03:39

I'm still learning ASP, but aren't GROUP BY and ORDER BY mutually exclusive?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-26 : 16:05:55

quote:
Originally posted by dzach

I'm still learning ASP, but aren't GROUP BY and ORDER BY mutually exclusive?

No. And those aren't ASP commands. They are T-SQL, which is the SQL Server language.

Tara Kizer

dzach
Starting Member

38 Posts

Posted - 2007-02-26 : 16:16:52

Adding Group By returns the same error. And you're right, it's SQL not ASP. I'm such an idiot.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-26 : 16:23:19

So run the query in Query Analyzer rather than through your application code. You need to the query working and returning the correct result set before ever attempting the query through your application.

Tara Kizer

dzach
Starting Member

38 Posts

Posted - 2007-02-26 : 16:40:51

How ironic that two different SQL/ASP forums can yield completely different results to the same question? Instead of the condescening attitude that's prevalent here, the other forum was extremely helpful, pointed out the errors and bam, it was fixed. Isn't that weird?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-26 : 16:53:47

Unbelievable! I don't see anything that was a "condescening attitude". I'll let other members point them out though if there were any.

But I'm glad your problem is fixed.

Next time when troubleshooting queries, you should always get the code working in Query Analyzer first. Then at least you know the problem is not with the T-SQL code but rather with the ASP code. You have to realize that this is a SQL Server site, so we will try to help with the T-SQL side of things. If your problem was with the ASP code, then I can see why an ASP forum was able to help you out.

Tara Kizer

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-02-26 : 21:32:52

Shh! Don't mention the SQL injection or use of varchars instead of numbers, or the fact it won't be able to use any indices or.....ooops. Don't want to be condecending.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 01:21:27

I don't understand where you got the soloution you needed.

Where was it?
[url]http://p2p.wrox.com/topic.asp?TOPIC_ID=52315[/url]
[url]http://p2p.wrox.com/topic.asp?TOPIC_ID=52322[/url]
[url]http://www.codingforums.com/archive/index.php?t-100702.html[/url]
[url]http://www.thescripts.com/forum/thread564343.html[/url]
[url]http://66.102.9.104/search?q=cache:8AGLgsCHP18J:aspalliance.com/groups/microsoft_public_inetserver_asp_general/ng-714164_Disappearing_Column_Data.aspx+Action_Summary+AssignedTechnician&hl=en&ct=clnk&cd=5[/url]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources