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">
        <MasterTableView>
            <CommandItemSettings ExportToPdfText="Export to Pdf"></CommandItemSettings>
            <RowIndicatorColumn>
            <HeaderStyle Width="20px"></HeaderStyle>
            </RowIndicatorColumn>
            <ExpandCollapseColumn>
            <HeaderStyle Width="20px"></HeaderStyle>
            </ExpandCollapseColumn>
                <Columns>
                    <telerik:GridBoundColumn DataField="Object" HeaderText="Table" 
                        SortExpression="Object" UniqueName="Object" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center">
                    </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="Select" HeaderText="Select" 
                        SortExpression="Select" UniqueName="Select" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center">
                    </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="Update" HeaderText="Update" 
                        SortExpression="Update" UniqueName="Update" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center">
                    </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="Insert" HeaderText="Insert" 
                        SortExpression="Insert" UniqueName="Insert" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="Delete" HeaderText="Delete" 
                        SortExpression="Delete" UniqueName="Delete" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center">
                    </telerik:GridBoundColumn>
                    <telerik:GridTemplateColumn UniqueName="TemplateColumn">
                        <ItemTemplate
                        <asp:Button ID="btn_ModifyPerms" runat="server" Text="Modify"  />
                        </ItemTemplate>
                    </telerik:GridTemplateColumn>
                </Columns>
        </MasterTableView>
    </telerik:RadGrid>

    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()
        rgd_TaskPermissions.DataBind()
    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
        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

    SP

    USE [AeriesAdmin]
    GO
      
    SET ANSI_NULLS ON
    GO
      
    SET QUOTED_IDENTIFIER ON
    GO
      
    CREATE PROCEDURE [dbo].[aa_task_table_permissions_by_object] 
      
    @dbname sysname, 
    @Grantee varchar(25),
    @Table varchar(25)
        
    AS 
    BEGIN 
      
    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 
    [Owner], 
    [Object], 
    [Grantee], 
    [Grantor], 
    [ProtectType], 
    [Action], 
    [Column
    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
      
    END
      
    GO

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

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

    VB.NET:
    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

    Thanks,
    Princy.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  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