Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 25th, 2008, 10:39 AM
Registered User
 
Join Date: Jul 2008
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using Buttons In Classic ASP



I am a newbie to the CLASSIC flavor of ASP. I have been wracking my brain with this to no avail. This should be very simple for someone proficient with CLASSIC ASP. I have tried this many different ways. The actual code is below.

The form has a drop down box and two buttons.

A user will select a person from the drop down box.
Then they will click on the Condensed OR Full Button.
This generates an EXCEL spreadsheet from a SQL statement.


We need to run different SQL code based on which button is clicked. That's It!!!!!

How would I do this in Classic ASP??

YOUR HELP IS GREATLY APPRECIATED!!!!


***************The Actual Code Is Below*******************

<%

'printSQL = -1



st = timer

Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3
Const adCmdText = &H0001

server.scriptTimeout = 3600
Session.Timeout = 60

DIM SLM
SLM = REQUEST("SLM")
NAM = REQUEST("NAM")

If len(SLM)<>4 AND len(SLM)<>6 Then
    SLM = ""
End If

If len(SLM) = 6 Then
    LTR = mid(SLM,6,1)
    SLM = mid(SLM,1,4)
End If


strDB = SESSION("STRDB")
Set con = CreateObject("ADODB.Connection")
con.Open strDB
Set rs = CreateObject("ADODB.Recordset")

If printSQL Then response.write "ORA_SLMS_ALL=" & SESSION("ORA_SLMS_ALL") & "" End If

SQL = "SELECT TO_CHAR(ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(SYSDATE,' MON-YYYY'),'MON-YYYY'),'YEAR'),-48),'YYYY') START_YEAR FROM DUAL"
If printSQL Then Response.Write SQL & "" End If
Set rs = con.Execute(SQL)
START_YEAR = RS("START_YEAR") - 1

SQL = "SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') TODAY FROM DUAL"
If printSQL Then Response.Write SQL & "" End If
Set rs = con.Execute(SQL)
TODAY = RS("TODAY")
CURRENT_DAY = cint(MID(TODAY,4,2)) - 1
CURRENT_MONTH = cint(MID(TODAY,1,2))

SQL = "SELECT TO_CHAR(LAST_DAY(SYSDATE),'MM/DD/YYYY') LAST_DAY FROM DUAL"
If printSQL Then Response.Write SQL & "" End If
Set rs = con.Execute(SQL)
LAST_DAY = cint(mid(RS("LAST_DAY"),4,2))


If printSQL Then Response.Write "START_YEAR=" & START_YEAR & " TODAY=" & TODAY & " CURRENT_DAY=" & CURRENT_DAY & " LAST_DAY=" & LAST_DAY & "" End If

If printSQL then SESSION("TERRITORY") = "" End If

' SQL BELOW returns Territory Number & Name
 ' Example = 0027 & McKenna, Bret
