OK...this may be a very easy question...that I just can't seem to figure out. In my code below, I am displaying sales information based on a date range. When the page is first displayed, a simple form asking for a start date and an end date is displayed. If the user does not fill one or both of the fields out, there is some VBScript that does some error handling and prompts the user to fill out the appropriate field. Once both fields are completed and submitted, the query runs fine and the report results are displayed in a table.
I am trying to add code to allow the user to be able to sort the data based on the column headers. I created a variable for my ORDER BY criteria and also set a default for when the report first runs. I have also added hyperlinks to each of the column headers (as seen in my code below). However, each time you click on the column header, the form refreshes...but I think my error handling scripts causes the date form to be displayed...instead of re-sorting the data. Can anyone help me figure out a way to get this to work??? I've posted my code below:
Any help would be greatly appreciated!
Thanks,
G
I am trying to add code to allow the user to be able to sort the data based on the column headers. I created a variable for my ORDER BY criteria and also set a default for when the report first runs. I have also added hyperlinks to each of the column headers (as seen in my code below). However, each time you click on the column header, the form refreshes...but I think my error handling scripts causes the date form to be displayed...instead of re-sorting the data. Can anyone help me figure out a way to get this to work??? I've posted my code below:
Code:
<%
If Request.QueryString("report")="lead_to_sold_time" Then
If Request.QueryString("error")="start_date" Then
errMsg="You must enter a start date.<br>Please try again."
End If
If Request.QueryString("error")="end_date" Then
errMsg="You must enter an end date.<br>Please try again."
End If
Response.Write "<form name=""lead_to_sold_time"" action=""reports.asp"" method=""post"">"
Response.Write "<table><tr><td colspan=""2"" align=""center""><b>CBS Connected Community<br>Sales Analysis Report</b></td></tr>"
Response.Write "<tr><td colspan=""2"" align=""center""><b>Please enter a date range.</b></td></tr>"
Response.Write "<tr><td colspan""2""> </td></tr>"
Response.Write "<tr><td colspan=""2"" align=""center"">" & errMsg & "</td></tr>"
' dynamic dropdown for city list starts here
'create recordset object
Response.Write "<tr><td align=""left""><b>Start Date:</b></td><td align=""left"">"
Response.Write "<input type=""text"" name=""start_date"" size=""10"">" & VbCrLf
Response.Write "</td></tr>" & VbCrLf
Response.Write "<tr><td align=""left""><b>End Date:</b></td><td align=""left"">"
Response.Write "<input type=""text"" name=""end_date"" size=""10"">" & VbCrLf
Response.Write "</td></tr>" & VbCrLf
Response.Write "<tr><td> </td><td align=""left""><input type=""submit"" name=""ltstimeBtn"" value="" Go ""></td></tr>"
Response.Write "</table></form>"
End If
If Request.Form("ltstimeBtn") <> "" Then
If Request.Form("start_date")="" Then
errMsg="You must enter a start date.<br>Please try again."
Response.Redirect("reports.asp?report=lead_to_sold_time&error=start_date")
Else If Request.Form("end_date")="" Then
errMsg="You must enter an end date.<br>Please try again."
Response.Redirect("reports.asp?report=lead_to_sold_time&error=end_date")
Else
date1=Request.Form("start_date")
date2=Request.Form("end_date")
End If
End If
sortOrder="test_request.soldDate"
If Request.QueryString("order")="by_salesID" Then
sortOrder="test_request.sales_num"
End If
'create recordset object
Set objRSLeadToSoldResults = Server.CreateObject("ADODB.Recordset")
sqlLeadToSoldResults = "SELECT test_request.sales_num, test_request.first_name, test_request.last_name, test_request.bus_name, test_request.curDate, test_request.soldDate, [test_request]![soldDate]-[test_request]![curDate] AS lts_time, test_request.ID_lead_status, install.install FROM (test_request INNER JOIN lead_status ON test_request.ID_lead_status = lead_status.ID_lead_status) INNER JOIN install ON test_request.ID_install = install.ID_install GROUP BY test_request.sales_num, test_request.first_name, test_request.last_name, test_request.bus_name, test_request.curDate, test_request.soldDate, [test_request]![soldDate]-[test_request]![curDate], test_request.ID_lead_status, install.install HAVING (((test_request.soldDate) Between #" & date1 & "# And #" & date2 & "#) AND ((test_request.ID_lead_status)=1 Or (test_request.ID_lead_status)=2)) ORDER BY " & sortOrder
'open the recordset
set objRSLeadToSoldResults = objConn.Execute(sqlLeadToSoldResults)
'open the recordset
'objRSDateCntResults.Open sqlDateCntResults, objConn, 2, 3
If objRSLeadToSoldResults.EOF OR objRSLeadToSoldResults.BOF Then
Response.Write "There are no records in the database<br>in the date range you selected."
Else
objRSLeadToSoldResults.MoveFirst
Response.Write "CBS Connected Community Sales Analysis Report<br>for leads received between <b>" & date1 & "</b> and <b>" & date2 & "</b><br><br>"
Response.Write "<table border=""0"" bgcolor=""#003399"">"
Response.Write "[b]<tr bgcolor=""#ffffff""><td width=""75"" align=""left""><b><a href=""[URL unfurl="true"]http://www.cbsconnected.com/includes/reports.asp?report=lead_to_sold_time&start_date="[/URL] & date1 & "&end_date=" & date2 & "&error=none&order=by_salesID"">Sales ID #:</a></b></td>[/b]<td width=""150"" align=""left""><b>Employee Name:</b></td><td width=""150"" align=""left""><b>Business Name:</b></td><td width=""85"" align=""left""><b>Date Received:</b></td><td width=""85"" align=""left""><b>Date Sold:</b><td width=""150"" align=""left""><b>Lead Sold:</b></td></td><td width=""100"" align=""left""><b>Days in Pipeline:</b></td><td width=""100"" align=""center""><b>Installed:</b></td>"
'Do While Not objRSDateSrchResults.EOF
'initialize counters
y = 1
Do While Not objRSLeadToSoldResults.EOF
If (y MOD 2 = 0 ) Then
varColor = "#ffffff"
Else
varColor = "#F0F0F0"
End If
Response.Write "<tr bgcolor=" & varColor & "><td width=""75"" align=""center"">" & objRSLeadToSoldResults("sales_num") & "</td>"
Response.Write "<td width=""150"" align=""left"">" & objRSLeadToSoldResults("first_name") & " " & objRSLeadToSoldResults("last_name") & "</td>"
Response.Write "<td width=""150"" align=""left"">" & objRSLeadToSoldResults("bus_name") & "</td>"
Response.Write "<td width=""75"" align=""left"">" & objRSLeadToSoldResults("curDate") & "</td>"
Response.Write "<td width=""75"" align=""left"">" & objRSLeadToSoldResults("soldDate") & "</td>"
If objRSLeadToSoldResults("ID_lead_status")=1 Then
Response.Write "<td width=""150"" align=""left"">New Customer - $50</td>"
Else If objRSLeadToSoldResults("ID_lead_status")=2 Then
Response.Write "<td width=""150"" align=""left"">Existing Customer - $25</td>"
Else
Response.Write "<td width=""150"" align=""left"">N/A</td>"
End If
End If
Response.Write "<td width=""100"" align=""center"">" & objRSLeadToSoldResults("lts_time") & "</td>"
Response.Write "<td width=""100"" align=""center"">" & objRSLeadToSoldResults("install") & "</td></tr>"
'move to the next record in the recordset
objRSLeadToSoldResults.MoveNext
'Loop
'increment the counter used to vary the row colors
y = y + 1
Loop
'---------------------------------
(rest of code for page)
%>
Any help would be greatly appreciated!
Thanks,
G