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.
Incidentally, the syntax error was because I forgot the clossing bracket in the iif
i had
iif(...
instead of
iif(...)
9  2485 
Here is a complete "classic asp" page that should get you started. Hopefully I haven't made any syntax errors :) -
<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
alternatively you can populate a combo box -
<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
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: - <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: - <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: - 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: - Error Type:
-
Microsoft JET Database Engine (0x80040E14)
-
Syntax error in FROM clause.
-
/mine/02.asp, line 16
Thank you again for your great help.
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.
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 -
<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>
-
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 -
SELECT iif([citymajor]='" & Cty & "',1,2) as Cls,CityMajor
-
FROM Country
-
to make it a sub query wrap it in brackets -
( 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] -
SELECT Citymajor
-
FROM
-
( SELECT iif([citymajor]='" & Cty & "',1,2) as Cls,CityMajor
-
FROM Country
-
)a
-
Order By Cls,CityMajor
-
Incidentally, the syntax error was because I forgot the clossing bracket in the iif
i had
iif(...
instead of
iif(...)
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.
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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",...
|
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?...
|
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,...
|
by: Nhmiller |
last post by:
Is there a way to do this? Thanks.
Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com
|
by: srikanth |
last post by:
please give me any helpful logics for the subject
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
