Finding and Editing Cell Data (ASP/C#)

[Matthew]

So here is my problem. I am trying to implement of sort of change log that happens during the insert/edit cycle. I can insert the users name and time stamp, but I cannot seem to grab whatever is currently available, add to it, and then return it back. Help would be greatly appreciated.

ASP:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CAF_Detail.aspx.cs" Inherits="DetailViews_CAF_Detail" MasterPageFile="~/EUEMain.master" %>

<%@ Register TagPrefix="telerik" Namespace="Telerik.Web.UI" Assembly="Telerik.Web.UI" %>
<asp:Content ID="Content1" ContentPlaceHolderID="cphLeftNav" runat="Server">
    <asp:HyperLink ID="HomeLink" runat="server" NavigateUrl="..\Default.aspx" Text="Home" />
    <%--<br />
    <asp:LinkButton runat="server" ID="ImageButton" Text="Export to Excel" OnClick="ImageButton_Click"></asp:LinkButton>
    <br />--%>
</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="cphContent" runat="Server">
    <h1>CAF Tracking System</h1>
    <telerik:RadScriptManager runat="server" ID="RadScriptManager1" />
    <telerik:RadSkinManager ID="RadSkinManager1" runat="server" ShowChooser="true" />
    <telerik:RadCodeBlock ID="RadCodeBlock2" runat="server">
        <script type="text/javascript">
            var manager;
            Sys.Application.add_load(function () {
                manager = $find('<%= RadAjaxManager.GetCurrent(Page).ClientID %>');
            });
        </script>
    </telerik:RadCodeBlock>
    <script type="text/javascript" src="../DetailViews/Scripts.js"></script>
    <telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">
        <AjaxSettings>
            <telerik:AjaxSetting AjaxControlID="RadGrid2">
                <UpdatedControls>
                    <telerik:AjaxUpdatedControl ControlID="RadGrid2" LoadingPanelID="RadAjaxLoadingPanel1"></telerik:AjaxUpdatedControl>
                </UpdatedControls>
            </telerik:AjaxSetting>
        </AjaxSettings>
    </telerik:RadAjaxManager>
    <telerik:RadAjaxLoadingPanel ID="RadAjaxLoadingPanel1" runat="server">
    </telerik:RadAjaxLoadingPanel>
    <div>
        <telerik:RadGrid ID="RadGrid2" DataSourceID="SqlDataSource1" AllowPaging="True" Width="100%"
            runat="server" AutoGenerateColumns="False" AllowSorting="True"
            AllowAutomaticInserts="true" AllowAutomaticUpdates="true" OnItemDataBound="RadGrid2_ItemDataBound" OnItemCommand="RadGrid2_ItemCommand">

            <MasterTableView DataKeyNames="Unique" AutoGenerateColumns="False" CommandItemDisplay="Top" DataSourceID="SqlDataSource1" EditMode="EditForms">
                <NestedViewTemplate>
                    <asp:Panel runat="server" ID="InnerContainer" Visible="False"></asp:Panel>
                    <telerik:RadMultiPage runat="server" ID="Multipage1" SelectedIndex="0" RenderSelectedPageOnly="False">

                        <telerik:RadPageView runat="server" ID="Details">
                            <asp:Label ID="Label1" Font-Bold="true" Font-Italic="true" Text='<%# Bind("Unique") %>'
                                Visible="False" runat="server"></asp:Label>
                            <telerik:RadGrid runat="server" ID="SubNotesGrid" DataSourceID="SqlDataSource3" ShowFooter="true"
                                AllowSorting="true" EnableLinqExpressions="false">
                                <MasterTableView ShowHeader="true" AutoGenerateColumns="False" AllowPaging="true"
                                    DataKeyNames="Unique, Changelog" PageSize="25" HierarchyLoadMode="Client">

                                    <Columns>
                                        <telerik:GridBoundColumn UniqueName="CAF_Number" DataField="CAF_Number" HeaderText="CAF Number" AllowFiltering="false" />
                                        <telerik:GridBoundColumn UniqueName="CAF_Rev" DataField="CAF_Rev" HeaderText="Revision" />
                                        <telerik:GridBoundColumn UniqueName="CAF_Approved" DataField="CAF_Approved" HeaderText="Approved" />
                                        <telerik:GridBoundColumn UniqueName="CAF_System" DataField="CAF_System" HeaderText="CAF System" />
                                        <telerik:GridBoundColumn UniqueName="CAF_Build" DataField="CAF_Build" HeaderText="Build" />
                                        <telerik:GridBoundColumn UniqueName="CAF_BNSF" DataField="CAF_BNSF" HeaderText="Affecting BNSF" />
                                        <telerik:GridBoundColumn UniqueName="CAF_Desc" DataField="CAF_Desc" HeaderText="CAF Description" AllowFiltering="False" />
                                        <telerik:GridBoundColumn UniqueName="CAF_Post_Date" DataField="CAF_Post_Date" HeaderText="Post Date" DataFormatString="{0:MM/dd/yyyy}" />
                                        <telerik:GridBoundColumn UniqueName="CAF_Due_Date" DataField="CAF_Due_Date" HeaderText="Due Date" DataFormatString="{0:MM/dd/yyyy}" />
                                        <telerik:GridBoundColumn UniqueName="Comments" DataField="Comments" HeaderText="Comments" />
                                        <telerik:GridBoundColumn UniqueName="ChangeLog" DataField="ChangeLog" HeaderText="ChangeLog" />

                                    </Columns>
                                </MasterTableView>
                            </telerik:RadGrid>
                            <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings: PtcDbModelEntities %>"
                                SelectCommand="SELECT * FROM [PtcDbTracker].[dbo].[CAFTable] where [Unique] = @Unique">
                                <SelectParameters>
                                    <asp:ControlParameter ControlID="Label1" Name="Unique" />
                                </SelectParameters>
                            </asp:SqlDataSource>
                        </telerik:RadPageView>
                    </telerik:RadMultiPage>
                </NestedViewTemplate>

                <Columns>
                    <telerik:GridEditCommandColumn />
                    <telerik:GridBoundColumn UniqueName="CAF_Number" DataField="CAF_Number" HeaderText="CAF Number" AllowFiltering="false" />

                    <telerik:GridTemplateColumn UniqueName="CAF_Desc" DataField="CAF_Desc" HeaderText="Description" AllowFiltering="False">
                        <ItemTemplate>
                            <asp:Label runat="server" ID="caf_desc" Text='<%# Bind("CAF_Desc") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox runat="server" ID="CAF_Desc_TextBox" Width="300px" Height="75px" Text='<%# Bind("CAF_Desc") %>' Wrap="True" TextMode="MultiLine"></asp:TextBox>
                        </EditItemTemplate>
                    </telerik:GridTemplateColumn>

                    <telerik:GridTemplateColumn UniqueName="CAF_System" Visible="False" HeaderText="System">
                        <EditItemTemplate>
                            <asp:DropDownList ID="Label6" runat="server" SelectedValue='<%# Bind("CAF_System") %>'
                                DataSource='<%# (new[] { "OB", "BOS"}) %>'
                                AppendDataBoundItems="True"
                                CausesValidation="False">
                                <asp:ListItem Selected="True" Text="Select" Value="">
                                </asp:ListItem>
                            </asp:DropDownList>
                        </EditItemTemplate>
                    </telerik:GridTemplateColumn>
                    <telerik:GridBoundColumn UniqueName="CAF_Rev" DataField="CAF_Rev" HeaderText="Revision" Visible="True" />
                    <telerik:GridTemplateColumn UniqueName="Comments" DataField="Comments" HeaderText="Comments" AllowFiltering="False" Visible="False">
                        <EditItemTemplate>
                            <asp:TextBox runat="server" ID="Comments_TextBox" Width="400px" Height="150px" Text='<%# Bind("Comments") %>' Wrap="True" TextMode="MultiLine"></asp:TextBox>
                        </EditItemTemplate>
                    </telerik:GridTemplateColumn>
                    <telerik:GridDateTimeColumn UniqueName="CAF_Post_Date" DataField="CAF_Post_Date" PickerType="DatePicker" HeaderText="Post Date" Visible="False" />
                    <telerik:GridDateTimeColumn UniqueName="CAF_Due_Date" DataField="CAF_Due_Date" PickerType="DatePicker" Visible="False" HeaderText="Due Date" />
                    <telerik:GridBoundColumn UniqueName="CAF_Build" DataField="CAF_Build" Visible="False" HeaderText="Build" />
                    <telerik:GridTemplateColumn UniqueName="CAF_BNSF" Visible="False" HeaderText="Affecting BNSF">
                        <EditItemTemplate>
                            <asp:DropDownList ID="Label5" runat="server" SelectedValue='<%# Bind("CAF_BNSF") %>'
                                DataSource='<%# (new[] { "None", "Low", "Medium", "High"}) %>'
                                AppendDataBoundItems="True"
                                CausesValidation="False">
                                <asp:ListItem Selected="True" Text="Select" Value="">
                                </asp:ListItem>
                            </asp:DropDownList>
                        </EditItemTemplate>
                    </telerik:GridTemplateColumn>
                    <telerik:GridTemplateColumn UniqueName="CAF_Approved" DataField="CAF_Approved" Visible="True" HeaderText="Approved">
                        <ItemTemplate>
                            <asp:Label runat="server" ID="caf_approved" Text='<%#Bind("CAF_Approved") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:DropDownList ID="Label12" runat="server" SelectedValue='<%# Bind("CAF_Approved") %>'
                                DataSource='<%# (new[] { "Approved", "Not Approved"}) %>'
                                AppendDataBoundItems="True"
                                CausesValidation="False">
                                <asp:ListItem Selected="True" Text="Select" Value="">
                                </asp:ListItem>
                            </asp:DropDownList>
                        </EditItemTemplate>
                    </telerik:GridTemplateColumn>

                    <telerik:GridAttachmentColumn SortExpression="FileName" UploadControlType="RadAsyncUpload" DataSourceID="SqlDataSource5"
                        EditFormHeaderTextFormat="Upload File:" HeaderText="Attachment Column" AttachmentDataField="BinaryData"
                        AttachmentKeyFields="Unique" FileNameTextField="FileName" DataTextField="FileName"
                        UniqueName="AttachmentColumn" />
                    <telerik:GridBoundColumn UniqueName="ChangeLog" DataField="ChangeLog" HeaderText="ChangeLog" ReadOnly="True" Visible="True" />

                </Columns>
            </MasterTableView>
            <ClientSettings>
                <ClientEvents OnCommand="gridCommand"></ClientEvents>
            </ClientSettings>
        </telerik:RadGrid>
        <br />
    </div>

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings: PtcDbModelEntities %>"
        OnInserted="SqlDataSource2_Inserted"
        OnUpdated="SqlDataSource2_Updated"
        InsertCommand="INSERT INTO [PtcDbTracker].[dbo].[CAFTable] ([CAF_Number], [CAF_System], [CAF_Rev], [CAF_Build], [CAF_BNSF], [CAF_Desc], [Comments], [CAF_Post_Date], [CAF_Due_Date], [BinaryData], [CAF_Approved])
        VALUES (@CAF_Number, @CAF_System, @CAF_Rev, @CAF_Build, @CAF_BNSF, @CAF_Desc, @Comments, @CAF_Post_Date, @CAF_Due_Date, @BinaryData, @CAF_Approved) SET @InsertedID = SCOPE_IDENTITY()"
        SelectCommand="SELECT * FROM [PtcDbTracker].[dbo].[CAFTable] Order By [CAF_Number] DESC, [CAF_Rev] DESC"
        UpdateCommand="UPDATE [PtcDbTracker].[dbo].[CAFTable] SET [CAF_Number] = @CAF_Number
        , [CAF_System] = @CAF_System
        , [CAF_Rev] = @CAF_Rev
        , [CAF_Build] = @CAF_Build
        , [CAF_BNSF] = @CAF_BNSF
        , [CAF_Desc] = @CAF_Desc
        , [Comments] = @Comments
        , [CAF_Due_Date] = @CAF_Due_Date
        , [CAF_Post_Date] = @CAF_Post_Date
        , [CAF_Approved] = @CAF_Approved
        WHERE [Unique] = @Unique">
        <DeleteParameters>
            <asp:Parameter Name="Unique" Type="Int32"></asp:Parameter>
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="CAF_Number" Type="String"></asp:Parameter>
            <asp:Parameter Name="CAF_System" Type="String"></asp:Parameter>
            <asp:Parameter Name="CAF_Rev" Type="String"></asp:Parameter>
            <asp:Parameter Name="CAF_Build" Type="String"></asp:Parameter>
            <asp:Parameter Name="CAF_BNSF" Type="String"></asp:Parameter>
            <asp:Parameter Name="CAF_DESC" Type="String"></asp:Parameter>
            <asp:Parameter Name="Comments" Type="String"></asp:Parameter>
            <asp:Parameter Name="CAF_Post_Date" DbType="Date"></asp:Parameter>
            <asp:Parameter Name="CAF_Due_Date" DbType="Date"></asp:Parameter>
            <asp:Parameter Name="BinaryData" Type="Byte"></asp:Parameter>
            <asp:Parameter Name="CAF_Approved" Type="String" />
            <asp:Parameter Name="fileName" Type="String" />
            <asp:Parameter Name="ChangeLog" Type="String" />
            <asp:Parameter Name="InsertedID" Type="Int32" Direction="Output"></asp:Parameter>
        </InsertParameters>
        <UpdateParameters>
            <asp:Parameter Name="CAF_Number" Type="String"></asp:Parameter>
            <asp:Parameter Name="CAF_System" Type="String"></asp:Parameter>
            <asp:Parameter Name="CAF_Rev" Type="String"></asp:Parameter>
            <asp:Parameter Name="CAF_Build" Type="String"></asp:Parameter>
            <asp:Parameter Name="CAF_BNSF" Type="String"></asp:Parameter>
            <asp:Parameter Name="CAF_DESC" Type="String"></asp:Parameter>
            <asp:Parameter Name="Comments" Type="String"></asp:Parameter>
            <asp:Parameter Name="CAF_Post_Date" DbType="Date"></asp:Parameter>
            <asp:Parameter Name="CAF_Due_Date" DbType="Date"></asp:Parameter>
            <asp:Parameter Name="BinaryData" Type="Byte"></asp:Parameter>
            <asp:Parameter Name="fileName" Type="String" />
            <asp:Parameter Name="CAF_Approved" Type="String" />
            <asp:Parameter Name="ChangeLog" Type="String" />
        </UpdateParameters>
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:PtcDbModelEntities %>"
        SelectCommand="SELECT [Unique], [BinaryData] FROM [PtcDbTracker].[dbo].[CAFTable] WHERE [Unique] = @Unique">
        <SelectParameters>
            <asp:Parameter Name="Unique" Type="Int32"></asp:Parameter>
        </SelectParameters>
    </asp:SqlDataSource>
</asp:Content>

C# Code Behind:

using System.Text;
using Telerik.Web.UI;
using System;
using xi = Telerik.Web.UI.ExportInfrastructure;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;

public partial class DetailViews_CAF_Detail : BNSF.EUECommonApplication.EUEBasePage
{
    int fileId;
    byte[] fileData;
    string fileName;
    string mystring1;


    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
        }
    }
    protected void RadGrid2_ItemDataBound(object sender, GridItemEventArgs e)
    {
        var editItem = e.Item as GridEditFormItem;
        if (editItem != null && editItem.IsInEditMode)
        {
            var datePicker = editItem["CAF_Post_Date"].Controls[0] as RadDatePicker;
            datePicker.Width = Unit.Pixel(100);
            var datePicker2 = editItem["CAF_Due_Date"].Controls[0] as RadDatePicker;
            datePicker2.Width = Unit.Pixel(100);
        }
    }

    protected void SqlDataSource2_Inserted(object sender, SqlDataSourceStatusEventArgs e)
    {

        var mystring = new StringBuilder(mystring1);
        mystring.Append(EUEUser.DisplayName + ' ' + DateTime.Now);
        fileId = (int)e.Command.Parameters["@InsertedID"].Value;
        UpdateFileData("UPDATE [PtcDbTracker].[dbo].[CAFTable] SET [BinaryData] = @BinaryData, [FileName] = @FileName WHERE [Unique] = @Unique", fileId, mystring.ToString());
    }

    protected void SqlDataSource2_Updated(object sender, SqlDataSourceStatusEventArgs e)
    {
        var mystring = new StringBuilder(mystring1);
        mystring.Append(EUEUser.DisplayName + ' ' + DateTime.Now);
        UpdateFileData("UPDATE [PtcDbTracker].[dbo].[CAFTable] SET [BinaryData] = @BinaryData, [FileName] = @FileName, [ChangeLog] = @ChangeLog WHERE [Unique] = @Unique", fileId, mystring.ToString());
    }

    private void UpdateFileData(string command, int fileId, string mystring)
    {

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["PtcDbModelEntities"].ConnectionString))
        {
            using (SqlCommand comm = new SqlCommand(command, conn))
            {
                if (fileData != null && fileData.Length > 0)
                {
                    comm.Parameters.Add(new SqlParameter("Unique", fileId));
                    comm.Parameters.Add(new SqlParameter("BinaryData", fileData));
                    comm.Parameters.Add(new SqlParameter("FileName", fileName));
                    comm.Parameters.Add(new SqlParameter("ChangeLog", mystring));
                    conn.Open();
                    comm.ExecuteNonQuery();
                }
            }
        }
    }

    protected void RadGrid2_ItemCommand(object source, GridCommandEventArgs e)
    {

        if (e.CommandName == RadGrid.UpdateCommandName ||
            e.CommandName == RadGrid.PerformInsertCommandName)
        {
            var item = e.Item as GridEditableItem;

            if (!(item is GridEditFormInsertItem))
            {
                fileId = (int)item.GetDataKeyValue("Unique");
                mystring1 = (string)item.GetDataKeyValue("ChangeLog");
            }
            var asyncUpload = item["AttachmentColumn"].Controls[0] as RadAsyncUpload;
            if (asyncUpload != null && asyncUpload.UploadedFiles.Count > 0)
            {
                var uploadedFile = asyncUpload.UploadedFiles[0];
                fileData = new byte[uploadedFile.ContentLength];
                fileName = uploadedFile.FileName;
                using (Stream str = uploadedFile.InputStream)
                {
                    str.Read(fileData, 0, (int)uploadedFile.ContentLength);
                }
            }

        }
    }
}

