Stored Procedure create Check Boxes

[Allan]

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.

[Princy]

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.

[Allan]

Works great!

Thank you.

[lenard]

How can i insert a selected checkbox in radgrid column in database , using sql server ?

 

[Rumen]

Hi there,

You can try the solution provided at SO - https://stackoverflow.com/questions/7432355/checkbox-in-radgrid-and-sending-data-to-database. 

Regards,
Rumen
Progress Telerik