This is a migrated thread and some comments may be shown as answers.

Refresh or Rebind Listbox

3 Answers 400 Views

ListBox

This is a migrated thread and some comments may be shown as answers.

Allan asked on 25 Feb 2011, 05:25 PM

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" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<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 />

</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

Refresh or Rebind Listbox

3 Answers, 1 is accepted