473,320 Members | 1,955 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,320 software developers and data experts.

How To Sort Records Alphabetically BUT Exclude Just One Record

nicebasic
I have already asked this question in the Access Department. A really intelligent programmer helped me solve the problem in Access IDE. But I'm working on an ASP project, and I need the solution in ASP, which is somehow different from Access VBA in the Syntax.

I would like to have a query for ASP Classic to be applied on an Access database.

1. Imagine that we have a table with the name of "Country".
2. Imagine that we have a column in "Country" that has this name: "CityMajor"
3. Imagine that we wish to have a query sort the items in this column, but with one exception: One of the records should stand on top of the other records while other records are sorted alphabetically.
4. An example could clarify this point. For example, we have the list of the following Major Cities in our list (Unsorted Alphabetically):

Paris
London
Tehran
Rome
New York
Berlin
Tokyo

5. Now we wish to have this list returned and sorted alphabetically, but with the exception that "New York" stands on top of the other cities. But the other items should be in alphabetical order:

New York
Berline
London
Paris
Rome
Tehran
Tokyo

How can I accomplish this query in ASP Classic for an Access Database?

Thank you in advance for any help in this regard.
Sep 21 '10 #1

✓ answered by Delerna

Incidentally, the syntax error was because I forgot the clossing bracket in the iif