[Konstantin Dikov]

Hi Matthew,

For retrieving the new values you can cast the item from the OnUpdateCommand and OnInsertCommand event arguments to the GridEditableItem and use the GridTableView ExtractValuesFromItem method:

Hashtable newValues = new Hashtable();
//The GridTableView will fill the values from all editable columns in the hash
editedItem.OwnerTableView.ExtractValuesFromItem(newValues, editedItem);

You can also use the ExctractValues method of the GridEditableItem in the same manner.

Another option that you have is to manually find all the controls that will hold the new values, by using the FindControl method from the GridEditableItem.

The following help articles should help you with your requirement:

• Accessing Cells and Rows
• Performing Batch Updates 
• Updating Values Using InPlace and EditForms Modes
  

Regards,
Konstantin Dikov
Telerik

 

 

[Matthew]

Sorry, I wasn't terribly clear. Yes, I can access them through OnUpdateCommand/OnInsertedCommand. But how do I make that work in conjunction with my SqlDataSource OnInserted/OnUpdated?

[Konstantin Dikov]

Hi Matthew,

If you need to add some data before executing the SQL command, you can handle the OnUpdating event of the SqlDataSource and change the Value of the Parameters you need to modify:

protected void SqlDataSource1_Updating(object sender, SqlDataSourceCommandEventArgs e)

