Adding a valus to a querystring from DB

[ReportingAnalyst]

Hi,

I am trying to generate a querystring to a URL from a DB Value. Is this the right way of adding a querystring?

Thanks.

<a href="Organization_UL_Revised2.asp?ID="<%=aTopManagers(c_Owner_Payee, 0)%> class="a_style" onclick="s_Hide('<%=aTopManagers(c_Owner_Payee, 0)%>'); return false;"><%=aTopManagers(c_Mgr_Name, 0)%></a><font size=1>(<%=aTopManagers(c_Mgr_EmpID, 0)%>)</font>

 

[Tarwn]

Two corrections, you missed an ending quote on your address after the querystring and you don't need quotes inside your querystring.

<a href="Organization_UL_Revised2.asp?ID=[COLOR=red]"[/color]<%=aTopManagers(c_Owner_Payee, 0)%>[highlight]"[/highlight] class="a_style" onclick="s_Hide('<%=aTopManagers(c_Owner_Payee, 0)%>'); return false;"><%=aTopManagers(c_Mgr_Name, 0)%></a>

Take out the red one, add the yellow one.

-T

 

[Sheco]

Also you might need to use Server.URLEncode if [tt]aTopManagers(c_Owner_Payee, 0)[/tt] ever gives you spaces, question marks, or other characters that would fark up your QueryString.

 

[ReportingAnalyst]

I am trying to output my querystring to check if I am passing the manager's id to get his subordinates.

<ul id="menu">
		<li>
			<input type="radio" checked name="empid" id="empid<%=aTopManagers(c_Mgr_EmpID, 0)%>" value="<%=aTopManagers(c_Mgr_EmpID, 0)%>">
			<a href="Organization_UL_Revised2.asp?ID=<%=aTopManagers(c_Owner_Payee, 0)%>" class="a_style" onclick="s_Hide('<%=aTopManagers(c_Owner_Payee, 0)%>'); return false;"><%=aTopManagers(c_Mgr_Name, 0)%></a><font size=1>(<%=aTopManagers(c_Mgr_EmpID, 0)%>)</font>
			<ul id="UI_<%=aTopManagers(c_Owner_Payee, 0)%>" style="display: none">
			<%
			QS = Server.URLEncode(Request.QueryString("ID"))

			If QS <> "" Then
				Response.Write QS
				'Call GetSubordinates (managerId, Counter)
			Else
				Response.Write "No QS"
			End If
			
			%>
			</ul>
		</li>
	</ul>

When I click, I see the else part of the If-end if block executed where the text "No QS" is printed onto the screen. What is it am I missing that I am not able to see the manager id passed to the querystring.

Thanks.

 

[Sheco]

In the context of the code above, Request.QueryString("ID") will return the value of ID from the QueryString of the page that is currently being run.

So if the URL in the address bar of the current page is something like:

http://testserver/reports/test.asp?ID=foo

... then Request.QueryString("ID") will return "foo"

 

[ReportingAnalyst]

On Load of the page, I populate my SQL Query with the value which is strUI

<%
Dim sSQL
Dim rsMgr
set rsMgr = Server.CreateObject("ADODB.Recordset")

'Dim strUI
'strUI = 862336 'Bill McMonigal
'strUI = 2271274 'Barbara Bakich
'strUI = 2109827 'Britt Healey
strUI = 4655 'Bill Adkins
'strUI = 1472 'Reinhard Nagel
'strUI = 2271274 'Mark Zabroaske

'This SQL displays all the top level managers from the OrgStructure table
sSQL = "SELECT DISTINCT AMP_AllMySubordinates_Postings.Owner_Payee, " & _
       "AMP_AllMySubordinates_Postings.Owner_DateOfPost, " & _
       "Employee.FirstName + ' ' + employee.LastName AS Emp_Name, " & _
       "Employee.EmpID " & _
       "FROM AMP_AllMySubordinates_Postings " & _
       "INNER JOIN Employee ON " & _
       "AMP_AllMySubordinates_Postings.Owner_Payee = Employee.UniqueIdentifier " & _
       "WHERE AMP_AllMySubordinates_Postings.Owner_Payee = " & strUI & " " & _
       "AND AMP_AllMySubordinates_Postings.Owner_DateOfPost = " & _
       "(Select MAX(AMP_AllMySubordinates_Postings.Owner_DateOfPost) FROM Amp_AllMySubordinates_Postings WHERE owner_payee = " & strUI & ") "

