ASP - SQL Sort help - ASP / Active Server Pages

4 New Member

I have the Dual Drop down in same page, my problem is the second drop down I can not figure out how to right an ORDER BY EPN asc or desc in there

Expand|Select|Wrap|Line Numbers

 <html>
 
<head>

<meta name="GENERATOR" content="Microsoft FrontPage 5.0">

<meta name="ProgId" content="FrontPage.Editor.Document">

<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">

<title>New Page 1</title>

</head>
 
<body bgcolor="#33CCFF">
 
<%

Set objDC = Server.CreateObject("ADODB.Connection")

objDC.Open Application("fred_connectionstring")
 
Set objRS = objDC.Execute("Select Distinct site FROM Results")

%>

  <table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber1">

    <tr>

      <td width="33%"><img border="0" src="images/untitled.jpg"></td>

      <td width="47%">

    <FORM METHOD="POST" NAME="Form1" ACTION="index.asp">

    <p align="left">

    <SELECT NAME="site" SIZE="1" ONCHANGE=Form1.submit()>

        <option selected><% = Request.Form("site") %></option>

    <%

    ' Continue until we get to the end of the recordset.

    Do Until objRS.EOF

        ' For each record we create a option tag and set it's value to the countr

        %>

        <OPTION><%= objRS("site") %></OPTION>

        <%

    ' Get next record

    objRS.MoveNext

    Loop

    %>

    </SELECT> <b><font face="Arial" size="2" color="#008080">Choose a site</font></b></p>

      </FORM>

    <%
 
' Close Data Access Objects and free DB variables

objRS.Close

Set objRS =  Nothing

objDC.Close

Set objDC = Nothing

%>
 
<!-- End first Drop Down -->

<!--Second drop down -->
 
<%

'Some code to hide the second drop down until we make a selection from the first

IF Request.Form("site") = "" Then

Else

'If site has a value then we get a list of cities for the second drop down

Set objDC = Server.CreateObject("ADODB.Connection")

objDC.Open Application("fred_connectionstring")
 
Set objRS = objDC.Execute("Select epn FROM Results WHERE site = '" & Request.Form("site") & "'" )

%>

    <FORM METHOD="POST" NAME="Form2" ACTION="index.asp">

    <p align="left">

    <font face="Arial"><font color="#008080"><b>

    <SELECT NAME="epn" SIZE="1" ONCHANGE=Form2.submit()>

        <option selected><% = Request.Form("epn") %></option>

    <%

    ' Continue until we get to the end of the recordset.

    Do Until objRS.EOF

        ' For each record we create a option tag and set it's value to the epn

        %>

        <OPTION><%= objRS("epn") %></OPTION>

        <%

    ' Get next record

    objRS.MoveNext

    Loop

    %>

    <%

    'Set a hidden value in the second form for the site 

    'so we can pass it along with the epn to the next query

    %>

    </SELECT></b></font><b><font size="2" color="#008080"> Choose a epn</font></b><font color="#008080"><b><input type="hidden" name="site" value="<% = Request.Form("site") %>"></b></font></font></p>

      </FORM>
 
<%

' Close Data Access Objects and free DB variables

objRS.Close

Set objRS =  Nothing

objDC.Close

Set objDC = Nothing

End IF

%>
 
<!-- Display the records -->
 
      </td>

      <td width="20%"><img border="0" src="images/sponsor_exelon.gif"></td>

    </tr>

  </table>

  <table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber2">

    <tr>

      <td width="24%"><font color="#33CCFF"><a href="default.asp">

      <font color="#33CCFF">Edit Database (Need Log In )</font></a></font></td>

      <td width="56%">

<% 

'Make sure we have submitted a epn and don't show results until we do

IF Request.Form("epn") = "" Then

Else

Set objDC = Server.CreateObject("ADODB.Connection")

objDC.Open Application("fred_connectionstring")
 
Set objRS = objDC.Execute("Select * FROM Results WHERE site = '" & Request.Form("site") & "' AND epn = '" & Request.Form("epn") & "'")

'[color=red]Assuming I need an ORDER BY ABOVE HERE[/color]

'Loop through the database and assign the appropriate values to variables

'that we will use later
 
Do Until objRS.EOF

site = objRS("site")

epn = objRS("epn")

unit = objRS("unit")

num_tubes = objRS("num_tubes")

component = objRS("component")

tube_size = objRS("tube_size")

tube_material = objRS("tube_material")

tube_length = objRS("tube_length")

straight_ubend = objRS("straight_ubend")

asme = objRS("asme")

wear = objRS("wear")

thin = objRS("thin")

