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

Stored Procedure create Check Boxes

4 Answers 69 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Allan
Top achievements
Rank 2
Allan asked on 14 Mar 2011, 08:35 PM
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.










4 Answers, 1 is accepted

Sort by
0
Accepted
Princy
Top achievements
Rank 1
answered on 15 Mar 2011, 05:53 AM
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.
0
Allan
Top achievements
Rank 2
answered on 15 Mar 2011, 04:05 PM
Works great!

Thank you.
0
lenard
Top achievements
Rank 1
answered on 21 Jan 2020, 10:26 AM

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

 

0
Rumen
Telerik team
answered on 23 Jan 2020, 08:38 AM

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

Get quickly onboarded and successful with UI for ASP.NET AJAX with the Virtual Classroom technical trainings, available to all active customers. Learn More.
Tags
Grid
Asked by
Allan
Top achievements
Rank 2
Answers by
Princy
Top achievements
Rank 1
Allan
Top achievements
Rank 2
lenard
Top achievements
Rank 1
Rumen
Telerik team
Share this question
or