Solved

recordset sort order

Posted on 2002-05-02
7
743 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 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
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!

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Index on a Table 6 35
send email part1 9 51
MS SQL and inserting dates into tables using stored procedures 6 44
Table header must be on top 2 52
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…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

734 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