If SESSION("TERRITORY") = "" Then
    SQL = "SELECT DISTINCT TERRITORY, SUBSTR(JRS.NAME,1,LEAST(INSTR(JRS.NAME||'-','-')-1,25)) NAME FROM APPS.WEB_SALES_HISTORY_SLM_MV WSS, " & _
        "AR.RA_TERRITORIES RT, AR.RA_SALESREP_TERRITORIES RST, JTF.JTF_RS_SALESREPS JRS " & _
        "WHERE WSS.TERRITORY=RT.SEGMENT1 " & _
        "AND RT.TERRITORY_ID=RST.TERRITORY_ID " & _
        "AND RST.SALESREP_ID=JRS.SALESREP_ID " & _
        "AND RST.END_DATE_ACTIVE IS NULL "

    If SESSION("USER") <> "TERRITORYSALES" Then
        SQL = SQL & "AND RT.SEGMENT1 IN (" & session("ORA_SLMS") & ") "
    End If

    SQL = SQL & "AND RT.SEGMENT2='ALL' " & _
        "ORDER BY TERRITORY"

    If printSQL Then Response.Write SQL & "" End If
    Set rs = con.Execute(SQL)
    Do While Not rs.EOF
         SLM_CHECK = rs("TERRITORY")
        If SLM_OK(SLM_CHECK) Then
            If instr(LOADED,"/" & SLM_CHECK & "/") = 0 Then
                inBuffer = inBuffer + "<SLMS><SLM>" & rs("TERRITORY") & "</SLM><NAM>" & clean(rs("NAME")) & "</NAM></SLMS>"
                LOADED = LOADED & "/" & SLM_CHECK & "/"
            End If
            If printSQL Then response.write "SLM_CHECK=" & SLM_CHECK & "" End IF
        End If
        rs.MoveNext
    Loop

 ' SQL BELOW returns Territory Number & Letter_Code & Name
 ' Example = 0027 & A & McKenna, Bret
    SQL = "SELECT DISTINCT TERRITORY, LETTER_CODE, SUBSTR(JRS.NAME,1,LEAST(INSTR(JRS.NAME||'-','-')-1,25)) NAME FROM APPS.WEB_SALES_HISTORY_SLM_MV WSS, " & _
            "AR.RA_TERRITORIES RT, AR.RA_SALESREP_TERRITORIES RST, JTF.JTF_RS_SALESREPS JRS " & _
            "WHERE WSS.TERRITORY=RT.SEGMENT1 " & _
            "AND RT.TERRITORY_ID=RST.TERRITORY_ID " & _
            "AND RST.SALESREP_ID=JRS.SALESREP_ID " & _
            "AND RST.END_DATE_ACTIVE IS NULL " & _
            "AND RT.SEGMENT1 IN (" & session("ORA_SLMS") & ") " & _
            "AND RT.SEGMENT2='ALL' " & _
            "ORDER BY TERRITORY, LETTER_CODE"

    If printSQL Then Response.Write SQL & "" End If
    Set rs = con.Execute(SQL)

    Do While Not rs.EOF
        SLM_CHECK = rs("TERRITORY") & rs("LETTER_CODE")
        If SLM_OK(SLM_CHECK) Then
            If instr(LOADED,"/" & SLM_CHECK & "/") = 0 Then
                inBuffer = inBuffer + "<SLMS><SLM>" & rs("TERRITORY") & "-" & rs("LETTER_CODE") & "</SLM><NAM>" & clean(rs("NAME")) & "</NAM></SLMS>"
                LOADED = LOADED & "/" & SLM_CHECK & "/"
            End If
' If printSQL Then response.write "SLM_CHECK=" & SLM_CHECK & "" End IF
        End If
        rs.MoveNext
    Loop

    SESSION("TERRITORY") = inBuffer
Else
    Inbuffer = SESSION("TERRITORY")
End If



'If printSQL Then Response.Write inbuffer & "" End If





If SLM<>"" Then
    SQL = SQL & " AND TERRITORY='" & SLM & "'"
End If

If LTR <> "" Then
    SQL = SQL & " AND LETTER_CODE='" & LTR & "'"
End If


If SLM<>"" Then


    SQL = "SELECT * FROM PO.PO_VENDORS WHERE VENDOR_ID<10"
    rs.close
    rs.CursorLocation = adUseClient
    rs.Open SQL, con, adOpenForwardOnly, adLockReadOnly, adCmdText


    If rs.RecordCount > 65000 Then
        response.write "<H1>" & rs.RecordCount & " Records found<br>Maximum records Exceeded<br>Retry Query with a smaller Ranges</H1>"
        response.end
    End If

    If Not rs.EOF Then
        call outxls(sql,outfile)
        response.end
    Else
        Message="No Data Found"
    End If



END If


SQL = "INSERT INTO XKFI.WEB_TRACKING (TRACKING_ID, TRACKING_DATE, USER_NAME, TRACKING_ACTION, ATTRIBUTE1,ORG_ID) " & _
    "VALUES (XKFI.WEB_TRACKING_S.NEXTVAL,SYSDATE,'" & SESSION("USER") & "','SALES_HISTORY','" & SLM&LTR & "'," & SESSION("ORG_ID") & ")"
