原创:ASP基于SQLSERVER存储过程的N级栏目分类
/*看到自己12月份的日志还是空的呢,赶紧忙中偷闲整理一篇,
不知道是不是有点造作或不符合BLOG的初衷了,不管这些了,
只是觉得自己当初做完这个的时候还有点成就感,共享共进,哈*/
/*Create by 流水男孩2004-12-07*/
1.表结构
CREATE TABLE [dbo].[SH_XTGL_LMLB] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[LMName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[ParentID] [int] NULL ,
[OrderID] [int] NULL ,
[Depth] [int] NULL ,
[RootID] [int] NULL ,
[Child] [int] NULL ,
[LMLink] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[TopPic] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Readme] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[strRight] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[SystemName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[IsLimit] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SH_XTGL_LMLB] ADD
CONSTRAINT [DF_SH_XTGL_LMLB_ParentID] DEFAULT (0) FOR [ParentID],
CONSTRAINT [DF_SH_XTGL_LMLB_OrderID] DEFAULT (1) FOR [OrderID],
CONSTRAINT [DF_SH_XTGL_LMLB_RootID] DEFAULT (1) FOR [RootID],
CONSTRAINT [DF_SH_XTGL_LMLB_Child] DEFAULT (0) FOR [Child],
CONSTRAINT [DF_SH_XTGL_LMLB_IsLimit] DEFAULT (0) FOR [IsLimit],
CONSTRAINT [PK_SH_XTGL_LMLB] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
GO
2.存储过程
CREATE PROCEDURE dbo.usp_Insert_LM
(
@LMName varchar(50),
@ParentId int,
@LMLink varchar(100),
@TopPic varchar(100),
@Readme nvarchar(255),
@strRight nvarchar(50),
@SystemName nvarchar(50),
@isLimit bit
)
AS
/* SET NOCOUNT ON */
DECLARE @maxrootid int,@maxorderid int
IF @ParentId =0
BEGIN
SELECT @maxrootid = ISNULL(MAX(RootId),0) FROM SH_XTGL_LMLB;
--SELECT @maxorderid = ISNULL(MAX(OrderId),0) FROM SH_XTGL_LMLB WHERE ParentID=0
INSERT INTO SH_XTGL_LMLB(LMName,ParentId,OrderID,Depth,RootID,LMLink,TopPic,ReadMe,strRight,systemName,isLimit)
VALUES(@LMName,@ParentId,0,0,@maxRootid+1,@LMLink,@TopPic,@Readme,@strRight,@systemname,@islimit);
UPDATE SH_XTGL_LMLB SET Child = Child+1 WHERE [ID] = @ParentID
END
ELSE
BEGIN
DECLARE @maxDepth int--,@maxid int
SELECT @maxrootid = RootId,@maxDepth = Depth+1 FROM SH_XTGL_LMLB WHERE [ID]=@ParentId;
SELECT @maxorderid = ISNULL(MAX(OrderId),(SELECT ORDERID FROM SH_XTGL_LMLB WHERE [ID]=@Parentid)) From SH_XTGL_LMLB WHERE ParentId = @ParentID
--SELECT @maxDepth = Depth+1 FROM SH_XTGL_LMLB WHERE [ID]=@ParentID
UPDATE SH_XTGL_LMLB SET OrderID = OrderID+1 WHERE RootID = @maxRootid AND OrderID>@maxOrderId
INSERT INTO SH_XTGL_LMLB(LMName,ParentId,OrderID,Depth,RootID,LMLink,TopPic,ReadMe,strRight,SystemName,islimit)
VALUES(@LMName,@ParentId,@maxorderid+1,@maxDepth,@maxRootid,@LMLink,@TopPic,@Readme,@strRight,@systemName,@islimit)
UPDATE SH_XTGL_LMLB SET Child = Child+1 WHERE [ID] = @ParentID
--SET @maxid=@@IDENTITY
END
--RETURN
GO
3.测试数据
SET IDENTITY_INSERT [SH_XTGL_LMLB] ON
INSERT [SH_XTGL_LMLB] ( [ID] , [LMName] , [ParentID] , [OrderID] , [Depth] , [RootID] , [Child] , [LMLink] , [TopPic] , [Readme] , [strRight] , [SystemName] , [IsLimit] ) VALUES ( 1 , '网站首页' , 0 , 0 , 0 , 1 , 2 , 'index.asp' , '我是图片' , '我是说明' , '我是权限' , '其它' , 0 )
INSERT [SH_XTGL_LMLB] ( [ID] , [LMName] , [ParentID] , [OrderID] , [Depth] , [RootID] , [Child] , [LMLink] , [TopPic] , [Readme] , [strRight] , [SystemName] , [IsLimit] ) VALUES ( 2 , '========' , 1 , 1 , 1 , 1 , 0 , 'index.asp#' , '我是图片' , '我是说明' , '我是权限' , '其它' , 0 )
INSERT [SH_XTGL_LMLB] ( [ID] , [LMName] , [ParentID] , [OrderID] , [Depth] , [RootID] , [Child] , [LMLink] , [TopPic] , [Readme] , [strRight] , [SystemName] , [IsLimit] ) VALUES ( 3 , '-------------------' , 1 , 2 , 1 , 1 , 1 , 'index.asp#' , '我是图片' , '我是说明' , '我是权限' , '其它' , 0 )
INSERT [SH_XTGL_LMLB] ( [ID] , [LMName] , [ParentID] , [OrderID] , [Depth] , [RootID] , [Child] , [LMLink] , [TopPic] , [Readme] , [strRight] , [SystemName] , [IsLimit] ) VALUES ( 4 , '程序设计' , 0 , 0 , 0 , 2 , 0 , 'index.asp' , '我是图片' , '我是说明' , '我是权限' , '其它' , 0 )
INSERT [SH_XTGL_LMLB] ( [ID] , [LMName] , [ParentID] , [OrderID] , [Depth] , [RootID] , [Child] , [LMLink] , [TopPic] , [Readme] , [strRight] , [SystemName] , [IsLimit] ) VALUES ( 5 , '电脑技术' , 0 , 0 , 0 , 3 , 0 , 'index.asp' , '我是图片' , '我是说明' , '我是权限' , '其它' , 0 )
INSERT [SH_XTGL_LMLB] ( [ID] , [LMName] , [ParentID] , [OrderID] , [Depth] , [RootID] , [Child] , [LMLink] , [TopPic] , [Readme] , [strRight] , [SystemName] , [IsLimit] ) VALUES ( 7 , '联系我们' , 0 , 0 , 0 , 5 , 0 , 'index.asp' , '我是图片' , '我是说明' , '我是权限' , '其它' , 0 )
INSERT [SH_XTGL_LMLB] ( [ID] , [LMName] , [ParentID] , [OrderID] , [Depth] , [RootID] , [Child] , [LMLink] , [TopPic] , [Readme] , [strRight] , [SystemName] , [IsLimit] ) VALUES ( 8 , '友情链接' , 0 , 0 , 0 , 6 , 0 , 'index.asp' , '我是图片' , '我是说明' , '我是权限' , '其它' , 0 )
INSERT [SH_XTGL_LMLB] ( [ID] , [LMName] , [ParentID] , [OrderID] , [Depth] , [RootID] , [Child] , [LMLink] , [TopPic] , [Readme] , [strRight] , [SystemName] , [IsLimit] ) VALUES ( 6 , '电子图书' , 0 , 0 , 0 , 4 , 0 , 'index.asp' , '我是图片' , '我是说明' , '我是权限' , '其它' , 0 )
INSERT [SH_XTGL_LMLB] ( [ID] , [LMName] , [ParentID] , [OrderID] , [Depth] , [RootID] , [Child] , [LMLink] , [TopPic] , [Readme] , [strRight] , [SystemName] , [IsLimit] ) VALUES ( 9 , 'pconline.com' , 0 , 0 , 0 , 7 , 0 , 'index.asp' , '我是图片' , '我是说明' , '我是权限' , '其它' , 0 )
INSERT [SH_XTGL_LMLB] ( [ID] , [LMName] , [ParentID] , [OrderID] , [Depth] , [RootID] , [Child] , [LMLink] , [TopPic] , [Readme] , [strRight] , [SystemName] , [IsLimit] ) VALUES ( 10 , 'test' , 3 , 3 , 2 , 1 , 1 , 'http://localhost/testmenu/' , '我是图片' , '我是说明' , '我是权限' , '其它' , 0 )
INSERT [SH_XTGL_LMLB] ( [ID] , [LMName] , [ParentID] , [OrderID] , [Depth] , [RootID] , [Child] , [LMLink] , [TopPic] , [Readme] , [strRight] , [SystemName] , [IsLimit] ) VALUES ( 11 , 'test' , 10 , 4 , 3 , 1 , 2 , 'http://localhost/testmenu/' , '我是图片' , '我是说明' , '我是权限' , '其它' , 0 )
INSERT [SH_XTGL_LMLB] ( [ID] , [LMName] , [ParentID] , [OrderID] , [Depth] , [RootID] , [Child] , [LMLink] , [TopPic] , [Readme] , [strRight] , [SystemName] , [IsLimit] ) VALUES ( 12 , 'test' , 11 , 5 , 4 , 1 , 2 , 'http://localhost/testmenu/' , '我是图片' , '我是说明' , '我是权限' , '其它' , 0 )
INSERT [SH_XTGL_LMLB] ( [ID] , [LMName] , [ParentID] , [OrderID] , [Depth] , [RootID] , [Child] , [LMLink] , [TopPic] , [Readme] , [strRight] , [SystemName] , [IsLimit] ) VALUES ( 13 , 'test' , 12 , 7 , 5 , 1 , 0 , 'http://localhost/testmenu/' , '我是图片' , '我是说明' , '我是权限' , '其它' , 0 )
INSERT [SH_XTGL_LMLB] ( [ID] , [LMName] , [ParentID] , [OrderID] , [Depth] , [RootID] , [Child] , [LMLink] , [TopPic] , [Readme] , [strRight] , [SystemName] , [IsLimit] ) VALUES ( 14 , 'test2' , 12 , 8 , 5 , 1 , 0 , 'http://localhost/testmenu/' , '我是图片' , '我是说明' , '我是权限' , '其它' , 0 )
INSERT [SH_XTGL_LMLB] ( [ID] , [LMName] , [ParentID] , [OrderID] , [Depth] , [RootID] , [Child] , [LMLink] , [TopPic] , [Readme] , [strRight] , [SystemName] , [IsLimit] ) VALUES ( 15 , 'test2' , 11 , 6 , 4 , 1 , 0 , 'http://localhost/testmenu/' , '我是图片' , '我是说明' , '我是权限' , '其它' , 0 )
SET IDENTITY_INSERT [SH_XTGL_LMLB] OFF
4.js文件myjs.js
function ConfirmDel()
{
if(confirm("将删除所有的子栏目，确定删除吗？"))
return true;
else
return false;
}
function okdel()
{
alert( '删除OK，将返回到栏目列表！' );
return;
}
function okadd()
{
alert( '添加OK，将返回到添加页面！' );
return;
}
function okedit()
{
alert( '修改OK，将返回到添加页面！' );
return;
}
function okedit2()
{
alert( '修改OK，将返回到列表页面！' );
return;
}
/*==================Menu Js byjinjian2004-11-26==================*/
//Last Edit:2004-11-30
function layervib(type,num){
var H=type;
var temp=(H='visible'?'hidden':'visible')
for(var i=0;i<mainLayer.length;i++){
var E=eval('document.all.index'+i+'.style');
var H=eval(i);
if(i==num){E.visibility=type}else{E.visibility=temp};
}
}
<!--
function MM_reloadPage(init) { //reloads the window if Nav4 resized
if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt(appVersion)==4)) {
document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }}
else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload();
}
//MM_reloadPage(true);
function MM_findObj(n, d) { //v4.0
var p,i,x; if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document);
if(!x && document.getElementById) x=document.getElementById(n); return x;
}
function MM_showHideLayers() { //v3.0
var i,p,v,obj,args=MM_showHideLayers.arguments;
for (i=0; i<(args.length-2); i+=3) if ((obj=MM_findObj(args[i]))!=null) { v=args[i+2];
if (obj.style) { obj=obj.style; v=(v=='show')?'visible':(v='hide')?'hidden':v; }
obj.visibility=v; }
}
/*==================Menu End==================*/
5.CSS文件mycss.css
body {
background-color: #FFF;
color: #000;
font-family: Georgia, Verdana, sans-serif;
font-size: 12px;
margin: 10;
padding: 0;
text-align: center;
}
td {
font-family: Georgia, Verdana, sans-serif;
font-size: 12px;
}
td.header{
font-family: Georgia, Verdana, sans-serif;
font-size: 15px;
vertical-align:middle;
text-align: center;
}
td.LMlist{
font-family: Georgia, Verdana, sans-serif;
font-size: 12px;
vertical-align:middle;
line-height: 10px;
}
form {
margin: 0;
}
a:link {
color: #00008B;
text-decoration: none;
}
a:visited {
color: #00008B;
text-decoration: none;
}
a:hover {
color: #00C;
background-color: #F0F8FF;
text-decoration: underline;
}
a:active {
color: #00008B;
text-decoration: none;
}
.TextBox {
font-family: Georgia, Verdana, sans-serif;
font-size: 12px;
background-color: #FFFFF0;
color: #191970;
border: 1px solid #369;
}
TEXTAREA {
border:1px solid #369;
background-color: #FFFFF0;
color:#191970
}
.Button {
font-family: Georgia, Verdana, sans-serif;
font-size: 12px;
background-color: #DCDCDC;
color: #000;
border: 1px solid #000000;
}
select{
font-family: Georgia, Verdana, sans-serif;
font-size: 12px;
background-color: #FFFFF0;
color: #191970;
border: 1px solid #369;
}
/*==================Menu by jinjian2004-11-26=====================*/
/*Last Edit:2004-11-30*/
/*Menu_Style1*/
.menu_table1 {background-color: #CC7029; color: #000000; border: 0 solid #CC7029;border-collapse: collapse;border-spacing: 0px;}
.submenu_table1 {background-color: #F8EFDA; color: #000000; border:0;}
.menu_td_on1 {background-color:#CC7029; border:0;cursor: hand;}
.menu_td_off1 {background-color: #F8EFDA; border:0; cursor: hand;}
/*Menu_Style2*/
.menu_table2 {background-color: #F4E5D2; color: #000000; border: 0 solid #CC7029;}
.submenu_table2 {background-color: #F8EFDA; color: #000000; border: 1 solid #CC7029;}
.menu_td_on2 {background-color:#F8FFDA; border: 1 solid #F8EFDA; color: #F8EFDA; cursor: hand;}
.menu_td_off2 {background-color: #F8EFDA; border: 1 solid #F8EFDA; color: #000000; cursor: hand;}
/*Menu_Style3*/
.menu_table3 {background-image: url("../images/zwinfo_01.gif"); color: #000000; border: 0 solid #CC7029;border-collapse: collapse;border-spacing: 0px;}
.submenu_table3 {background-color: #F8EFDA; color: #000000; border:0;}
.menu_td_on3 {background-color:#CC7029; border:0;cursor: hand;}
.menu_td_off3 {background-color: #F8EFDA; border:0; cursor: hand;}
/*==================Menu END=====================*/
6.栏目管理admin_index.asp
<%@ Language=VBScript Codepage=936%>
<%
Dim SqlDatabaseName,SqlPassword,SqlUserName,SqlLocalName
sqlDatabaseName="testdb"
SqlPassword=""
SqlUserName="sa"
SqlLocalName="(local)"
Dim connstr,conn
connstr="Provider=Sqloledb;User ID=" & SqlUserName & "; Password=" & SqlPassword & "; Initial Catalog= " & SqlDatabaseName & "; Data Source=" & SqlLocalName & ";"
Set conn=server.createobject("ADODB.Connection")
conn.open connstr
sub Header()
Response.Write("<table align='center' width='760' cellpadding='0' cellspacing='0' border ='1'><tr><td align=center class='header'>栏目管理系统</td></tr>")
Response.Write("<tr><td><a href='admin_index.asp?action=add'>添加栏目</a> -- <a href='admin_index.asp'>栏目列表</a> -- <a href='admin_index.asp?action=editorder&parentid=0'>编辑排序</a></td></tr>")
Response.Write("</table>")
end sub
sub add()
set rs = conn.Execute("select * from SH_XTGL_LMLB order by rootid,OrderID")
%>
<Form method='post' action='Admin_Index.asp?action=savenew'>
<table align='center' width='760' cellpadding='0' cellspacing='0' border ='1'><tr><td align=center class='header' colspan='2'>添加栏目</td></tr>
<tr><td>栏目名称</td><td><input type='text' name='lmName' class='textbox' size='60'></td></tr>
<tr><td>栏目说明</td><td><textarea name="Readme" cols="83" rows="3">我是说明</textarea>
</td></tr>
<tr><td>所属栏目</td><td>
<select name='ParentID'>
<option value='0'>根分类</option>
<%while(not rs.eof)%>
<option value="<%=rs("id")%>" <%if request("ParentID")<>"" and cint(request("ParentID"))=rs("id") then%>selected<%end if%>>
<%if rs("depth")>0 then%>
<%for i=1 to rs("depth")%>
│
<%next%>
<%end if%>├<%=rs("lmName")%></option>
<%
rs.MoveNext
wend
rs.close()
%>
</select>
</td></tr>
<tr><td>栏目链接</td><td><input type='text' name='LMLink' class='textbox' size='100'></td></tr>
<tr><td>图片链接</td><td><input type='text' name='TopPic' class='textbox' size='100' value='我是图片'></td></tr>
<tr><td>权限描述</td><td><input type='text' name='strRight' class='textbox' size='100' value='我是权限'></td></tr>
<tr><td>所属系统</td>
<td>
<select name="SystemName">
<option value="其它">其它</option>
</select>
</td></tr>
<tr><td>是否受限</td><td><select name='isLimit'><option value='0' selected>否</option><option value='1'>是</option></select></td></tr>
<tr><td colspan='2' align='center'><input type='submit' value='确定' class='button'></td></tr>
</table>
</Form>
<%
end sub
sub savenew()
if request("LMName")="" then
Errmsg=Errmsg+"<br>"+"<li>请输入栏目名称。"
founderr=true
else
LMName = Server.HTMLENCODE(Trim(request("LMName")))
end if
if request("ParentID")="" then
Errmsg=Errmsg+"<br>"+"<li>请选择栏目分类。"
founderr=true
else
ParentID=cint(request("ParentID"))
end if
if request("readme")="" then
Errmsg=Errmsg+"<br>"+"<li>请输入栏目说明。"
founderr=true
else
readme = Server.HTMLENCODE(request("readme"))
end if
if request("LMLink")="" then
Errmsg=Errmsg+"<br>"+"<li>请输入栏目链接。"
founderr=true
else
LMLink = Server.HTMLENCODE(request("LMLink"))
end if
if request("TopPic")="" then
Errmsg=Errmsg+"<br>"+"<li>请输入栏目图片链接。"
founderr=true
else
TopPic = Server.HTMLENCODE(request("TopPic"))
end if
if request("strRight")="" then
Errmsg=Errmsg+"<br>"+"<li>请输入栏目权限。"
founderr=true
else
strRight = Server.HTMLENCODE(request("strRight"))
end if
if request("systemName")="" then
Errmsg=Errmsg+"<br>"+"<li>请输入所属系统。"
founderr=true
else
systemName = Server.HTMLENCODE(request("systemName"))
end if
if request("isLimit")="" or (not isnumeric(request("isLimit")))then
Errmsg=Errmsg+"<br>"+"<li>是否限制判断错误。"
founderr=true
else
isLimit =cint(request("isLimit"))
end if
if founderr=true then
Response.Write errmsg
exit sub
end if
set rs=conn.execute("select * from SH_XTGL_LMLB where LMName = '"&LMName&"' and Parentid="&Parentid)
if not (rs.eof and rs.bof) then
Response.Write "<br><Li>该栏目同一类别中当已经存在了!"
exit sub
end if
conn.execute("usp_insert_lm '"& LMName &"',"& ParentID &",'"& LMLink &"','"& TopPic &"','"& ReadMe &"','"& strRight &"','"& systemName &"',"&isLimit)
Response.Write("<script language='JavaScript'>okadd();this.document.location.href='admin_index.asp?action=add';</script>")
end sub
sub list()
Response.Write"<table align='center' width='760' cellpadding='0' cellspacing='0' border ='1' bgcolor='#DDDDDD'><tr><td class='header' colspan='4'>栏目列表</td></tr>"
set rs = conn.Execute("select * from SH_XTGL_LMLB order by rootid,OrderID")
while not rs.eof
Response.Write("<tr><td class='LMlist' valign=top height='10'>")
if rs("depth")>0 then
for i=1 to rs("depth")
response.write("<img src='images/v.gif'>")
next
end if
if rs("child")>0 then
Response.Write("<img src='images/mn.gif'>")
else
Response.Write("<img src='images/n.gif'>")
end if
Response.Write("<img src='images/fo_open.gif'><a href='../"&rs("LMlink")&"' target='_blank'>"&rs("LMName")&"</a> "&rs("orderid")&"</td><td><a href='admin_index.asp?action=edit&id="&rs("id")&"'>编辑基本信息</a> <a href='admin_index.asp?action=del&id="&rs("id")&"' onClick='return ConfirmDel()';>删除</a> ")
if rs("child")>1 then
Response.Write("<a href='admin_index.asp?action=editorder&parentid="&rs("id")&"'>编辑子栏目排序</a></td></tr>")
else
Response.Write("</td></tr>")
end if
'Response.Write()
rs.movenext
wend
Response.Write"</table>"
end sub
sub del()
if Request.Querystring("id")="" or (not isnumeric(Request.QueryString("id"))) then
Response.Write"<br><li>没有选择可删除的栏目！"
exit sub
end if
id = cint("0"&Request.querystring("id"))
if id=0 then
Response.Write"<br><li>没有选择可删除的栏目！"
exit sub
end if
set prs=conn.execute("select parentid from sh_xtgl_lmlb where id="&id)
if not prs.eof then
parentid=prs(0)
prs.close()
set prs=nothing
else
prs.close()
set prs=nothing
parentid=0
end if
call delsub(id)
conn.execute("update sh_xtgl_lmlb set child=child-1 where id="&parentid)
Response.Write("<script lagnguage='javascript'>okdel();this.document.location.href='admin_index.asp?action=list';</script>")
end sub
Function delsub(id)
conn.Execute("delete from SH_XTGL_LMLB where [id]="&id)
set rs=conn.Execute("select [id] from SH_XTGL_LMLB where parentid="&id)
while not rs.eof
delsub(rs("id"))
rs.movenext
wend
end function
sub editorder()
Response.Write"<table align='center' width='760' cellpadding='0' cellspacing='0' border ='1' bgcolor=''><tr><td class='header' colspan='4'>修改栏目排序</td></tr>"
if request.Querystring("parentid")="" or (not isnumeric(request.Querystring("parentid"))) then
Response.write("<br><li>没有选择父栏目！")
exit sub
else
id=cint(request.Querystring("parentid"))
end if
'set rs = conn.Execute("select * from SH_XTGL_LMLB where parentid = "&id&" order by OrderID")
set rs=Server.CreateObject("ADODB.RecordSet")
rs.open "select * from SH_XTGL_LMLB where parentid = "&id&" order by rootid, OrderID",conn,1,1
if rs.recordcount<2 then
response.write"<br><li>没有子栏目或小于1，不需要排序！"
rs.close()
exit sub
else
set prs=conn.execute("select LMName from sh_xtgl_lmlb where id='"&id&"'")
if not prs.eof then
Response.Write("<tr><td colspan='4'>上级栏目:"&prs(0)&"</td></tr>")
else
Response.Write("<tr><td colspan='4'>上级栏目:根</td></tr>")
end if
prs.close()
set prs=nothing
while not rs.eof
if id=0 then
Response.Write("<form method='post' action='admin_index.asp?action=saveorder&parentid="&id&"'><tr><td>"&rs("LMName")&"</td><td><input name='neworderid' type='text' class='textbox' value='"&rs("Rootid")&"'><input type='submit' value='确定' class='button'><input name='orderid' type='hidden' value='"&rs("rootid")&"'></td></tr></form>")
else
'Response.Write("<form method='post' action='admin_index.asp?action=saveorder&parentid="&id&"'><tr><td>"&rs("LMName")&"</td><td><input name='neworderid' type='text' class='textbox' value='"&rs("orderid")&"'><input type='submit' value='确定' class='button'><input name='orderid' type='hidden' value='"&rs("orderid")&"'></td></tr></form>")
Response.Write("<tr><td>"&rs("LMName")&"</td><td><a href='admin_index.asp?action=moveup&id="&rs("id")&"'>上移</a> <a href='admin_index.asp?action=movedown&id="&rs("id")&"'>下移</a></td></tr>")
end if
rs.movenext
wend
end if
end sub
sub saveorder()
if request("parentid")="" or (not isnumeric(request("parentid"))) then
Response.write "<br><li>已经丢失了上级栏目ID,请正常操作！"
exit sub
else
parentid = cint(request("Parentid"))
end if
if Request("orderid")="" or (not isnumeric(Request("orderid"))) then
Response.Write("<br><li>已经丢失本栏目的原有排序,请正常操作！")
else
orderid = cint(request("orderid"))
end if
if Request("neworderid")="" or (not isnumeric(Request("neworderid"))) then
Response.Write("<br><li>已经丢失本栏目的原有排序,请正常操作！")
else
neworderid = cint(request("neworderid"))
end if
if parentid =0 then
set prs=conn.execute("select * from sh_xtgl_lmlb where parentid="&parentid&" and Rootid= "&neworderid)
else
set prs=conn.execute("select * from sh_xtgl_lmlb where parentid="&parentid&" and orderid= "&neworderid)
end if
if not prs.eof then
Response.Write("<br><li>该序号已经存在！")
prs.close()
set prs=nothing
exit sub
else
prs.close()
set prs=nothing
end if
if parentid=0 then
conn.execute("update sh_xtgl_lmlb set Rootid="&neworderid&" where rootid="&orderid)
else
conn.execute("update sh_xtgl_lmlb set orderid="&neworderid&" where parentid="& parentid &" and orderid="&orderid)
end if
Response.Write("<script lagnguage='javascript'>okedit();this.document.location.href='admin_index.asp?action=editorder&parentid="&parentid&"';</script>")
end sub
sub edit()
if request("id")="" or (not isnumeric(request("id"))) then
Response.Write("<br><li>丢失了栏目ID，请正常操作！")
exit sub
else
id=cint(request("id"))
end if
set rs=conn.execute("select * from sh_xtgl_lmlb where [id]="&id)
if rs.eof then
Response.Write("<br><li>这个栏目可能刚刚被管理员删除！")
rs.close()
set rs=nothing
exit sub
end if
set prs = conn.Execute("select * from SH_XTGL_LMLB where id <>"&id&" order by rootid,OrderID")
%>
<Form method='post' action='Admin_Index.asp?action=saveedit'>
<table align='center' width='760' cellpadding='0' cellspacing='0' border ='1'><tr><td align=center class='header' colspan='2'>修改栏目</td></tr>
<tr><td>栏目名称</td><td><input type='text' name='lmName' class='textbox' size='60' value='<%=rs("LMName")%>'></td></tr>
<tr><td>栏目说明</td><td><textarea name="Readme" cols="83" rows="3"><%=rs("readme")%></textarea>
</td></tr>
<tr><td>所属栏目</td><td>
<select name='ParentID'>
<option value='0'>根分类</option>
<%while(not prs.eof)%>
<option value="<%=prs("id")%>" <%if rs("parentid")=prs("id") then%>selected<%end if%>>
<%if prs("depth")>0 then%>
<%for i=1 to prs("depth")%>
│
<%next%>
<%end if%>├<%=prs("lmName")%></option>
<%
prs.MoveNext
wend
prs.close()
%>
</select>
</td></tr>
<tr><td>栏目链接</td><td><input type='text' name='LMLink' class='textbox' size='100' value='<%=rs("LMLink")%>'></td></tr>
<tr><td>图片链接</td><td><input type='text' name='TopPic' class='textbox' size='100' value='<%=rs("TopPic")%>'></td></tr>
<tr><td>权限描述</td><td><input type='text' name='strRight' class='textbox' size='100' value='<%=rs("strRight")%>'></td></tr>
<tr><td>所属系统</td>
<td>
<select name="SystemName" ID="Select1">
<option value="其它">其它</option>
</select>
</td></tr>
<tr><td>是否受限</td><td><select name='isLimit'><option value='0' <%if rs("islimit")=False then response.write "selected"%>>否</option><option value='1' <%if rs("islimit")=True then response.write "selected"%>>是</option></select></td></tr>
<tr><td colspan='2' align='center'><input type='submit' value='确定' class='button'><input type='hidden' value='<%=id%>' name='id'></td></tr>
</table>
</Form>
<%
end sub
sub saveedit()
if Request("id")="" or (not isnumeric(request("id"))) then
Errmsg=errmsg+"<br><li>丢失了栏目ID，请正常操作！"
founderr=true
else
id=cint(request("id"))
end if
if request("LMName")="" then
Errmsg=Errmsg+"<br>"+"<li>请输入栏目名称。"
founderr=true
else
LMName = Server.HTMLENCODE(Trim(request("LMName")))
end if
if request("ParentID")="" then
Errmsg=Errmsg+"<br>"+"<li>请选择栏目分类。"
founderr=true
else
ParentID=cint(request("ParentID"))
end if
if request("readme")="" then
Errmsg=Errmsg+"<br>"+"<li>请输入栏目说明。"
founderr=true
else
readme = Server.HTMLENCODE(request("readme"))
end if
if request("LMLink")="" then
Errmsg=Errmsg+"<br>"+"<li>请输入栏目链接。"
founderr=true
else
LMLink = Server.HTMLENCODE(request("LMLink"))
end if
if request("TopPic")="" then
Errmsg=Errmsg+"<br>"+"<li>请输入栏目图片链接。"
founderr=true
else
TopPic = Server.HTMLENCODE(request("TopPic"))
end if
if request("strRight")="" then
Errmsg=Errmsg+"<br>"+"<li>请输入栏目权限。"
founderr=true
else
strRight = Server.HTMLENCODE(request("strRight"))
end if
if request("systemName")="" then
Errmsg=Errmsg+"<br>"+"<li>请输入所属系统。"
founderr=true
else
systemName = Server.HTMLENCODE(request("systemName"))
end if
if request("isLimit")="" or (not isnumeric(request("isLimit")))then
Errmsg=Errmsg+"<br>"+"<li>是否限制判断错误。"
founderr=true
else
isLimit =cint(request("isLimit"))
end if
if founderr=true then
Response.Write errmsg
exit sub
end if
set rs=conn.execute("select * from SH_XTGL_LMLB where id <>"&id&" and LMName = '"&LMName&"' and Parentid="&Parentid)
if not (rs.eof and rs.bof) then
Response.Write "<br><Li>该栏目同一类别中当已经存在了!"
rs.close()
exit sub
end if
rs.close()
conn.execute("Update SH_XTGL_LMLB set LMName='"&LMName&"',LMLink='"&LMLink&"',ReadMe='"&readme&"',TopPic='"&TopPic&"',strRight='"&strRight&"',systemname='"&systemname&"',islimit="&islimit&" where id="&id)
rs=conn.execute("select parentid from sh_xtgl_lmlb where id="&id)
oldparentid=rs(0)
if oldparentid=parentid then
Response.Write("<script lagnguage='javascript'>okedit2();this.document.location.href='admin_index.asp?action=list';</script>")
exit sub
end if
'response.write found(parentid,id)
if found(id,parentid) then
Response.Write("<br><li>不能把移到它的下级栏目，其它信息已经更新！")
exit sub
else
response.write parentid
end if
if parentid=0 then
rs=conn.execute("select max(rootid) from sh_xtgl_lmlb")
maxrootid=rs(0)+1
rs=conn.execute("select depth from sh_xtgl_lmlb where id="&id)
depth=rs(0)
conn.execute("update sh_xtgl_lmlb set child=child-1 where id=(select parentid from sh_xtgl_lmlb where id="&id&");update sh_xtgl_lmlb set parentid=0,orderid=0,depth=0,rootid="&maxrootid&" where id="&id)
call updatesub(id,maxrootid,depth)
else
set rs=conn.execute("select * from sh_xtgl_lmlb where id="&parentid)
tid=rs("id"):trootid=rs("rootid"):torderid=rs("orderid"):tdepth=rs("depth")
conn.execute("update sh_xtgl_lmlb set child=child-1 where id=(select parentid from sh_xtgl_lmlb where id="&id&");update sh_xtgl_lmlb set child=child+1 where id="&parentid&";update sh_xtgl_lmlb set parentid="&tid&" where id="&id)
call usub(id,trootid,torderid,tdepth)
end if
Response.Write("<script lagnguage='javascript'>okedit2();this.document.location.href='admin_index.asp?action=list';</script>")
end sub
sub usub(id,rootid,orderid,depth)'递归更新子档目的rootid,depth(将档目不移为根)
'subid=id
suborderid=orderid
subdepth=depth
conn.execute("update sh_xtgl_lmlb set orderid=orderid+1 where orderid>"&suborderid&" and rootid="&rootid)
suborderid=suborderid+1
subdepth=subdepth+1
conn.execute("update sh_xtgl_lmlb set rootid="&rootid&",orderid="&suborderid&",depth="&subdepth&" where id="&id)
set rs=conn.execute("select * from sh_xtgl_lmlb where parentid="&id)
while not rs.eof
if rs("child")>0 then
temp=rs("id")
call updatesub1(temp,rootid,suborderid,subdepth)
end if
rs.movenext
wend
end sub
sub updatesub(id,rootid,depth)'递归更新子档目的rootid,depth(将子档目移为根)
conn.execute("update sh_xtgl_lmlb set depth=depth-"&depth&",rootid="&rootid&" where parentid="&id)
set rs=conn.execute("select * from sh_xtgl_lmlb where parentid="&id)
while not rs.eof
if rs("child")>0 then
temp=rs("id")
call updatesub(temp,rootid,depth)
end if
rs.movenext
wend
end sub
Function Found(id,fid)'递归判断是否移到子栏目
response.write id
set frs=conn.execute("select * from sh_xtgl_lmlb where parentid="&id)
if frs.eof then
Found=False
exit function
end if
while not frs.eof
if frs("id")=fid then
Found=true
exit function
end if
if frs("child")>0 then
Found=Found(temp,fid)
end if
frs.movenext
wend
end function
function getsubcount(id)
set rs=server.createobject("adodb.recordset")
rs.open "select child,id from sh_xtgl_lmlb where parentid="&id,conn,1,1
if not rs.eof then
getsubcount=getsubcount+rs.recordcount
while not rs.eof
getsubcount=getsubcount+getsubcount(rs(1))
rs.movenext
wend
else
getsubcount=0
end if
end function
sub moveup()
if Request("id")="" or (not isnumeric(request("id"))) then
Response.Write "<br><li>丢失了栏目ID，请正常操作！"
exit sub
else
id=cint(request("id"))
end if
set rs=conn.execute("select * from sh_xtgl_lmlb where id="&id)
if rs.eof then
Response.write "<br><li>错误的栏目ID，请正常操作！"
rs.close()
set rs=nothing
exit sub
else
depth=rs("depth")
rootid=rs("rootid")
orderid=rs("orderid")
parentid=rs("parentid")
count1=getsubcount(id)+1
end if
set rs=conn.execute("select top 1 orderid,id from sh_xtgl_lmlb where rootid="&rootid&" and depth="&depth&" and parentid="&parentid&" and orderid<"&orderid&" order by orderid desc")
if rs.eof then
Response.Write"<br><li>不能向前移了！"
rs.close()
set rs=nothing
exit sub
else
orderid2=rs("orderid")
count2=getsubcount(rs("id"))+1
end if
set rs=server.createobject("adodb.recordset")
topcount=count1+count2
rs.open "select top "&topcount&" orderid from sh_xtgl_lmlb where rootid="&rootid&" and orderid>="&orderid2&" order by rootid,orderid",conn,1,3
neworderid=orderid2+count1
for i=1 to count2
rs("orderid")=neworderid
neworderid=neworderid+1
'rs.update
rs.movenext
next
'rs.MovePrevious()
neworderid=orderid2
for i=1 to count1
rs("orderid")=neworderid
neworderid=neworderid+1
'rs.update
rs.movenext
next
rs.updatebatch(3)
rs.close()
set rs=nothing
Response.Write("<script lagnguage='javascript'>okedit2();this.document.location.href='admin_index.asp?action=editorder&parentid="&parentid&"';</script>")
end sub
sub movedown()
if Request("id")="" or (not isnumeric(request("id"))) then
Response.Write "<br><li>丢失了栏目ID，请正常操作！"
exit sub
else
id=cint(request("id"))
end if
set rs=conn.execute("select * from sh_xtgl_lmlb where id="&id)
if rs.eof then
Response.write "<br><li>错误的栏目ID，请正常操作！"
rs.close()
set rs=nothing
exit sub
else
depth=rs("depth")
rootid=rs("rootid")
orderid=rs("orderid")
parentid=rs("parentid")
count1=getsubcount(id)+1
end if
set rs=conn.execute("select top 1 orderid,id from sh_xtgl_lmlb where rootid="&rootid&" and depth="&depth&" and parentid="&parentid&" and orderid>"&orderid&" order by orderid asc")
if rs.eof then
Response.Write"<br><li>不能向后移了！"
rs.close()
set rs=nothing
exit sub
else
orderid2=rs("orderid")
count2=getsubcount(rs("id"))+1
end if
set rs=server.createobject("adodb.recordset")
topcount=count1+count2
rs.open "select top "&topcount&" orderid from sh_xtgl_lmlb where rootid="&rootid&" and orderid>="&orderid&" order by rootid,orderid",conn,1,3
neworderid=orderid+count2
for i=1 to count1
rs("orderid")=neworderid
neworderid=neworderid+1
rs.movenext
next
'rs.MovePrevious()
neworderid=orderid
for i=1 to count2
rs("orderid")=neworderid
neworderid=neworderid+1
rs.movenext
next
rs.updatebatch(3)
rs.close()
set rs=nothing
Response.Write("<script lagnguage='javascript'>okedit2();this.document.location.href='admin_index.asp?action=editorder&parentid="&parentid&"';</script>")
end sub
%>
<html>
<head>
<title>N级栏目管理</title>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<link rel="stylesheet" href="mycss.css" type="text/css">
<script language="JavaScript" src="myjs.js"></sCript>
</head>
<body>
<%
header()
select case request("action")
case "add"
call add()
case "savenew"
call savenew()
case "del"
call del()
case "editorder"
call editorder()
case "saveorder"
call saveorder()
case "edit"
call edit()
CASE "saveedit"
call saveedit()
case "movedown"
call movedown()
case "moveup"
call moveup()
case else
call list()
end select
%>
</body>
</html>
7.显示为菜单menu.asp
<%@ Language="JScript" Codepage=936%>
<%
var SqlDatabaseName="testdb";
var SqlPassword="";
var SqlUserName="sa";
var SqlLocalName="(local)";
var connstr,conn;
connstr="Provider=Sqloledb;User ID=" + SqlUserName + "; Password=" + SqlPassword + "; Initial Catalog= "+ SqlDatabaseName + "; Data Source="+ SqlLocalName +";";
var conn=Server.CreateObject("ADODB.Connection");
conn.open(connstr);
/*===============Menu Create by jinjian 2004-11-25===============*/
/*Last Edit:2004-11-30*/
var leftwidth=-3;
//var topwidth=8;
var menuwidth=90;
function getrights()
{
return "";
}
function getpath(pathstr)
{
var pathstr=pathstr.Value;
if(pathstr.search(/\?/)!=-1) pathstr+="&";
else pathstr+="?";
if(pathstr.substr(0,7).toUpperCase()=="HTTP://")
{
return pathstr;
}
else
{
if(Request.ServerVariables("APPL_PHYSICAL_PATH")==(Server.MapPath("/")+"\\"))
{
return "/"+pathstr+String(Request.ServerVariables("Query_String"));
}
else
{
str=String(Request.ServerVariables("Path_info"));
return str.substr(0,str.indexOf("/",2)+1)+pathstr+String(Request.ServerVariables("Query_String"));
}
}
}
function Menu_ShowMain(styletype,topwidth)
{
var showstr="";
var hidestr="";
var getright=getrights();
//if(isNaN(styletype))styletype=1;
//Response.Write("select * from sh_xtgl_lmlb where "+getright+" parentid=0");
var objRs=conn.Execute("select * from sh_xtgl_lmlb where "+getright+" parentid=0 order by rootid");
Response.Write("<table class=\"menu_table"+styletype+"\" width=\'758\' align=\'center\' heigth=\"24\"><tr><td align=center><table class=\"menu_table"+styletype+"\" cellpadding=0 cellspacing=0><tr><td width=\'1\'><div style=\"POSITION: absolute;top:10;width:1px;z-index:1; visibility: hidden;\">");
while(!objRs.EOF)
{
showstr="";
hidestr="";
if (objRs("child")>0)
{
showstr="\'sub"+objRs("id")+"',\'\',\'show\'";
hidestr="\'sub"+objRs("id")+"',\'\',\'hide\'";
Menu_ShowSub(objRs("id"),leftwidth,topwidth,showstr,hidestr,styletype,getright);
}
objRs.MoveNext();
leftwidth+=menuwidth;
}
Response.Write("</div></td>")
if(!objRs.BOF)objRs.MoveFirst();
while(!objRs.EOF)
{
showstr="";
hidestr="";
Response.Write("<td width=\'"+menuwidth+"\' align=center ")
if(objRs("child")>0)
{
showstr="\'sub"+objRs("id")+"',\'\',\'show\'";
hidestr="\'sub"+objRs("id")+"',\'\',\'hide\'";
Response.Write("onMouseOver=\"MM_showHideLayers("+showstr+")\" onMouseOut=\"MM_showHideLayers("+hidestr+")\"");
}
Response.Write("><a href=\'"+getpath(objRs("LMLink"))+"\' target=\'_self\' class=\"underline\">"+objRs("LMName")+"</a></td>");
objRs.MoveNext();
}
Response.Write("</tr></table></td></tr></table>");
}
function Menu_ShowSub(id,leftwidth,topwidth,showstr,hidestr,styletype,getright)
{
var subleftwidth=leftwidth;
var objRs=conn.Execute("select * from sh_xtgl_lmlb where "+getright+" parentid="+id+" order by orderid");
if(!objRs.EOF && !objRs.BOF)
{
Response.Write("<div id=\"sub"+id+"\" onMouseOver=\"MM_showHideLayers("+showstr+")\" onMouseOut=\"MM_showHideLayers("+hidestr+")\" style=\"POSITION: absolute;top:"+topwidth+";left:"+subleftwidth+";width:"+menuwidth+"px;z-index:2; visibility: hidden;filter:Alpha(Opacity=90);\"><table class=\"submenu_table"+styletype+"\" cellpadding=1 cellspacing=1 align=center>");
//showstr=showstr+",\'sub"+objRs("id")+"',\'\',\'show\'";
//hidestr=hidestr+",\'sub"+objRs("id")+"',\'\',\'hide'";
while(!objRs.EOF)
{
subshowstr="";
subhidestr="";
subshowstr=showstr+",\'sub"+objRs("id")+"',\'\',\'show\'";
subhidestr=hidestr+",\'sub"+objRs("id")+"',\'\',\'hide\'";
//showstr=showstr+",\'sub"+objRs("id")+"',\'\',\'show\'";
var str="<tr><td align=center width=\'100%\'><div style=\"width="+menuwidth+"px;z-index:2;border:0;\"";
if(objRs("child")>0)
{
str+=" onMouseOver=\"this.className=\'menu_td_on"+styletype+"\';MM_showHideLayers("+subshowstr+")\" onMouseOut=\"this.className=\'menu_td_off"+styletype+"\';MM_showHideLayers("+subhidestr+")\"";
str+="><a href=\'"+getpath(objRs("LMLink"))+"\' target=\'_self\'>"+objRs("LMName")+"</a></div></td><td align=right><font face=\'webdings\'>4</font></td></tr>";
}
else
{
str+="onMouseOver=\"this.className=\'menu_td_on"+styletype+"\';\" onMouseOut=\"this.className=\'menu_td_off"+styletype+"\';\"><a href=\'"+getpath(objRs("LMLink"))+"\' target=\'_self\'>"+objRs("LMName")+"</a></div></td></tr>";
}
Response.Write(str);
objRs.MoveNext();
}
Response.Write("</table></div>");
objRs.MoveFirst();
subleftwidth+=menuwidth;
while(!objRs.EOF)
{
subshowstr="";
subhidestr="";
subshowstr=showstr+",\'sub"+objRs("id")+"',\'\',\'show\'";
subhidestr=hidestr+",\'sub"+objRs("id")+"',\'\',\'hide\'";
if(objRs("child")>0)
Menu_ShowSub(objRs("id"),subleftwidth,topwidth,subshowstr,subhidestr,styletype,getright);
objRs.MoveNext();
topwidth+=22;
}
}
}
/*===============Menu End===============*/
%>
8.测试菜单文件index.asp
<!--#include file="menu.asp"-->
<html>
<head>
<title>测试</title>
<link rel="stylesheet" href="mycss.css" type="text/css">
<script language="JavaScript" src="myjs.js"></sCript>
</head>
<body>
<%Menu_ShowMain(2,15);//2为菜单样式，15为到顶上的距离%>
</body>
</html>
9.用到的图片(放在images目录下)
http://www.sinzy.net/Test/UploadedPix/20041210115750_idggqw2l_v.gif
http://www.sinzy.net/Test/UploadedPix/20041210115753_wlgopn55_n.gif
http://www.sinzy.net/Test/UploadedPix/20041210115756_iltnnu45_mn.gif
http://www.sinzy.net/Test/UploadedPix/20041210115759_u55wgt3j_fo_open.gif
10.一点说明
原项目要求比这个要复杂，当时有比较复杂的权限判断，在这里被我剪了。
有些地方可以递归出sql语句，再查一次数据库，有兴趣可以改进一下。
这里只有增加菜单用了存储过程，起个抛砖引玉的作用~:D
图片的颜色是白色，要选中了差不多才看得见
menu.asp文件和index.asp当时根据项目需要是用JScript开发，如果熟悉VBScript开发ASP应该不会有障碍。