rsMgr.open sSQL, Conn
Dim aTopManagers

If not rsMgr.EOF then
    aTopManagers = rsMgr.GetRows()

	'Close the Recordset object
	rsMgr.Close
	'Delete the Recordset Object
	Set rsMgr = Nothing

	'Declare Constants for the above SQL columns for better readability
	'Use these Constants instead of referring to the array numeric indexes
	Const c_Owner_Payee = 0
	Const c_DateOfPost = 1
	Const c_Mgr_Name = 2
	Const c_Mgr_EmpID = 3

	Dim iRows
	iRows = UBound(aTopManagers, 2)

	Dim managerId
	managerId = aTopManagers(c_Owner_Payee, 0)
	%>
	<ul id="menu">
		<li>
			<input type="radio" checked name="empid" id="empid<%=aTopManagers(c_Mgr_EmpID, 0)%>" value="<%=aTopManagers(c_Mgr_EmpID, 0)%>">
			<a href="Organization_UL_Revised2.asp?ID=<%=aTopManagers(c_Owner_Payee, 0)%>" class="a_style" onclick="s_Hide('<%=aTopManagers(c_Owner_Payee, 0)%>'); return false;"><%=aTopManagers(c_Mgr_Name, 0)%></a><font size=1>(<%=aTopManagers(c_Mgr_EmpID, 0)%>)</font>
			<ul id="UI_<%=aTopManagers(c_Owner_Payee, 0)%>" style="display: none">
			<%
			'QS = Server.URLEncode(Request.QueryString("ID"))
			QS = 4655

			If QS <> "" Then
				Response.Write QS
				'Call GetSubordinates (managerId, Counter)
			Else
				Response.Write "No QS"
			End If

			%>
			</ul>
		</li>
	</ul>
			<%
Else

So I am trying to display only the top most manager first. Based on the querystring value, I call the subordinates using the subordinates function.

 

[ReportingAnalyst]

I would also like to add that when I point the cursor over the link, I can see the URL follwed by 'ID=4655'.

So it means that my ID is grabbing the value from the recordset.

However, when I click on the link, it still executes the 'else' part of the block instead of 'if' part of the block. Why would that be so?

Thanks.

 

[ReportingAnalyst]

Can we do a request.querystring from within a same page?

Even though I can see the right ID of the person after th URL, I have a feeling that the Request.Querystring("ID") is not being grabbed because I am not re-loading the page. Hence the function never gets called.

How can I make the page load to submit the querystring to the function?

Frankly speaking I do not like refreshing of the page or reloading of the page, but once I figure this out, I shall try using AJAX to pass this querystring to get the subordinates. But that step is for later.

Thanks.

 

[ReportingAnalyst]

Any ideas how to submit the request.querystring value to the page ?

Thanks.

 

[Sheco]

The server-side code runs before the page is sent to the browser...

The purpose of ASP code is to dynamically modify the text of the HTTP Response returned to the browser.

The browser may recognize the text as HTML, XML, JavaScript, VBScript, CSS, etc but, to the ASP, it is all just text.

While the ASP is executing, it has access to the HTTP Request that caused it to execute. If there was a QueryString as part of the Request then you can see it with Request.QueryString. If there was a form you could get the values of its elements with Request.Form. You can see most of the header information by doing this:
<%
for each foo in request.servervariables
response.write foo & " = " & request.servervariables(foo) & "<br>" & vbCrLf
next
%>

 

[ReportingAnalyst]

OK. I can see the URL with the querystring. But still I am not able to see the subordinates because of a Javascript show and hide function which I used to expand and collapse the hierarchy.

THanks.

 

[ReportingAnalyst]

I wish I can get this working soon. Because if I get this on demand request for the empid and then call the function to build the list of subordinates for this manager on whom I clicked on, it will reduce my time drastically. It will come down from 32 secs to 1 sec even for the top most manager, for example, VP of sales.

My code here is:

<%
Dim sSQL
Dim rsMgr
set rsMgr = Server.CreateObject("ADODB.Recordset")

