473,543 Members | 2,320 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

ASP - SQL Sort help

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
  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 2058
jhardman
3,406 Recognized Expert Specialist
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
jrb22250
4 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
  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
jrb22250
4 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
  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
jrb22250
4 New Member
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 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

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

Similar topics

4
3273
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; }
6
5788
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." --------------------------------THe code is given below. String strSQL; strSQL = "SELECT .......... WHERE C.Client = '" + this.cboClient.SelectedValue + "' ";
2
1837
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 sort a list of pointers. It seems correct to me, and when I read on these boards I could not find any one complaining about the same problem. But...
4
5118
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 Filter/Sort window: A window in which you can create a filter from scratch. You enter criteria expressions in the filter design grid to restrict the...
7
25152
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 (Bubble, insertion, selection..). I have an array of int's and am passing them to each sort function.
34
7276
by: Mark Kamoski | last post by:
Hi-- Please help. I need a code sample for bubble sort. Thank you. --Mark
3
1475
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 you for your help. Alex using System;
48
4412
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 lot faster with both methods using .NET 1.1, that's why I am pretty sure its a (rather serious) bug. Below you can find C# test case that should...
0
1470
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 of the field I want to compare is different, but when all the elements are same, the list still gets sorted(meaning the sequence of the elements...
5
7798
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 columns, but I cannot figure out how to do that. I read somewhere that it is possible to do: but it does not work. Can anyone help me with this? PS:...
0
7336
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7329
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7675
tracyyun
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5257
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4884
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 then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3384
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 last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3385
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1809
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
626
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.