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

Updating grid column programmatically on insert

5 Answers 247 Views
Grid
This is a migrated thread and some comments may be shown as answers.
SIT
Top achievements
Rank 1
SIT asked on 27 Mar 2009, 08:06 PM
I have the following tables:

mytest table
mytest_id int identity pk
mytest_name nvarchar(50)
mytest_lookup_id int fk
mytest_key nvarchar(10)

lookup table
lookup_id int identity pk
lookup_desc nvarchar(50)

I have the following working in a sharepoint web part:

I can insert, edit, and delete records from the mytest table using radgrid.
I can select values from a combo box bound to one of the columns in the grid and the value is selected from the lookup table.

I need to add the following functionality:

When a new record is inserted into the table, I need to be able to programmatically assign a value stored as a property of the class into the mytest_key field of the newly inserted record.  In the following example, I would like to be able to assign the myvalue property value into the mytest_key field when a new record is inserted.  I attempted to use the grid.ItemInserted event but I am not sure how to go about assigning the value into the mytest_key grid column for the newly inserted record.

Here is the web part code:


using System;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Serialization;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;
using Telerik.Web.UI;


namespace WebPart7
{
    [Guid("656556bd-6adc-43a0-ad6a-4ddb8b5039c8")]
    public class WebPart7:System.Web.UI.WebControls.WebParts.WebPart
    {
        string cServer = "myserver";
        string cDB = "mydb";
        string myvalue = "test";
        
        public WebPart7()
        {
        }

        protected override void CreateChildControls()
        {
        base.CreateChildControls();

        // TODO: add custom rendering code here.
        // Label label = new Label();
        // label.Text = "Hello World";
        // this.Controls.Add(label);
        
        }

 protected override void OnInit(EventArgs e)
{
   base.OnInit(e);
   Page.ClientScript.RegisterStartupScript(typeof(WebPart7), this.ID, "_spOriginalFormAction = document.forms[0].action;_spSuppressFormOnSubmitWrapper=true;", true);
   if (this.Page.Form != null)
   {
       string formOnSubmitAtt = this.Page.Form.Attributes["onsubmit"];
       if (!string.IsNullOrEmpty(formOnSubmitAtt) && formOnSubmitAtt == "return _spFormOnSubmitWrapper();")
       {
           this.Page.Form.Attributes["onsubmit"] = "_spFormOnSubmitWrapper();";
       }
   }
}

void Grid_ItemInserted(object source, GridInsertedEventArgs e)
{
// I would like to be able to assign the value stored in the myvalue property to the mytest_key field of the newly inserted record.
}

 protected override void OnLoad(EventArgs e)
{
   base.OnLoad(e);
   Panel panel = new Panel();
   panel.ID = "Panel1";
   panel.EnableViewState = true;
   this.Controls.Add(panel);

   string lcConnString;
   string lcCommand;
   SqlDataSource SqlDataSource1;
   lcConnString = "Data Source=" + this.cServer + ";Initial Catalog=" + this.cDB + ";Integrated Security=SSPI;";
   lcCommand = "SELECT mytest_id, mytest_name, mytest_lookup_id, mytest_key FROM mytest";
   SqlDataSource1 = new SqlDataSource(lcConnString,lcCommand);
   SqlDataSource1.UpdateCommand = "UPDATE mytest SET mytest_name = @mytest_name, mytest_lookup_id = @mytest_lookup_id, mytest_key = @mytest_key WHERE (mytest_id = @mytest_id)";
   SqlDataSource1.DeleteCommand="DELETE FROM mytest WHERE (mytest_id = @mytest_id)";
   SqlDataSource1.InsertCommand="INSERT INTO mytest(mytest_name, mytest_lookup_id, mytest_key) VALUES (@mytest_name, @mytest_lookup_id, @mytest_key)";
   SqlDataSource1.ID = "sqlds1";

   string lcConnString2;
   string lcCommand2;
   lcConnString2 = "Data Source=" + this.cServer + ";Initial Catalog=" + this.cDB + ";Integrated Security=SSPI;";
   lcCommand2 = "SELECT lookup_id, lookup_desc FROM lookup";
   SqlDataSource SqlDataSource2 = new SqlDataSource(lcConnString2,lcCommand2);
   SqlDataSource2.ID = "sqlds2";

   RadGrid grid = new RadGrid();
   grid.ID = "RadGrid1";
   grid.AutoGenerateColumns = false;
   grid.AllowAutomaticDeletes = true;
   grid.AllowAutomaticInserts = true;
   grid.AllowAutomaticUpdates = true;
   grid.DataSourceID = SqlDataSource1.ID;
   grid.Width = 400;
   
   GridBoundColumn mycolumn1 = new GridBoundColumn();
   mycolumn1.HeaderText = "mytest_name";
   mycolumn1.DataField = "mytest_name";
   mycolumn1.SortExpression="mytest_name";
   mycolumn1.UniqueName="mytest_name";
   grid.MasterTableView.Columns.Add(mycolumn1);

   string[] mydatakeynames;
   mydatakeynames = new string[1];
   mydatakeynames[0] = "mytest_id";

   grid.MasterTableView.DataKeyNames = mydatakeynames;
   grid.MasterTableView.EditMode = GridEditMode.PopUp;
   grid.MasterTableView.CommandItemDisplay = GridCommandItemDisplay.Top;

   GridDropDownColumn mycolumn2 = new GridDropDownColumn();
   mycolumn2.HeaderText = "Lookup";
   mycolumn2.DataField = "mytest_lookup_id";
   mycolumn2.ListValueField = "lookup_id";
   mycolumn2.ListTextField = "lookup_desc";
   mycolumn2.DataSourceID = SqlDataSource2.ID;
   grid.MasterTableView.Columns.Add(mycolumn2);

   GridBoundColumn mycolumn3 = new GridBoundColumn();
   mycolumn3.HeaderText = "mytest_key";
   mycolumn3.DataField = "mytest_key";
   mycolumn3.SortExpression="mytest_key";
   mycolumn3.UniqueName="mytest_key";
   grid.MasterTableView.Columns.Add(mycolumn3);


   GridEditCommandColumn mycolumn4 = new GridEditCommandColumn();
   grid.MasterTableView.Columns.Add(mycolumn4);

   GridButtonColumn mycolumn5 = new GridButtonColumn();
   mycolumn5.HeaderText = "Delete";
   mycolumn5.UniqueName = "DelCol";
   mycolumn5.CommandName = "Delete";
   mycolumn5.Text = "Delete";
   mycolumn5.ConfirmText = "Delete?";
   grid.MasterTableView.Columns.Add(mycolumn5);   
 
   grid.ItemInserted += new GridInsertedEventHandler(Grid_ItemInserted);

   panel.Controls.Add(SqlDataSource1);
   panel.Controls.Add(SqlDataSource2);
   panel.Controls.Add(grid);

   RadAjaxManager ajaxManager = RadAjaxManager.GetCurrent(this.Page);
   if (ajaxManager == null)
   {
       ajaxManager = new RadAjaxManager();
       ajaxManager.ID = "RadAjaxManager1";
       Controls.Add(ajaxManager);
       this.Page.Items.Add(typeof(RadAjaxManager), ajaxManager);
   }
   ajaxManager.AjaxSettings.AddAjaxSetting(grid, panel);
}
}
}

5 Answers, 1 is accepted

Sort by
0
SIT
Top achievements
Rank 1
answered on 28 Mar 2009, 03:16 PM
Looking at the examples found here:

http://www.telerik.com/help/aspnet-ajax/grdinsertupdatedeleteatdatabaselevel.html

http://www.telerik.com/community/code-library/aspnet-ajax/grid/manual-insert-update-delete-operations-using-auto-generated-editform-with-sql-statements-from-the-code-behind.aspx

I am attempting to manually perform the insert, update, and delete operations.  I am having difficulty accessing the mytest_lookup_id value from the GridDropDownColumn of the grid.  I have tried the following:

string mytest_lookup_id = (editedItem["mytest_lookup_id"].Controls[0] as DropDownList).Text;

I get the generic sharepoint error "unknown error 500" when using the above syntax.  How do I access the datafield value of the griddropdowncolumn of the grid from the grid's updatecommand event handler?

Complete code:

using System;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Serialization;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;
using Telerik.Web.UI;
using System.Data;
using System.Data.SqlClient;


namespace WebPart7
{
    [Guid("656556bd-6adc-43a0-ad6a-4ddb8b5039c8")]
    public class WebPart7:System.Web.UI.WebControls.WebParts.WebPart
    {
        string cServer = "myserver";
        string cDB = "mydb";

        //Declare a global DataTable dtTable     
        public static DataTable dtTable;   
        //Get the connectionstring from the webconfig and declare a global SqlConnection "SqlConnection"     
        public static string connectionString = "Data Source=" + "myserver" + ";Initial Catalog=" + "mydb" + ";Integrated Security=SSPI;";   
        public SqlConnection SqlConnection = new SqlConnection(connectionString);   
        //Declare a global SqlDataAdapter SqlDataAdapter     
        public SqlDataAdapter SqlDataAdapter = new SqlDataAdapter();   
        //Declare a global SqlCommand SqlCommand     
        public SqlCommand SqlCommand = new SqlCommand();

        RadGrid grid = new RadGrid();


        
        public WebPart7()
        {
        }

        protected override void CreateChildControls()
        {
        base.CreateChildControls();

        // TODO: add custom rendering code here.
        // Label label = new Label();
        // label.Text = "Hello World";
        // this.Controls.Add(label);
        
        }

 protected override void OnInit(EventArgs e)
{
   base.OnInit(e);
   Page.ClientScript.RegisterStartupScript(typeof(WebPart7), this.ID, "_spOriginalFormAction = document.forms[0].action;_spSuppressFormOnSubmitWrapper=true;", true);
   if (this.Page.Form != null)
   {
       string formOnSubmitAtt = this.Page.Form.Attributes["onsubmit"];
       if (!string.IsNullOrEmpty(formOnSubmitAtt) && formOnSubmitAtt == "return _spFormOnSubmitWrapper();")
       {
           this.Page.Form.Attributes["onsubmit"] = "_spFormOnSubmitWrapper();";
       }
   }
}

void Grid_ItemInserted(object source, GridInsertedEventArgs e)
{        
}

void Grid_InsertCommand(object source, GridCommandEventArgs e)
{        
}

 protected override void OnLoad(EventArgs e)
{
   base.OnLoad(e);
   Panel panel = new Panel();
   panel.ID = "Panel1";
   panel.EnableViewState = true;
   this.Controls.Add(panel);

   string lcConnString;
   string lcCommand;
   SqlDataSource SqlDataSource1;
   lcConnString = "Data Source=" + this.cServer + ";Initial Catalog=" + this.cDB + ";Integrated Security=SSPI;";
   lcCommand = "SELECT mytest_id, mytest_name, mytest_lookup_id, mytest_key FROM mytest";
   SqlDataSource1 = new SqlDataSource(lcConnString,lcCommand);
   SqlDataSource1.UpdateCommand = "UPDATE mytest SET mytest_name = @mytest_name, mytest_lookup_id = @mytest_lookup_id, mytest_key = @mytest_key WHERE (mytest_id = @mytest_id)";
   SqlDataSource1.DeleteCommand="DELETE FROM mytest WHERE (mytest_id = @mytest_id)";
   SqlDataSource1.InsertCommand="INSERT INTO mytest(mytest_name, mytest_lookup_id, mytest_key) VALUES (@mytest_name, @mytest_lookup_id, @mytest_key)";
   SqlDataSource1.ID = "sqlds1";

   string lcConnString2;
   string lcCommand2;
   lcConnString2 = "Data Source=" + this.cServer + ";Initial Catalog=" + this.cDB + ";Integrated Security=SSPI;";
   lcCommand2 = "SELECT lookup_id, lookup_desc FROM lookup";
   SqlDataSource SqlDataSource2 = new SqlDataSource(lcConnString2,lcCommand2);
   SqlDataSource2.ID = "sqlds2";

   
   grid.ID = "RadGrid1";
   grid.AutoGenerateColumns = false;
   grid.AllowAutomaticDeletes = true;
   grid.AllowAutomaticInserts = true;
   grid.AllowAutomaticUpdates = true;
   grid.Width = 400;
   //grid.DataSourceID = SqlDataSource1.ID;

   //Populate the Radgrid     
   dtTable = new DataTable();   
   //Open the SqlConnection     
   SqlConnection.Open();   
   try   
   {   
       //Select Query to populate the RadGrid with data from table Employees.     
       string selectQuery = "SELECT mytest_id, mytest_name, mytest_lookup_id, mytest_key FROM mytest";   
       SqlDataAdapter.SelectCommand = new SqlCommand(selectQuery, SqlConnection);   
       SqlDataAdapter.Fill(dtTable);   
       grid.DataSource = dtTable;   
   }   
   finally   
   {   
       //Close the SqlConnection     
       SqlConnection.Close();   
   }     

    
   
   
   GridBoundColumn mycolumn1 = new GridBoundColumn();
   mycolumn1.HeaderText = "mytest_name";
   mycolumn1.DataField = "mytest_name";
   mycolumn1.SortExpression="mytest_name";
   mycolumn1.UniqueName="mytest_name";
   grid.MasterTableView.Columns.Add(mycolumn1);

   string[] mydatakeynames;
   mydatakeynames = new string[1];
   mydatakeynames[0] = "mytest_id";

   grid.MasterTableView.DataKeyNames = mydatakeynames;
   grid.MasterTableView.EditMode = GridEditMode.PopUp;
   grid.MasterTableView.CommandItemDisplay = GridCommandItemDisplay.Top;

   GridDropDownColumn mycolumn2 = new GridDropDownColumn();
   mycolumn2.HeaderText = "Lookup";
   mycolumn2.DataField = "mytest_lookup_id";
   mycolumn2.ListValueField = "lookup_id";
   mycolumn2.ListTextField = "lookup_desc";
   mycolumn2.DataSourceID = SqlDataSource2.ID;
   grid.MasterTableView.Columns.Add(mycolumn2);

   GridBoundColumn mycolumn3 = new GridBoundColumn();
   mycolumn3.HeaderText = "mytest_key";
   mycolumn3.DataField = "mytest_key";
   mycolumn3.SortExpression="mytest_key";
   mycolumn3.UniqueName="mytest_key";
   grid.MasterTableView.Columns.Add(mycolumn3);


   GridEditCommandColumn mycolumn4 = new GridEditCommandColumn();
   grid.MasterTableView.Columns.Add(mycolumn4);

   GridButtonColumn mycolumn5 = new GridButtonColumn();
   mycolumn5.HeaderText = "Delete";
   mycolumn5.UniqueName = "DelCol";
   mycolumn5.CommandName = "Delete";
   mycolumn5.Text = "Delete";
   mycolumn5.ConfirmText = "Delete?";
   grid.MasterTableView.Columns.Add(mycolumn5);   
 
   
   grid.UpdateCommand += new GridCommandEventHandler(grid_UpdateCommand);

   panel.Controls.Add(SqlDataSource1);
   panel.Controls.Add(SqlDataSource2);
   panel.Controls.Add(grid);

   RadAjaxManager ajaxManager = RadAjaxManager.GetCurrent(this.Page);
   if (ajaxManager == null)
   {
       ajaxManager = new RadAjaxManager();
       ajaxManager.ID = "RadAjaxManager1";
       Controls.Add(ajaxManager);
       this.Page.Items.Add(typeof(RadAjaxManager), ajaxManager);
   }
   ajaxManager.AjaxSettings.AddAjaxSetting(grid, panel);
}


   protected void grid_UpdateCommand(object source, Telerik.Web.UI.GridCommandEventArgs e)   
   {   
     
       //Get the GridEditableItem of the RadGrid     
       GridEditableItem editedItem = e.Item as GridEditableItem;   
       //Get the primary key value using the DataKeyValue.     
       string mytest_id = editedItem.OwnerTableView.DataKeyValues[editedItem.ItemIndex]["mytest_id"].ToString();   
      //Access the textbox from the edit form template and store the values in string variables.     
       string mytest_name = (editedItem["mytest_name"].Controls[0] as TextBox).Text;   
       string mytest_lookup_id = (editedItem["mytest_lookup_id"].Controls[0] as DropDownList).Text;   
       string mytest_key = (editedItem["mytest_key"].Controls[0] as TextBox).Text;   
   
       try   
       {   
           //Open the SqlConnection     
           SqlConnection.Open();   
           //Update Query to update the Datatable      
           string updateQuery = "UPDATE mytest SET mytest_name = " + "'" + mytest_name + "'" + " WHERE (mytest_id = " + mytest_id + ")";   
           SqlCommand.CommandText = updateQuery;   
           SqlCommand.Connection = SqlConnection;   
           SqlCommand.ExecuteNonQuery();   
           //Close the SqlConnection     
           SqlConnection.Close();   
 
 
       }   
       catch (Exception ex)   
       {   
           grid.Controls.Add(new LiteralControl("Unable to update mytest. Reason: " + ex.Message));   
           e.Canceled = true;   
       }     
 
   }   


}
}




http://www.telerik.com/community/code-library/aspnet-ajax/grid/manual-insert-update-delete-operations-using-auto-generated-editform-with-sql-statements-from-the-code-behind.aspx

0
SIT
Top achievements
Rank 1
answered on 28 Mar 2009, 05:09 PM
I replaced DropDownList with RadComboBox and now I can access the value in the column.

string mytest_lookup_id = (editedItem["mytest_lookup_id"].Controls[0] as RadComboBox).Text;

Edit:  the previous command returns the text in the combobox that comes from the lookup table.

The following command returns the actual value stored in the mytest table.

string mytest_lookup_id = (editedItem["mytest_lookup_id"].Controls[0] as RadComboBox).SelectedValue.ToString();






0
SIT
Top achievements
Rank 1
answered on 29 Mar 2009, 04:35 AM
Since switching from the "automatic" method to "manual", the insert and update screens are not closing when the respective insert and update links are activated.  The appropriate insert and update functions are being called and the data is being updated properly.  The insert and update screen simply does not go away when the links are clicked.  This is contrary to the functionality observed when the insert, update, and delete operations were being performed "automatically" without the help of the manually implemented insertcommand, updatecommand, and deletecommand functions.  How do I restore this functionality?

Example:

using System;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Serialization;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;
using Telerik.Web.UI;
using System.Data;
using System.Data.SqlClient;


namespace WebPart7
{
    [Guid("656556bd-6adc-43a0-ad6a-4ddb8b5039c8")]
    public class WebPart7:System.Web.UI.WebControls.WebParts.WebPart
    {
        string cServer = "myserver";
        string cDB = "mydb";

        //Declare a global DataTable dtTable     
        public static DataTable dtTable;   
        //Get the connectionstring from the webconfig and declare a global SqlConnection "SqlConnection"     
        public static string connectionString = "Data Source=" + "myserver" + ";Initial Catalog=" + "mydb" + ";Integrated Security=SSPI;";   
        public SqlConnection SqlConnection = new SqlConnection(connectionString);   
        //Declare a global SqlDataAdapter SqlDataAdapter     
        public SqlDataAdapter SqlDataAdapter = new SqlDataAdapter();   
        //Declare a global SqlCommand SqlCommand     
        public SqlCommand SqlCommand = new SqlCommand();

        RadGrid grid = new RadGrid();


        
        public WebPart7()
        {
        }

        protected override void CreateChildControls()
        {
        base.CreateChildControls();

        // TODO: add custom rendering code here.
        // Label label = new Label();
        // label.Text = "Hello World";
        // this.Controls.Add(label);
        
        }

 protected override void OnInit(EventArgs e)
{
   base.OnInit(e);
   Page.ClientScript.RegisterStartupScript(typeof(WebPart7), this.ID, "_spOriginalFormAction = document.forms[0].action;_spSuppressFormOnSubmitWrapper=true;", true);
   if (this.Page.Form != null)
   {
       string formOnSubmitAtt = this.Page.Form.Attributes["onsubmit"];
       if (!string.IsNullOrEmpty(formOnSubmitAtt) && formOnSubmitAtt == "return _spFormOnSubmitWrapper();")
       {
           this.Page.Form.Attributes["onsubmit"] = "_spFormOnSubmitWrapper();";
       }
   }
}

void Grid_ItemInserted(object source, GridInsertedEventArgs e)
{        
}

void Grid_InsertCommand(object source, GridCommandEventArgs e)
{        
}

 protected override void OnLoad(EventArgs e)
{
   base.OnLoad(e);
   Panel panel = new Panel();
   panel.ID = "Panel1";
   panel.EnableViewState = true;
   this.Controls.Add(panel);

   string lcConnString;
   string lcCommand;
   SqlDataSource SqlDataSource1;
   lcConnString = "Data Source=" + this.cServer + ";Initial Catalog=" + this.cDB + ";Integrated Security=SSPI;";
   lcCommand = "SELECT mytest_id, mytest_name, mytest_lookup_id, mytest_key FROM mytest";
   SqlDataSource1 = new SqlDataSource(lcConnString,lcCommand);
   SqlDataSource1.UpdateCommand = "UPDATE mytest SET mytest_name = @mytest_name, mytest_lookup_id = @mytest_lookup_id, mytest_key = @mytest_key WHERE (mytest_id = @mytest_id)";
   SqlDataSource1.DeleteCommand="DELETE FROM mytest WHERE (mytest_id = @mytest_id)";
   SqlDataSource1.InsertCommand="INSERT INTO mytest(mytest_name, mytest_lookup_id, mytest_key) VALUES (@mytest_name, @mytest_lookup_id, @mytest_key)";
   SqlDataSource1.ID = "sqlds1";

   string lcConnString2;
   string lcCommand2;
   lcConnString2 = "Data Source=" + this.cServer + ";Initial Catalog=" + this.cDB + ";Integrated Security=SSPI;";
   lcCommand2 = "SELECT lookup_id, lookup_desc FROM lookup";
   SqlDataSource SqlDataSource2 = new SqlDataSource(lcConnString2,lcCommand2);
   SqlDataSource2.ID = "sqlds2";

   
   grid.ID = "RadGrid1";
   grid.AutoGenerateColumns = false;
   grid.AllowAutomaticDeletes = true;
   grid.AllowAutomaticInserts = true;
   grid.AllowAutomaticUpdates = true;
   grid.Width = 400;
   

   //grid.DataSourceID = SqlDataSource1.ID;

   //Populate the Radgrid     
   dtTable = new DataTable();   
   //Open the SqlConnection     
   SqlConnection.Open();   
   try   
   {   
       //Select Query to populate the RadGrid with data from table Employees.     
       string selectQuery = "SELECT mytest_id, mytest_name, mytest_lookup_id, mytest_key FROM mytest";   
       SqlDataAdapter.SelectCommand = new SqlCommand(selectQuery, SqlConnection);   
       SqlDataAdapter.Fill(dtTable);   
       grid.DataSource = dtTable;   
   }   
   finally   
   {   
       //Close the SqlConnection     
       SqlConnection.Close();   
   }      
   
   GridBoundColumn mycolumn1 = new GridBoundColumn();
   mycolumn1.HeaderText = "mytest_name";
   mycolumn1.DataField = "mytest_name";
   mycolumn1.SortExpression="mytest_name";
   mycolumn1.UniqueName="mytest_name";
   grid.MasterTableView.Columns.Add(mycolumn1);

   string[] mydatakeynames;
   mydatakeynames = new string[1];
   mydatakeynames[0] = "mytest_id";

   grid.MasterTableView.DataKeyNames = mydatakeynames;
   grid.MasterTableView.EditMode = GridEditMode.PopUp;
   grid.MasterTableView.CommandItemDisplay = GridCommandItemDisplay.Top;

   GridDropDownColumn mycolumn2 = new GridDropDownColumn();
   mycolumn2.HeaderText = "Lookup";
   mycolumn2.DataField = "mytest_lookup_id";
   mycolumn2.ListValueField = "lookup_id";
   mycolumn2.ListTextField = "lookup_desc";
   mycolumn2.DataSourceID = SqlDataSource2.ID;
   grid.MasterTableView.Columns.Add(mycolumn2);

   GridBoundColumn mycolumn3 = new GridBoundColumn();
   mycolumn3.HeaderText = "mytest_key";
   mycolumn3.DataField = "mytest_key";
   mycolumn3.SortExpression="mytest_key";
   mycolumn3.UniqueName="mytest_key";
   grid.MasterTableView.Columns.Add(mycolumn3);


   GridEditCommandColumn mycolumn4 = new GridEditCommandColumn();
   grid.MasterTableView.Columns.Add(mycolumn4);

   GridButtonColumn mycolumn5 = new GridButtonColumn();
   mycolumn5.HeaderText = "Delete";
   mycolumn5.UniqueName = "DelCol";
   mycolumn5.CommandName = "Delete";
   mycolumn5.Text = "Delete";
   mycolumn5.ConfirmText = "Delete?";
   grid.MasterTableView.Columns.Add(mycolumn5);   
 
   grid.InsertCommand += new GridCommandEventHandler(grid_InsertCommand);
   grid.UpdateCommand += new GridCommandEventHandler(grid_UpdateCommand);
   grid.DeleteCommand += new GridCommandEventHandler(grid_DeleteCommand);

   panel.Controls.Add(SqlDataSource1);
   panel.Controls.Add(SqlDataSource2);
   panel.Controls.Add(grid);

   RadAjaxManager ajaxManager = RadAjaxManager.GetCurrent(this.Page);
   if (ajaxManager == null)
   {
       ajaxManager = new RadAjaxManager();
       ajaxManager.ID = "RadAjaxManager1";
       Controls.Add(ajaxManager);
       this.Page.Items.Add(typeof(RadAjaxManager), ajaxManager);
   }
   ajaxManager.AjaxSettings.AddAjaxSetting(grid, panel);
}


   protected void grid_UpdateCommand(object source, Telerik.Web.UI.GridCommandEventArgs e)   
   {   
     
       //Get the GridEditableItem of the RadGrid     
       GridEditableItem editedItem = e.Item as GridEditableItem;   
       //Get the primary key value using the DataKeyValue.     
       string mytest_id = editedItem.OwnerTableView.DataKeyValues[editedItem.ItemIndex]["mytest_id"].ToString();   
      //Access the textbox from the edit form template and store the values in string variables.     
       string mytest_name = (editedItem["mytest_name"].Controls[0] as TextBox).Text;   
       string mytest_lookup_id = (editedItem["mytest_lookup_id"].Controls[0] as RadComboBox).SelectedValue.ToString();   
       string mytest_key = (editedItem["mytest_key"].Controls[0] as TextBox).Text;   
   
       try   
       {   
           //Open the SqlConnection     
           SqlConnection.Open();   
           //Update Query to update the Datatable      
           string updateQuery = "UPDATE mytest SET mytest_name = " + "'" + mytest_name + "',mytest_lookup_id = " + mytest_lookup_id + ", mytest_key = " + "'" + mytest_key + "'" + " WHERE (mytest_id = " + mytest_id + ")";   
           SqlCommand.CommandText = updateQuery;   
           SqlCommand.Connection = SqlConnection;   
           SqlCommand.ExecuteNonQuery();   
           //Close the SqlConnection     
           SqlConnection.Close();   
 
 
       }   
       catch (Exception ex)   
       {   
           grid.Controls.Add(new LiteralControl("Unable to update mytest. Reason: " + ex.Message));   
           e.Canceled = true;   
       }     
 
   }   


   protected void grid_DeleteCommand(object source, Telerik.Web.UI.GridCommandEventArgs e)   
   {   
       //Get the GridDataItem of the RadGrid     
       GridDataItem item = (GridDataItem)e.Item;   
       //Get the primary key value using the DataKeyValue.     
       string mytest_id = item.OwnerTableView.DataKeyValues[item.ItemIndex]["mytest_id"].ToString();   
       try   
       {   
           //Open the SqlConnection     
           SqlConnection.Open();   
           string deleteQuery = "DELETE from mytest where mytest_id=" + mytest_id;   
           SqlCommand.CommandText = deleteQuery;   
           SqlCommand.Connection = SqlConnection;   
           SqlCommand.ExecuteNonQuery();   
           //Close the SqlConnection     
           SqlConnection.Close();   
 
       }   
       catch (Exception ex)   
       {   
           grid.Controls.Add(new LiteralControl("Unable to delete mytest. Reason: " + ex.Message));   
           e.Canceled = true;   
       }     
 
   }   


   protected void grid_InsertCommand(object source, Telerik.Web.UI.GridCommandEventArgs e)   
   {   
       //Get the GridEditFormInsertItem of the RadGrid     
       GridEditFormInsertItem insertedItem = (GridEditFormInsertItem)e.Item;   
 
       //string EmployeeID = (insertedItem["EmployeeID"].Controls[0] as TextBox).Text;   
 
       string mytest_name = (insertedItem["mytest_name"].Controls[0] as TextBox).Text;   
       string mytest_lookup_id = (insertedItem["mytest_lookup_id"].Controls[0] as RadComboBox).SelectedValue.ToString();   
       string mytest_key = (insertedItem["mytest_key"].Controls[0] as TextBox).Text;   
         
       try   
       {   
           //Open the SqlConnection     
           SqlConnection.Open();   
           //Update Query to insert into  the database      
         //  string insertQuery = "INSERT into  Employees(LastName,FirstName,Title,Address,City) values('" + LastName + "','" + FirstName + "','" + Title + "','" + Address + "','" + City + "')";   
           string insertQuery = "INSERT into mytest(mytest_name,mytest_lookup_id,mytest_key) values(" + "'" + mytest_name + "'," + mytest_lookup_id + "," + "'" + mytest_key + "'" + ")";   
       
           SqlCommand.CommandText = insertQuery;   
           SqlCommand.Connection = SqlConnection;   
           SqlCommand.ExecuteNonQuery();   
           //Close the SqlConnection     
           SqlConnection.Close();   
 
 
       }   
       catch (Exception ex)   
       {   
           grid.Controls.Add(new LiteralControl("Unable to insert mytest. Reason: " + ex.Message));   
           e.Canceled = true;   
       }     
 
   }   

}
}

0
Accepted
Sebastian
Telerik team
answered on 31 Mar 2009, 10:49 AM

Hello Dave,

I am pasting in this forum thread the reply I provided fro you in the support ticket you opened with regards to this subject:

When you implement manual updates/inserts/deletes, you need to set the AllowAutomaticXXXX properties of the grid to false. Thus you will be able to handle the actual operations inside the UpdateCommand/insertCommand/DeleteCommand handlers of the control.

One more thing I noticed in your code that you need to add the columns to the Columns collection of the grid first before setting their properties (when building the grid programmatically inside the OnLoad handler). More information on this subject can be found
here.

Additionally, you may consider advanced binding with 
NeedDataSource event handling as illustrates in this help article.

Best regards,

Sebastian
the Telerik team

Check out Telerik Trainer , the state of the art learning tool for Telerik products.
0
SIT
Top achievements
Rank 1
answered on 31 Mar 2009, 03:47 PM
Here is the working sample:

using System;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Serialization;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;
using Telerik.Web.UI;
using System.Data;
using System.Data.SqlClient;


namespace WebPart7
{
[Guid("656556bd-6adc-43a0-ad6a-4ddb8b5039c8")]
public class WebPart7:System.Web.UI.WebControls.WebParts.WebPart
{
   string cServer = "myserver";
   string cDB = "mydb";

   public static DataTable dtTable;   
   public static string connectionString = "Data Source=" + "mlw1" + ";Initial Catalog=" + "encompass" + ";Integrated Security=SSPI;";   
   public SqlConnection SqlConnection = new SqlConnection(connectionString);   
   public SqlDataAdapter SqlDataAdapter = new SqlDataAdapter();   
   public SqlCommand SqlCommand = new SqlCommand();
   RadGrid grid = new RadGrid();
        
public WebPart7()
{
}

protected override void CreateChildControls()
{
   base.CreateChildControls();

   // TODO: add custom rendering code here.
   // Label label = new Label();
   // label.Text = "Hello World";
   // this.Controls.Add(label);       
}


protected void grid_NeedDataSource(object source, Telerik.Web.UI.GridNeedDataSourceEventArgs e)   
{   
   dtTable = new DataTable();   
   SqlConnection.Open();   
   try   
   {        
       string selectQuery = "SELECT mytest_id, mytest_name, mytest_lookup_id, mytest_key FROM mytest";   
       SqlDataAdapter.SelectCommand = new SqlCommand(selectQuery, SqlConnection);   
       SqlDataAdapter.Fill(dtTable);   
       grid.DataSource = dtTable;   
   }   
   finally   
   {       
       SqlConnection.Close();   
   }     
 
}  

 protected override void OnInit(EventArgs e)
{
   base.OnInit(e);
   Page.ClientScript.RegisterStartupScript(typeof(WebPart7), this.ID, "_spOriginalFormAction = document.forms[0].action;_spSuppressFormOnSubmitWrapper=true;", true);
   if (this.Page.Form != null)
   {
       string formOnSubmitAtt = this.Page.Form.Attributes["onsubmit"];
       if (!string.IsNullOrEmpty(formOnSubmitAtt) && formOnSubmitAtt == "return _spFormOnSubmitWrapper();")
       {
           this.Page.Form.Attributes["onsubmit"] = "_spFormOnSubmitWrapper();";
       }
   }
}


 protected override void OnLoad(EventArgs e)
{
   base.OnLoad(e);

   // Panel
   Panel panel = new Panel();
   panel.ID = "Panel1";
   panel.EnableViewState = true;
   this.Controls.Add(panel);

   // Grid
   grid.ID = "RadGrid1";
   grid.AutoGenerateColumns = false;
   grid.AllowAutomaticDeletes = false;
   grid.AllowAutomaticInserts = false;
   grid.AllowAutomaticUpdates = false;
   grid.Width = 400;
   grid.NeedDataSource += new GridNeedDataSourceEventHandler(grid_NeedDataSource);
   grid.InsertCommand += new GridCommandEventHandler(grid_InsertCommand);
   grid.UpdateCommand += new GridCommandEventHandler(grid_UpdateCommand);
   grid.DeleteCommand += new GridCommandEventHandler(grid_DeleteCommand);
   string[] mydatakeynames;
   mydatakeynames = new string[1];
   mydatakeynames[0] = "mytest_id";
   grid.MasterTableView.DataKeyNames = mydatakeynames;
   grid.MasterTableView.EditMode = GridEditMode.PopUp;
   grid.MasterTableView.CommandItemDisplay = GridCommandItemDisplay.Top;   

   // Column 1
   GridBoundColumn mycolumn1 = new GridBoundColumn();
   grid.MasterTableView.Columns.Add(mycolumn1);
   mycolumn1.HeaderText = "mytest_name";
   mycolumn1.DataField = "mytest_name";
   mycolumn1.SortExpression="mytest_name";
   mycolumn1.UniqueName="mytest_name";
   
   // Column 2   
   GridDropDownColumn mycolumn2 = new GridDropDownColumn();
   grid.MasterTableView.Columns.Add(mycolumn2);
   mycolumn2.HeaderText = "Lookup";
   mycolumn2.DataField = "mytest_lookup_id";
   mycolumn2.ListValueField = "lookup_id";
   mycolumn2.ListTextField = "lookup_desc";
   string lcConnString;
   string lcCommand;
   lcConnString = "Data Source=" + this.cServer + ";Initial Catalog=" + this.cDB + ";Integrated Security=SSPI;";
   lcCommand = "SELECT lookup_id, lookup_desc FROM lookup";
   SqlDataSource SqlDataSource1 = new SqlDataSource(lcConnString,lcCommand);
   SqlDataSource1.ID = "sqlds2";
   mycolumn2.DataSourceID = SqlDataSource1.ID;
   
   // Column 3
   GridBoundColumn mycolumn3 = new GridBoundColumn();
   grid.MasterTableView.Columns.Add(mycolumn3);
   mycolumn3.HeaderText = "mytest_key";
   mycolumn3.DataField = "mytest_key";
   mycolumn3.SortExpression="mytest_key";
   mycolumn3.UniqueName="mytest_key";
   
   // Column 4
   GridEditCommandColumn mycolumn4 = new GridEditCommandColumn();
   grid.MasterTableView.Columns.Add(mycolumn4);

   // Column 5
   GridButtonColumn mycolumn5 = new GridButtonColumn();
   grid.MasterTableView.Columns.Add(mycolumn5);
   mycolumn5.HeaderText = "Delete";
   mycolumn5.UniqueName = "DelCol";
   mycolumn5.CommandName = "Delete";
   mycolumn5.Text = "Delete";
   mycolumn5.ConfirmText = "Delete?";
   
   // Add the SqlDataSource1 and grid to the panel
   panel.Controls.Add(SqlDataSource1);
   panel.Controls.Add(grid);

   // Ajax manager
   RadAjaxManager ajaxManager = RadAjaxManager.GetCurrent(this.Page);
   if (ajaxManager == null)
   {
       ajaxManager = new RadAjaxManager();
       ajaxManager.ID = "RadAjaxManager1";
       Controls.Add(ajaxManager);
       this.Page.Items.Add(typeof(RadAjaxManager), ajaxManager);
   }
   ajaxManager.AjaxSettings.AddAjaxSetting(grid, panel);
}

// Grid Update
protected void grid_UpdateCommand(object source, Telerik.Web.UI.GridCommandEventArgs e)   
{     
    GridEditableItem editedItem = e.Item as GridEditableItem;       
    string mytest_id = editedItem.OwnerTableView.DataKeyValues[editedItem.ItemIndex]["mytest_id"].ToString();      
    string mytest_name = (editedItem["mytest_name"].Controls[0] as TextBox).Text;   
    string mytest_lookup_id = (editedItem["mytest_lookup_id"].Controls[0] as RadComboBox).SelectedValue.ToString();   
    string mytest_key = (editedItem["mytest_key"].Controls[0] as TextBox).Text;   
   
    try   
    {   
        SqlConnection.Open();   
        string updateQuery = "UPDATE mytest SET mytest_name = " + "'" + mytest_name + "',mytest_lookup_id = " + mytest_lookup_id + ", mytest_key = " + "'" + mytest_key + "'" + " WHERE (mytest_id = " + mytest_id + ")";   
        SqlCommand.CommandText = updateQuery;   
        SqlCommand.Connection = SqlConnection;   
        SqlCommand.ExecuteNonQuery();   
        SqlConnection.Close();   
    }   
    catch (Exception ex)   
    {   
        grid.Controls.Add(new LiteralControl("Unable to update mytest. Reason: " + ex.Message));   
        e.Canceled = true;   
    }
    
}   

// Grid Delete
protected void grid_DeleteCommand(object source, Telerik.Web.UI.GridCommandEventArgs e)   
{       
   GridDataItem item = (GridDataItem)e.Item;       
   string mytest_id = item.OwnerTableView.DataKeyValues[item.ItemIndex]["mytest_id"].ToString();
 
   try   
   {      
       SqlConnection.Open();   
       string deleteQuery = "DELETE from mytest where mytest_id=" + mytest_id;   
       SqlCommand.CommandText = deleteQuery;   
       SqlCommand.Connection = SqlConnection;   
       SqlCommand.ExecuteNonQuery();    
       SqlConnection.Close();
   }   
   catch (Exception ex)   
   {   
       grid.Controls.Add(new LiteralControl("Unable to delete mytest. Reason: " + ex.Message));   
       e.Canceled = true;   
   }     
 
}   

// Grid Insert
protected void grid_InsertCommand(object source, Telerik.Web.UI.GridCommandEventArgs e)   
{       
   GridEditFormInsertItem insertedItem = (GridEditFormInsertItem)e.Item;   
   string mytest_name = (insertedItem["mytest_name"].Controls[0] as TextBox).Text;   
   string mytest_lookup_id = (insertedItem["mytest_lookup_id"].Controls[0] as RadComboBox).SelectedValue.ToString();   
   string mytest_key = (insertedItem["mytest_key"].Controls[0] as TextBox).Text;   
         
   try   
   {       
       SqlConnection.Open();   
       string insertQuery = "INSERT into mytest(mytest_name,mytest_lookup_id,mytest_key) values(" + "'" + mytest_name + "'," + mytest_lookup_id + "," + "'" + mytest_key + "'" + ")";
       SqlCommand.CommandText = insertQuery;   
       SqlCommand.Connection = SqlConnection;   
       SqlCommand.ExecuteNonQuery();        
       SqlConnection.Close();
    }   
    catch (Exception ex)   
    {   
       grid.Controls.Add(new LiteralControl("Unable to insert mytest. Reason: " + ex.Message));   
       e.Canceled = true;   
    }
}   

}
}

Tags
Grid
Asked by
SIT
Top achievements
Rank 1
Answers by
SIT
Top achievements
Rank 1
Sebastian
Telerik team
Share this question
or