How to modify an element value in InsertCommand in a grid with automatic operations just before it is saved in the database?

[bertrand]

Hello,

I have a grid with automatic insert and update operations. Just before data was recording in database, i woult like modify them. In this case, i have 0130 in "Durée" field of my insert form, and i want to record 01:30 in database (see RadGridTemps_InsertCommand in the cs file below)

Is it possible?

001.<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPageSingleMenu.master" AutoEventWireup="true" CodeFile="Temps.aspx.cs" Inherits="Temps" %>
002.
003.<%@ Register TagPrefix="telerik" Namespace="Telerik.Web.UI" Assembly="Telerik.Web.UI" %>
004.
005.<asp:Content ID="Content0" ContentPlaceHolderID="head" Runat="Server">
006.    <link href="styles/default.css" rel="stylesheet" />
007.</asp:Content>
008.
009.<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
010.    <telerik:RadPageLayout runat="server" ID="RadPageLayout1">
011.        <Rows>
012.            <telerik:LayoutRow>
013.                <Columns>
014.                    <telerik:LayoutColumn CssClass="jumbotron">
015.                        <h1>Temps</h1>
016.                    </telerik:LayoutColumn>
017.                </Columns>
018.            </telerik:LayoutRow>
019.        </Rows>
020.    </telerik:RadPageLayout>
021.    <asp:Label ID="ctrl_label_debug" runat="server" Text="Zone de debug"></asp:Label>
022.</asp:Content>
023.
024.<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder2" runat="Server">
025.
026.    <telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">
027.        <AjaxSettings>
028.            <telerik:AjaxSetting AjaxControlID="RadGridTemps">
029.                <UpdatedControls>
030.                    <telerik:AjaxUpdatedControl ControlID="RadGridTemps" LoadingPanelID="RadAjaxLoadingPanel1"></telerik:AjaxUpdatedControl>
031.                </UpdatedControls>
032.            </telerik:AjaxSetting>
033.        </AjaxSettings>
034.    </telerik:RadAjaxManager>
035.
036.    <telerik:RadAjaxLoadingPanel ID="RadAjaxLoadingPanel1" runat="server">
037.    </telerik:RadAjaxLoadingPanel>
038.
039.    <telerik:RadFormDecorator ID="RadFormDecoratorTemps" runat="server" DecorationZoneID="bloc_temps" DecoratedControls="All" EnableRoundedCorners="false" BorderStyle="None" />
040.
041.    <div id="bloc_temps" class="demo-container no-bg">
042.
043.        <telerik:RadComboBox ID="RadComboBoxPersonnes" AutoPostBack="True" runat="server" Culture="fr-FR" DataSourceID="SDSPersonnes" DataValueField="IndexPersonnes" DataTextField="NomCompletPrenomNom" AppendDataBoundItems="true" Skin="Metro" MarkFirstMatch="True" Width="275">
044.            <Items>
045.                <telerik:RadComboBoxItem Text="Sélectionnez un collaborateur" Value="0" Selected="true"></telerik:RadComboBoxItem>
046.            </Items>
047.        </telerik:RadComboBox>
048.
049.        <asp:Button ID="ctrl_but_set_combo" runat="server" OnClick="ctrl_but_set_combo_Click" Text="Select human" />
050.
051.        <br />
052.        <br />
053.
054.        <%-- Grille - Temps --%>
055.        <telerik:RadGrid ID="RadGridTemps" runat="server" CellSpacing="-1" ShowStatusBar="True" Culture="fr-FR" DataSourceID="SDSTemps" GridLines="Both" OnItemDeleted="RadGridTemps_ItemDeleted" OnItemInserted="RadGridTemps_ItemInserted" OnItemUpdated="RadGridTemps_ItemUpdated" AllowAutomaticDeletes="True" AllowAutomaticInserts="True" AllowAutomaticUpdates="True" AllowFilteringByColumn="True" AllowPaging="True" AllowSorting="True" AutoGenerateDeleteColumn="True" AutoGenerateEditColumn="True" AutoGenerateColumns="False" Skin="Simple" BorderStyle="None" ShowGroupPanel="True" MasterTableView-EditMode="EditForms" OnInsertCommand="RadGridTemps_InsertCommand" OnItemCommand="RadGridTemps_ItemCommand"  >
056.
057.            <GroupingSettings CollapseAllTooltip="Collapse all groups"></GroupingSettings>
058.
059.            <ExportSettings>
060.            </ExportSettings>
061.            <ClientSettings AllowDragToGroup="True"></ClientSettings>
062.
063.            <MasterTableView DataKeyNames="column1" DataSourceID="SDSTemps" AutoGenerateColumns="False" NoDetailRecordsText="Pas d'enregistrement" NoMasterRecordsText="Pas d'enregistrement" ShowFooter="True" CommandItemDisplay="TopAndBottom" InsertItemPageIndexAction="ShowItemOnFirstPage">
064.
065.                <CommandItemSettings ShowExportToCsvButton="true" ShowExportToExcelButton="true" ShowExportToPdfButton="False" ShowExportToWordButton="False" RefreshText="Recharger" SaveChangesText="Sauver les modifications" PrintGridText="Imprimer la grille" NextFrozenColumnText="Suivant" PrevFrozenColumnText="Précédent" ExportToWordText="Exportation  Word" ExportToPdfText="Exportation  PDF" ExportToExcelText="Exportation  Excel" ExportToCsvText="Exportation  CSV" AddNewRecordText="Temps" CancelChangesText="Annuler les modifications" />
066.
067.                <Columns>
068.
069.                    <telerik:GridDropDownColumn DataField="Id_Personnes" DataSourceID="SDSPersonnes" FilterControlAltText="Filter Id_Personnes column" FilterListOptions="AllowAllFilters" HeaderText="Personnes" ListTextField="NomCompletPrenomNom" ListValueField="IndexPersonnes" UniqueName="Id_Personnes">
070.                    </telerik:GridDropDownColumn>
071.
072.                    <telerik:GridDropDownColumn DataField="Id_Projets" DataSourceID="SDSProjets" FilterControlAltText="Filter Id_Projets column" FilterListOptions="AllowAllFilters" HeaderText="Projets" ListTextField="Nom_compte_et_projet" ListValueField="IndexProjets" UniqueName="Id_Projets" FooterText="Projets sur lesquels vous avez passé du temps" ShowMoreResultsBox="True" DropDownControlType="DropDownList" FilterCheckListEnableLoadOnDemand="True">
073.                    </telerik:GridDropDownColumn>
074.
075.                    <telerik:GridDateTimeColumn DataField="Date_de_la_tache" HeaderText="Date" SortExpression="Date_de_la_tache"
076.                        UniqueName="Date_de_la_tache" DataType="System.DateTime" PickerType="DatePicker" FilterControlAltText="Filter Date_de_modification column">
077.                    </telerik:GridDateTimeColumn>
078.
079.                    <telerik:GridBoundColumn DataField="Durée" HeaderText="Durée" SortExpression="Durée" UniqueName="Durée" FilterControlAltText="Filter Durée column"></telerik:GridBoundColumn>
080.
081.                    <telerik:GridBoundColumn DataField="Commentaire" HeaderText="Commentaire" SortExpression="Commentaire" UniqueName="Commentaire" FilterControlAltText="Filter Commentaire column"></telerik:GridBoundColumn>
082.
083.                    <telerik:GridDropDownColumn DataField="Id_Type_de_temps" DataSourceID="SDSTypeDeTemps" FilterControlAltText="Filter Id_Type_detemps column" FilterListOptions="AllowAllFilters" HeaderText="Type de temps" ListTextField="Nom" ListValueField="IndexTypeDeTemps" UniqueName="Id_Type_de_temps">
084.                    </telerik:GridDropDownColumn>
085.
086.                </Columns>
087.
088.                <EditFormSettings EditFormType="Template">
089.                    <FormTemplate>
090.                        <table id="Table2" cellspacing="2" cellpadding="1" width="100%" border="0" rules="none"
091.                            style="border-collapse: collapse;">
092.                            <tr class="EditFormHeader">
093.                                <td colspan="2">
094.                                    <b>Détails de la saisie</b>
095.                                </td>
096.                            </tr>
097.                            <tr>
098.                                <td>
099.                                    <table id="Table3" width="450px" border="0" class="module">
100.
101.                                        <tr>
102.                                            <td>Personne :
103.                                            </td>
104.                                            <td>
105.                                                <telerik:RadComboBox ID="Ctrl_DropDownList_People_Form" runat="server" SelectedValue='<%# Bind("Id_Personnes") %>' TabIndex="3" DataSourceID="SDSPersonnes" DataTextField="NomCompletPrenomNom" DataValueField="IndexPersonnes" AppendDataBoundItems="True" MarkFirstMatch="True" Width="300">
106.                                                    <Items>
107.                                                        <telerik:RadComboBoxItem Text="All" Value="0" Selected="true"></telerik:RadComboBoxItem>
108.                                                    </Items>
109.                                                </telerik:RadComboBox>
110.                                            </td>
111.                                        </tr>
112.
113.                                        <tr>
114.                                            <td>Projets :
115.                                            </td>
116.                                            <td>
117.                                                <telerik:RadComboBox ID="Ctrl_DropDownList_Projets_Form" runat="server" SelectedValue='<%# Bind("Id_Projets") %>' TabIndex="4" DataSourceID="SDSProjets" DataTextField="Nom_compte_et_projet" DataValueField="IndexProjets" AppendDataBoundItems="True" MarkFirstMatch="True" Filter="Contains" Width="300">
118.                                                    <Items>
119.                                                        <telerik:RadComboBoxItem Text="All" Value="0" Selected="true"></telerik:RadComboBoxItem>
120.                                                    </Items>
121.                                                </telerik:RadComboBox>
122.                                            </td>
123.                                        </tr>
124.                                        <tr>
125.                                            <td>Date de la tâche :
126.                                            </td>
127.                                            <td>
128.                                                <telerik:RadDatePicker RenderMode="Lightweight" ID="Ctrl_RadDatePicker_Date_Task_Form" runat="server" MinDate="1/1/1900" DbSelectedDate='<%# Bind("Date_de_la_tache") %>'
129.                                                    TabIndex="5">
130.                                                </telerik:RadDatePicker>
131.                                            </td>
132.                                        </tr>
133.                                        <tr>
134.                                            <td>Durée :
135.                                            </td>
136.                                            <td>
137.                                                <telerik:RadMaskedTextBox RenderMode="Lightweight" ID="Ctrl_RadMaskedTextBox_Duration_Form" runat="server" SelectionOnFocus="SelectAll" Text='<%# Bind("Durée") %>' PromptChar="_" Mask="##:##"
138.                                                    TabIndex="6">
139.                                                </telerik:RadMaskedTextBox>
140.                                            </td>
141.                                        </tr>
142.                                        <tr>
143.                                            <td>Commentaire :
144.                                            </td>
145.                                            <td>
146.                                                <asp:TextBox ID="Ctrl_TextBox_Comment_Form" Text='<%# Bind("Commentaire") %>' runat="server" TextMode="MultiLine" Rows="5" Columns="40" TabIndex="7" Width="300">
147.                                                </asp:TextBox>
148.                                            </td>
149.                                        </tr>
150.                                        <tr>
151.                                            <td>Type de temps :
152.                                            </td>
153.                                            <td>
154.                                                <asp:DropDownList ID="Ctrl_DropDownList_Time_Type_Form" runat="server" SelectedValue='<%# Bind("Id_Type_de_temps") %>' DataSourceID='SDSTypeDeTemps' TabIndex="8" AppendDataBoundItems="True" DataTextField="Nom" DataValueField="IndexTypeDeTemps">
155.                                                    <asp:ListItem Selected="True" Text="Select" Value="">
156.                                                    </asp:ListItem>
157.                                                </asp:DropDownList>
158.                                            </td>
159.                                        </tr>
160.                                        <tr>
161.                                            <td align="right" colspan="2">
162.                                                <asp:Button ID="btnUpdate" Text='<%# (Container is GridEditFormInsertItem) ? "Insérer" : "Mettre à jour" %>'
163.                                                    runat="server" CommandName='<%# (Container is GridEditFormInsertItem) ? "PerformInsert" : "Update" %>' TabIndex="9"></asp:Button>
164.                                                <asp:Button ID="btnCancel" Text="Annuler" runat="server" CausesValidation="False"
165.                                                    CommandName="Cancel" TabIndex="10"></asp:Button>
166.                                            </td>
167.                                        </tr>
168.                                    </table>
169.                                </td>
170.                            </tr>
171.                        </table>
172.                    </FormTemplate>
173.                </EditFormSettings>
174.
175.
176.            </MasterTableView>
177.
178.            <FilterMenu RenderMode="Lightweight"></FilterMenu>
179.            <HeaderContextMenu RenderMode="Lightweight"></HeaderContextMenu>
180.
181.        </telerik:RadGrid>
182.
183.        <asp:SqlDataSource ID="SDSPersonnes" runat="server" ConnectionString='<%$ ConnectionStrings:GoodSalesConnectionString %>' SelectCommand="SELECT [ID] AS IndexPersonnes, [Nom complet Prénom Nom] AS NomCompletPrenomNom FROM [Personnes] WHERE ([Date sortie] IS NULL) ORDER BY [Nom complet Prénom Nom]" ></asp:SqlDataSource>
184.        <asp:SqlDataSource ID="SDSProjets" runat="server" ConnectionString='<%$ ConnectionStrings:GoodSalesConnectionString %>' SelectCommand="SELECT [N°] AS IndexProjets, [Nom compte et projet] AS Nom_compte_et_projet FROM [Projets] WHERE ([Date archivage] IS NULL) ORDER BY [Index] DESC"></asp:SqlDataSource>
185.        <asp:SqlDataSource ID="SDSTypeDeTemps" runat="server" ConnectionString='<%$ ConnectionStrings:GoodSalesConnectionString %>' SelectCommand="SELECT [N°] AS IndexTypeDeTemps, [Nom] FROM [Type de temps] ORDER BY [Ordre]"></asp:SqlDataSource>
186.
187.        <asp:SqlDataSource ID="SDSTemps" runat="server" ConflictDetection="CompareAllValues" ConnectionString='<%$ ConnectionStrings:GoodSalesConnectionString %>' DeleteCommand="DELETE FROM [Temps] WHERE [N°] = @original_column1 AND [Id Personnes] = @original_Id_Personnes AND [Id Projets] = @original_Id_Projets AND [Date de la tâche] = @original_Date_de_la_tache AND [Durée] = @original_Durée AND [Commentaire] = @original_Commentaire AND [Id Type de temps] = @original_Id_Type_de_temps" InsertCommand="INSERT INTO [Temps] ([Id Personnes], [Id Projets], [Date de la tâche], [Durée], [Commentaire], [Id Type de temps]) VALUES (@Id_Personnes, @Id_Projets, @Date_de_la_tache, @Durée, @Commentaire, @Id_Type_de_temps)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [N°] AS column1, [Id Personnes] AS Id_Personnes, [Id Projets] AS Id_Projets, [Date de la tâche] AS Date_de_la_tache, [Durée], [Commentaire], [Id Type de temps] AS Id_Type_de_temps FROM [Temps] WHERE ([Id Personnes] = @Id_Personnes) ORDER BY [Date de la tâche] DESC" UpdateCommand="UPDATE [Temps] SET [Id Personnes] = @Id_Personnes, [Id Projets] = @Id_Projets, [Date de la tâche] = @Date_de_la_tache, [Durée] = @Durée, [Commentaire] = @Commentaire, [Id Type de temps] = @Id_Type_de_temps WHERE [N°] = @original_column1 AND [Id Personnes] = @original_Id_Personnes AND [Id Projets] = @original_Id_Projets AND [Date de la tâche] = @original_Date_de_la_tache AND [Durée] = @original_Durée AND [Commentaire] = @original_Commentaire AND [Id Type de temps] = @original_Id_Type_de_temps">
188.            <DeleteParameters>
189.                <asp:Parameter Name="original_column1" Type="Int32"></asp:Parameter>
190.                <asp:Parameter Name="original_Id_Personnes" Type="Int32"></asp:Parameter>
191.                <asp:Parameter Name="original_Id_Projets" Type="Int32"></asp:Parameter>
192.                <asp:Parameter Name="original_Date_de_la_tache" Type="DateTime"></asp:Parameter>
193.                <asp:Parameter Name="original_Duree" Type="String"></asp:Parameter>
194.                <asp:Parameter Name="original_Commentaire" Type="String"></asp:Parameter>
195.                <asp:Parameter Name="original_Id_Type_de_temps" Type="Int32"></asp:Parameter>
196.            </DeleteParameters>
197.            <InsertParameters>
198.                <asp:Parameter Name="Id_Personnes" Type="Int32"></asp:Parameter>
199.                <asp:Parameter Name="Id_Projets" Type="Int32"></asp:Parameter>
200.                <asp:Parameter Name="Date_de_la_tache" Type="DateTime"></asp:Parameter>
201.                <asp:Parameter Name="Duree" Type="String"></asp:Parameter>
202.                <asp:Parameter Name="Commentaire" Type="String"></asp:Parameter>
203.                <asp:Parameter Name="Id_Type_de_temps" Type="Int32"></asp:Parameter>
204.            </InsertParameters>
205.            <SelectParameters>
206.                <asp:ControlParameter ControlID="RadComboBoxPersonnes" PropertyName="SelectedValue" Name="Id_Personnes" Type="Int32"></asp:ControlParameter>
207.            </SelectParameters>
208.            <UpdateParameters>
209.                <asp:Parameter Name="Id_Personnes" Type="Int32"></asp:Parameter>
210.                <asp:Parameter Name="Id_Projets" Type="Int32"></asp:Parameter>
211.                <asp:Parameter Name="Date_de_la_tache" Type="DateTime"></asp:Parameter>
212.                <asp:Parameter Name="Duree" Type="String"></asp:Parameter>
213.                <asp:Parameter Name="Commentaire" Type="String"></asp:Parameter>
214.                <asp:Parameter Name="Id_Type_de_temps" Type="Int32"></asp:Parameter>
215.                <asp:Parameter Name="original_column1" Type="Int32"></asp:Parameter>
216.                <asp:Parameter Name="original_Id_Personnes" Type="Int32"></asp:Parameter>
217.                <asp:Parameter Name="original_Id_Projets" Type="Int32"></asp:Parameter>
218.                <asp:Parameter Name="original_Date_de_la_tache" Type="DateTime"></asp:Parameter>
219.                <asp:Parameter Name="original_Duree" Type="String"></asp:Parameter>
220.                <asp:Parameter Name="original_Commentaire" Type="String"></asp:Parameter>
221.                <asp:Parameter Name="original_Id_Type_de_temps" Type="Int32"></asp:Parameter>
222.            </UpdateParameters>
223.        </asp:SqlDataSource>
224.
225.    </div>
226.</asp:Content>

 

