 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion GroupsGeneralPHPASPPerlColdFusionFlashHTML, CSS, ScriptsBrowsers |
Webmaster Forum / ASP / Database Access / March 2007Simple SQL ORDER BY doesn't work!
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 > 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.  SignatureMicrosoft 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 > 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. > Quite right: long day, frustrating problem. > [quoted text clipped - 17 lines] > 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 [quoted text clipped - 18 lines] > 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. SignatureMicrosoft 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 > 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. > Bob, thanks for responding! > [quoted text clipped - 16 lines] >> since I worked with Access, but I recall that the the order by >> modifiers always worked correctly for me. SignatureMicrosoft 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.