din database:
user
****
id (autonummer)
userName (text)
(flere efter ønske, men disse er krævet)
menuItem
********
id (autonummer)
menuItem (text)
(flere efter ønske, men disse er krævet)
users_menuItem
**************
id (autonummer)
userID (number)
menuItemID (number)
"admin.asp"
***********
<%@ language="VBScript" %>
<% option explicit %>
<%
Dim objConn : Set objConn = Server.CreateObject( "ADODB.Connection" )
Dim strDSN : strDSN = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath( "db.mdb" )
objConn.Open strDSN
' Bygger SQL'er
Dim strMenuItemSQL : strMenuItemSQL = "SELECT id, menuItem FROM menuItem"
Dim strUsersSQL : strUsersSQL = "SELECT id, userName FROM users"
Dim arrUsers, arrMenuItems, i, j
Dim objMenuItemRS : Set objMenuItemRS = objConn.Execute( strMenuItemSQL )
Dim objUsersRS : Set objUsersRS = objConn.Execute( strUsersSQL )
if( not objUsersRS.EOF ) then
arrUsers = objUsersRS.getRows()
end if
if( not objMenuItemRS.EOF ) then
arrMenuItems = objMenuItemRS.getRows()
end if
objUsersRS.Close
Set objUsersRS = Nothing
objMenuItemRS.Close
Set objMenuItemRS = Nothing
if( Request( "mode" ) = "save" ) then
Dim strSelectSQL : strSelectSQL = "SELECT * FROM users_menuItem WHERE "
Dim strDeleteSQL : strDeleteSQL = "DELETE FROM users_menuItem WHERE "
Dim strInsertSQL : strInsertSQL = "INSERT INTO users_menuItem ( userID, menuItemID ) VALUES "
Dim strTmpSQL, strValue, objTmpRS
for i = 0 To UBound( arrUsers, 2 )
for j = 0 To UBound( arrMenuItems, 2 )
strValue = Request( "chkUserMenuItem_" & arrUsers( 0, i ) & "_" & arrMenuItems( 0, j ) )
if( strValue = "" ) then
strTmpSQL = strDeleteSQL & "userID = " & arrUsers( 0, i ) & " AND menuItemID = " & arrMenuItems( 0, j )
objConn.Execute( strTmpSQL )
else
strTmpSQL = strSelectSQL & "userID = " & arrUsers( 0, i ) & " AND menuItemID = " & arrMenuItems( 0, j )
Set objTmpRS = objConn.Execute( strTmpSQL )
if( objTmpRS.EOF ) then
strTmpSQL = strInsertSQL & "(" & arrUsers( 0, i ) & ", " & arrMenuItems( 0, j ) & ")"
objConn.Execute( strTmpSQL )
end if
objTmpRS.Close
end if
next
next
end if
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Untitled</title>
</head>
<body>
<form id="frm" name="frm" method="POST">
<table>
<tr>
<td></td>
<%
for i = 0 To UBound( arrUsers, 2 )
Response.Write( "<td><b>" & arrUsers( 1, i ) & "</b></td>" )
next
%>
</tr>
<%
Dim strUsersMenuItemsSQL : strUsersMenuItemsSQL = "SELECT userID, menuItemID FROM users_menuItem WHERE "
Dim objUsersMenuItemRS, arrUsersMenuItems, strUsersMenuItems, strChecked
for i = 0 To UBound( arrMenuItems, 2 )
Response.Write( "<tr><td><b>" & arrMenuItems(1, i) & "</b></td>" )
for j = 0 To UBound( arrUsers, 2 )
strChecked = ""
Set objUsersMenuItemRS = objConn.Execute( strUsersMenuItemsSQL & "userID = " & arrUsers( 0, j ) & " AND menuItemID = " & arrMenuItems(0, i) )
if( Not objUsersMenuItemRS.EOF ) then
strChecked = " checked=""checked"""
end if
Response.Write( "<td><input type=""checkbox"" name=""chkUserMenuItem_" & arrUsers( 0, j ) & "_" & arrMenuItems(0, i) & """ value=""" & arrUsers( 0, i ) & "¤" & arrMenuItems(0, i) & """" & strChecked & "></td>" )
next
Response.Write( "</tr>" )
next
%>
</table>
<br>
<input type="hidden" name="mode" value="save">
<input type="submit" value="save">
</form>
<%
objConn.Close
Set objConn = Nothing
%>
</body>
</html>
Derefter kan du hænte ud menupunkter med denne lille kode:
Dim strSQL : strSQL = "SELECT * FROM menuItem INNER JOIN users_menuItem ON menuItem.id = users_menuItem.menuItemID WHERE userID = [USERID]"
Dim objMenuRS : Set objMenuRS = objConn.Execute( strSQL )
while not objMenuRS.eof
Response.write( objMenuRS( "menuItem" ) & "<br>" )
objMenuRS.MoveNext
wend
/nute
