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 2483
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: 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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
|
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...
| |