If printSQL then Response.Write SQL & "" End IF
rs = con.Execute(SQL)

Set rs = Nothing

con.close
Set con = Nothing



inBuffer = "<SALESMAN><NAME>" & SLM & LTR & " - " & NAM & "</NAME>" & inBuffer & TMPA & "</SALESMAN>"

'RESPONSE.WRITE inbuffer
'response.end

dim styleFile
styleFile = Server.MapPath("SLMTEST.XSL")

dim source
set source = Server.CreateObject("Microsoft.XMLDOM")
source.async = false
source.loadXML(inBuffer)

dim style
set style = Server.CreateObject("Microsoft.XMLDOM")
style.async = false
style.load(styleFile)

result = source.transformNode(style)

Response.Write(result)


Private Function SLM_OK(Src)
    TMPOK = 0
    TMPA = split(REPLACE(MID(SESSION("ORA_SLMS_ALL"),2),"//","/"),"/")
    TMPN = UBound(TMPA)
    For TMPL = 0 to TMPN - 1
' If printSQL Then RESPONSE.WRITE "SRC = " & SRC & "-" & TMPA(TMPL) & " - " & tmpn & "" End If
        If TMPA(TMPL) = mid(SRC,1,len(TMPA(TMPL))) Then
            TMPOK = -1
        End If
    Next
    SLM_OK = TMPOK

End Function

sub outxls(sql,outfile)

    Response.ContentType = "application/vnd.ms-excel"
    response.write "<table BORDER=1>"

    outstr=""
    for Each rf In rs.Fields
        outstr = outstr & "<TD NOWRAP=TRUE BGCOLOR=#DDDDDD><B>" & rf.name & "</B></TD>"
    Next
    response.Write "<tr>" & outstr & "</tr>"

' outstr=""
' for Each rf In rs.Fields
' outstr = outstr & "<TD NOWRAP=TRUE>" & rf.type & "</TD>"
' Next
' response.Write "<tr>" & outstr & "</tr>"


    While not rs.eof
        outstr=""
        For Each rf In rs.Fields
            tmp = rs(rf.Name)
            If rf.type = "5" then
                outstr = outstr & "<TD NOWRAP=TRUE>" & tmp & "</TD>"
            Else
                outstr = outstr & "<TD NOWRAP=TRUE>&nbsp;" & tmp & "</TD>"
            End If
        Next
        response.Write "<tr>" & outstr & "</tr>"
    rs.MoveNext
    Wend
    rs.close
    set rs = nothing

    response.write "</table>"

end sub

%>


<body>

<BODY onLoad="document.frm.book.focus()">


<table border="0" width=670 cellspacing="0" bgcolor="white" cellpadding="0" align="left">
<FORM id="reporttype" NAME="frm" ACTION="" METHOD="post">
<tr>
</tr>
<tr><input type="SUBMIT" value="Condensed" size="20">
<input type="SUBMIT" value="Full" size="20">
               <td> &nbsp; </td>
             <tr>
         <td>


<tr>
<td>

</tr>
<tr>
               <td> &nbsp; </td>
             <tr>



<TR><TD COLSPAN=3><TD></TR>
</FORM>
</table>













Similar Threads
Thread Thread Starter Forum Replies Last Post
ASP Classic ab289 Classic ASP Basics 6 March 10th, 2008 02:53 PM
Converting Classic Asp to Asp.Net 2.0 vikaspatyal ASP.NET 2.0 Professional 2 October 7th, 2007 06:33 PM
ASP classic looping twice melkin Classic ASP Basics 2 September 6th, 2007 01:00 PM
asp.net vs classic asp "URGENT" naeem_ul_hussan Classic ASP Professional 1 August 1st, 2007 03:14 PM
classic asp with asp.net on same iis bostonrose .NET Framework 2.0 6 January 10th, 2007 12:38 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.