| Author |
 Topic  |
|
|
snaayk
Senior Member
USA
1061 Posts |
 Posted - 25 April 2002 : 14:24:00
|
I got two problems that are driving me nutz.
I have an if statement that should display something if rs.eof. However, its not doing it. for some reason its returning an empty record, any idea why this would happend? Its summing 3 or 4 columns with no group by, however if I group by then it will return no records.
The other problem is a date, I use a date function like snitz, 03012002. Now I have a where clause for between dates. If I put days and months that are not within the period where i have records for (I only have 5 dates in there 3/27,3/28,3/29,3/30.3/31) I have no problem. But if I put dates between the period that I do then regardless of the year portion it will return a record.
in other words:
I have 50 records between these dates: 03272002-03312002
03012002-03202002 = no records found
03012002-03312002 = records found
03012001-03202001 = no records found
03012001-03312001 = RECORDS FOUND
Why? does that make any sense, is something wrong in the db setup?
Hope someone can help, because I've tried everything I can think of to no avail. 
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 25 April 2002 : 17:07:51
|
It's kinda hard to tell what you are doing without the database configuration and the sql statements you are using.
For the date problem, it's my guess that you are storing the dates as text. Depending on what your sql statement is, you most likely are comparing text values, which is not what you want to do. You want to compare date values.
Post your sql statement and the database fields/types.
Nikkol |
 |
