Stored Procedure create Check Boxes

3 posts, 1 answers
  1. Allan
    Allan avatar
    121 posts
    Member since:
    Jun 2008

    Posted 14 Mar 2011 Link to this post

    I have the following Grid on a form:

        <telerik:RadGrid ID="rgd_TaskPermissions" runat="server"  GridLines="None" AutoGenerateColumns="False" Width="320px">
            <CommandItemSettings ExportToPdfText="Export to Pdf"></CommandItemSettings>
            <HeaderStyle Width="20px"></HeaderStyle>
            <HeaderStyle Width="20px"></HeaderStyle>
                    <telerik:GridBoundColumn DataField="Object" HeaderText="Table" 
                        SortExpression="Object" UniqueName="Object" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center">
                            <telerik:GridBoundColumn DataField="Select" HeaderText="Select" 
                        SortExpression="Select" UniqueName="Select" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center">
                            <telerik:GridBoundColumn DataField="Update" HeaderText="Update" 
                        SortExpression="Update" UniqueName="Update" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center">
                            <telerik:GridBoundColumn DataField="Insert" HeaderText="Insert" 
                        SortExpression="Insert" UniqueName="Insert" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center">
                    <telerik:GridBoundColumn DataField="Delete" HeaderText="Delete" 
                        SortExpression="Delete" UniqueName="Delete" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center">
                    <telerik:GridTemplateColumn UniqueName="TemplateColumn">
                        <asp:Button ID="btn_ModifyPerms" runat="server" Text="Modify"  />

    I fill the grid by calling a Stored Procedure in SQL

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Label1.Text = Session("Object")
        rgd_TaskPermissions.DataSource = GetPermissions()
    End Sub
    Private Function GetPermissions() As DataTable
        Dim Table As String = Session("Object")
        Dim DatabaseName As String = Session("DBName")
        Dim Grantee As String = Session("TaskRole")
        Dim connectionString = New SqlConnection("server=DO-IT-AB\MSSQLAB;database=AeriesAdmin;UID=XXX;PWD=XXX;")
        Dim command = New SqlCommand("aa_task_table_permissions_by_object", connectionString)
        command.CommandType = CommandType.StoredProcedure
        command.Parameters.Add("@DBName", SqlDbType.VarChar).Value = DatabaseName
        command.Parameters.Add("@Grantee", SqlDbType.VarChar).Value = Grantee
        command.Parameters.Add("@Table", SqlDbType.VarChar).Value = Table
        Dim myDataAdapter As New SqlDataAdapter(command)
        Dim myDataSet As New DataSet
        Dim dtData As New DataTable
        Return myDataSet.Tables(0)
    End Function


    USE [AeriesAdmin]
    CREATE PROCEDURE [dbo].[aa_task_table_permissions_by_object] 
    @dbname sysname, 
    @Grantee varchar(25),
    @Table varchar(25)
    IF OBJECT_ID('tempdb..#tmpResults') IS NOT NULL 
    DROP TABLE #tmpResults 
    CREATE TABLE #tmpResults 
    [Owner] NVARCHAR(MAX), 
    [Object] NVARCHAR(MAX), 
    [Grantee] NVARCHAR(MAX), 
    [Grantor] NVARCHAR(MAX),  
    [ProtectType] NVARCHAR(MAX), 
    [Action] NVARCHAR(MAX), 
    [Column] NVARCHAR(MAX
    DECLARE @sp_executesql nvarchar(1024) 
    SELECT @sp_executesql = @dbname + '.sys.sp_executesql' 
    INSERT INTO #tmpResults 
    EXECUTE @sp_ExecuteSQL N'sp_Helprotect' 
    ;with cte as (select Object, Grantee, Action,  
    row_number() over (partition by Object, Grantee  ORDER BY Action) as Row from #TmpResults) 
    select Object, Grantee, max(case when Action = 'Delete' then char(88) end) as [Delete],
    max(case when Action = 'Insert' then char(88) end) as [Insert],
    max(case when Action = 'Select' then char(88) end) as [Select],
    max(case when Action = 'Update' then char(88) end) as [Update]
    from #TmpResults
    WHERE Grantee = @Grantee AND [Object] LIKE @Table
    GROUP by Object, Grantee

    This all works great. However, I would like to extend this somewhat in that rather than just displaying the letter X in the row of the grid for each column, I would like to display a checkbox that is either checked or unched depending on the value in the cell. Is there a way to accomplish this, and if so, would it be possible to see an example?

    Thank you in advance.

  2. Answer
    Princy avatar
    17421 posts
    Member since:
    Mar 2007

    Posted 15 Mar 2011 Link to this post

    Hello Allan,

    You can add one Checkbox in GridTemplateColumn and from code behind based on the cell value select the CheckBox state accordingly.

         <telerik:GridBoundColumn DataField="Object" UniqueName="Object">
         <telerik:GridTemplateColumn UniqueName="TemplateColumn">
                 <asp:CheckBox ID="CheckBox1" runat="server" />

    Protected Sub RadGrid1_ItemDataBound(sender As Object, e As GridItemEventArgs)
        If TypeOf e.Item Is GridDataItem Then
            Dim item As GridDataItem = DirectCast(e.Item, GridDataItem)
            Dim chkbox As CheckBox = DirectCast(item.FindControl("CheckBox1"), CheckBox)
            If item("Object").Text = "1" Then
                'your condition
                chkbox.Checked = True
            End If
        End If
    End Sub

  3. DevCraft R3 2016 release webinar banner
  4. Allan
    Allan avatar
    121 posts
    Member since:
    Jun 2008

    Posted 15 Mar 2011 Link to this post

    Works great!

    Thank you.
Back to Top