Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

recordset sort order

Posted on 2002-05-02
7
Medium Priority
?
749 Views
Last Modified: 2008-02-01
I need help on the sort order, following is the code.
I hope someone can help. Thanks.

<%@ Language="VBScript"%>
<%


      custno = session("ALcustno")

      sfiletype = trim(request("strMasterType"))
      txtacctcode = trim(request("txtacctcode")) & "%"
      selfield = request("selfield")
      txtacctcode = UCASE(txtacctcode)
      
      if request("B1") = "Export_Excel" then %>
            <base target = "_blank">
            <SCRIPT SRC="/SharedScript/security.js" LANGUAGE="JavaScript"></SCRIPT>

                  <form method="post" action="accloc2.asp" name="formaccloc">
                  <input  type="hidden" value=<%=sfiletype%> name="strMasterType">
                  <input  type="hidden" value=<%=txtacctcode%> name="txtacctcode">
                  <input  type="hidden" value=<%=selfield%> name="selfield">

                  <script>document.formaccloc.submit();</script>
      <%                        
      '      Response.Redirect "accloc2.asp?strMasterType="&sfiletype&"&txtacctcode="&txtacctcode&"&selfield="&selfield&""
      '      Response.end
      end if
      
            if request("strMasterType") = "0" or (request("strMasterType") = "0" and selfield = "1") then
            Response.Write "<table><tr><td class=""clsSubhead"">Please select Master File </td></TR></table>"
            Response.End
            end if
            if request("strMasterType") <> " "  and selfield = "0"  then
            Response.Write "<table><tr><td class=""clsSubhead"">Please select Field to Search </td></TR></table>"
            Response.End
            end if
      
      
      Set objCTSI = Server.CreateObject("NETSERVICE.DBCONNECT")
      retStatus = objCTSI.ConnectToDB("","","","ACCLOC")
      Set RsAcct = objCTSI.FetchRecord("Select * from acctmast where filetype='"&sfiletype&"' and custno='"& custno &"' and displayflg='Y' order by displayord ")
      defaultfield = trim(RsAcct("defaultfld"))
      defaultfieldvalue = trim(RsAcct("defaultfldval"))&"%"
      strfilesource = trim(RsAcct("filesource"))
            
            if selfield = " " OR selfield = "0" OR request("selfield")= "" then  selfield = defaultfield
            if txtacctcode = "%" then txtacctcode = defaultfieldvalue&"%"
      
      do while not RsAcct.EOF
         strTable = RsAcct("FileName")
         strfield = strfield&", "&RsAcct("fieldname")
        
           'HERE CREATE THE HEADER, NEED SORT ORDER FOR EVERY COLUMN
         strHeader = strHeader & "<td class=""clsSubhead"">" &  RsAcct("FieldDesp") & "</td>"
         RsAcct.MoveNext
      Loop
      if selfield <> "1" then
            if selfield <> defaultfield  then
                  'Split the selfield into fieldname and fielddesc
                  selfld = split(selfield, "-")
                  selfield1 = trim(selfld(0))
                  selfield = selfield1
                  defaultfield = trim(selfield1)
                  defaultfielddesc = trim(selfield2)
                  selfield2 = trim(selfld(1))
                  if displayfielddesc <> selfield2 then
                        displayfielddesc = selfield2
                  end if
            end if
      end if
      strfield1 = mid(strfield, 2,500)


      
      if sfiletype="ACCOUNT" then
            if selfield = "1" then
                  sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " order by "& defaultfield &""
            else
                  sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " WHERE " & selfield &" LIKE '" & txtacctcode &"'"
            end if
      end if
      if sfiletype="LOCATION" then
            if selfield = "1" then
                  sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " WHERE CLCUCD ='"& custno &"' order by "& defaultfield &""
            else
                  sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " WHERE CLCUCD ='"& custno &"' and " & selfield &" LIKE '" & txtacctcode &"'"
            end if
      end if
      if sfiletype="EXCLUSIVE" then
            if selfield = "1" then
                  sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " WHERE CRCUCD ='"& custno &"' order by "& defaultfield &""
            else
                  sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " WHERE CRCUCD ='"& custno &"' and " & selfield &" LIKE '" & txtacctcode &"'"
            end if
      end if
      if sfiletype="REMIT" then
            if selfield = "1" then
                  sqlstate = "SELECT * FROM " & strTable & " order by "& defaultfield &""
            else
                  sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " WHERE " & selfield &" LIKE '" & txtacctcode &"'"
            end if
      end if
      if sfiletype="BILL" then
            if selfield = "1" then
                  if custno="1646" or custno="1650" then
                        sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " WHERE PTCUCD ='"& custno &"' order by "& defaultfield &""
                  else
                  sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " WHERE BTCUCD ='"& custno &"' order by "& defaultfield &""
                  end if
                  'sqlstate = "SELECT *  FROM CTSIPROD/" & strTable & " "
            else
                  if custno="1646" or custno="1650" then
                        sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " WHERE PTCUCD ='"& custno &"' and " & selfield &" LIKE '" & txtacctcode &"'"
                  else
                  sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " WHERE BTCUCD ='"& custno &"' and " & selfield &" LIKE '" & txtacctcode &"'"
                  end if
            end if
      end if

            
