I have a listbox that call a java function that executes a SQL Stored Procedure. Everything works fine, however, I can't seem to get the listbox to refresh. Any help apprciated.
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="aeries_users.aspx.vb" Inherits="_Default" culture="auto" meta:resourcekey="PageResource1" uiculture="auto" %>
<%@ Register assembly="Telerik.Web.UI" namespace="Telerik.Web.UI" tagprefix="telerik" %>
<head id="Head1" runat="server">
<title>IT Central</title>
<link rel="stylesheet" type="text/css" href="~/includes/ITC_Main.css" />
</head>
<body>
<form id="form1" runat="server">
<telerik:RadCodeBlock ID="RadCodeBlock1" runat="server">
<script type="text/javascript">
function SiteAdd() {
var ajaxManager = $find("<%= RadAjaxManager1.ClientID %>");
ajaxManager.ajaxRequest();
}
</script>
</telerik:RadCodeBlock>
<div id="header">
<table cellpadding="0" cellspacing="0" style="width: 100%; height: 40px;">
<tr>
<td style="width: 200px; vertical-align: middle; text-align: center;">
<img alt="" src="../../images/aalogo.gif" style="width: 169px; height: 35px" /></td>
<td style="text-align: center;">
<datelbl:ShowDate ID="ShowDate1" runat="server" />
</td>
<td style="width: 300px; text-align: center;">
<table cellpadding="2" cellspacing="0"
style="width: 292px; height: 40px; background-image: url('../../images/headerinset.gif'); text-align: center;">
<tr>
<td style="font-weight: bold; font-size: small; font-family: Arial, Helvetica, sans-serif; color: #00B000; text-align: center;">
Logged in as:
<asp:LoginName ID="LoginName1" runat="server" Font-Names="Arial"
Font-Size="Small" ForeColor="#597791"
meta:resourcekey="LoginName1Resource1" />
<asp:LoginStatus ID="LoginStatus1" runat="server" CssClass="login"
meta:resourcekey="LoginStatus1Resource1" />
</td>
</tr>
</table></td>
<td style="text-align: center; vertical-align: middle; width: 175px;">
<img alt="" src="../../images/aatusdlogo.gif" style="width: 152px; height: 36px" /></td>
</tr>
</table>
</div>
<div id="subheader">
</div>
<div>
<table cellpadding="0" cellspacing="0" style="width: 100%">
<tr>
<td style="width: 250px; background-image: url('../../images/menuback.gif'); background-color: #E6E6E6; border-bottom-style: solid; border-bottom-width: thin; border-bottom-color: #000000;"
valign="top">
<telerik:RadPanelBar ID="rpb_AA_Menu" Runat="server" DataFieldID="DataFieldID"
DataFieldParentID="DataFieldParentID"
DataNavigateUrlField="DataNavigateUrlField" DataSourceID="sds_AA_Menu"
DataTextField="DataTextField" DataValueField="DataTextField" Width="250px"
PersistStateInCookie="True" Skin="Office2007">
<DataBindings>
<telerik:RadPanelItemBinding SelectedImageUrlField="ImageURL"
ExpandedImageUrlField="ImageURL" HoveredImageUrlField="ImageURL"
ImageUrlField="ImageURL" />
<telerik:RadPanelItemBinding ImageUrlField="ImageURL" />
</DataBindings>
</telerik:RadPanelBar>
</td>
<td valign="top"
style="background-color: #FFFFFF; border-bottom-style: solid; border-bottom-width: thin; border-bottom-color: #000000;">
<table cellpadding="0" cellspacing="0" style="width: 100%">
<tr>
<td style="padding: 10px">
<h1>
User Manager - Users</h1>
<table cellpadding="0" cellspacing="0" style="width: 100%">
<tr>
<td class="tbltdHeader">
Year</td>
</tr>
<tr>
<td>
<telerik:RadComboBox ID="rcb_Database" Runat="server"
DataSourceID="sds_Database" DataTextField="DatabaseName"
DataValueField="DatabaseName" AutoPostBack="True" AppendDataBoundItems="True">
<Items>
<telerik:RadComboBoxItem Value = "" Text="Select" />
</Items>
</telerik:RadComboBox>
<telerik:RadComboBox ID="rcb_Database0" Runat="server"
DataSourceID="sds_Database" DataTextField="DatabaseName"
DataValueField="DatabaseName" AutoPostBack="True"
AppendDataBoundItems="True" SelectedValue='<%#Session("DBName") %>'>
<Items>
<telerik:RadComboBoxItem Value = "" Text="Select" />
</Items>
</telerik:RadComboBox>
</td>
</tr>
<tr>
<td style="height: 16px">
</td>
</tr>
<tr>
<td class="tbltdHeader">
User Details</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
<telerik:RadComboBox ID="rcb_Users" Runat="server" DataTextField="memberName"
DataValueField="memberName" AutoPostBack="True" Height="300px"
AppendDataBoundItems="True">
<Items>
<telerik:RadComboBoxItem Value = "" Text="Select" />
</Items>
</telerik:RadComboBox>
</td>
</tr>
</table>
<p>
<asp:FormView ID="fvw_UserDetails" runat="server" DataSourceID="sds_UserDetails"
Width="750px">
<ItemTemplate>
<table cellpadding="0" cellspacing="0" style="width: 100%">
<tr>
<td class="tbltd" style="width: 87px">
Full Name:</td>
<td style="width: 220px">
<asp:Label ID="FullNameLabel" runat="server" Text='<%# Bind("FullName") %>' />
</td>
<td class="tbltd" style="width: 75px">
Site:</td>
<td>
<asp:Label ID="OfficeLabel" runat="server" Text='<%# Bind("Site") %>' />
</td>
</tr>
<tr>
<td class="tbltd" style="width: 87px">
User Name:
</td>
<td style="width: 220px">
<asp:Label ID="UserNameLabel" runat="server" Text='<%# Bind("UserName") %>' />
</td>
<td class="tbltd" style="width: 75px">
IP Phone:</td>
<td>
<asp:Label ID="IP_PhoneLabel" runat="server" Text='<%# Bind("PhoneIP") %>' />
</td>
</tr>
<tr>
<td class="tbltd" style="width: 87px">
Job Title:</td>
<td style="width: 220px">
<asp:Label ID="Job_TitleLabel" runat="server" Text='<%# Bind("JobTitle") %>' />
</td>
<td class="tbltd" style="width: 75px">
Phone:</td>
<td>
<asp:Label ID="Phone_NumberLabel" runat="server" Text='<%# Bind("Phone1") %>' />
</td>
</tr>
<tr>
<td class="tbltd" style="width: 87px">
Department:</td>
<td style="width: 220px">
<asp:Label ID="DepartmentLabel" runat="server"
Text='<%# Bind("Department") %>' />
</td>
<td class="tbltd" style="width: 75px">
Email:</td>
<td>
<asp:HyperLink NavigateUrl='<%# Bind("EmailAddress", "mailto:{0}") %>'
Text='<%# Bind("EmailAddress") %>' runat="server" ID="hlEmail"></asp:HyperLink>
</td>
</tr>
</table>
</ItemTemplate>
</asp:FormView>
</p>
<h2>
Roles</h2>
<hr color="#7698BA" class="hrblue"/>
<table cellpadding="0" cellspacing="0">
<tr>
<td style="width: 250px; vertical-align: top;" class="tbltdHeader">
Job Roles</td>
<td style="width: 250px; vertical-align: top;" class="tbltdHeader">
<table cellpadding="0" cellspacing="0" style="width: 100%">
<tr>
<td style="width: 92px">
Task Roles</td>
<td>
<asp:ImageButton
ID="ImageButton1" runat="server" ImageUrl="~/images/i-icon.gif" />
</td>
</tr>
</table>
</td>
<td style="width: 250px; vertical-align: top;" class="tbltdHeader">
Explicit Task Roles</td>
</tr>
<tr>
<td style="width: 250px; vertical-align: top;">
<telerik:RadListBox ID="rlb_JobRole" runat="server"
DataKeyField="DbRole"
DataSortField="DbRole" DataTextField="DbRole"
DataValueField="DbRole" Width="245px" AutoPostBack="True"
AllowAutomaticUpdates="True">
</telerik:RadListBox>
<table cellpadding="0" cellspacing="0" style="width: 100%">
<tr>
<td>
</td>
</tr>
<tr>
<td>
<asp:Button ID="btn_DeleteJobRole" runat="server" Text="Delete Job" />
</td>
</tr>
<tr>
<td style="text-align: center">
</td>
</tr>
<tr>
<td class="tbltdHeader">
Add Job Role</td>
</tr>
<tr>
<td>
<telerik:RadComboBox ID="rcb_JobRoles" Runat="server"
DataTextField="DbRole"
DataValueField="DbRole" AppendDataBoundItems="True"
Height="300px">
<Items>
<telerik:RadComboBoxItem Value = "" Text="Select" />
</Items>
</telerik:RadComboBox>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
<asp:Button ID="btn_AddJobRole" runat="server" Text="Add Job" />
</td>
</tr>
</table>
</td>
<td style="width: 250px; vertical-align: top;">
<telerik:RadListBox ID="rlb_TaskRole" runat="server"
DataKeyField="dbRole"
DataSortField="dbRole" DataTextField="dbRole"
DataValueField="dbRole" Width="245px">
</telerik:RadListBox>
</td>
<td style="width: 250px; vertical-align: top;">
<telerik:RadListBox ID="rlb_ExplicitTaskRole" runat="server"
DataKeyField="DbRole"
DataSortField="DbRole" DataTextField="DbRole"
DataValueField="DbRole" Width="245px"
AllowAutomaticUpdates="True">
</telerik:RadListBox>
<table cellpadding="0" cellspacing="0" style="width: 100%">
<tr>
<td>
</td>
</tr>
<tr>
<td>
<asp:Button ID="btn_DeleteTaskRole" runat="server" Text="Delete Task" />
</td>
</tr>
<tr>
<td style="text-align: center">
</td>
</tr>
<tr>
<td class="tbltdHeader">
Add Task Role</td>
</tr>
<tr>
<td>
<telerik:RadComboBox ID="rcb_TaskRoles" Runat="server"
DataTextField="DbRole"
DataValueField="DbRole" AppendDataBoundItems="True"
Height="300px">
<Items>
<telerik:RadComboBoxItem Value = "" Text="Select" />
</Items>
</telerik:RadComboBox>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
<asp:Button ID="btn_AddTaskRole" runat="server" Text="Add Task" />
</td>
</tr>
</table>
</td>
</tr>
</table>
<hr class="hrblue"/>
<table cellpadding="0" cellspacing="0" style="width: 100%">
<tr>
<td class="tbltdHeader" valign="top" width="275px">
Assigned Sites</td>
<td class="tbltdHeader" valign="top">
Available Sites</td>
</tr>
<tr>
<td valign="top" width="275px">
<telerik:RadListBox ID="rlb_UserSites" runat="server"
DataKeyField="SiteName" DataSortField="SiteName" DataTextField="SiteName"
DataValueField="SiteName" Width="245px" AllowAutomaticUpdates="True">
<ItemTemplate>
<div>
<table cellpadding="0" cellspacing="0" style="padding: 0px; margin: 0px; width: 100%; border-collapse: collapse; border-spacing: 0px;">
<tr>
<td style="width: 20px">
<%# DataBinder.Eval(Container.DataItem, "IDSite")%></td>
<td style="width: 8px; border-left-style: solid; border-left-width: 1px; border-left-color: #000000; ">
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "SiteName")%></td>
</tr>
</table>
</div>
</ItemTemplate>
</telerik:RadListBox>
</td>
<td valign="top">
<telerik:RadListBox ID="rlb_UserNotInSites" runat="server"
DataKeyField="SiteName" DataSortField="SiteName" DataTextField="SiteName"
DataValueField="SiteName" Width="245px" AllowAutomaticUpdates="True"
onclientitemdoubleclicked="SiteAdd" AutoPostBack="True">
<ItemTemplate>
<div>
<table cellpadding="0" cellspacing="0" style="padding: 0px; margin: 0px; width: 100%; border-collapse: collapse; border-spacing: 0px;">
<tr>
<td style="width: 20px">
<%# DataBinder.Eval(Container.DataItem, "IDSite")%></td>
<td style="width: 8px; border-left-style: solid; border-left-width: 1px; border-left-color: #000000; ">
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "SiteName")%></td>
</tr>
</table>
</div>
</ItemTemplate>
</telerik:RadListBox>
</td>
</tr>
</table>
<hr class="hrblue"/>
<h2>
User Notes</h2>
<telerik:RadGrid ID="rgd_UserNotes" runat="server" DataSourceID="sds_UserNotes"
GridLines="None" AutoGenerateDeleteColumn="True" AutoGenerateEditColumn="True"
Width="900px" AllowAutomaticDeletes="True" AllowAutomaticInserts="True"
AllowAutomaticUpdates="True" CssClass="radgrid" Skin="Telerik">
<MasterTableView CommandItemDisplay="Bottom" AutoGenerateColumns="False" DataKeyNames="IDUserNotes" DataSourceID="sds_UserNotes">
<RowIndicatorColumn>
<HeaderStyle Width="20px"></HeaderStyle>
</RowIndicatorColumn>
<ExpandCollapseColumn>
<HeaderStyle Width="20px"></HeaderStyle>
</ExpandCollapseColumn>
<CommandItemSettings ExportToPdfText="Export to Pdf"></CommandItemSettings>
<Columns>
<telerik:GridBoundColumn DataField="EnterDate" DefaultInsertValue="" HeaderText=" " SortExpression="EnterDate" UniqueName="EnterDate" ItemStyle-VerticalAlign="Middle" ItemStyle-Width="75px" DataFormatString="{0:d}">
<ItemStyle VerticalAlign="Middle"></ItemStyle>
</telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="UserName" DefaultInsertValue="" HeaderText="UserName" SortExpression="UserName" UniqueName="UserName" ItemStyle-VerticalAlign="Middle" Visible="False">
<ItemStyle VerticalAlign="Middle"></ItemStyle>
</telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="Notes" DefaultInsertValue="" HeaderText=" " SortExpression="Notes" UniqueName="Notes" Visible="True">
</telerik:GridBoundColumn>
</Columns>
<EditFormSettings EditFormType="Template">
<FormTemplate>
<h2>
Item</h2>
<table cellpadding="0" cellspacing="0" style="width: 100%">
<tr>
<td class="tbltd" style="width: 98px">
User Name:</td>
<td style="width: 210px">
<asp:TextBox ID="tbx_UserName" Text='<%# Bind("UserName") %>' runat="server"></asp:TextBox>
</td>
<td class="tbltd">
</td>
</tr>
<tr>
<td class="tbltd" style="width: 98px">
Notes</td>
<td style="width: 210px">
<asp:TextBox ID="tbx_Notes" Text='<%# Bind("Notes") %>' runat="server"></asp:TextBox>
</td>
</tr>
</table>
<table cellpadding="0" cellspacing="0" style="width: 100%">
<tr>
<td style="width: 66px">
<asp:Button ID="btnUpdate" Text='<%# IIf((TypeOf(Container) is GridEditFormInsertItem), "Insert", "Update") %>' runat="server" CommandName='<%# IIf((TypeOf(Container) is GridEditFormInsertItem), "PerformInsert", "Update")%>'></asp:Button>
</td>
<td>
<asp:Button ID="btnCancel" Text="Cancel" runat="server" CausesValidation="False" CommandName="Cancel"></asp:Button>
</td>
<td>
</td>
</tr>
</table>
<br />
</FormTemplate>
</EditFormSettings>
</MasterTableView>
</telerik:RadGrid>
<p>
</p>
<p>
</p>
</td>
</tr>
</table>
</td>
</tr>
</table>
</div>
<div id="footer">
</div>
<telerik:RadToolTip ID="RadToolTip4" runat="server" Height="25px" Width="150px"
TargetControlID="ImageButton1" RelativeTo="Element"
Position="TopCenter" EnableShadow="true" Sticky="True" >
Click on a Job Role to<br /> display its associated<br /> Task Roles.
</telerik:RadToolTip>
<asp:SqlDataSource ID="sds_AA_Menu" runat="server"
ConnectionString="<%$ ConnectionStrings:AeriesAdmin_ConnectionString %>"
SelectCommand="SELECT [DataFieldID], [DataFieldParentID], [DataTextField], [DataNavigateUrlField], [ImageURL], [UserLevel] FROM [AA_Menu] WHERE ([UserLevel] LIKE '%' + @UserLevel + '%')">
<SelectParameters>
<asp:SessionParameter Name="UserLevel" SessionField="RoleName" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<telerik:RadScriptManager ID="rsm_AeriesAdmin" Runat="server">
</telerik:RadScriptManager>
<telerik:RadFormDecorator ID="rfd_AeriesAdmin" Runat="server" Skin="WebBlue" />
<br />
<asp:SqlDataSource ID="sds_UserDetails" runat="server"
ConnectionString="<%$ ConnectionStrings:AeriesAdmin_ConnectionString %>"
SelectCommand="SELECT [FullName], [UserName], [EmailAddress], [JobTitle], [Phone1], [Site], [Department], [PhoneIP] FROM [vw_AA_Users] WHERE ([UserName] = @UserName)">
<SelectParameters>
<asp:ControlParameter ControlID="rcb_Users" Name="UserName"
PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="sds_Database" runat="server"
ConnectionString="<%$ ConnectionStrings:AeriesAdmin_ConnectionString %>"
SelectCommand="SELECT DISTINCT [DatabaseName] FROM [vw_AA_Databases] ORDER BY [DatabaseName]">
</asp:SqlDataSource>
<asp:SqlDataSource ID="sds_UserNotes" runat="server"
ConnectionString="<%$ ConnectionStrings:AeriesAdmin_ConnectionString %>"
DeleteCommand="DELETE FROM [AA_UserNotes] WHERE [IDUSerNotes] = @IDUSerNotes"
InsertCommand="INSERT INTO [AA_UserNotes] ([UserName], [Notes]) VALUES (@UserName, @Notes)"
SelectCommand="SELECT [IDUSerNotes], [EnterDate], [UserName], [Notes] FROM [AA_UserNotes] WHERE ([UserName] = @UserName)"
UpdateCommand="UPDATE [AA_UserNotes] SET [UserName] = @UserName, [Notes] = @Notes WHERE [IDUSerNotes] = @IDUSerNotes">
<DeleteParameters>
<asp:Parameter Name="IDUSerNotes" Type="Int32" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="UserName" Type="String" />
<asp:Parameter Name="Notes" Type="String" />
</InsertParameters>
<SelectParameters>
<asp:ControlParameter ControlID="rcb_Users" Name="UserName"
PropertyName="SelectedValue" Type="String" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="UserName" Type="String" />
<asp:Parameter Name="Notes" Type="String" />
<asp:Parameter Name="IDUSerNotes" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>
<telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">
<AjaxSettings>
<telerik:AjaxSetting AjaxControlID="rlb_UserNotInSites">
</telerik:AjaxSetting>
</AjaxSettings>
</telerik:RadAjaxManager>
<br />
<br />
</form>
</body>
</html>
Imports System.Data
Imports System.Data.SqlClient
Imports Telerik.Web.UI
Imports System.CodeDom
Imports System.Web
Imports System.Web.Security
Imports System.Web.Security.Roles
Imports System.Web.Security.Membership
Imports System.Security
Imports System.Security.Principal.WindowsIdentity
Imports System.Drawing
Imports System.Threading
Partial Class _Default
Inherits System.Web.UI.Page
'Protected Sub RadPanelBar1_DataBound1(ByVal sender As Object, ByVal e As System.EventArgs) Handles rpb_AA_Menu.DataBound
' rpb_AA_Menu.Items(0).Expanded = True
' rpb_AA_Menu.Items(1).Expanded = True
'End Sub
'**********************************************************************
'Format combo boxes
'**********************************************************************
Protected Sub rcb_Roles_DataBound(sender As Object, e As System.EventArgs) Handles rcb_JobRoles.DataBound
Dim cbox = DirectCast(sender, RadComboBox)
Dim MaxWidth As Integer = 155
For Each item As RadComboBoxItem In cbox.Items
Dim Width As Integer = TextWidth(item.Text)
If Width > MaxWidth Then
MaxWidth = Width
End If
Next
cbox.DropDownWidth = New Unit(MaxWidth)
End Sub
Protected Sub rcb_TaskRoles_DataBound(sender As Object, e As System.EventArgs) Handles rcb_TaskRoles.DataBound
Dim cbox = DirectCast(sender, RadComboBox)
Dim MaxWidth As Integer = 155
For Each item As RadComboBoxItem In cbox.Items
Dim Width As Integer = TextWidth(item.Text)
If Width > MaxWidth Then
MaxWidth = Width
End If
Next
cbox.DropDownWidth = New Unit(MaxWidth)
End Sub
Public Shared Function TextWidth(TheText As [String]) As Integer
Dim DrawFont As Font = Nothing
Dim DrawGraphics As Graphics = Nothing
Dim TextBitmap As Bitmap = Nothing
Try
TextBitmap = New Bitmap(1, 1)
DrawGraphics = Graphics.FromImage(TextBitmap)
DrawFont = New Font("Segoe UI", 12)
Dim Width As Integer = CInt(DrawGraphics.MeasureString(TheText, DrawFont).Width)
Return Width
Finally
TextBitmap.Dispose()
DrawFont.Dispose()
DrawGraphics.Dispose()
End Try
End Function
Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
rcb_Database.Visible = True
ElseIf Page.IsPostBack Then
rcb_Database.Visible = False
End If
If Not Page.IsPostBack Then
rcb_Database0.Visible = False
ElseIf Page.IsPostBack Then
rcb_Database0.Visible = True
End If
End Sub
'**********************************************************************
'Select year and populate user data combo box
'**********************************************************************
Protected Sub rcb_Database_SelectedIndexChanged(o As Object, e As Telerik.Web.UI.RadComboBoxSelectedIndexChangedEventArgs) Handles rcb_Database.SelectedIndexChanged
Dim DatabaseName As String = rcb_Database.SelectedValue
Session("DBName") = DatabaseName
rcb_Users.DataSource = GetUsers()
rcb_Users.DataBind()
End Sub
Protected Sub rcb_Database0_SelectedIndexChanged(o As Object, e As Telerik.Web.UI.RadComboBoxSelectedIndexChangedEventArgs) Handles rcb_Database0.SelectedIndexChanged
Dim DatabaseName As String = rcb_Database0.SelectedValue
Session("DBName") = DatabaseName
rcb_Users.DataSource = GetUsers()
rcb_Users.DataBind()
rlb_JobRole.Items.Clear()
rlb_JobRole.DataSource = GetJobRoles()
rlb_JobRole.DataBind()
rlb_ExplicitTaskRole.DataSource = GetExplicitTaskRole()
rlb_ExplicitTaskRole.DataBind()
rcb_JobRoles.DataSource = GetJobRoleList()
rcb_JobRoles.DataBind()
rcb_TaskRoles.DataSource = GetTaskRoleList()
rcb_TaskRoles.DataBind()
rlb_UserSites.DataSource = GetUsersInSites()
rlb_UserSites.DataBind()
rlb_UserNotInSites.DataSource = GetUsersNotInSites()
rlb_UserNotInSites.DataBind()
End Sub
Private Function GetUsers() As DataTable
Dim DatabaseName As String = Session("DBName")
Dim connectionString = New SqlConnection("server=DO-IT-AB\MSSQLAB;database=AeriesAdmin;UID=itc.sysadmin;PWD=m1s.@dm1n6;")
Dim command = New SqlCommand("aa_users", connectionString)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@dbname", SqlDbType.VarChar).Value = DatabaseName + "."
command.Connection.Open()
Dim myDataAdapter As New SqlDataAdapter(command)
Dim myDataSet As New DataSet
Dim dtData As New DataTable
myDataAdapter.Fill(myDataSet)
Return myDataSet.Tables(0)
command.Connection.Close()
End Function
'**********************************************************************
'Get User's Job Roles
'**********************************************************************
Protected Sub rcb_Users_SelectedIndexChanged(o As Object, e As Telerik.Web.UI.RadComboBoxSelectedIndexChangedEventArgs) Handles rcb_Users.SelectedIndexChanged
Dim UserName As String = rcb_Users.SelectedValue
Session("UserInRoleName") = UserName
rlb_JobRole.DataSource = GetJobRoles()
rlb_JobRole.DataBind()
rlb_ExplicitTaskRole.DataSource = GetExplicitTaskRole()
rlb_ExplicitTaskRole.DataBind()
rcb_JobRoles.DataSource = GetJobRoleList()
rcb_JobRoles.DataBind()
rcb_TaskRoles.DataSource = GetTaskRoleList()
rcb_TaskRoles.DataBind()
rlb_UserSites.DataSource = GetUsersInSites()
rlb_UserSites.DataBind()
rlb_UserNotInSites.DataSource = GetUsersNotInSites()
rlb_UserNotInSites.DataBind()
End Sub
Private Function GetJobRoles() As DataTable
Dim DatabaseName As String = Session("DBName")
Dim MemberName As String = Session("UserInRoleName")
Dim connectionString = New SqlConnection("server=DO-IT-AB\MSSQLAB;database=AeriesAdmin;UID=itc.sysadmin;PWD=m1s.@dm1n6;")
Dim command = New SqlCommand("aa_user_in_job_role", connectionString)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@dbname", SqlDbType.VarChar).Value = DatabaseName + "."
command.Parameters.Add("@rolename", SqlDbType.VarChar).Value = MemberName
command.Connection.Open()
Dim myDataAdapter As New SqlDataAdapter(command)
Dim myDataSet As New DataSet
Dim dtData As New DataTable
myDataAdapter.Fill(myDataSet)
Return myDataSet.Tables(0)
command.Connection.Close()
End Function
'**********************************************************************
'Get User's Task Roles
'**********************************************************************
Protected Sub rlb_JobRole_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles rlb_JobRole.SelectedIndexChanged
Session("JobRole") = rlb_JobRole.SelectedValue
rlb_TaskRole.DataSource = GetTaskRoles()
rlb_TaskRole.DataBind()
End Sub
Private Function GetTaskRoles() As DataTable
Dim DatabaseName As String = Session("DBName")
Dim RoleName As String = Session("JobRole")
Dim connectionString = New SqlConnection("server=DO-IT-AB\MSSQLAB;database=AeriesAdmin;UID=itc.sysadmin;PWD=m1s.@dm1n6;")
Dim command = New SqlCommand("aa_user_in_task_role", connectionString)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@dbname", SqlDbType.VarChar).Value = DatabaseName + "."
command.Parameters.Add("@rolename", SqlDbType.VarChar).Value = RoleName
command.Connection.Open()
Dim myDataAdapter As New SqlDataAdapter(command)
Dim myDataSet As New DataSet
Dim dtData As New DataTable
myDataAdapter.Fill(myDataSet)
Return myDataSet.Tables(0)
command.Connection.Close()
End Function
'**********************************************************************
'Get Explicit Task Roles
'**********************************************************************
Private Function GetExplicitTaskRole() As DataTable
Dim DatabaseName As String = Session("DBName")
Dim MemberName As String = Session("UserInRoleName")
Dim connectionString = New SqlConnection("server=DO-IT-AB\MSSQLAB;database=AeriesAdmin;UID=itc.sysadmin;PWD=m1s.@dm1n6;")
Dim command = New SqlCommand("aa_user_in_explicit_task_role", connectionString)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@dbname", SqlDbType.VarChar).Value = DatabaseName + "."
command.Parameters.Add("@rolename", SqlDbType.VarChar).Value = MemberName
command.Connection.Open()
Dim myDataAdapter As New SqlDataAdapter(command)
Dim myDataSet As New DataSet
Dim dtData As New DataTable
myDataAdapter.Fill(myDataSet)
Return myDataSet.Tables(0)
command.Connection.Close()
End Function
'**********************************************************************
'Populate lists
'**********************************************************************
Private Function GetJobRoleList() As DataTable
Dim DatabaseName As String = Session("DBName")
Dim connectionString = New SqlConnection("server=DO-IT-AB\MSSQLAB;database=AeriesAdmin;UID=itc.sysadmin;PWD=m1s.@dm1n6;")
Dim command = New SqlCommand("aa_roles_jobs", connectionString)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@dbname", SqlDbType.VarChar).Value = DatabaseName + "."
command.Connection.Open()
Dim myDataAdapter As New SqlDataAdapter(command)
Dim myDataSet As New DataSet
Dim dtData As New DataTable
myDataAdapter.Fill(myDataSet)
Return myDataSet.Tables(0)
command.Connection.Close()
End Function
Private Function GetTaskRoleList() As DataTable
Dim DatabaseName As String = Session("DBName")
Dim connectionString = New SqlConnection("server=DO-IT-AB\MSSQLAB;database=AeriesAdmin;UID=itc.sysadmin;PWD=m1s.@dm1n6;")
Dim command = New SqlCommand("aa_roles_tasks", connectionString)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@dbname", SqlDbType.VarChar).Value = DatabaseName + "."
command.Connection.Open()
Dim myDataAdapter As New SqlDataAdapter(command)
Dim myDataSet As New DataSet
Dim dtData As New DataTable
myDataAdapter.Fill(myDataSet)
Return myDataSet.Tables(0)
command.Connection.Close()
End Function
Private Function GetUsersInSites() As DataTable
Dim DatabaseName As String = Session("DBName")
Dim MemberName As String = Session("UserInRoleName")
Dim connectionString = New SqlConnection("server=DO-IT-AB\MSSQLAB;database=AeriesAdmin;UID=itc.sysadmin;PWD=m1s.@dm1n6;")
Dim command = New SqlCommand("aa_users_in_sites", connectionString)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@dbname", SqlDbType.VarChar).Value = DatabaseName
command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = MemberName
command.Connection.Open()
Dim myDataAdapter As New SqlDataAdapter(command)
Dim myDataSet As New DataSet
Dim dtData As New DataTable
myDataAdapter.Fill(myDataSet)
Return myDataSet.Tables(0)
command.Connection.Close()
End Function
Private Function GetUsersNotInSites() As DataTable
Dim DatabaseName As String = Session("DBName")
Dim MemberName As String = Session("UserInRoleName")
Dim connectionString = New SqlConnection("server=DO-IT-AB\MSSQLAB;database=AeriesAdmin;UID=itc.sysadmin;PWD=m1s.@dm1n6;")
Dim command = New SqlCommand("aa_users_not_in_sites", connectionString)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@dbname", SqlDbType.VarChar).Value = DatabaseName
command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = MemberName
command.Connection.Open()
Dim myDataAdapter As New SqlDataAdapter(command)
Dim myDataSet As New DataSet
Dim dtData As New DataTable
myDataAdapter.Fill(myDataSet)
Return myDataSet.Tables(0)
command.Connection.Close()
End Function
'**********************************************************************
'Button click events
'**********************************************************************
Protected Sub btn_DeleteJobRole_Click(sender As Object, e As System.EventArgs) Handles btn_DeleteJobRole.Click
For Each item As RadListBoxItem In rlb_JobRole.SelectedItems
Session("DropJobRole") = item.Value.ToString()
Next
Dim DatabaseName As String = Session("DBName")
Dim MemberName As String = Session("UserInRoleName")
Dim connectionString = New SqlConnection("server=DO-IT-AB\MSSQLAB;database=AeriesAdmin;UID=itc.sysadmin;PWD=m1s.@dm1n6;")
Dim command = New SqlCommand("aa_user_job_role_drop", connectionString)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@dbname", SqlDbType.VarChar).Value = DatabaseName + "."
command.Parameters.Add("@membername", SqlDbType.VarChar).Value = MemberName
command.Parameters.Add("@rolename", SqlDbType.VarChar).Value = Session("DropJobRole")
command.Connection.Open()
command.ExecuteScalar()
command.Connection.Close()
rlb_JobRole.DataSource = GetJobRoles()
rlb_JobRole.DataBind()
End Sub
Protected Sub btn_AddJobRole_Click(sender As Object, e As System.EventArgs) Handles btn_AddJobRole.Click
Dim UserName As String = Session("UserInRoleName")
Dim JobRole As String = rcb_JobRoles.SelectedValue
Dim DatabaseName As String = Session("DBName")
Dim connectionString = New SqlConnection("server=DO-IT-AB\MSSQLAB;database=AeriesAdmin;UID=itc.sysadmin;PWD=m1s.@dm1n6;")
Dim command = New SqlCommand("aa_user_job_role_add", connectionString)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@rolename", SqlDbType.VarChar).Value = JobRole
command.Parameters.Add("@membername", SqlDbType.VarChar).Value = UserName
command.Parameters.Add("@dbname", SqlDbType.VarChar).Value = DatabaseName + "."
command.Connection.Open()
command.ExecuteScalar()
command.Connection.Close()
rlb_JobRole.DataSource = GetJobRoles()
rlb_JobRole.DataBind()
End Sub
Protected Sub btn_DeleteTaskRole_Click(sender As Object, e As System.EventArgs) Handles btn_DeleteTaskRole.Click
For Each item As RadListBoxItem In rlb_ExplicitTaskRole.SelectedItems
Session("DropJobRole") = item.Value.ToString()
Next
Dim DatabaseName As String = Session("DBName")
Dim MemberName As String = Session("UserInRoleName")
Dim connectionString = New SqlConnection("server=DO-IT-AB\MSSQLAB;database=AeriesAdmin;UID=itc.sysadmin;PWD=m1s.@dm1n6;")
Dim command = New SqlCommand("aa_user_job_role_drop", connectionString)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@dbname", SqlDbType.VarChar).Value = DatabaseName + "."
command.Parameters.Add("@membername", SqlDbType.VarChar).Value = MemberName
command.Parameters.Add("@rolename", SqlDbType.VarChar).Value = Session("DropJobRole")
command.Connection.Open()
command.ExecuteScalar()
command.Connection.Close()
rlb_ExplicitTaskRole.DataSource = GetExplicitTaskRole()
rlb_ExplicitTaskRole.DataBind()
End Sub
Protected Sub btn_AddTaskRole_Click(sender As Object, e As System.EventArgs) Handles btn_AddTaskRole.Click
Dim UserName As String = Session("UserInRoleName")
Dim JobRole As String = rcb_TaskRoles.SelectedValue
Dim DatabaseName As String = Session("DBName")
Dim connectionString = New SqlConnection("server=DO-IT-AB\MSSQLAB;database=AeriesAdmin;UID=itc.sysadmin;PWD=m1s.@dm1n6;")
Dim command = New SqlCommand("aa_user_job_role_add", connectionString)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@rolename", SqlDbType.VarChar).Value = JobRole
command.Parameters.Add("@membername", SqlDbType.VarChar).Value = UserName
command.Parameters.Add("@dbname", SqlDbType.VarChar).Value = DatabaseName + "."
command.Connection.Open()
command.ExecuteScalar()
command.Connection.Close()
rlb_ExplicitTaskRole.DataSource = GetExplicitTaskRole()
rlb_ExplicitTaskRole.DataBind()
End Sub
Protected Sub rgd_UserNotes_ItemDataBound1(ByVal sender As Object, ByVal e As Telerik.Web.UI.GridItemEventArgs) Handles rgd_UserNotes.ItemDataBound
If TypeOf e.Item Is GridEditFormInsertItem AndAlso e.Item.OwnerTableView.IsItemInserted Then
Dim insertItem As GridEditFormInsertItem = DirectCast(e.Item, GridEditFormInsertItem)
Dim UserName As TextBox = DirectCast(e.Item.FindControl("tbx_UserName"), TextBox)
UserName.Text = Session("UserInRoleName")
End If
End Sub
Protected Sub RadAjaxManager1_AjaxRequest1(sender As Object, e As Telerik.Web.UI.AjaxRequestEventArgs) Handles RadAjaxManager1.AjaxRequest
RemoveUserFromSite()
End Sub
Private Sub RemoveUserFromSite()
Dim DatabaseName As String = Session("DBName")
Dim UserName As String = Session("UserInRoleName")
Dim IDSite As String = "4"
Dim connectionString = New SqlConnection("server=DO-IT-AB\MSSQLAB;database=AeriesAdmin;UID=itc.sysadmin;PWD=m1s.@dm1n6;")
Dim command = New SqlCommand("aa_users_in_sites_add", connectionString)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@dbname", SqlDbType.VarChar).Value = DatabaseName
command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = UserName
command.Parameters.Add("@IDSite", SqlDbType.VarChar).Value = IDSite
command.Connection.Open()
command.ExecuteScalar()
command.Connection.Close()
rlb_UserNotInSites.Items.Clear()
End Sub
End Class