Simple Querystring...How to pass the querystring?

[ReportingAnalyst]

Hi,

This is a simple Northwind based ASP page which I created to show the top level categories. Now I would like to show the products based on the querystring value.

How can I do that?

Thanks.

<%

Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
sSQL = "SELECT CategoryID, CategoryName FROM Categories order by CategoryName"

rs.open sSQL, Conn
'Response.Write sSQL

Dim aCategory

If not rs.EOF Then
	'Dump the recordset into the above array
	aCategory = rs.getRows()

	rs.Close
	Set rs = Nothing

	Dim iRows

	For iRows = 0 to UBound(aCategory, 2)
	CatID = aCategory(0, iRows)
	%>
	<ul id="menu">
		<li>
			<input type="radio" checked name="empid" id="Catid<%=aCategory(0, iRows)%>" value="<%=aCategory(0, iRows)%>">
			<a href="Categories1.asp?ID=<%=aCategory(0, iRows)%>" class="a_style" onclick="s_Hide('<%=aCategory(0, iRows)%>'); return false;"><%=aCategory(1, iRows)%></a>
			<ul id="UI_<%=aCategory(0, iRows)%>" style="display: none">
			[b]<%
			'On load of the page, we see only the top level categories.
			'When I point my cursor on the link, then I can see the querystring with
			'the category id affixed to the URL.
			'Only when we click on a category, the following function needs to be called.
			'How to do that?
			
			QS = Request.Querystring("ID")

			If QS <> "" Then
				'Now pass the querystring to the following function.
				Call GetProducts (QS, Counter)
			End If
			%>[/b]
			</ul>
		</li>
	</ul>
	<%
	Next 'iRows
End If
%>

 

[Sheco]

You could use a single Active Server Page to show either all rows or just the row specified by the QueryString.

Something like this:
[tt]
<%
sSQL = "SELECT CategoryID, CategoryName FROM Categories "

If Request.Querystring("ID") <> "" Then
sSQL = sSQL = "WHERE CategoryID = '" & Request.Querystring("ID") & "' "
End If

sSQL = sSQL & "ORDER BY CategoryName"


Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
rs.open sSQL, Conn


'etc.....
[/tt]

 

[ReportingAnalyst]

But then I have shown all the top level categories. Only when I click on the link, I want to show the products for that category using a function. I know for something as small as this, we do not need a function. But I want to incorporate this concept for another concept in the live application.

Thanks.

 

[Sheco]

Why a function?

Do you want to avoid having the page reload?

 

[ReportingAnalyst]

Yes. I already wrote something like this and wanted to include that functionality like this. So first rying with this sample data. Is this possible?

With a click on the Category URL, I submit the ID to the function.

Or did you mean getting the products written in an entirely different page and called a SSI.

THanks.

 

[Sheco]

If you want to do it without reloading the page you've got 2 choices:

A) Send all the data to the page when it is first loaded and use client-side script to show or hide portions of the data as needed.

B) Use client-side script to create an instance of the XMLHTTPRequest object. Use the object to request updates from the web server. Then use client-side script to show/hide the Response. Its like a remote procedure call. AJAX is the cute name for this technique but all browsers are capable of supporting it.


Other than than you'll need to reload the page.

 

[ReportingAnalyst]

Hi Sheco,

I use Option A right now. But with a large database, It is taking a long time 32 secons for getting the hierarchial structure of the top most node. I use client side Javascript to show the branches or hide the branches. So my page does not re-load.

Looks like I will have to try AJAX for this.

Thanks for your suggestions.

 

[ReportingAnalyst]

Hi,

I am able to pass the querystring to the Function - getProducts(CatID, Counter)

When I click on the link - Beverage, then I am able to see the products under the category.

As per your suggestion regarding using AJAX, I was trying to use it to expand the clicked category, but how do I use the same onClick event to show and hide the UL tags?

My javascript is:

<script type="text/javascript">
var xmlHttp