if strfilesource = "A" then
      
      ' Get Date from AS400
      
      SET ace = Server.CreateObject("ace400.ace400")
      SET ACEAcctRequest = ace.OpenDatabase("","","","USER=XPPOPER;PASSWORD=OWPX;MACHINE=IBM730")
      RsAcctDetail = sqlstate
      SET RsDetail = ACEAcctRequest.OpenRecordset(RsAcctDetail,dbReadonly)
      if selfield = " " OR selfield = "0" OR request("selfield")= "" then  selfield = defaultfield
      if txtacctcode = "%" then txtacctcode = defaultfieldvalue&"%"
      
      if selfield1 = "" or selfield1 = " " then
            displayfielddesc = defaultfield
      end if
else

      Set RsDetail = objCTSI.FetchRecord(sqlstate)
      if selfield = " " OR selfield = "0" OR request("selfield")= "" then  selfield = defaultfield
      if txtacctcode = "%" then txtacctcode = defaultfieldvalue&"%"
      
      if selfield1 = "" or selfield1 = " " then
            displayfielddesc = defaultfield
      end if
      
end if

      if selfield = "1" then
            finaldisplay = "All Records"
      else
            finaldisplay = displayfielddesc & " = " & txtacctcode
      end if


%>
<html>

<head>
<title>CTSI Master</title>
<link REL="stylesheet" TYPE="text/css" HREF="/SharedScript/csi_css.css">
</head>
<body>
<form method="POST" action="accloc.asp" id="form1" name="form1">
  <input type="hidden" name="ALcustno" value="<%=custno%>">
  <input type="hidden" name="filetype" value="<%=filetype%>">
  <table width="<%=session("screen_width")%>" class="clsBorder">
    <tr>
      <td class="clssubhead" align="center" width="100%"><%=finaldisplay%></td>
    </tr>

    <tr>
      <td class="clssubhead" align="center" width="100%"><a class="clsHREFActive" target="_blank" HREF="accloc2.asp?strMasterType=<%=sfiletype%>&txtacctcode=<%=txtacctcode%>&selfield=<%=selfield%>"><font color="white">Export to Excel</font></a></td>
    </tr>

  </table>
  <table class="clsBorder" WIDTH="<%=session("screen_width")%>">
      <tr>
<%'I NEED THE SORT ORDER IN THERE, THE HEADING%>
<%=strHeader%>
    </tr>
<%
      Do While Not RsDetail.Eof %>
    <tr>
<%      For I = 0 to RsDetail.Fields.count  -1
            if RsDetail.Fields(i).Value = "9/9/99" then
                  Response.Write ("<td class=""clsInput"" >1/1/00</td>")
            else      
        Response.Write ("<td class=""clsInput"" >"& RsDetail.Fields(i).Value &"</td>")
        end if
    Next
            RsDetail.MoveNext
      %>
    </tr>
<%      Loop
%>
  </table>
</form>
</body>
</html>
0
Comment
Question by:sylvia7707
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 30

Accepted Solution

by:
third earned 800 total points
ID: 6988036
this link gives you an example on how to sort data for every column,

http://www.w3schools.com/ado/showasp.asp?filename=demo_sort2
0
 

Author Comment

by:sylvia7707
ID: 6988505
third
thanks for the link, I have a question about the example. They hard code the Header, but my header is dynamic, how do I put the sort function in <%=strHeader%>
0
 

Author Comment

by:sylvia7707
ID: 6988527
Also, I need both Acen & desc order...
help..
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:mgfranz
ID: 6988731
This is really ugly code sylvia, (no offence), may I suggest the use of Select Case instead?

Sorting is not that difficult if you think abbout what you are sorting, data, columns, rows?

You are using Order By, but what about Asc or Desc?  I did something similar that mirrored Cnet a while back, I'll have to dig up the code...
0
 
LVL 10

Expert Comment

by:Dxpert
ID: 6988747
Hey sylvia, here some code...
I think it will help you get an idea. I also change your if statements for a select statement.

I didn't have time to test it, but i have it working on some page i made for work, so just let us know if you need help.

=:-)

<%
'------
'------

dim strFieldSort, strSortOrder, strNewSortOrder
dim strHeaderLinkPage, strHeaderLink, strHeaderLinkAdd
dim strFieldDesp

strFieldSort = trim(request("FIELD_SORT"))
strSortOrder = trim(request("SORT_ORDER"))

'------
' for first time these fields will be empty so u gotta set default
' here or later on your sql statement
'------

'------
'------

if strSortOrder = "asc" then
      strNewSortOrder = "desc"
else
      strNewSortOrder = "asc"
end if


'-----
'-----

strHeaderLinkPage = "thispage.asp?" & request.querystring

do while not RsAcct.EOF
    strTable = RsAcct("FileName")
    strfield = strfield&", "&RsAcct("fieldname")
      
      strFieldDesp = trim(RsAcct("FieldDesp"))

      
      if strFieldDesp = strFieldOrder then
            strHeaderLinkAdd = "&FIELD_SORT=" & strFieldSort & "&SORT_ORDER=" & strNewSortOrder
      else
            strHeaderLinkAdd = "&FIELD_SORT=" & strFieldDesp & "&SORT_ORDER=asc"
      end if
      
      strHeaderLink = "<a href=""" & strHeaderLinkPage & strHeaderLinkAdd & ">" & strFieldDesp & "</a>"
   
      
      
      'HERE CREATE THE HEADER, NEED SORT ORDER FOR EVERY COLUMN
    strHeader = strHeader & "<td class=""clsSubhead"">" & strHeaderLink  & "</td>"
    RsAcct.MoveNext
 Loop


 
 '-----
 'add the strFieldSort and strSortOrder in your sql statements to sort the records
 'like here your defaultfield could be
 'defaultfield = strFieldSort & " " & strSortOrder
 '----

 
 select case  sfiletype

       case "ACCOUNT"
          if selfield = "1" then
               sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " order by "& defaultfield &""
          else
               sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " WHERE " & selfield &" LIKE '" & txtacctcode &"'"
          end if

      case "LOCATION"
          if selfield = "1" then
               sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " WHERE CLCUCD ='"& custno &"' order by "& defaultfield &""
          else
               sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " WHERE CLCUCD ='"& custno &"' and " & selfield &" LIKE '" & txtacctcode &"'"
          end if
             
      case "EXCLUSIVE"
          if selfield = "1" then
               sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " WHERE CRCUCD ='"& custno &"' order by "& defaultfield &""
          else
               sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " WHERE CRCUCD ='"& custno &"' and " & selfield &" LIKE '" & txtacctcode &"'"
          end if
      
       case "REMIT"
          if selfield = "1" then
               sqlstate = "SELECT * FROM " & strTable & " order by "& defaultfield &""
          else
               sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " WHERE " & selfield &" LIKE '" & txtacctcode &"'"
          end if
      
      case "BILL"
          if selfield = "1" then
               if custno="1646" or custno="1650" then
                    sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " WHERE PTCUCD ='"& custno &"' order by "& defaultfield &""
               else
                     sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " WHERE BTCUCD ='"& custno &"' order by "& defaultfield &""      
               end if
               'sqlstate = "SELECT *  FROM CTSIPROD/" & strTable & " "
          else
               if custno="1646" or custno="1650" then
                    sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " WHERE PTCUCD ='"& custno &"' and " & selfield &" LIKE '" & txtacctcode &"'"
               else
                     sqlstate = "SELECT " & strfield1 &" FROM " & strTable & " WHERE BTCUCD ='"& custno &"' and " & selfield &" LIKE '" & txtacctcode &"'"
               end if
          end if
             
end select    

         
 
%>
0
 
LVL 10

Expert Comment

by:Dxpert
ID: 6988754
this line will not work 2nd time you load the page:

strHeaderLinkPage = "thispage.asp?" & request.querystring

You will have 2 instances of the sort_field and sort_order

You might want to add fields individualy.

=:-)
0
 

Author Comment

by:sylvia7707
ID: 6989041
thanks all
I figured it out from the demo page that third provided.

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question