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<R & "'," & 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> " & 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> </td>
<tr>
<td>
<tr>
<td>
</tr>
<tr>
<td> </td>
<tr>
<TR><TD COLSPAN=3><TD></TR>
</FORM>
</table>
Using Buttons In Classic ASP