|
|
work mule
Senior Member
USA
1358 Posts |
Posted - 25 April 2002 : 18:16:49
|
I know this probably doesn't help, but...
Sometimes...just sometimes, depending on how you open a recordset, it's closed before you can even check for .eof. The only way to check is the following.
If (objRS.State <> 0) Then
'is open
Else
'is closed
End If
|
|
|
|
snaayk
Senior Member
USA
1061 Posts |
Posted - 26 April 2002 : 08:25:01
|
Set rs=con.Execute ("exec perf_idist_c '"&sdate&"' , '"&edate&"' ")
if rs.EOF then
Response.Write "<br> <br> No Data Found, Check your input and try again<br> <br> If you continue to have trouble <a href=""support.asp"">contact support here</a><br> <br> <br> <br> <br> <br> "
else
set thrs = rs("thrs")
set tot = rs("tot")
set ctasks = rs("ctasks")
set ttasks = rs("ttasks")
Set rs1 = con.Execute ("exec zhrs_dist '"&sdate&"' , '"&edate&"' ")
set zhrs = rs1("zhrs")
%>
hello
<%
rs.Close
set rs = Nothing
rs1.Close
set rs1 = Nothing
end if %>
The SQL statement for the stored proc:
SELECT Sum(dailyactivity.thrs) AS thrs, Sum(dailyactivity.tot) AS tot, Sum(dailyactivity.ttasks) AS ttasks, Sum(dailyactivity.ctasks) AS ctasks
FROM dailyactivity
WHERE (((dailyactivity.date) Between [d1] And [e2]));
The funny thing is if I group by the date in the above statement I will get no records but as is I get back an empty record. I have 5 other similar stored procs but since they have group by staements they work fine.
DATE:
Nikkol, you're right, I have the date as a text. I thought that it would work. Now I have to change the pages and db....pain in the butt.
|
|
|
|
snaayk
Senior Member
USA
1061 Posts |
Posted - 26 April 2002 : 09:03:57
|
I fixed the date function. I just changed form text to date. I thought I would have to do a bunch of editing but I only had to add "-" between the dates.
Now if I could figure out this eof thing.....
|
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 26 April 2002 : 09:12:19
|
Gosh snaayk...I don't know anything about stored procedures and hence, a lot of that code looks like gibberish to me!
Nikkol |
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 26 April 2002 : 09:14:34
|
BUT ... let me take a stab ...
the dates d1 and e2 ... where are they coming from? what format are they in?
Nikkol |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 26 April 2002 : 10:12:12
|
quote:
BUT ... let me take a stab ...
the dates d1 and e2 ... where are they coming from? what format are they in?
Nikkol
Nikkol, this is just an Access query with two parameters being executed from an ASP Script. The parameters values are passed in the command line: exec zhrs_dist '"&sdate&"' , '"&edate&"'
exec ( to force execution - like command.Execute), query name,parameter1, parameter2
Tony,
Maybe that happens when you use a totals query without group by and you have no records. Maybe you'll need to add another test in case EOF is not true, to check for the empty record...
I can't help more because I've never did anything similar.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
snaayk
Senior Member
USA
1061 Posts |
Posted - 26 April 2002 : 11:02:35
|
quote:
Maybe you'll need to add another test in case EOF is not true, to check for the empty record...
hmm. sounds like that should work. LEt me try and see if it works.
|
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 26 April 2002 : 11:28:23
|
Yeah, but the problem as I understand it is that records are returned correctly for the between dates if you query for the year 2002, but if you query for 2001, records are also returned even though all of the records are in the year 2002.
NEVERMIND - I got the two problems mixed up.
Nikkol
Edited by - Nikkol on 26 April 2002 11:29:37 |
|
|
|
work mule
Senior Member
USA
1358 Posts |
Posted - 26 April 2002 : 11:58:54
|
It's probably what Nikkol said...
quote:
I have 50 records between these dates: 03272002-03312002
03012002-03202002 = no records found
03012002-03312002 = records found
03012001-03202001 = no records found
03012001-03312001 = RECORDS FOUND
Look at this, not from a date perspective, but as numbers.
The last one where records are found, numerically speaking...
MIN - 03012001
MAX - 03312001
If you treat these as numbers, then you will have records which fall within that range.
You're records 03272002-03312002 -> 03272002 is between the MIN and MAX values of your query.
Okay...but you said you fixed something...
What did you change? Did you change the field type in the database to a date type or is it still a varchar/text type and the data was modified to include '-' characters? If so, you really haven't fixed anything with this query.
The reason why Snitz works (even though it's stored as character data) is because of the format:
20010427101010 --> yyyymmddhhmmss
So when a comparison is done, SQL treats as numeric and it'll be in order.
You're format is:
03202002 --> mmddyyyy
So when ordering numerically, Jan records will always come before March and Apr, regardless of the year.
01xxxxxx - Jan records
02xxxxxx - Feb records
03xxxxxx - Mar records
04xxxxxx - Apr records
(does that make sense?)
The good news is if you want to stick with your format, it's can be done. It'll require more SQL to be written when doing comparisons, but not impossible. Included below is an example of how you'd work with your format. Copy and paste into Query Analyzer and you'll see how it works.
------------------- START OF SQL -----------------------
DECLARE @TESTDATE as varchar(8)
SET @TESTDATE = '03302002'
/* These accomplish the same thing */
SELECT CAST((SUBSTRING(@TESTDATE,1,2)+'/'+SUBSTRING (@TESTDATE,3,2)+'/'+SUBSTRING (@TESTDATE,5,4)) as datetime)
SELECT CONVERT(datetime,(SUBSTRING(@TESTDATE,1,2)+'/'+SUBSTRING (@TESTDATE,3,2)+'/'+SUBSTRING (@TESTDATE,5,4)))
/*
Allows you to specify what the output will look like
What's important to note is that when you specify the style for dates,
it'll only work if the original is already in a datetime format.
The original data is character data, has to massaged into a date format.
This is the reason for the substrings.
Once we have a datetime data, we can then convert that to any style we want
by using CONVERT.
For a list of styles, check the SQL Server Books Online and lookup up CONVERT.
*/
/* mon dd yyyy hh:miAM (or PM) */
SELECT CONVERT(varchar(20),CAST((SUBSTRING(@TESTDATE,1,2)+'/'+SUBSTRING (@TESTDATE,3,2)+'/'+SUBSTRING (@TESTDATE,5,4)) as datetime),100)
/* dd mon yyyy hh:mm:ss:mmm(24h) */
SELECT CONVERT(varchar(20),CAST((SUBSTRING(@TESTDATE,1,2)+'/'+SUBSTRING (@TESTDATE,3,2)+'/'+SUBSTRING (@TESTDATE,5,4)) as datetime),113)
/* yyyy-mm-dd hh:mi:ss(24h) */
SELECT CONVERT(varchar(20),CAST((SUBSTRING(@TESTDATE,1,2)+'/'+SUBSTRING (@TESTDATE,3,2)+'/'+SUBSTRING (@TESTDATE,5,4)) as datetime),121)
------------------- END OF SQL -----------------------
Now, back to something I had mentioned earlier. In case some of you were wondering what the heck I was talking about, here's an explanation.
If (objRS.State <> 0) Then
'is open
Else
'is closed
End If
Sometimes when using stored procs, it's possible that no recordset was created at all. So objRS.EOF won't work, in fact, depending on how it's written, it would error out because the objRS object was closed right away.
This occurs if in the stored proc, you're checking for a value before executing a SQL statement. If that value doesn't exist or doesn't meet you're criteria and you do nothing, then you'll have a case where you're recordset object (if doing a select) will close right away.
Checking the State of the recordset object, you can check to see if it's still open or not. That's what that snippet of code is doing.
Just an additional piece of information...
State Property
Indicates for all applicable objects whether the state of the object is open or closed.
Indicates for all applicable objects executing an asynchronous method, whether the current state of the object is connecting, executing, or retrieving.
http://msdn.microsoft.com/library/en-us/ado270/htm/mdprostate.asp
ObjectStateEnum
Specifies whether an object is open or closed, connecting to a data source, executing a command, or retrieving data.
Constant Value Description
adStateClosed 0 Indicates that the object is closed.
adStateOpen 1 Indicates that the object is open.
adStateConnecting 2 Indicates that the object is connecting.
adStateExecuting 4 Indicates that the object is executing a command.
adStateFetching 8 Indicates that the rows of the object are being retrieved.
http://msdn.microsoft.com/library/en-us/ado270/htm/mdcstobjectstateenum.asp
Probably not likely you'll ever encounter this, but store this away in your head and maybe some day it'll save you some time troubleshooting. 
Edited by - work mule on 26 April 2002 14:36:01 |
|
|
|
snaayk
Senior Member
USA
1061 Posts |
Posted - 30 April 2002 : 09:05:56
|
Wow, great post workmule. I'll have to save that one.
Actually, I changed the field type to date and it works now. I see what you were saying about the numbers, 01 will always come before 02 regardless of the year, DUH, should have seen that.
The empty recordset might be suffering of what you posted. I have to check and see but that will probably be whats happening.
thx
|
|
|
| |
Topic |
|
|
|
Topic Locked
