Solved

recordset sort order

Posted on 2002-05-02
7
736 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
7 Comments
 
LVL 30

Accepted Solution

by:
third earned 200 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now