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

PivotTable Insert/Update

4 Answers 73 Views
Grid
This is a migrated thread and some comments may be shown as answers.
JSON
Top achievements
Rank 1
JSON asked on 21 Sep 2012, 05:54 PM
In a pivot table format grid, how does one go about either inserting or updating a row. Need to loop through each column in a row. Since most column UniqueNames contain an item's value. How do you first loop through columns and then identify each where there is no distinct unique name. And also because each record spans multiple dates which requires a multi-record update.

The pivot table I have created, lists date columns and statistics rows.

                     | 09/02/2012 | 09/03/2012 | 09/05/2012 | 09/09/2012
Stat1            |         1         |          0         |         5         |         3       
Stat2            |         8         |          0         |         1         |         0       
Stat3            |         0         |          6         |         2         |         7       

Thank you,

SteveO

4 Answers, 1 is accepted

Sort by
0
Radoslav
Telerik team
answered on 26 Sep 2012, 08:48 AM
Hi Steve,

I am sending you a simple example which demonstrates how to achieve the desired functionality. Please check it out and let me know if it helps you. Looking forward for your reply.

Greetings,
Radoslav
the Telerik team
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
0
JSON
Top achievements
Rank 1
answered on 26 Sep 2012, 11:33 AM
Thank you, but not sure this will satisfy my task. How do I determine which column/row intersection the returned columns are in.  I have a buton defined to run the UpdateCommand.

It may just be that I do not completely understand the example. The grid is configured to set all rows in edit mode on render.

SteveO

 

 

<telerik:RadGrid ID="RadGrid1"

 

 

 

 

 

 

 

AllowPaging="true"

 

 

 

runat="server"

 

 

 

OnPreRender="RadGrid1_PreRender"

 

 

 

OnItemUpdate="RadGrid1_UpdateCommand"

 

 

 

OnColumnCreated="RadGrid1_ColumnCreated"

 

 

 

 

 

 

 

OnItemCreated="RadGrid1_ItemCreated"

 

 

 

 

 

 

 

OnNeedDataSource="RadGrid1_NeedDataSource1"

 

 

 

 

 

 

 

height="550px"

 

 

 

 

 

 

 

PageSize="40"

 

 

 

 

 

 

 

GridLines="None">

 

 

 

 

 

 

 

 

<MasterTableView EditMode="InPlace"

 

 

 

CommandItemDisplay="Top">

 

 

 

<MasterTableView EditMode="InPlace"

 

 

 

CommandItemDisplay="Top">

 

 

 

 

 

 

 

<CommandItemSettings ExportToPdfText="Export to PDF"></CommandItemSettings>

 

 

 

 

 

 

 

<CommandItemTemplate>

 

 

 

 

 

 

 

<table width="100%">

 

 

 

 

 

 

 

 

<tr>

 

 

 

 

 

 

 

<td style="text-align:left;">

 

 

 

 

 

 

 

<asp:LinkButton ID="btnAddNew" runat="server" CommandName="AddNewRow">

 

 

 

 

 

 

 

<img style="border:0px;vertical-align:middle;" alt="" src="../../Images/add-icon[2].png" />&nbsp;Add New Row</asp:LinkButton>&nbsp;&nbsp;

 

 

 

 

 

 

 

</td>

 

 

 

 

 

 

 

<td style="text-align:right;">

 

 

 

<asp:LinkButton ID="btnSave" runat="server" CommandName="Save">

 

 

 

 

 

 

 

<img style="border:0px;vertical-align:middle;" alt="" src="../../Images/accept-icon[1].png" />&nbsp;Save in Database</asp:LinkButton>&nbsp;&nbsp;

 

 

 

 

 

 

 

</td>

 

 

 

 

 

 

 

</tr>

 

 

 

 

 

 

 

</table>

 

 

 

 

 

 

 

 

</CommandItemTemplate>

 

 

 

 

 

 

 

 

