ASP - SQL Sort help

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • jrb22250
    New Member
    • Sep 2007
    • 4
    #1

    ASP - SQL Sort help

    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
    [code=asp]<html>

    <head>
    <meta name="GENERATOR " content="Micros oft FrontPage 5.0">
    <meta name="ProgId" content="FrontP age.Editor.Docu ment">
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
    <title>New Page 1</title>
    </head>

    <body bgcolor="#33CCF F">


    <%
    Set objDC = Server.CreateOb ject("ADODB.Con nection")
    objDC.Open Application("fr ed_connectionst ring")

    Set objRS = objDC.Execute(" Select Distinct site FROM Results")
    %>
    <table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#1 11111" width="100%" id="AutoNumber1 ">
    <tr>
    <td width="33%"><im g border="0" src="images/untitled.jpg"></td>
    <td width="47%">
    <FORM METHOD="POST" NAME="Form1" ACTION="index.a sp">
    <p align="left">
    <SELECT NAME="site" SIZE="1" ONCHANGE=Form1. submit()>
    <option selected><% = Request.Form("s ite") %></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("s ite") = "" Then
    Else
    'If site has a value then we get a list of cities for the second drop down
    Set objDC = Server.CreateOb ject("ADODB.Con nection")
    objDC.Open Application("fr ed_connectionst ring")

    Set objRS = objDC.Execute(" Select epn FROM Results WHERE site = '" & Request.Form("s ite") & "'" )
    %>
    <FORM METHOD="POST" NAME="Form2" ACTION="index.a sp">
    <p align="left">
    <font face="Arial"><f ont color="#008080" ><b>
    <SELECT NAME="epn" SIZE="1" ONCHANGE=Form2. submit()>
    <option selected><% = Request.Form("e pn") %></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("s ite") %>"></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%"><im g border="0" src="images/sponsor_exelon. gif"></td>
    </tr>
    </table>
    <table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#1 11111" width="100%" id="AutoNumber2 ">
    <tr>
    <td width="24%"><fo nt color="#33CCFF" ><a href="default.a sp">
    <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("e pn") = "" Then
    Else
    Set objDC = Server.CreateOb ject("ADODB.Con nection")
    objDC.Open Application("fr ed_connectionst ring")

    Set objRS = objDC.Execute(" Select * FROM Results WHERE site = '" & Request.Form("s ite") & "' AND epn = '" & Request.Form("e pn") & "'")
    '[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_tube s")
    component = objRS("componen t")
    tube_size = objRS("tube_siz e")
    tube_material = objRS("tube_mat erial")
    tube_length = objRS("tube_len gth")
    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"><b r>
    </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:\\Ex elon1\exelon1\C ORESTAR\<%Respo nse.Write site & "\Unit_" & unit & "\" & epn %>" title="">Databa se On Computer.</A>
    <a href="./standards/<%Response.Writ e asme & ".pdf" %>" title="Adobe">A SME Standard drawing.</A>


    </table>

    <p align="center"> <img border="0" src="./images/<%Response.Writ e site & "_" & epn & ".jpg" %>"></p>

    </body>

    </html>[/code]Thanks for any help
    Last edited by jhardman; Sep 5 '07, 05:37 PM. Reason: put code in code tags. please use code tags in the future
    Tags: None
    • jhardman
      Recognized Expert Specialist
      • Jan 2007
      • 3405
      #2
      ORDER BY goes here:[code=asp]
      IF Request.Form("s ite") = "" Then
      Else
      'If site has a value then we get a list of cities for the second drop down
      Set objDC = Server.CreateOb ject("ADODB.Con nection")
      objDC.Open Application("fr ed_connectionst ring")

      Set objRS = objDC.Execute(" Select epn FROM Results WHERE site = '" & Request.Form("s ite") & "' ORDER BY epn, DESC" )
      %>[/code]
      I also suggest something like this in the code for the first drop down box:
      [code=asp] <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>[/code] 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

        Comment

        • jrb22250
          New Member
          • Sep 2007
          • 4
          #3
          Still lost on Order by what line is that then?
          Thanks for the Drop down, now now duplicates

            Comment

            • jhardman
              Recognized Expert Specialist
              • Jan 2007
              • 3405
              #4
              Originally posted by jrb22250
              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:
              Code:
              "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

                Comment

                • jrb22250
                  New Member
                  • Sep 2007
                  • 4
                  #5
                  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

                    Comment

                    • markrawlingson
                      Recognized Expert Contributor
                      • Aug 2007
                      • 346
                      #6
                      Minor mistake/typo in the code... ORDER BY epn, DESC

                      Remove the comma after epn - like so..

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

                      Code:
                      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" )

                        Comment

                        • jrb22250
                          New Member
                          • Sep 2007
                          • 4
                          #7
                          Originally posted by markrawlingson
                          Minor mistake/typo in the code... ORDER BY epn, DESC

                          Remove the comma after epn - like so..

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

                          Code:
                          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

                            Comment

                            • jhardman
                              Recognized Expert Specialist
                              • Jan 2007
                              • 3405
                              #8
                              Originally posted by markrawlingson
                              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

                                Comment

                                Previous template Next
                                Working...