{

    e.Command.Parameters["@ChangeLog"].Value = "some value";
     // ...

}

Please let me know if the above is what you are looking for.


Regards,
Konstantin Dikov
Telerik

 

 

[Matthew]

I understand that will allow me to set the value. What I was having trouble doing is setting the value to ChangeLog to its Current value, plus some new information. So, how do I get the current value of ChangeLog in SqlDataSource1_Updating? Basically I want to use stringbuilder:

var changeLog = new StringBuilder(Get Current value of ChangeLog);

// Current value of changeLog will be UserName + Date/Time of last insert/update.

changeLog.AppendLine(UserName + DateTime.Now());

e.Command.Parameters["@ChangeLog"].Value = changeLog.ToString();

[Konstantin Dikov]

Hello Matthew,

If I understand correctly, you need to have the old and the new value of the ChangeLog within the SqlDataSource Updating event. If that is the case, you need to handle the OnUpdateCommand event of the grid, get reference to the GridEditableItem, retrieve the old value and store it in a global variable that will then be available within the OnUpdating event. For easily retrieving the old value you can set the data field in the DataKeyNames collection of the MasterTableView:

<MasterTableView DataKeyNames="Unique, ChangeLog">

And the code-behind:

private string oldChangeLogValue;

protected void RadGrid1_UpdateCommand(object sender, GridCommandEventArgs e)
{
    oldChangeLogValue = (e.Item as GridEditableItem).GetDataKeyValue("ChangeLog").ToString();
}

protected void SqlDataSource1_Updating(object sender, SqlDataSourceCommandEventArgs e)
{
    // use the oldChangeLogValue variable
}

Let me know if the above is what you were looking for.


Best Regards,
Konstantin Dikov
Telerik

 

 

[Matthew]

This is exactly what I needed to do. Thank you very much.