How To Sort Records Alphabetically BUT Exclude Just One Record

**Delerna** · Sep 23 '10, 04:57 AM

Here is a complete "classic asp" page that should get you started. Hopefully I haven't made any syntax errors :)

Code:

<html>
<head> 
</head>
   
<body>
<%    
      Cty=Request.QueryString("City") 
      if cty="" then cty="New York"
	  set cnn = CreateObject("ADODB.Connection")  
      cnn.provider="Microsoft.Jet.OLEDB.4.0"
	  cnn.open "path/YourDB.mdb"
      set rst = CreateObject("ADODB.Recordset")    

	  strsql="SELECT City FROM (SELECT iif([city]='" & Cty & "',1,2 as Cls,City FROM theTable)a Order By Cls,City " 
      rst.Open strsql, cnn
      response.write("<table border=1 cellspacing=0>")
	  while not rst.eof
         response.write("<tr>")
         for i=0 to rst.fields.count-1
		    response.write("<td>" & rst.fields(i) & "</td>")
         next
		 response.write("</tr>")
         rst.movenext
      wend
      response.write("</table>")

      rst.close : set rst=nothing
      cnn.close : set cnn=nothing   
%>
</body>
</html>

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

**Delerna** · Sep 23 '10, 05:06 AM

alternatively you can populate a combo box

Code:

<html>
<head> 
</head>
 
<body>
<%    
      Cty=Request.QueryString("City") 
      if cty="" then cty="New York"
      set cnn = CreateObject("ADODB.Connection")  
      cnn.provider="Microsoft.Jet.OLEDB.4.0"
      cnn.open "path/YourDB.mdb"
      set rst = CreateObject("ADODB.Recordset")    
 
      strsql="SELECT City FROM (SELECT iif([city]='" & Cty & "',1,2 as Cls,City FROM theTable)a Order By Cls,City " 
      rst.Open strsql, cnn
      response.write<select ID=cboCity>")
      while not rst.eof
         response.write("<option value=" & rst.fields(0) & ">" & rst.fields(0) & "</option>")
         rst.movenext
      wend
      response.write("</select>")
 
      rst.close : set rst=nothing
      cnn.close : set cnn=nothing   
%>
</body>
</html>

Hopefully I have answered your question

**nicebasic** · Sep 23 '10, 10:06 AM

Thank you again.

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:

Code:

<html>
<head> 
</head>
 
<body>
<%    
      Cty = Request.QueryString("City")
      if cty = "" Then cty = "New York"

      set cnn = Server.CreateObject("ADODB.Connection")
      MyDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Request.ServerVariables("APPL_PHYSICAL_PATH") & "\Sort.mdb"
      cnn.open MyDSN

      set rst = CreateObject("ADODB.Recordset")
      strSQL = "SELECT City FROM (SELECT iif([city]='" & Cty & "',1,2 as Cls,City FROM theTable)a Order By Cls,City "
      rst.Open strSQL, cnn

      response.write("<table border=1 cellspacing=0>")
      while not rst.eof
         response.write("<tr>")
         for i=0 to rst.fields.count-1
            response.write("<td>" & rst.fields(i) & "</td>")
         next
         response.write("</tr>")
         rst.movenext
      wend
      response.write("</table>")
 
      rst.close : set rst = nothing
      cnn.close : set cnn = nothing   
%>
</body>
</html>

The Second Code:

Code:

<html>
<head> 
</head>
 
<body>
<%    
      Cty = Request.QueryString("City")
      if cty = "" Then cty = "New York"

      set cnn = Server.CreateObject("ADODB.Connection")
      MyDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Request.ServerVariables("APPL_PHYSICAL_PATH") & "\Sort.mdb"
      cnn.open MyDSN

      set rst = CreateObject("ADODB.Recordset")
      strSQL = "SELECT City FROM (SELECT iif([city]='" & Cty & "',1,2 as Cls,City FROM theTable)a Order By Cls,City "
      rst.Open strSQL, cnn

      response.write("<select ID=cboCity>")
      while not rst.eof
         response.write("<option value=" & rst.fields(0) & ">" & rst.fields(0) & "</option>")
         rst.movenext
      wend
      response.write("</select>")
 
      rst.close : set rst = nothing
      cnn.close : set cnn = nothing   
%>
</body>
</html>

In the First Code, I get this error when running it:

Code:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in FROM clause.
/mine/01.asp, line 16

In the Second Code, I get this error when running it:

Code:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in FROM clause.
/mine/02.asp, line 16

Thank you again for your great help.

Attached Files

Sort.zip (8.1 KB, 157 views)

**Delerna** · Sep 23 '10, 10:34 PM

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.

**Delerna** · Sep 23 '10, 10:47 PM

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

Code:

<html> 
<head>  
</head> 
  
<body> 
<%     
      Cty = Request.QueryString("City") 
      if cty = "" Then cty = "New York" 
  
      set cnn = Server.CreateObject("ADODB.Connection") 
      MyDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Request.ServerVariables("APPL_PHYSICAL_PATH") & "\Sort.mdb" 
      cnn.open MyDSN 
  
      set rst = CreateObject("ADODB.Recordset") 
      strSQL = "SELECT Citymajor FROM (SELECT iif([citymajor]='" & Cty & "',1,2) as Cls,CityMajor FROM Country)a Order By Cls,CityMajor " 
	  rst.Open strSQL, cnn 
  
      response.write("<table border=1 cellspacing=0>") 
      while not rst.eof 
         response.write("<tr>") 
         for i=0 to rst.fields.count-1 
            response.write("<td>" & rst.fields(i) & "</td>") 
         next 
         response.write("</tr>") 
         rst.movenext 
      wend 
      response.write("</table>") 
  
      rst.close : set rst = nothing 
      cnn.close : set cnn = nothing    
%> 
</body> 
</html>

**Delerna** · Sep 23 '10, 10:59 PM

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.fiel ds,c.fields
from(theSubQuer y)a
join(anotherSub Query)b on a.field=b.field
join(aThirdSubQ uery)c on a.field=c.field

so in the relatively simple query in the code I gave you
the subquery is

Code:

SELECT iif([citymajor]='" & Cty & "',1,2) as Cls,CityMajor 
FROM Country

to make it a sub query wrap it in brackets

Code:

(  SELECT iif([citymajor]='" & Cty & "',1,2) as Cls,CityMajor 
   FROM Country
)a

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]

Code:

SELECT Citymajor 
FROM 
(  SELECT iif([citymajor]='" & Cty & "',1,2) as Cls,CityMajor 
   FROM Country
)a 
Order By Cls,CityMajor

**Delerna** · Sep 23 '10, 11:14 PM

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

i had
iif(...

instead of
iif(...)

**nicebasic** · Sep 24 '10, 02:08 AM

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.

**Delerna** · Sep 26 '10, 09:50 PM

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

How To Sort Records Alphabetically BUT Exclude Just One Record

How To Sort Records Alphabetically BUT Exclude Just One Record

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment