• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 751
  • Last Modified:

recordset sort order

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
sylvia7707
Asked:
sylvia7707
1 Solution
 
thirdCommented:
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
 
sylvia7707Author Commented:
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
 
sylvia7707Author Commented:
Also, I need both Acen & desc order...
help..
0
Independent Software Vendors: 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!

 
mgfranzCommented:
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
 
DxpertCommented:
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
 
DxpertCommented:
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
 
sylvia7707Author Commented:
thanks all
I figured it out from the demo page that third provided.

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now