Simple SQL ORDER BY doesn't work! -- inetserver asp db

Steve Bottoms
3/20/2007 8:11:46 PM

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

[quoted text, click to view]

Bob Barrows [MVP]
3/20/2007 9:50:46 PM

[quoted text, click to view]
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"

Bob Barrows [MVP]
3/21/2007 12:00:00 AM

[quoted text, click to view]

Is CD12 in the bBody field? Or is CD in bBody and 12 in bNumber?

[quoted text, click to view]

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.

[quoted text, click to view]

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"

Steve Bottoms
3/21/2007 11:09:39 AM

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

[quoted text, click to view]

Bob Barrows [MVP]
3/21/2007 2:35:27 PM

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.

[quoted text, click to view]

--
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.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about inetserver asp db

inetserver asp db : Simple SQL ORDER BY doesn't work!