|
| |
|
|
whitec00
Posts: 32
From: Dallas Texas USA
Status: offline
 |
Using SQL DB for a Menu System - 6/13/2005 15:23:16
I have created a menu system for my web application and am using it with the Spooky Login database (for access level control).
The app is an internal company app so I cannot post the link...Sorry ( ).
Anyway... I can post the fields and tell you the logic behind it...
MenuName - Used to display on the actual menu
MenuCategory - The main category that the menu item falls under. When you see the menu, you only see the main category items. You do not see sub category menu items until you click on the main item.
subMenuCategory - Same as above but for the sub menu items.
Description - just for my benefit in case I come up with alzheimers
SortOrder - Determines the order in which items are displayed on the menu
AccessLevel - Determines who has access to what menu item (based on a query)
Link - Tells HTML where to go when clicked.
LinkDest - Tells HTML what target window to launch the link in (using frames)
MenuStatus - Tells wheter or not the menu can be seen at all (based on query)
The problem I am having is with my query statement to access the menu items.
If I have an access level of 5, but I set a particular menu item to level 4, when I access the menu, it shows me not only the main items, but the sub-category item that is set to level 4. But I do not want to see that item unless I click on the main menu item.
Here is my query statement from within ASP...
"SELECT * FROM MenuSystem WHERE (MenuCategory = '::Selection::' OR MenuCategory = 'main')AND (MenuStatus = 'Active')AND AccessLevel = '" &session("NEWCTS_AccessLevel")& "' OR AccessLevel < '" &session("NEWCTS_AccessLevel")& "' ORDER BY SortOrder ASC"
Below is the HTML that this query statement is in, just in case someone wants to try and re-create my scenario.
<html>
<head>
<title>AccuClaim Plus Customer Management</title>
<base target="_top">
<meta http-equiv="AccountRep" content="<%=Request("UserName")%>">
</head>
<body stylesrc="../PageConfig/navbar_defaults.htm">
<table border="0" width="100%">
<tr>
<td width="100%">
<p align="center"><b><font size="2" face="Arial"><a href="../Auth_Home.asp">Home</a></font></b></td>
</tr>
</table>
<div align="center">
<center>
<table>
<tbody>
<!--#include file="../_fpclass/fpdblib.inc"-->
<%
fp_sQry="SELECT * FROM MenuSystem WHERE (MenuCategory = '::Selection::' OR MenuCategory = 'main')AND (MenuStatus = 'Active')AND AccessLevel = '" &session("NEWCTS_AccessLevel")& "' OR AccessLevel < '" &session("NEWCTS_AccessLevel")& "' ORDER BY SortOrder ASC"
fp_sDefault="Selection=main"
fp_sNoRecords="<tr><td colspan=1 align=left width=""100%"">Menu Error - Contact Sysadmin</td></tr>"
fp_sDataConn="APLUS"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="MenuName"
fp_sMenuValue="MenuName"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<tr>
<td <%Dim x, bgcolor
if x = 1 then
bgcolor="00,65,9C"
response.write "bgcolor='00,65,9C'"
x=2
Else
bgcolor="00,65,9C"
Response.write "bgcolor='00,65,9C'"
x=1
End if %> <%MI= FP_FieldVal(fp_rs,"subMenuCategory")
If MI = "sub" then
bgcolor="RED"
end if
%> >
<td bgcolor='<%=bgcolor%>'><a target="<%=FP_FieldURL(fp_rs,"LinkDest")%>" href="<%=FP_FieldLink(fp_rs,"Link")%>"><b><font face="Times New Roman" size="2"><%=FP_FieldVal(fp_rs,"MenuName")%>
</font></b></a>
</td>
</tr>
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>
</center>
</div>
</body>
</html>
< Message edited by Spooky -- 6/13/2005 17:21:44 >
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Using SQL DB for a Menu System - 6/13/2005 15:44:59
quote:
"SELECT * FROM MenuSystem WHERE (MenuCategory = '::Selection::' OR MenuCategory = 'main')AND (MenuStatus = 'Active')AND AccessLevel = '" &session("NEWCTS_AccessLevel")& "' OR AccessLevel < '" &session("NEWCTS_AccessLevel")& "' ORDER BY SortOrder ASC"
How 'bout this one:
"SELECT * FROM MenuSystem WHERE (MenuCategory = '::Selection::' OR MenuCategory = 'main') AND (MenuStatus = 'Active') AND (AccessLevel <= " & cint(session("NEWCTS_AccessLevel")) & " ORDER BY SortOrder ASC"
That is if AccessLevel is numeric. If it is text, you may have to do something like:
"SELECT *, cint(AccessLevel) AS AccessLevelNumber FROM MenuSystem WHERE (MenuCategory = '::Selection::' OR MenuCategory = 'main') AND (MenuStatus = 'Active') AND (AccessLevelNumber <= " & cint(session("NEWCTS_AccessLevel")) & " ORDER BY SortOrder ASC"
That any help?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
whitec00
Posts: 32
From: Dallas Texas USA
Status: offline
|
RE: Using SQL DB for a Menu System - 6/13/2005 15:55:51
Yes it is a numeric field. It is the Session variable AccessLevel that gets set from the Spooky Login DB. Thanks for such a quick response. I will try this out and see if it does the job.
CW
| |
|
|
|
whitec00
Posts: 32
From: Dallas Texas USA
Status: offline
|
RE: Using SQL DB for a Menu System - 6/13/2005 16:18:57
I received an error:
Database Results Error
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'ORDER'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers
Any idea what is causing that?
Thanks,
CW
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Using SQL DB for a Menu System - 6/13/2005 16:22:07
Can you post the exact SQL you're using?
EDIT:
Although I think I had 1 too few parens in there:
"SELECT *, cint(AccessLevel) AS AccessLevelNumber FROM MenuSystem WHERE (MenuCategory = '::Selection::' OR MenuCategory = 'main') AND (MenuStatus = 'Active') AND (AccessLevelNumber <= " & cint(session("NEWCTS_AccessLevel"))) & " ORDER BY SortOrder ASC"
< Message edited by rdouglass -- 6/13/2005 16:34:48 >
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
