Simple query, ordered by dDateField desc, cCharField asc, nNumericField asc.
Irrespective of whether I have the "ASC" keywords in there, it's behaving as
though the query is specifying DESC on all three order fields. This seems
pretty basic, but it doesn't work in either IIS6 or IIS7. Is there a bug
related to doing this kind of Order clause?
Help would be appreciated... :) Thanks!
Steve
Steve Bottoms wrote:
> Simple query, ordered by dDateField desc, cCharField asc,
> nNumericField asc. Irrespective of whether I have the "ASC" keywords
> in there, it's behaving as though the query is specifying DESC on all
> three order fields. This seems pretty basic, but it doesn't work in
> either IIS6 or IIS7. Is there a bug related to doing this kind of
> Order clause?
Can't help you if you don't help us. For starters, what database type and
version are you using?
I suspect you are going to need to provide a repro script for this,
providing sample data, table schema and asp code that reproduces the
problem.
--
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"
Quite right: long day, frustrating problem.
Database= Access 2k3
Script = Classic ASP
Server = Windows 2k3 running IIS6 -- and --
Server = Windows Vista Business running IIS7
Code:
strQuery = "select
bLastUpdate,bBody,bNumber,bTitle,bURL,bUpdates,bFiscalNotes, id from Bills
where bStatus='Active' and bLastUpdate is not null order by bLastUpdate
desc,bBody asc,bNumber asc"
set objRS = objConnection.Execute(strQuery)
Fields:
bLastUpdate = smalldatetime (indexed, dupes OK)
bBody = string(5) (indexed, dupes OK)
bNumber = integer (non-negative) (indexed, dupes OK)
Actual results like this:
3/1/07....CD12
3/1/07....CC12
3/1/07....CB12
2/28/07....BB13
2/28/07....AA12
2/27/07....AA12
2/27/07....AA10
Results should be something like this:
3/1/07....CB12
3/1/07....CC12
3/1/07....CD12
2/28/07....AA12
2/28/07....BB13
2/27/07....AA10
2/27/07....AA12
Basically, I'm trying to sort by the date field DESCENDING, and within each
unique day sort on bBody and bNumber ASCENDING. Tried doing a string
conversion on the numeric field and adding (concatenating) it to the string
field; no effect. Tried using CAST and CONVERT (transact sql functions);
don't work with Access apparently. Doesn't matter whether I use the ASC
modifier or not on the second and third field names.
Seems so cut and dry; similar queries work perfectly with SQL Server, so I'm
wondering if it's an Access driver bug. Any suggestions welcome.
Steve
"Bob Barrows [MVP]"
news:%23N2iXt1aHHA.2296@TK2MSFTNGP03.phx.gbl...
> Can't help you if you don't help us. For starters, what database type and
> version are you using?
> I suspect you are going to need to provide a repro script for this,
> providing sample data, table schema and asp code that reproduces the
> problem.
>
Steve Bottoms wrote:
> Quite right: long day, frustrating problem.
>
> Database= Access 2k3
> Script = Classic ASP
> Server = Windows 2k3 running IIS6 -- and --
> Server = Windows Vista Business running IIS7
>
> Code:
> strQuery = "select
> bLastUpdate,bBody,bNumber,bTitle,bURL,bUpdates,bFiscalNotes, id from
> Bills where bStatus='Active' and bLastUpdate is not null order by
> bLastUpdate desc,bBody asc,bNumber asc"
> set objRS = objConnection.Execute(strQuery)
>
> Fields:
> bLastUpdate = smalldatetime (indexed, dupes OK)
> bBody = string(5) (indexed, dupes OK)
> bNumber = integer (non-negative) (indexed, dupes OK)
>
> Actual results like this:
> 3/1/07....CD12
Is CD12 in the bBody field? Or is CD in bBody and 12 in bNumber?
> 3/1/07....CC12
> 3/1/07....CB12
> 2/28/07....BB13
> 2/28/07....AA12
> 2/27/07....AA12
> 2/27/07....AA10
>
> Results should be something like this:
> 3/1/07....CB12
> 3/1/07....CC12
> 3/1/07....CD12
> 2/28/07....AA12
> 2/28/07....BB13
> 2/27/07....AA10
> 2/27/07....AA12
>
> Basically, I'm trying to sort by the date field DESCENDING, and
> within each unique day sort on bBody and bNumber ASCENDING. Tried
> doing a string conversion on the numeric field and adding
> (concatenating) it to the string field; no effect. Tried using CAST
> and CONVERT (transact sql functions); don't work with Access
> apparently.
Nope - you have to use VBA functions (CStr, etc.) - this is one of the
things that makes ti difficult to port between Access and SQL Server.
> Doesn't matter whether I use the ASC modifier or not on
> the second and third field names.
> Seems so cut and dry; similar queries work perfectly with SQL Server,
> so I'm wondering if it's an Access driver bug. Any suggestions
> welcome.
When you run the same query in the Access environment (Access Query
Builder), do you get the correct results? If so, we need to investigate the
ASP end of things. I will need to set up a test database and try to
reproduce your result. It's been a long time since I worked with Access, but
I recall that the the order by modifiers always worked correctly for me.
--
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"
Bob, thanks for responding!
Well, I did a little more digging. The query performs the exact same way in
Access 2k7's Query Builder: incorrect sort order. Now, the date field in
question contains a DATETIME value, and not JUST a date value. When I
replaced all of the DATETIME values with just DATE values, the query started
working correctly (Access and ASP)!
It looks like a bug to me: use DATETIME values, and Order By doesn't work
correctly. Use a DATE value, and the Order By works correctly!
Curious, no? Thanks!
Steve
"Bob Barrows [MVP]"
news:OoyKKg6aHHA.4312@TK2MSFTNGP05.phx.gbl...
> When you run the same query in the Access environment (Access Query
> Builder), do you get the correct results? If so, we need to investigate
> the ASP end of things. I will need to set up a test database and try to
> reproduce your result. It's been a long time since I worked with Access,
> but I recall that the the order by modifiers always worked correctly for
> me.
Just as in SQL Server, Access Date/Time fields store both time and date,
regardless of what has been entered. Jet stores these values as Double
numbers, with the whole number portion representing the number of days
since the seed date, and the decimal representing the time of day (.5 =
noon)
Are you sure the time values are not corresponding with the character
and numeric entries to make it appear that they were being sorted in
descending order? For example, with your actual results:
3/1/0719:50....CD12
3/1/0713:30....CC12
3/1/07 7:30 ....CB12
2/28/07 23:00....BB13
2/28/07 8:30....AA12
2/27/07 16:25....AA12
2/27/071:30....AA10
This is the correct sort order.
Steve Bottoms wrote:
> Bob, thanks for responding!
>
> Well, I did a little more digging. The query performs the exact same
> way in Access 2k7's Query Builder: incorrect sort order. Now, the
> date field in question contains a DATETIME value, and not JUST a date
> value. When I replaced all of the DATETIME values with just DATE
> values, the query started working correctly (Access and ASP)!
>
> It looks like a bug to me: use DATETIME values, and Order By doesn't
> work correctly. Use a DATE value, and the Order By works correctly!
>
> Curious, no? Thanks!
> Steve
>
>
> "Bob Barrows [MVP]"
> news:OoyKKg6aHHA.4312@TK2MSFTNGP05.phx.gbl...
>
>> When you run the same query in the Access environment (Access Query
>> Builder), do you get the correct results? If so, we need to
>> investigate the ASP end of things. I will need to set up a test
>> database and try to reproduce your result. It's been a long time
>> since I worked with Access, but I recall that the the order by
>> modifiers always worked correctly for me.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.