pit = objRS("pit")

ref = objRS("ref")

freq= objRS("freq")

probe = objRS("probe")

fill = objRS("fill")

objRS.MoveNext

Loop
 
objRS.Close

Set objRS = Nothing

objDC.Close

Set objDC = Nothing

%>

      </div>

<p align="left"><br>

</p>

<p align="left"><b><font face="Arial" size="2" color="#008080">Information you requested: </font>

<font face="Arial" size="3" color="#30303"> <% Response.Write epn & " at  " & site %> </font></b>

<br>

<font face="Arial" size="4" color="#008080">

<%

'Set up the display of the record

Response.Write "Site: " & site & "<br>"

Response.Write "EPN: " & epn & "<br>"

Response.Write component & "<br>"

Response.Write "# of Tubes: " & num_tubes & "<br>"

Response.Write "Size: " & tube_size & "<br>"

Response.Write "Material: " & tube_material & "<br>"

Response.Write "Length: " & tube_length & "<br>"

Response.Write "U-Bend or Straight: " & straight_ubend & "<br>"

Response.Write "ASME: " & asme & "<br>"

Response.Write "WEAR: " & wear & "<br>"

Response.Write "THIN: " & thin & "<br>"

Response.Write "ID Pitting: " & pit & "<br>"

Response.Write "Referance: " & ref & "<br>"

Response.Write "Freq's: " & freq & "<br>"

Response.Write "Probe: " & probe & "<br>"

Response.Write "Fill: " & fill & "<br>"
 
End IF

%> 
 
</font>

</p>

<p align="left">

<a href="file:\\Exelon1\exelon1\CORESTAR\<%Response.Write site & "\Unit_" & unit & "\" & epn  %>" title="">Database On  Computer.</A>

<a href="./standards/<%Response.Write asme & ".pdf" %>" title="Adobe">ASME Standard drawing.</A>
 
  </table>
 
<p align="center"><img border="0" src="./images/<%Response.Write site & "_" & epn & ".jpg" %>"></p>
 
</body>
 
</html>

Thanks for any help

Sep 5 '07 #1

Subscribe Reply

2057

jhardman

3,406

Recognized Expert Specialist

ORDER BY goes here:

Expand|Select|Wrap|Line Numbers

 
IF Request.Form("site") = "" Then

Else

'If site has a value then we get a list of cities for the second drop down

Set objDC = Server.CreateObject("ADODB.Connection")

objDC.Open Application("fred_connectionstring")
 
Set objRS = objDC.Execute("Select epn FROM Results WHERE site = '" & Request.Form("site") & "' ORDER BY epn, DESC" )

%>

I also suggest something like this in the code for the first drop down box:

Expand|Select|Wrap|Line Numbers

     <SELECT NAME="site" SIZE="1" ONCHANGE=Form1.submit()>

    <%

    ' Continue until we get to the end of the recordset.

    Do Until objRS.EOF

        ' For each record we create a option tag and set it's value to the countr

        if request("site") = objRS("site") then

            %>

            <OPTION selected><%= objRS("site") %></OPTION>

        <%

        else %>

            <OPTION><%= objRS("site") %></OPTION>

        <%

        end if

    ' Get next record

    objRS.MoveNext

    Loop

    %>

    </SELECT>

Your version reprints the selected site as the first option. My version just selects it within the list. Let me know if this helps.

Jared

Sep 5 '07 #2

jrb22250

New Member

Still lost on Order by what line is that then?
Thanks for the Drop down, now now duplicates

Sep 5 '07 #3

jhardman

3,406

Recognized Expert Specialist

Still lost on Order by what line is that then?
Thanks for the Drop down, now no duplicates

It's part of the query statement. The query using a "SELECT" usually goes something like:

Expand|Select|Wrap|Line Numbers

 "SELECT " & a list of fields you want to get

"FROM " & a list of tables where the data is held

"WHERE " & qualifying statements such as "recordID = 346 AND userTable.userID = entryTable.userID"

"ORDER BY " & the fields by which the data is sorted

So I put it in the last line of my first code snippet above, my line #7 corresponds to your code line #61. Do you see where it goes?

Jared

Sep 5 '07 #4

jrb22250

New Member

That is what I thought you ment.

I still get "Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in ORDER BY clause.
/test4/index5.asp, line 86"

Operator ERROR ...Me...
I left 2 &&. Been doing it all along. Thanks alot for your help

Sep 5 '07 #5

markrawlingson

346

Recognized Expert Contributor

Minor mistake/typo in the code... ORDER BY epn, DESC

Remove the comma after epn - like so..

