472,122 Members | 1,465 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,122 software developers and data experts.

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
Expand|Select|Wrap|Line Numbers
  1. <html>
  2.  
  3. <head>
  4. <meta name="GENERATOR" content="Microsoft FrontPage 5.0">
  5. <meta name="ProgId" content="FrontPage.Editor.Document">
  6. <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
  7. <title>New Page 1</title>
  8. </head>
  9.  
  10. <body bgcolor="#33CCFF">
  11.  
  12.  
  13. <%
  14. Set objDC = Server.CreateObject("ADODB.Connection")
  15. objDC.Open Application("fred_connectionstring")
  16.  
  17. Set objRS = objDC.Execute("Select Distinct site FROM Results")
  18. %>
  19.   <table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber1">
  20.     <tr>
  21.       <td width="33%"><img border="0" src="images/untitled.jpg"></td>
  22.       <td width="47%">
  23.     <FORM METHOD="POST" NAME="Form1" ACTION="index.asp">
  24.     <p align="left">
  25.     <SELECT NAME="site" SIZE="1" ONCHANGE=Form1.submit()>
  26.         <option selected><% = Request.Form("site") %></option>
  27.     <%
  28.     ' Continue until we get to the end of the recordset.
  29.     Do Until objRS.EOF
  30.         ' For each record we create a option tag and set it's value to the countr
  31.         %>
  32.         <OPTION><%= objRS("site") %></OPTION>
  33.         <%
  34.     ' Get next record
  35.     objRS.MoveNext
  36.     Loop
  37.     %>
  38.     </SELECT> <b><font face="Arial" size="2" color="#008080">Choose a site</font></b></p>
  39.       </FORM>
  40.     <%
  41.  
  42. ' Close Data Access Objects and free DB variables
  43. objRS.Close
  44. Set objRS =  Nothing
  45. objDC.Close
  46. Set objDC = Nothing
  47. %>
  48.  
  49.  
  50. <!-- End first Drop Down -->
  51. <!--Second drop down -->
  52.  
  53. <%
  54. 'Some code to hide the second drop down until we make a selection from the first
  55. IF Request.Form("site") = "" Then
  56. Else
  57. 'If site has a value then we get a list of cities for the second drop down
  58. Set objDC = Server.CreateObject("ADODB.Connection")
  59. objDC.Open Application("fred_connectionstring")
  60.  
  61. Set objRS = objDC.Execute("Select epn FROM Results WHERE site = '" & Request.Form("site") & "'" )
  62. %>
  63.     <FORM METHOD="POST" NAME="Form2" ACTION="index.asp">
  64.     <p align="left">
  65.     <font face="Arial"><font color="#008080"><b>
  66.     <SELECT NAME="epn" SIZE="1" ONCHANGE=Form2.submit()>
  67.         <option selected><% = Request.Form("epn") %></option>
  68.     <%
  69.     ' Continue until we get to the end of the recordset.
  70.     Do Until objRS.EOF
  71.         ' For each record we create a option tag and set it's value to the epn
  72.         %>
  73.         <OPTION><%= objRS("epn") %></OPTION>
  74.         <%
  75.     ' Get next record
  76.     objRS.MoveNext
  77.     Loop
  78.     %>
  79.     <%
  80.     'Set a hidden value in the second form for the site 
  81.     'so we can pass it along with the epn to the next query
  82.     %>
  83.     </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>
  84.       </FORM>
  85.  
  86. <%
  87. ' Close Data Access Objects and free DB variables
  88. objRS.Close
  89. Set objRS =  Nothing
  90. objDC.Close
  91. Set objDC = Nothing
  92. End IF
  93. %>
  94.  
  95. <!-- Display the records -->
  96.  
  97.       </td>
  98.       <td width="20%"><img border="0" src="images/sponsor_exelon.gif"></td>
  99.     </tr>
  100.   </table>
  101.   <table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber2">
  102.     <tr>
  103.       <td width="24%"><font color="#33CCFF"><a href="default.asp">
  104.       <font color="#33CCFF">Edit Database (Need Log In )</font></a></font></td>
  105.       <td width="56%">
  106. <% 
  107. 'Make sure we have submitted a epn and don't show results until we do
  108. IF Request.Form("epn") = "" Then
  109. Else
  110. Set objDC = Server.CreateObject("ADODB.Connection")
  111. objDC.Open Application("fred_connectionstring")
  112.  
  113. Set objRS = objDC.Execute("Select * FROM Results WHERE site = '" & Request.Form("site") & "' AND epn = '" & Request.Form("epn") & "'")
  114. '[color=red]Assuming I need an ORDER BY ABOVE HERE[/color]
  115. 'Loop through the database and assign the appropriate values to variables
  116. 'that we will use later
  117.  
  118. Do Until objRS.EOF
  119. site = objRS("site")
  120. epn = objRS("epn")
  121. unit = objRS("unit")
  122. num_tubes = objRS("num_tubes")
  123. component = objRS("component")
  124. tube_size = objRS("tube_size")
  125. tube_material = objRS("tube_material")
  126. tube_length = objRS("tube_length")
  127. straight_ubend = objRS("straight_ubend")
  128. asme = objRS("asme")
  129. wear = objRS("wear")
  130. thin = objRS("thin")
  131. pit = objRS("pit")
  132. ref = objRS("ref")
  133. freq= objRS("freq")
  134. probe = objRS("probe")
  135. fill = objRS("fill")
  136. objRS.MoveNext
  137. Loop
  138.  
  139. objRS.Close
  140. Set objRS = Nothing
  141. objDC.Close
  142. Set objDC = Nothing
  143. %>
  144.       </div>
  145. <p align="left"><br>
  146. </p>
  147. <p align="left"><b><font face="Arial" size="2" color="#008080">Information you requested: </font>
  148. <font face="Arial" size="3" color="#30303"> <% Response.Write epn & " at  " & site %> </font></b>
  149. <br>
  150. <font face="Arial" size="4" color="#008080">
  151. <%
  152. 'Set up the display of the record
  153. Response.Write "Site: " & site & "<br>"
  154. Response.Write "EPN: " & epn & "<br>"
  155. Response.Write component & "<br>"
  156. Response.Write "# of Tubes: " & num_tubes & "<br>"
  157. Response.Write "Size: " & tube_size & "<br>"
  158. Response.Write "Material: " & tube_material & "<br>"
  159. Response.Write "Length: " & tube_length & "<br>"
  160. Response.Write "U-Bend or Straight: " & straight_ubend & "<br>"
  161. Response.Write "ASME: " & asme & "<br>"
  162. Response.Write "WEAR: " & wear & "<br>"
  163. Response.Write "THIN: " & thin & "<br>"
  164. Response.Write "ID Pitting: " & pit & "<br>"
  165. Response.Write "Referance: " & ref & "<br>"
  166. Response.Write "Freq's: " & freq & "<br>"
  167. Response.Write "Probe: " & probe & "<br>"
  168. Response.Write "Fill: " & fill & "<br>"
  169.  
  170. End IF
  171. %> 
  172.  
  173. </font>
  174. </p>
  175. <p align="left">
  176. <a href="file:\\Exelon1\exelon1\CORESTAR\<%Response.Write site & "\Unit_" & unit & "\" & epn  %>" title="">Database On  Computer.</A>
  177. <a href="./standards/<%Response.Write asme & ".pdf" %>" title="Adobe">ASME Standard drawing.</A>
  178.  
  179.  
  180.   </table>
  181.  
  182. <p align="center"><img border="0" src="./images/<%Response.Write site & "_" & epn & ".jpg" %>"></p>
  183.  
  184. </body>
  185.  
  186. </html>
Thanks for any help
Sep 5 '07 #1
7 1987
jhardman
3,406 Expert 2GB
ORDER BY goes here:
Expand|Select|Wrap|Line Numbers
  1. IF Request.Form("site") = "" Then
  2. Else
  3. 'If site has a value then we get a list of cities for the second drop down
  4. Set objDC = Server.CreateObject("ADODB.Connection")
  5. objDC.Open Application("fred_connectionstring")
  6.  
  7. Set objRS = objDC.Execute("Select epn FROM Results WHERE site = '" & Request.Form("site") & "' ORDER BY epn, DESC" )
  8. %>
I also suggest something like this in the code for the first drop down box:
Expand|Select|Wrap|Line Numbers
  1.     <SELECT NAME="site" SIZE="1" ONCHANGE=Form1.submit()>
  2.     <%
  3.     ' Continue until we get to the end of the recordset.
  4.     Do Until objRS.EOF
  5.         ' For each record we create a option tag and set it's value to the countr
  6.         if request("site") = objRS("site") then
  7.             %>
  8.             <OPTION selected><%= objRS("site") %></OPTION>
  9.         <%
  10.         else %>
  11.             <OPTION><%= objRS("site") %></OPTION>
  12.         <%
  13.         end if
  14.     ' Get next record
  15.     objRS.MoveNext
  16.     Loop
  17.     %>
  18.     </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
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 Expert 2GB
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
  1. "SELECT " & a list of fields you want to get
  2. "FROM " & a list of tables where the data is held
  3. "WHERE " & qualifying statements such as "recordID = 346 AND userTable.userID = entryTable.userID"
  4. "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
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 Expert 100+
Minor mistake/typo in the code... ORDER BY epn, DESC

Remove the comma after epn - like so..

Expand|Select|Wrap|Line Numbers
  1. Set objRS = objDC.Execute("Select epn FROM Results WHERE site = '" & Request.Form("site") & "' ORDER BY epn DESC" )
  2.  
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
  1. sSQL = "Select epn FROM Results WHERE site = '" & Request.Form("site") & "' ORDER BY epn DESC"
  2. Response.Write sSQL
  3. 'Set objRS = objDC.Execute("Select epn FROM Results WHERE site = '" & Request.Form("site") & "' ORDER BY epn DESC" )
  4.  
Sep 5 '07 #6
Minor mistake/typo in the code... ORDER BY epn, DESC

Remove the comma after epn - like so..

Expand|Select|Wrap|Line Numbers
  1. Set objRS = objDC.Execute("Select epn FROM Results WHERE site = '" & Request.Form("site") & "' ORDER BY epn DESC" )
  2.  
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
  1. sSQL = "Select epn FROM Results WHERE site = '" & Request.Form("site") & "' ORDER BY epn DESC"
  2. Response.Write sSQL
  3. 'Set objRS = objDC.Execute("Select epn FROM Results WHERE site = '" & Request.Form("site") & "' ORDER BY epn DESC" )
  4.  
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 Expert 2GB
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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Seeker | last post: by
2 posts views Thread by Joel | last post: by
7 posts views Thread by ritchie | last post: by
34 posts views Thread by Mark Kamoski | last post: by
3 posts views Thread by Alexander Widera | last post: by
48 posts views Thread by Alex Chudnovsky | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.