i had
iif(...

instead of
iif(...)

9 2483
Delerna
1,134 Expert 1GB
Here is a complete "classic asp" page that should get you started. Hopefully I haven't made any syntax errors :)

Expand|Select|Wrap|Line Numbers
  1. <html>
  2. <head> 
  3. </head>
  4.  
  5. <body>
  6. <%    
  7.       Cty=Request.QueryString("City") 
  8.       if cty="" then cty="New York"
  9.       set cnn = CreateObject("ADODB.Connection")  
  10.       cnn.provider="Microsoft.Jet.OLEDB.4.0"
  11.       cnn.open "path/YourDB.mdb"
  12.       set rst = CreateObject("ADODB.Recordset")    
  13.  
  14.       strsql="SELECT City FROM (SELECT iif([city]='" & Cty & "',1,2 as Cls,City FROM theTable)a Order By Cls,City " 
  15.       rst.Open strsql, cnn
  16.       response.write("<table border=1 cellspacing=0>")
  17.       while not rst.eof
  18.          response.write("<tr>")
  19.          for i=0 to rst.fields.count-1
  20.             response.write("<td>" & rst.fields(i) & "</td>")
  21.          next
  22.          response.write("</tr>")
  23.          rst.movenext
  24.       wend
  25.       response.write("</table>")
  26.  
  27.       rst.close : set rst=nothing
  28.       cnn.close : set cnn=nothing   
  29. %>
  30. </body>
  31. </html>
  32.  

It will write the city names into an html table in the order you specified.
I added a URL parameter as one way you could pass the city dynamically
Sep 23 '10 #2
Delerna
1,134 Expert 1GB
alternatively you can populate a combo box

Expand|Select|Wrap|Line Numbers
  1. <html>
  2. <head> 
  3. </head>
  4.  
  5. <body>
  6. <%    
  7.       Cty=Request.QueryString("City") 
  8.       if cty="" then cty="New York"
  9.       set cnn = CreateObject("ADODB.Connection")  
  10.       cnn.provider="Microsoft.Jet.OLEDB.4.0"
  11.       cnn.open "path/YourDB.mdb"
  12.       set rst = CreateObject("ADODB.Recordset")    
  13.  
  14.       strsql="SELECT City FROM (SELECT iif([city]='" & Cty & "',1,2 as Cls,City FROM theTable)a Order By Cls,City " 
  15.       rst.Open strsql, cnn
  16.       response.write<select ID=cboCity>")
  17.       while not rst.eof
  18.          response.write("<option value=" & rst.fields(0) & ">" & rst.fields(0) & "</option>")
  19.          rst.movenext
  20.       wend
  21.       response.write("</select>")
  22.  
  23.       rst.close : set rst=nothing
  24.       cnn.close : set cnn=nothing   
  25. %>
  26. </body>
  27. </html>
  28.  

Hopefully I have answered your question
Sep 23 '10 #3
Thank you again, Delerna.

I used your code. It is really complicated for me. I think there's something wrong with the way I have used your code.

Since your code is an advanced one, I couldn't use it. Here's your code with the sample Database from ADezii. I tried to run it, but it failed.

I'm sure something is wrong with the names of the Columns in the Query Line. But I can't fix it. I think it's related to the name City and CityMajor, but I don't know how to solve it.


The First Code:
Expand|Select|Wrap|Line Numbers
  1. <html>
  2. <head> 
  3. </head>
  4.  
  5. <body>
  6. <%    
  7.       Cty = Request.QueryString("City")
  8.       if cty = "" Then cty = "New York"
  9.  
  10.       set cnn = Server.CreateObject("ADODB.Connection")
  11.       MyDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Request.ServerVariables("APPL_PHYSICAL_PATH") & "\Sort.mdb"
  12.       cnn.open MyDSN
  13.  
  14.       set rst = CreateObject("ADODB.Recordset")
  15.       strSQL = "SELECT City FROM (SELECT iif([city]='" & Cty & "',1,2 as Cls,City FROM theTable)a Order By Cls,City "
  16.       rst.Open strSQL, cnn
  17.  
  18.       response.write("<table border=1 cellspacing=0>")
  19.       while not rst.eof
  20.          response.write("<tr>")
  21.          for i=0 to rst.fields.count-1
  22.             response.write("<td>" & rst.fields(i) & "</td>")
  23.          next
  24.          response.write("</tr>")
  25.          rst.movenext
  26.       wend
  27.       response.write("</table>")
  28.  
  29.       rst.close : set rst = nothing
  30.       cnn.close : set cnn = nothing   
  31. %>
  32. </body>
  33. </html>



The Second Code:
Expand|Select|Wrap|Line Numbers
  1. <html>
  2. <head> 
  3. </head>
  4.  
  5. <body>
  6. <%    
  7.       Cty = Request.QueryString("City")
  8.       if cty = "" Then cty = "New York"
  9.  
  10.       set cnn = Server.CreateObject("ADODB.Connection")
  11.       MyDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Request.ServerVariables("APPL_PHYSICAL_PATH") & "\Sort.mdb"
  12.       cnn.open MyDSN
  13.  
  14.       set rst = CreateObject("ADODB.Recordset")
  15.       strSQL = "SELECT City FROM (SELECT iif([city]='" & Cty & "',1,2 as Cls,City FROM theTable)a Order By Cls,City "
  16.       rst.Open strSQL, cnn
  17.  
  18.       response.write("<select ID=cboCity>")
  19.       while not rst.eof
  20.          response.write("<option value=" & rst.fields(0) & ">" & rst.fields(0) & "</option>")
  21.          rst.movenext
  22.       wend
  23.       response.write("</select>")
  24.  
  25.       rst.close : set rst = nothing
  26.       cnn.close : set cnn = nothing   
  27. %>
  28. </body>
  29. </html>

In the First Code, I get this error when running it:
Expand|Select|Wrap|Line Numbers
  1. Error Type:
  2. Microsoft JET Database Engine (0x80040E14)
  3. Syntax error in FROM clause.
  4. /mine/01.asp, line 16
  5.  

In the Second Code, I get this error when running it:
Expand|Select|Wrap|Line Numbers
  1. Error Type:
  2. Microsoft JET Database Engine (0x80040E14)
  3. Syntax error in FROM clause.
  4. /mine/02.asp, line 16
Thank you again for your great help.
Attached Files
File Type: zip Sort.zip (8.1 KB, 96 views)
Sep 23 '10 #4
Delerna
1,134 Expert 1GB
The first thing I notice is the error is comming from access and not asp
so yes the problem is in the sql code
which the "syntax error in FROM clause" confirms.

The second thing I notice is you didn't change the table name to the actual name of your table in your database.

Also you didn't change the field name into the actual field names in the table.
Sep 23 '10 #5
Delerna
1,134 Expert 1GB
I took the db from the zip and made a mock up on my web server

This code should work, as is, for you
All you needed to do was change the table and field names to the actuals.

Oh, and also add the missing closing bracket that I missed in my sample....oops

Expand|Select|Wrap|Line Numbers
  1. <html> 
  2. <head>  
  3. </head> 
  4.  
  5. <body> 
  6. <%     
  7.       Cty = Request.QueryString("City") 
  8.       if cty = "" Then cty = "New York" 
  9.  
  10.       set cnn = Server.CreateObject("ADODB.Connection") 
  11.       MyDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Request.ServerVariables("APPL_PHYSICAL_PATH") & "\Sort.mdb" 
  12.       cnn.open MyDSN 
  13.  
  14.       set rst = CreateObject("ADODB.Recordset") 
  15.       strSQL = "SELECT Citymajor FROM (SELECT iif([citymajor]='" & Cty & "',1,2) as Cls,CityMajor FROM Country)a Order By Cls,CityMajor " 
  16.       rst.Open strSQL, cnn 
  17.  
  18.       response.write("<table border=1 cellspacing=0>") 
  19.       while not rst.eof 
  20.          response.write("<tr>") 
  21.          for i=0 to rst.fields.count-1 
  22.             response.write("<td>" & rst.fields(i) & "</td>") 
  23.          next 
  24.          response.write("</tr>") 
  25.          rst.movenext 
  26.       wend 
  27.       response.write("</table>") 
  28.  
  29.       rst.close : set rst = nothing 
  30.       cnn.close : set cnn = nothing    
  31. %> 
  32. </body> 
  33. </html> 
  34.  
Sep 23 '10 #6
Delerna
1,134 Expert 1GB
By the way that query wrapped up in brackets is called a subquery or more accurately a derived table.
It is a handy way of combining multiple queries into 1 query
You can select from (and join to) a subquery as if it was a table

select a.fields,b.fields,c.fields
from(theSubQuery)a
join(anotherSubQuery)b on a.field=b.field
join(aThirdSubQuery)c on a.field=c.field



so in the relatively simple query in the code I gave you
the subquery is
Expand|Select|Wrap|Line Numbers
  1. SELECT iif([citymajor]='" & Cty & "',1,2) as Cls,CityMajor 
  2. FROM Country
  3.  
to make it a sub query wrap it in brackets
Expand|Select|Wrap|Line Numbers
  1. (  SELECT iif([citymajor]='" & Cty & "',1,2) as Cls,CityMajor 
  2.    FROM Country
  3. )a 
  4.  
and give it a name....in this case the name is [a]


in a query the subquery can be used as if it was a table called [a]
Expand|Select|Wrap|Line Numbers
  1. SELECT Citymajor 
  2. FROM 
  3. (  SELECT iif([citymajor]='" & Cty & "',1,2) as Cls,CityMajor 
  4.    FROM Country
  5. )a 
  6. Order By Cls,CityMajor 
  7.  
Sep 23 '10 #7
Delerna
1,134 Expert 1GB
Incidentally, the syntax error was because I forgot the clossing bracket in the iif

i had
iif(...

instead of
iif(...)
Sep 23 '10 #8
Thank you a million times, Delerna.

I don't know how to thank you. You are so kind and actually, I have to confess that you are a real expert. I have asked this question from many people around me, but no one was able to give a great solution like yours.

You gave a "One Line" solution to this problem. This is the way experts solve a problem like this.

I called one of my friends to ask him help about this. He called this idea a "Crazy One". He said, "There is no reason to use such a funny idea in your programs."

You're a great expert, Delerna. Your way of solving this problem was excellent!!!

Thank you again and again and again.

Good luck.
Sep 24 '10 #9
Delerna
1,134 Expert 1GB
He called this idea a "Crazy One".
There is no reason to use such a funny idea in your programs
Maybe your friend is right and it is a crazy idea. It wouldn't be the first time I have had one...nor will it be the last :)

I would be iterested to know how your friend thinks it should be done. My phillosophy is If it works and performance is acceptable then 'problem solved' But I am always willing to listen to and use a better method if someone cares to suggest it.


I am glad it worked for you
Sep 26 '10 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Phil Powell | last post by:
Very simplistic but I am utterly STUMPED at this one. I have a db table "person" that I can sort by title, first_name, last_name, or city with no problems at all because "title", "first_name",...
3
by: Lad | last post by:
What is the best( easiest)way how to sort a file? I have a file where each record consists of 3 fields( 3 words) and I would like to sort records by the first field( word)in each record. Any idea?...
12
by: MLH | last post by:
I have created two forms: frmBrowseNegsMainform and frmBrowseNegsSubform. I put a subform control on the first of these. The SourceObject property for the subform control is, of course,...
22
by: Nhmiller | last post by:
Is there a way to do this? Thanks. Neil Cat Paintings At Carol Wilson Gallery http://www.carolwilsongallery.com
3
by: srikanth | last post by:
please give me any helpful logics for the subject
1
by: rdraider | last post by:
Hi all, We have an app that uses SQL 2000. I am trying to track when a code field (selcode) is changed on an order which then causes a status field (status) to change. I tried a trigger but...
3
by: usaccess | last post by:
Hi, I have a form/subform. There is a priority field and then a case status field. I want to display records so that it sorts by case status as "actives" then "on holds" then "completes" and...
2
by: Catch_22 | last post by:
Hi, I have a stored procedure that has to extract the child records for particular parent records. The issue is that in some cases I do not want to extract all the child records only a...
3
by: sail87 | last post by:
I inherited an Access 2003 database and need to change the way a form is sorted. It seems to work like this. There is a table containing work records of work requests. The user fills out a form...
3
by: ITSimTech | last post by:
I'm trying to learn how/do two things here: 1) If the user searches for "Data" ($searchtext = "Data") the output should also include the fourth record because Field1 contains "all". 2) But the...
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
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: 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.