473,322 Members | 1,480 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

RecordSet not returning Rows

Hi All,

The following query returns one row when run in Query Analyzer:

DECLARE @IntCnt AS integer
SET @IntCnt = 0
SELECT @IntCnt = @IntCnt + COUNT(DISTINCT
B.ServiceID)
FROM dbo.TblServices A INNER JOIN
dbo.TblOccurences B
ON A.ServiceID = B.ServiceID
WHERE ({ fn LCASE(A.Prim_Sec) } =
'primary') AND (B.[Sort Date] > GETDATE()) AND (A.Assessment = 1)
GROUP BY B.ServiceID
SELECT
'Assessment' AS Whatever, @IntCnt AS Totals

How ever, if I run the same query in Enterprise Manager, or within an
ASP script, no rows are returned.

Any ideas?

Thanks,
Simon.

Apr 6 '06 #1
1 1234
we*********@poole.gov.uk wrote:
Hi All,

The following query returns one row when run in Query Analyzer:

DECLARE @IntCnt AS integer
SET @IntCnt = 0
SELECT @IntCnt = @IntCnt + COUNT(DISTINCT
B.ServiceID)
FROM dbo.TblServices A INNER JOIN
dbo.TblOccurences B
ON A.ServiceID = B.ServiceID
WHERE ({ fn LCASE(A.Prim_Sec) } = 'primary')
Is your SQL Server set up with a case-sensitive collation? By default, SQL
Server is case-insensitive so you do not have to bother manipulating the
case like this. Also, T-SQL has built-in string functions to enable you to
modify the case without resorting to the ODBC functions as you have done
here.

I've never tried using ODBC functions like this so I don't know I don't know
if that is causing your problem. The best way to find out is to change this
to

LOWER(A.Prim_Sec) = 'primary'

and see if it makes a difference.
AND (B.[Sort Date] > GETDATE())
AND (A.Assessment = 1)
GROUP BY B.ServiceID
SELECT
'Assessment' AS Whatever, @IntCnt AS Totals

How ever, if I run the same query in Enterprise Manager, or within an
EM should never be used to run queries. Stick with QA.
ASP script, no rows are returned.

Any ideas?

Show us the code.
Really, the batch you've shown above should be encapsulated in a stored
procedure, at which point it is very easy to use the technique described
here:
http://tinyurl.com/jyy0

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Apr 6 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Eli Sidwell | last post by:
Trying to return a Recordset to an ASP and the Recordset is empty. The StorredProc works in the query analyzer and it even works from a quick VB app that I wrote to test it. The storedproc that...
1
by: Andrew Banks | last post by:
I've been handed an ASP project and having worked on .NET for the past year I have completely forgotten what to do. I am querying a SQL server DB and returning XML. The following code seems to...
8
by: mgm | last post by:
hello, I have a query that is supposed to return only 1 record, however I recently found that because of an error in the database it can return more than 1. So what I need to do is capture if it...
6
by: Alan Silver | last post by:
Hello, I have an ASP that takes a connection string and SQL statement in the querystring and is supposed to return the XML representation of the recordset to the Response stream (don't worry,...
3
by: Steve | last post by:
I have implemented the Stored Procedure "RowCount" method of controlling paged recordsets in ASP as shown in this page http://www.aspfaq.com/show.asp?id=2120. I have had to make heavy...
2
by: Sreedhar Vankayala | last post by:
Hi, I have a table IV00108wc which contains several thousand records in whcih ITEMNMBR is the primary key. I have a com+ object returning recordset which has ITEMNMBR and another details...
36
by: kjvt | last post by:
Based on a prior posting, I've written a function to convert a recordset to a dataview. The first call to the function for a given recordset works perfectly, but the second call always returns a...
1
by: vinodkus | last post by:
I M BEGINNER IN ASP I WANT TO RETURN TOTAL RECORDS FROM A TABLE. THERE ARE TWO FORMS CLASS1.ASP AND CLASS2.ASP THROUGH FIRST FORM I JUST POST THE NAME OF TABLE SO I M WRITING THE CODE OF...
9
ADezii
by: ADezii | last post by:
One question which pops up frequently here at TheScripts is: 'How do I retrieve data from a Recordset once I've created it?' One very efficient, and not that often used approach, is the GetRows()...
3
ADezii
by: ADezii | last post by:
Last Tip, we demonstrated the technique for retrieving data from a DAO Recordset, and placing it into a 2-dimensional Array using the GetRows() Method. This week, we will cover the same exact Method...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.