function showProducts(str)
{
xmlHttp=GetXmlHttpObject()
if (xmlHttp==null)
{
alert ("Browser does not support HTTP Request")
return
}

var url="Categories_dynamic.asp"
url=url+"?q="+str
url=url+"&sid="+Math.random()
xmlHttp.onreadystatechange=stateChanged
xmlHttp.open("GET",url,true)
xmlHttp.send(null)
}

function stateChanged()
{
if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete")
{
[b]//get the element ID of this UL tag which I would like to show in the following getElementById - how to do that?[/b]

document.getElementById[b]("content_products")[/b].innerHTML=xmlHttp.responseText
}
}

function GetXmlHttpObject()
{
var objXMLHttp=null
if (window.XMLHttpRequest) //Safari, Mozilla browers
{
	objXMLHttp=new XMLHttpRequest()
}
else if (window.ActiveXObject) //IE browsers
{
	objXMLHttp=new ActiveXObject("Microsoft.XMLHTTP")
}
return objXMLHttp
}

[b]//How can I associate 2 functions with the onClick event 
//If I click, it should do remote call to get the products.
//If exapnded, it should collapse and vice-versa.[/b]
function s_Hide(el){
	objID = 'UI_'+el;
	obj = document.getElementById(objID).style;
	(obj.display == 'none')? obj.display = 'block' : obj.display = 'none';
}

My ASP code is:

<%

Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
sSQL = "SELECT CategoryID, CategoryName FROM Categories WHERE CategoryID = 1"

rs.open sSQL, Conn
'Response.Write sSQL

Dim aCategory

If not rs.EOF Then
	'Dump the recordset into the above array
	aCategory = rs.getRows()

	rs.Close
	Set rs = Nothing

	Dim iRows

	For iRows = 0 to UBound(aCategory, 2)
	CatID = aCategory(0, iRows)
	%>
	<ul id="menu">
		<li>
			<input type="radio" checked name="prodid" id="Catid<%=aCategory(0, iRows)%>" value="<%=aCategory(0, iRows)%>">
			<a href="Categories_dynamic.asp?ID=<%=aCategory(0, iRows)%>" class="a_style" [b]onclick="showProducts(<%=aCategory(0, iRows)%>)"[/b]><%=aCategory(1, iRows)%></a>
			<ul id="UI_<%=aCategory(0, iRows)%>" style="display: ;">
			<%
			QS = Request.Querystring("ID")

			'Now pass the querystring to the following function.
			if QS = trim(aCategory(0, iRows)) then
				Call GetProducts (QS, Counter)
			end if
			%>
			</ul>
		</li>
	</ul>
	<%
	Next 'iRows
End If
%>

 

[Abraxus]

To call two functions with the onClick event you have two options:

1. You could call s_Hide at the end of the showProducts function

2. You could write your event like this:

onclick="showProducts(<%=aCategory(0, iRows)%>)[COLOR=red];s_Hide(this);[/color]"

 

[ReportingAnalyst]

I am still not able to get this working.

I can see the Products for the category in the page source of the page, but not on the browser.

What else can I do?

Thanks.

 

[ReportingAnalyst]

This is the entire code. Please let me know what I am doing wrong.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/html4/loose.dtd">[/URL]
<html>
<%
Dim conn

Set conn = Server.CreateObject("ADODB.Connection")

Conn.open "PROVIDER=SQLOLEDB;DATA SOURCE=ServerName;UID=dbo;PWD=password;DATABASE=Northwind "

'If Conn.State = 0 Then
'  Response.Write "The ADO Connection is closed."
'  Response.End
'End If

'If Conn.State = 1 Then
'  Response.Write "The ADO Connection is open."
'  Response.End
'End If

%>

<head>
<style type="text/css">
* {
   border: 0;
   padding: 0;
   margin: 0;
}

#menu {
  padding:0;
  margin:0;
  }
#menu li {
  list-style-type:none;
  }

#menu ul {
padding: 0;
margin: 6px;
list-style-type: none;
}

