<% 'create recordset to show database categories and subcategories Dim subcatRS, RS, rsCatalog, subcatSQL, SQL, priorCatalog, curCatalog, catalogID, catalogName, subcatalogName, subcatalogID Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open ConString 'check to see if there are any subcatalogs subcatSQL = "SELECT * FROM subCatalogs ORDER BY subcatalogID ASC" Set subcatRS = Conn.Execute( subcatSQL ) If subcatRS.BOF or subcatRS.EOF then Set subcatRS = Nothing 'if there are no subcatalogs create recordset to show categories only Set rsCatalog = Conn.Execute("SELECT * FROM catalogs ORDER BY catalogID ASC") While Not rsCatalog.EOF %> <li><font face="Arial" size="4"><b> <a href="productsByCategory.asp?intCatalogID=<% Response.Write rsCatalog("catalogID") %>&strCatalog_NAME=<% Response.Write Server.URLEncode(rsCatalog("catalogName"))%>"> <%= rsCatalog("catalogName") %></a></b></font></li> <% rsCatalog.MoveNext Wend rsCatalog.Close Set rsCatalog = Nothing else ' if there are subcatalogs, create recordset to show categories and subcategories (NOTE: you will need a subcategory to display a category) SQL = "SELECT * FROM catalogs,subcatalogs " _ & "WHERE subcatalogs.catalogID = catalogs.catalogID " _ & "ORDER BY catalogs.catalogName, subcatalogs.subcatalogName" Set RS = Conn.Execute( SQL ) priorCatalog = "" Do Until RS.EOF curCatalog = RS("catalogName") catalogID = RS("catalogs.catalogID") catalogName = RS("catalogName") subcatalogName = RS("subcatalogName") subcatalogID = RS("subcatalogID") If curCatalog <> priorCatalog Then If priorCatalog <> "" Then Response.Write "</ul>" End If Response.Write "<font face='Arial' size='4'><b><a href='productsByCategory.asp?intCatalogID=" Response.Write catalogID & "&strCatalog_NAME=" Response.Write Server.URLEncode(catalogName) & "'>" Response.Write curCatalog & "</a></b></font><ul>" priorCatalog = curCatalog End If Response.Write "<font face='Arial' size='1'><b><a href='productsByCategory.asp?strSubCatalogID=" Response.Write subcatalogID & "&curCatalogID=" Response.Write catalogID & "&strSubCatalog_NAME=" Response.Write Server.URLEncode(subcatalogName) & "'>" Response.Write subcatalogName & "</a></b></font><br>" RS.MoveNext Loop Response.Write "</ul>" ' clean up the tags! RS.Close Set RS = Nothing End If %>