'Dim strUI
strUI = 1472

'This SQL displays all the top level managers from the OrgStructure table
sSQL = "SELECT DISTINCT AMP_AllMySubordinates_Postings.Owner_Payee, " & _
       "AMP_AllMySubordinates_Postings.Owner_DateOfPost, " & _
       "Employee.FirstName + ' ' + employee.LastName AS Emp_Name, " & _
       "Employee.EmpID " & _
       "FROM AMP_AllMySubordinates_Postings " & _
       "INNER JOIN Employee ON " & _
       "AMP_AllMySubordinates_Postings.Owner_Payee = Employee.UniqueIdentifier " & _
       "WHERE AMP_AllMySubordinates_Postings.Owner_Payee = " & strUI & " " & _
       "AND AMP_AllMySubordinates_Postings.Owner_DateOfPost In " & _
       "(Select MAX(AMP_AllMySubordinates_Postings.Owner_DateOfPos  t) FROM Amp_AllMySubordinates_Postings WHERE owner_payee = " & strUI & ");"


rsMgr.open sSQL, Conn
Dim aTopManagers

If not rsMgr.EOF then
    aTopManagers = rsMgr.GetRows()

	'Close the Recordset object
	rsMgr.Close
	'Delete the Recordset Object
	Set rsMgr = Nothing

	'Declare Constants for the above SQL columns for better readability
	'Use these Constants instead of referring to the array numeric indexes
	Const c_Owner_Payee = 0
	Const c_DateOfPost = 1
	Const c_Mgr_Name = 2
	Const c_Mgr_EmpID = 3

	Dim iRows
	iRows = UBound(aTopManagers, 2)

	Dim managerId
	managerId = aTopManagers(c_Owner_Payee, 0)
	%>
	<input type="radio" checked name="empid" id="empid" value="<%=aTopManagers(3, 0)%>" onClick="showReports(this.value)">
	<a href="Organization_QS.asp?ID=<%=aTopManagers(c_Owner_Payee, 0)%>" class="a_style" onclick="s_Hide('<%=aTopManagers(c_Owner_Payee, 0)%>'); return false;"><%=aTopManagers(c_Mgr_Name, 0)%></a><font size=1>(<%=aTopManagers(c_Mgr_EmpID, 0)%>)</font><br>
	<span id="UI_<%=aTopManagers(c_Owner_Payee, 0)%>" style="display: none">
	<input type="hidden" name="ID" id="ID_<%=aTopManagers(c_Owner_Payee, 0)%>" value=<%=Request.Querystring("ID")%>>
	<%
	ID_Val = Request.Querystring("ID")
	Response.Write ID_Val & "<br>"
	If ID_Val <> "" Then
                          'Call this function onclick of the above mgr
		Call GetSubordinates (ID_Val, Counter)	End If
	%>
	</span>
	<%
Else
	'Get the Employee Name from the Employee table.

	Dim rs1
	set rs1 = Server.CreateObject("ADODB.Recordset")

	sSQL = "Select UniqueIdentifier, EmpID, " & _
	       "Employee.FirstName + ' ' + employee.LastName AS Emp_Name " & _
	       "FROM Employee " & _
	       "WHERE Employee.UniqueIdentifier = " & strUI

	rs1.open sSQL, Conn

	If Not rs1.EOF Then
		%>
		<input type="radio" name="empid" id="empid" value="<%=rs1("EmpID")%>"><%=rs1("Emp_Name")%>
		<%
	Else
		Response.Write "You are not set up in the Commission Database"
	End If
	rs1.close
	set rs1 = nothing
End If

I know that this is not a Javascript forum, but thought you would like to know that the onclick event for the <a> tag is:

function s_Hide(el){
	objID = 'UI_'+el
	obj = document.getElementById(objID).style;
	(obj.display == 'none')? obj.display = 'block' : obj.display = 'none';

	document.form.submit();
}

What this Javascript function does is collapse and expand the hierarchial list which does beautifully.

However ASP builds the entire list of the org structure and then renders it to the HTML page. That building is 32 secs. So I thought that using a querystring would build only that many recordsets as required by the user. So when user clicks on a manager, only that manager's subordinates would be displayed.

Thanks.