a.a_style:link {color:#0000ff; text-decoration:none;}
a.a_style:visited {color:#0000ff; text-decoration:none;}
a.a_style:hover {color:#ff0000; text-decoration:underline;}
a.a_style:hover {color:#ff0000; text-decoration:underline;}

</style>

<script type="text/javascript">

var xmlHttp

function showProducts(str)
{
xmlHttp=GetXmlHttpObject()
if (xmlHttp==null)
{
alert ("Browser does not support HTTP Request")
return
}

var url="Categories_dynamic.asp"
url=url+"?ID="+str
alert(url);
//url=url+"&sid="+Math.random()
xmlHttp.onreadystatechange=stateChanged
xmlHttp.open("GET",url,true)
xmlHttp.send(null)
}

function stateChanged()
{
	if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete")
	{
		objID = 'UI_'+el;
		document.getElementById(objID).innerHTML=xmlHttp.responseText
		//document.getElementById("content_products").innerHTML=xmlHttp.responseText
	}
}

function GetXmlHttpObject()
{
var objXMLHttp=null
if (window.XMLHttpRequest) //Safari, Mozilla browers
{
	objXMLHttp=new XMLHttpRequest()
}
else if (window.ActiveXObject) //IE browsers
{
	objXMLHttp=new ActiveXObject("Microsoft.XMLHTTP")
}
return objXMLHttp
}

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

</script>

<title>On Demand Building of tree with User OnClick</title>
</head>

<body>

<form name="Customers" id="Customers" action="">
<%
startTime = Now()

'get the Products using a function

Function GetProducts(CatID, Counter)

		sql = "SELECT CategoryID, ProductName, ProductID FROM Products " & _
		      "where CategoryID = " & CatID & " " & _
		      "order by ProductName "

		'Response.Write sql
		'Response.End

		Dim rs2
		set rs2 = Server.CreateObject("ADODB.Recordset")
		rs2.open sql, Conn

		Dim aProducts

		If not rs2.EOF then

			aProducts = rs2.GetRows()

			'Close Recordset to use the new array with the 2 columns data
			rs2.Close()
			set rs2 = Nothing

			'Declare Constants for the above SQL columns for better readability
			'Use these Constants instead of referring to the array numeric indexes
			Const c_CatID = 0
			Const c_ProductName = 1
			Const c_ProductID = 2

			'Ubound(MyArray,1) 'Returns the Number of Columns
			'Ubound(MyArray,2) 'Returns the Number of Rows

			Dim iRowLoop

			For iRowLoop = 0 to UBound(aProducts, 2)
			%>
			<li>
					<input type="radio" name="prodid" id="prodid<%=aProducts(c_ProductID,iRowLoop)%>" value="<%=aProducts(c_ProductID,iRowLoop)%>">
					<%=aProducts(c_ProductName,iRowLoop)%>
					<%
					Counter = Counter + 1
					Counter = Counter - 1
					%>

			</li>
			<%
			Next 'iRowLoop
		End If 'rs2.EOF

End Function
%>
<%

Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
sSQL = "SELECT CategoryID, CategoryName FROM Categories WHERE CategoryID = 1"

rs.open sSQL, Conn
'Response.Write sSQL

Dim aCategory

If not rs.EOF Then
    'Dump the recordset into the above array
    aCategory = rs.getRows()

    rs.Close
    Set rs = Nothing

    Dim iRows

    For iRows = 0 to UBound(aCategory, 2)
    CatID = aCategory(0, iRows)
    %>
    <ul id="menu">
        <li>
            <input type="radio" checked name="prodid" id="Catid<%=aCategory(0, iRows)%>" value="<%=aCategory(0, iRows)%>">
            <a href="Categories_dynamic.asp?ID=<%=aCategory(0, iRows)%>" class="a_style" onclick="showProducts(<%=aCategory(0, iRows)%>);s_Hide(this);"><%=aCategory(1, iRows)%></a>
            <ul id="UI_<%=aCategory(0, iRows)%>" style="display:none;">
            <%
            QS = Request.Querystring("ID")

            'Now pass the querystring to the following function.
            if QS = trim(aCategory(0, iRows)) then
                Call GetProducts (QS, Counter)
            end if
            %>
            </ul>
        </li>
    </ul>
    <%
    Next 'iRows
End If
%>
</form>
</body>
</html>
<%
Conn.Close
Set Conn = Nothing
%>