</MasterTableView>

 

 

 

 

 

 

 

 

<ClientSettings>

 

 

 

 

 

 

 

<Scrolling AllowScroll="true" ScrollHeight="300" UseStaticHeaders="true" />

 

 

 

 

 

 

 

</ClientSettings>

 

 

 

 

 

 

 

</telerik:RadGrid>

 

 

 

-------------------------------------------------------------------------------------------
protected void RadGrid1_UpdateCommand(object sender, GridCommandEventArgs e)

    {
        Hashtable h = new Hashtable();
        (e.Item as GridEditFormItem).ExtractValues(h);
  
        List<string> allColumnNames = RadGrid1.MasterTableView.RenderColumns.Select(c => c.UniqueName).ToList();
  
        for (int i = 4; i < allColumnNames.Count; i++)
        {
            string name = allColumnNames[i];
            String ConnString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
            SqlConnection conn = new SqlConnection(ConnString);
            try
            {
                conn.Open();
                var ID = (e.Item as GridEditFormItem).GetDataKeyValue("ID").ToString();
                string updateSql = "UPDATE Customers " + "SET " + ID +
                    " = @Name " + "WHERE CustomerID = @ID";
  
                SqlCommand UpdateCmd = new SqlCommand(updateSql, conn);
  
                UpdateCmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50, ID);
                UpdateCmd.Parameters.Add("@ID", SqlDbType.NChar, 5, "CustomerID");
  
                UpdateCmd.Parameters["@ID"].Value = name;
                UpdateCmd.Parameters["@Name"].Value = h[name].ToString();
                int number  = UpdateCmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
            }
            finally
            {
                conn.Close();
            }
        }
    }

0
Radoslav
Telerik team
answered on 28 Sep 2012, 08:19 AM
Hi Steve,

The provided code in my previous post will work only when you have one row in edit mode and you try to update it. However if you want to update all records with a single postback you need to iterate over all items and execute the logic from my previous post on RadGrid1_ItemCommand:
void RadGrid1_ItemCommand(object sender, GridCommandEventArgs e)
    {
        if (e.CommandName == "Save")
        {
            for (int j = 0; j < RadGrid1.MasterTableView.Items.Count; j++)
            {
                GridDataItem item = RadGrid1.MasterTableView.Items[j] as GridDataItem;
                Hashtable h = new Hashtable();
                item.ExtractValues(h);
 
                List<string> allColumnNames = RadGrid1.MasterTableView.RenderColumns.Select(c => c.UniqueName).ToList();
 
                for (int i = 3; i < allColumnNames.Count; i++)
                {
                    string name = allColumnNames[i];
                    String ConnString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
                    SqlConnection conn = new SqlConnection(ConnString);
                    try
                    {
                        conn.Open();
                        var ID = item.GetDataKeyValue("ID").ToString();
                        string updateSql = "UPDATE Customers " + "SET " + ID +
                            " = @Name " + "WHERE CustomerID = @ID";
 
                        SqlCommand UpdateCmd = new SqlCommand(updateSql, conn);
 
                        UpdateCmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50, ID);
                        UpdateCmd.Parameters.Add("@ID", SqlDbType.NChar, 5, "CustomerID");
 
                        UpdateCmd.Parameters["@ID"].Value = name;
                        UpdateCmd.Parameters["@Name"].Value = h[name].ToString();
                        int number = UpdateCmd.ExecuteNonQuery();
                    }
                    catch (SqlException ex)
                    {
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }
    }
Additionally I am sending you the modified version of the example. Please check it out and let me know if it helps you.

Kind regards,
Radoslav
the Telerik team
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
0
JSON
Top achievements
Rank 1
answered on 28 Sep 2012, 12:37 PM
Tags
Grid
Asked by
JSON
Top achievements
Rank 1
Answers by
Radoslav
Telerik team
JSON
Top achievements
Rank 1
Share this question
or