001.using System;
002.using System.Collections;
003.using System.Web.UI;
004.using System.Web.UI.WebControls;
005.using Telerik.Web.UI;
006.
007.public partial class Temps : System.Web.UI.Page
008.{
009.    private string c_grid_message = null;
010.
011.    protected GoodSalesDatabase c_goodsales_databse;
012.    GoodSalesHuman c_goodsales_human = null;
013.    string c_name_user = "";
014.    GoodSalesSql c_goodsales_sql = null;
015.    Int32 c_human_id = -1;
016.
017.    public Temps()
018.    {
019.        c_goodsales_human = new GoodSalesHuman();
020.        c_name_user = c_goodsales_human.GetName();
021.        c_goodsales_sql = new GoodSalesSql();
022.        c_human_id = c_goodsales_sql.GetHumanIndex(c_name_user);
023.    }
024.
025.    protected void Page_Load(object sender, EventArgs e)
026.    {
027.    }
028.
029.    protected void RadGridTemps_ItemDeleted(object sender, GridDeletedEventArgs e)
030.    {
031.        if (e.Exception != null)
032.        {
033.            e.ExceptionHandled = true;
034.            SetMessage("Echec de la suppression. Raison : " + e.Exception.Message);
035.        }
036.        else
037.        {
038.            SetMessage("Enregistrement supprimé");
039.        }
040.    }
041.
042.    protected void RadGridTemps_ItemInserted(object sender, GridInsertedEventArgs e)
043.    {
044.        if (e.Exception != null)
045.        {
046.            e.ExceptionHandled = true;
047.            SetMessage("Echec de l'ajout de l'enregistrement. Raison : " + e.Exception.Message);
048.        }
049.        else
050.        {
051.            SetMessage("L'enregistrement a été ajouté");
052.        }
053.    }
054.
055.    protected void RadGridTemps_ItemUpdated(object sender, GridUpdatedEventArgs e)
056.    {
057.        if (e.Exception != null)
058.        {
059.            e.ExceptionHandled = true;
060.            SetMessage("Echec de la mise à jour. Raison : " + e.Exception.Message);
061.        }
062.        else
063.        {
064.            SetMessage("L'enregistrement a été mis à jour");
065.        }
066.    }
067.
068.    private void DisplayMessage(string text)
069.    {
070.        RadGridTemps.Controls.Add(new LiteralControl(string.Format("<span style='color:red'>{0}</span>", text)));
071.    }
072.
073.    private void SetMessage(string message)
074.    {
075.        c_grid_message = message;
076.    }
077.
078.    protected void RadGridTemps_InsertCommand(object sender, GridCommandEventArgs e)
079.    {
080.        Int32 cur_human_id = -1;
081.
082.        if (c_name_user != "")
083.        {
084.            cur_human_id = c_goodsales_sql.GetHumanIndex(c_name_user);
085.            SetMessage("Le nom de l'utilisateur est : " + c_name_user  + " et son Index est : " + cur_human_id.ToString());
086.        }
087.
088.        if (e.Item is GridEditableItem)
089.        {
090.            GridEditableItem editedItem = e.Item as GridEditableItem;
091.
092.            Hashtable newValues = new Hashtable();
093.            e.Item.OwnerTableView.ExtractValuesFromItem(newValues, editedItem);
094.
095.            string cur_time = (string)newValues["Durée"];
096.            cur_time = cur_time.Insert(2, ":");
097.
098.            //How do I update the "Durée" field before it is saved?
099.        }
100.
101.    }
102.
103.    ~Temps()
104.    {
105.    }
106.
107.    protected void RadGridTemps_ItemCommand(object sender, GridCommandEventArgs e)
108.    {
109.        if (e.CommandName == RadGrid.InitInsertCommandName)
110.        {
111.            e.Canceled = true;
112.            Hashtable values = new Hashtable();
113.            values["Id_Personnes"] = c_human_id;
114.            values["Date_de_la_tache"] = DateTime.Now;
115.            values["Durée"] = "00:00";
116.            values["Id_Type_de_temps"] = GoodSalesGlobalVar.glb_const_index_type_temps_prod;
117.            e.Item.OwnerTableView.InsertItem(values);
118.        }
119.
120.
121.        if (e.CommandName == RadGrid.PerformInsertCommandName)
122.        {
123.            /*
124.            TableCell cell = editItem["Commentaire"];
125.            string itemValue = (cell.Controls[0] as TextBox).Text;
126.            var value = editItem.GetDataKeyValue("Id_Personnes");
127.
128.            GridEditFormInsertItem item = (GridEditFormInsertItem)e.Item;
129.            var editableItem = ((GridEditableItem)e.Item);
130.            Hashtable values = new Hashtable();
131.            editableItem.ExtractValues(values);
132.
133.            string cur_duration = values["Durée"].ToString();
134.
135.            if (e.Item.Edit)
136.            {
137.                TableCellCollection cur_table_cell_collection = item.Cells;
138.
139.                //e.Item.OwnerTableView.ExtractValuesFromItem(newValues, editedItem);
140.            }
141.            */
142.        }
143.    }
144.
145.    protected void ctrl_but_set_combo_Click(object sender, EventArgs e)
146.    {
147.        int cur_index = RadComboBoxPersonnes.FindItemIndexByValue(c_human_id.ToString());
148.        RadComboBoxPersonnes.SelectedIndex = cur_index;
149.    }
150.}

[Marin Bratanov]

Hello Bertrand,

I have just answered your support ticket with this question and for anyone else having the same question, here's the answer:

---

You can use the SqlDataSource OnInserting and OnUpdating events to modify the query.

Alternatively, you can try using a RadTimePicker if you want to capture a time duration, as its SelectedTime property is of type TimeSpan, in case that fits your needs.

The third option is to use manual CRUD operations instead of the automatic operations, so that you can extract, modify, validate values before deciding to send them to the database.

Regards,
Marin Bratanov
Progress Telerik