using column headers to re-sort query results

[gchaves]

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:

			<%

			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"">&nbsp;</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>&nbsp;</td><td align=""left""><input type=""submit"" name=""ltstimeBtn"" value=""&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Go&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;""></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

 

[Sheco]

Well your form submits with a POST but if you use a link then it will be a GET.

If you want to keep using the links then you need to copy all of the values needed to for your page to function into the URL's QueryString.

Another way to go about it would be to make the column headers into submit buttons.

 

[SecondToNone]

Try this:

once the user enters both the dates save these values to two session variables...

Session("start_date")=Request.Form("start_date")
Session("end_date")=Request.Form("end_date")

then check on these check variables...when the results are displayed for the first time and user just wants to sort...the request.form("start_date") and request.form("start_date") will become null but the session variables still retain values...

so put the condition as if session variables are not null then just go to sort function..

Hope you got it...

-SecondToNone