Expand|Select|Wrap|Line Numbers

 
Set objRS = objDC.Execute("Select epn FROM Results WHERE site = '" & Request.Form("site") & "' ORDER BY epn DESC" )

If you still get an error generated by the SQL statement.. set it into a variable and response.write so you can see what it actually says.

Expand|Select|Wrap|Line Numbers

 
sSQL = "Select epn FROM Results WHERE site = '" & Request.Form("site") & "' ORDER BY epn DESC"

Response.Write sSQL

'Set objRS = objDC.Execute("Select epn FROM Results WHERE site = '" & Request.Form("site") & "' ORDER BY epn DESC" )

Sep 5 '07 #6

jrb22250

New Member

Minor mistake/typo in the code... ORDER BY epn, DESC

Remove the comma after epn - like so..

Expand|Select|Wrap|Line Numbers

Set objRS = objDC.Execute("Select epn FROM Results WHERE site = '" & Request.Form("site") & "' ORDER BY epn DESC" )

If you still get an error generated by the SQL statement.. set it into a variable and response.write so you can see what it actually says.

Expand|Select|Wrap|Line Numbers

sSQL = "Select epn FROM Results WHERE site = '" & Request.Form("site") & "' ORDER BY epn DESC"

Response.Write sSQL

'Set objRS = objDC.Execute("Select epn FROM Results WHERE site = '" & Request.Form("site") & "' ORDER BY epn DESC" )

It is working now, from before. I just could not figure out error. Then I looked at it again after getting a way and say my mistake.

Thanks again for the super fast help

Sep 5 '07 #7

jhardman

3,406

Recognized Expert Specialist

Minor mistake/typo in the code... ORDER BY epn, DESC

Remove the comma after epn - like so..

he he. I knew you deserved to be an expert! Now I feel chagrined.

Jared

Sep 6 '07 #8

Similar topics

3270

Secondary sort

by: Seeker | last post by:

Hi, I have an array of objects. My object definition is given below: function tempArray(code,height,weight) { this.code = code; this.height = height; this.weight = weight; }

Javascript

5780

I can't sort my combo box. Please Help!

by: raj | last post by:

When I try to sort my combo box I get the following error. "Cannot sort a ComboBox that has a DataSource set. Sort the data using the underlying data model." ...

.NET Framework

1833

problem compiling functor with sort

by: Joel | last post by:

I am having some problems compiling my code on Mandrake 10 with g++ (GCC 3.3.2). The problem seems to be in that I try to define a functor that compares two pointer objects, and use that functor to...

C / C++

5116

Advanced Filter/Sort window -- where is it?

by: Nhmiller | last post by:

This is directly from Access' Help: "About designing a query When you open a query in Design view, or open a form, report, or datasheet and show the Advanced Filter/Sort window (Advanced...

Microsoft Access / VBA

25146

C Array Sort - Pass By Reference.........

by: ritchie | last post by:

Hi all, I am new to this group and I have question that you may be able to help me with. I am trying to learn C but am currently stuck on this. First of all, I have a function for each sort...

C / C++

7270

need a code sample for bubble sort

by: Mark Kamoski | last post by:

Hi-- Please help. I need a code sample for bubble sort. Thank you. --Mark

C# / C Sharp

1470

sort this thing

by: Alexander Widera | last post by:

Hi, I have a problem with this code ... (see below) ... I want to sort an instance of MyList ... by MyData.Shortname ... Shortname is of the type string.... how can I sort the entries? Thank...

ASP.NET

4406

.NET 2.0 performance bug in ArrayList.Sort

by: Alex Chudnovsky | last post by:

I have come across with what appears to be a significant performance bug in ..NET 2.0 ArrayList.Sort method when compared with Array.Sort on the same data. Same data on the same CPU gets sorted a...

.NET Framework

1463

result of List.Sort on already elements with same value.

by: Amar | last post by:

Hi, I have a generic list with a some objects of a particular class in it. I have implemented a IComparer for the the class and pass it to the List. The list.sort method works fine when the value...

.NET Framework

7794

Is there an easy way to sort a list by two criteria?

by: neocortex | last post by:

Hello! I am a newbie in Python. Recently, I get stuck with the problem of sorting by two criteria. In brief, I have a two-dimensional list (for a table or a matrix). Now, I need to sort by two...

Python

7131

What is ONU?

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,...

General

7174

Problem With Comparison Operator <=> in G++

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,...

C / C++

7220

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

7388

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

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...

General

5470

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

Career Advice

4600

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...

C# / C Sharp

3099

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...

Networking - Hardware / Configuration

3091

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

665

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP