ASP.NET SQL Server Working Sample You Can Have

[dinosf@sbcglobal.net]

If Anyone would like the sample of a working ASP.NET menu, using SQLDataClient rather than ADO This code works. It uses a connection string stored in the web.config file, and though I intend to modify the query to filter by permission level, I'm quite pleased that it works. If you have less than version 3.0 of the Pro edition I can't say if it will work, mine didn't before the upgrade.

This code is for a control file (.ascx) that I actually insert into another control (a header with a logo and the menu below it).

Here's the code. Basically if you have your connection string in the web config file you should be able to copy everything after the --- Add menu items here --- text, modify the query for your table and field names and swap out the field names a bit lower and your good to go. Oh - remove the page language=vb statement in the output code if your going to make this a control. The aspcompat statement they mention in the sample is also not needed. There's no code in the referenced codebehind page, it's all here.

<%@ Import NameSpace="System.Diagnostics" %>
<%@ Import NameSpace="System.Web.Security" %>
<%@ Import NameSpace="System.Web.UI.HtmlControls" %>
<%@ Import NameSpace="System.Configuration" %>
<%@ Import NameSpace="Microsoft.VisualBasic" %>
<%@ Import NameSpace="System.Web.UI.WebControls" %>
<%@ Import NameSpace="System.Web.UI" %>
<%@ Import NameSpace="System.Text" %>
<%@ Import NameSpace="System" %>
<%@ Import NameSpace="System.Security" %>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<%@ Control Language="vb" AutoEventWireup="false" Codebehind="AdminMenu.ascx.vb" Inherits="Application.WebUserControl1" TargetSchema="http://schemas.microsoft.com/intellisense/ie5" %>
<script language="JavaScript" type="text/javascript">
<!--
var ver='3.0.2';
var m1=new Object;
m1.name='m1';
m1.fnm='menu_m1';
if(!window.lastm||window.lastm<1)lastm=1;
m1.v17=null;
m1.v17Timeout='';
var maxZ=1000;
m1.v18;
m1.targetFrame;
var docLoaded=false;
m1.bIncBorder=true;
m1.v29=null;
m1.v29Str='';
m1.v55=50;
m1.scrollStep=10;
m1.fadingSteps=8;
m1.itemOverDelay=0;
m1.transTLO=0;
m1.fixSB=0;
m1.v21="../Menus";
m1.maxlev=0;
m1.v22=0;
m1.sepH=10;
m1.bHlNL=1;
m1.showA=1;
m1.bVarWidth=0;
m1.bShowDel=0;
m1.scrDel=0;
m1.v23=160;
m1.levelOffset=20;
m1.bord=2;
m1.vertSpace=3;
m1.sep=0;
m1.v19=false;
m1.bkv=0;
m1.rev=0;
m1.shs=4;
m1.shc='#C0C0C0';
m1.xOff=0;
m1.yOff=0;
m1.v20=false;
m1.cntFrame="";
m1.menuFrame="";
m1.v24="";
m1.mout=true;
m1.iconSize=8;
m1.closeDelay=1000;
m1.tlmOrigBg="White";
m1.tlmOrigCol="White";
m1.v25=true;
m1.v52=false;
m1.v60 = 0;
m1.v11=true;
m1.bord=0;
m1.v10=8;
m1.ppLeftPad = 2;
m1.v54 = 0;
m1.v01=2;
m1.tlmHlBg="#FFCC00";
m1.tlmHlCol="White";
m1.borderCol="Black";
m1.menuHorizontal=true;
m1.scrollHeight=6;

m1.attr = new Array ("12px",false,false,"White","White","Black","Verdana,Arial,Helvetica","#0093FF","White","White") ;

window.m1templFold = "<td width=\"100\" nowrap=\"nowrap\" onmouseover=\"chgBg(m1,'m1tlm(menuind)',3);exM(m1,'m1mn(foldind)','m1tlm(menuind)',event)\" onmouseout=\"chgBg(m1,'m1tlm(menuind)',0);coM(m1,'m1mn(foldind)')\" id=\"m1tlm(menuind)\" style=\"color:(textcol);\" class=\"m1mit\" background=\"../Menus/menu_m1bg1.gif\"><a id=\"m1tlm(menuind)a\" (status)style=\"color:(textcol);\" class=\"m1CL0\" href=\"(menuurl)\" target=\"(trgframe)\" ><nobr>(menutxt)<img width=\"8\" height=\"8\" src=\"../Menus/menu_m1iad.gif\" align=\"middle\" border=\"0\" alt=\"\" /></nobr></a></td>\n";
window.m1templFile = "<td width=\"100\" nowrap=\"nowrap\" onmouseover=\"chgBg(m1,'m1tlm(menuind)',3);exM(m1,'none','',event)\" onmouseout=\"chgBg(m1,'m1tlm(menuind)',0,1)\" id=\"m1tlm(menuind)\" style=\"color:(textcol);\" class=\"m1mit\" background=\"../Menus/menu_m1bg1.gif\"><a id=\"m1tlm(menuind)a\" (status)style=\"color:(textcol);\" class=\"m1CL0\" href=\"(menuurl)\" target=\"(trgframe)\">(menutxt)</a></td>\n";
window.m1templSep = "<td nowrap=\"nowrap\" width=\"20\"><img src=\"../Menus/menu_m1bg2.gif\" alt=\"\" /></td>";
window.m1tbl1 = "<table id=\"m1mainSXMenu2\" cellspacing=\"0\" cellpadding=\"0\" border=\"0\" >\n<tr align=\"center\">\n<td align=\"right\" nowrap=\"nowrap\" width=\"40\"><img src=\"../Menus/menu_m1bg0.gif\" alt=\"\" /></td>";
window.m1tbl2 = "<td align=\"left\" nowrap=\"nowrap\" width=\"14\"><img src=\"../Menus/menu_m1bg3.gif\" alt=\"\" /></td></tr>\n</table>";
m1mn = new Array ();
function gpc (ar)
{
var i;
var cnt = 0;
for (i = 0; i < ar.length / 5; i++)
{
if (ar [i * 5 + 2] == 1)
{
cnt++;
}
}
return cnt;
}

function insertItemToMenu (itemText, itemUrl, parentMenu, bPopup, targetFrame, itemIcon, nMenu)
{
var parentMenuId = parentMenu ? parentMenu : ((nMenu ? nMenu : 'm1') + 'mn');
var arr = eval ('window.' + parentMenuId);
if(!arr)
{
eval ('window.' + parentMenuId + '= new Array ();');
arr = eval ('window.' + parentMenuId);
}
var item = parentMenu ? (arr.length / 5 + 1) : (gpc (arr) + 1);
if (arr.push) arr.push (itemText, itemUrl, bPopup ? 1 : 0, targetFrame, itemIcon);
else
{
arr [arr.length] = itemText;
arr [arr.length] = itemUrl;
arr [arr.length] = bPopup?1:0;
arr [arr.length] = targetFrame;
arr [arr.length] = itemIcon;
}
var newId = (parentMenuId + (parentMenu ? '_' : '') + item.toString());
if (bPopup)
{
eval ("document." + newId + "prop = \'" + itemIcon + "\';");
}
return newId;
}

function addSubMenu (itemText, itemUrl, targetFrame, parentMenu, itemIcon, nMenu)
{
return insertItemToMenu (itemText, itemUrl, parentMenu, 1, targetFrame, itemIcon, nMenu);
}

function addMenuItem (itemText, itemUrl, targetFrame, parentMenu, itemIcon, nMenu)
{
return insertItemToMenu (itemText, itemUrl, parentMenu, 0, targetFrame, itemIcon, nMenu);
}

function addTopLevelMenu (itemText, itemUrl, targetFrame, itemIcon, nMenu)
{
var url;
if (!itemUrl || itemUrl == '')
{
url = 'javascript:void(0);';
}
else
{
url=itemUrl;
}
return addSubMenu (itemText, url, null, targetFrame, itemIcon, nMenu);
}

function getTopLevelMenu (nSubMenu, nMenu)
{
return ((nMenu ? nMenu : 'm1') + 'mn' + nSubMenu);
}
absPath="";
if (m1.v19 && !m1.v20)
{
if (window.location.href.lastIndexOf("\\")>window.location.href.lastIndexOf("/")) {sepCh = "\\" ;} else {sepCh = "/" ;}
absPath = window.location.href.substring(0,window.location.href.lastIndexOf(sepCh)+1);
}
m1.v61 = 1;
m1.v02=m1.v23;
var stp="<img width=1 height=1 style='display:none' id=";
var stq=" src='../Menus/menu_m1";
document.write(stp+"'m1menubg4'"+stq+"bg4.gif'>");
document.write(stp+"'m1menubg5'"+stq+"bg5.gif'>");
document.write(stp+"'m1menubg6'"+stq+"bg6.gif'>");
document.write("<style type='text/css'>\n.m1CL0,.m1CL0:link{text-decoration:none;width:100%;color:White; }\n.m1CL0:visited{color:White}\n.m1mit{padding-left:10px;padding-right:10px;color:White; font-family:Verdana,Arial,Helvetica; font-size:12px; }\n"+"</"+"style>");
document.write("<script language='JavaScript1.2' src='../Menus/menu_dom.js'></"+"script>");
//-->
</script>

<div id="menubar_m1"></div>
<script language="JavaScript" type="text/javascript">
<!--

<script runat="server" language="VB">

dim ssNextMenuId = 1
dim outCode = ""

Function SSInsertItemToMenu (functionName, itemText, itemUrl, targetFrame, parentMenu, itemIcon, nMenu)
dim currentId = "ssMnu" & ssNextMenuId
outCode = outCode & ("var ssMnu" & ssNextMenuId & "=" & functionName & "(""" & itemText & """, """ & itemUrl & """")
if targetFrame <> "" or itemIcon <> "" or nMenu <> "" or functionName <> "addTopLevelMenu" then
outCode = outCode & (",""" & targetFrame & """")
if functionName <> "addTopLevelMenu" then
if parentMenu <> "" then
outCode = outCode & ("," & parentMenu)
else
outCode = outCode & (",null")
end if
end if
if itemIcon <> "" or nMenu <> "" then
outCode = outCode & (",""" & itemIcon & """")
if nMenu <> "" then
outCode = outCode & (",""" & nMenu & """")
end if
end if
end if

outCode = outCode & (");" & chr (13))
ssNextMenuId = ssNextMenuId + 1
SSInsertItemToMenu = currentId
End Function

Function SSAddTopLevelMenu (itemText, itemUrl, targetFrame, itemIcon, nMenu)
dim itemId = SSInsertItemToMenu ("addTopLevelMenu", itemText, itemUrl, targetFrame, "", itemIcon, nMenu)
SSAddTopLevelMenu = itemId
End Function

Function SSAddSubMenu (itemText, itemUrl, targetFrame, parentMenu, itemIcon, nMenu)
dim itemId = SSInsertItemToMenu ("addSubMenu", itemText, itemUrl, targetFrame, parentMenu, itemIcon, nMenu)
SSAddSubMenu = itemId
End Function

Function SSAddMenuItem (itemText, itemUrl, targetFrame, parentMenu, itemIcon, nMenu)
dim itemId = SSInsertItemToMenu ("addMenuItem", itemText, itemUrl, targetFrame, parentMenu, itemIcon, nMenu)
SSAddMenuItem = itemId
End Function

Function SSGetTopLevelMenu (nSubMenu, nMenu)
dim currentId = "ssMnu" & ssNextMenuId
outCode = outCode & ("var ssMnu" & ssNextMenuId & "=getTopLevelMenu(" & nSubMenu)
if nMenu <> "" then
outCode = outCode & (",""" & nMenu & """")
end if
outCode = outCode & (");" & chr (13))
ssNextMenuId = ssNextMenuId + 1
SSGetTopLevelMenu = currentId
End Function

</script>
<%
' --------------------------
' Add items to the menu here
' --------------------------
%>
<script runat="server" language="VB">

Function getFindChildrenQuery (db_id)
' MODIFICATION REQUIRED
' You might need to modify the query below
' Its goal is to fetch all entries whose parent is identified by db_id
getFindChildrenQuery = "select * from " & session("strPrefix") & "_tblMenu where parent_id=" & db_id
End Function
' The function hasChildren returns true if item identified by db_id
' has children or false otherwise
Function hasChildren (db_id)
Dim conn = ConfigurationSettings.AppSettings("DBConnectionString")
Dim myConnection As New SqlConnection(conn)
Dim myCommand As New SqlCommand
Dim myDataReader as SQLDataReader
If myConnection.State = ConnectionState.Closed Then myConnection.Open()
Try
myCommand.CommandText = getFindChildrenQuery(db_id)
myCommand.Connection = myConnection
myDataReader = myCommand.ExecuteReader()

If myDataReader.HasRows = False Then
hasChildren = False
Else
hasChildren = True
End If
If Not myDataReader.IsClosed = True Then myDataReader.close()
Catch ex As Exception
Throw New System.Exception(ex.Message + " : " + ex.StackTrace)
Finally
If myConnection.State = ConnectionState.Open Then myConnection.Close()
End Try
End Function

Function createMenuLevel (db_parentId, menu_parentId)
Dim conn = ConfigurationSettings.AppSettings("DBConnectionString")
Dim myConnection As New SqlConnection(conn)
dim text
dim url
dim id
dim bFolder
If myConnection.State = ConnectionState.Closed Then myConnection.Open()
Try
Dim myCommand As New SqlCommand
Dim myDataReader as SQLDataReader
myCommand.CommandText = getFindChildrenQuery(db_parentId)
myCommand.Connection = myConnection
myDataReader = myCommand.ExecuteReader()
IF myDataReader.HasRows = True Then
While myDataReader.Read()
text = myDataReader("node_name")
If Not isDBNull(myDataReader("node_url")) Then
url = myDataReader("node_url")
Else
url = Nothing
End If
id = myDataReader("id")
bFolder = hasChildren (id)
dim menu_id
If bFolder = True Then
If menu_parentId = "0" Then
menu_id = SSAddTopLevelMenu (text, url, "", "", "")
Else
menu_id = SSAddSubMenu (text, url, "", menu_parentId, "", "")
End If
createMenuLevel (id, menu_id)
Else
SSAddMenuItem (text, url, "", menu_parentId, "", "")
End If
End While
End If
If Not myDataReader.IsClosed = True Then myDataReader.close()
Catch ex As Exception
Throw New System.Exception(ex.Message + " : " + ex.StackTrace)
Finally
If myConnection.State = ConnectionState.Open Then myConnection.Close()
End Try
End Function

Function createMenu ()

createMenuLevel (0, "0")

End Function

</script>
<%
createmenu
Response.Write (outCode)
%>

//-->
</script>

[Prasad]

Hi

The code you have given for dynamic menu creation using sqlclient is very good. Can you please tell me how to use that code in my .ascx file.

Thanks in Advance,

Prasad