[Solved] Radgrid demo WebUserTemplate not inserting/updating

[Felice]

I have copied as it is this demo but I am unable to insert/update the records.

private static DataTable GetDataTable(string queryString)
      {
          String connString = ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString;
           SqlConnection mySqlConnection = new SqlConnection(connString);
           SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
           mySqlDataAdapter.SelectCommand = new SqlCommand(queryString, mySqlConnection);

           DataTable myDataTable = new DataTable();
           mySqlConnection.Open();
           try
           {
                mySqlDataAdapter.Fill(myDataTable);
           }
           finally
           {
                mySqlConnection.Close();
           }

           return myDataTable;
      }

      private DataTable Employees
      {
           get
           {
                object obj = this.Session["Employees"];
                if ((!(obj == null)))
                {
                     return ((DataTable)(obj));
                }
                DataTable myDataTable = new DataTable();
                myDataTable = GetDataTable("SELECT * FROM Employees");
                this.Session["Employees"] = myDataTable;
                return myDataTable;
           }
      }

      protected void RadGrid1_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
      {
           this.RadGrid1.DataSource = this.Employees;
           this.Employees.PrimaryKey = new DataColumn[] { this.Employees.Columns["EmployeeID"] };
      }

      protected void RadGrid1_UpdateCommand(object source, GridCommandEventArgs e)
      {
           GridEditableItem editedItem = e.Item as GridEditableItem;
           UserControl userControl = (UserControl)e.Item.FindControl(GridEditFormItem.EditFormUserControlID);

           //Prepare new row to add it in the DataSource
           DataRow[] changedRows = this.Employees.Select("EmployeeID = " + editedItem.OwnerTableView.DataKeyValues[editedItem.ItemIndex]["EmployeeID"]);

           if (changedRows.Length != 1)
           {
                RadGrid1.Controls.Add(new LiteralControl("Unable to locate the Employee for updating."));
                e.Canceled = true;
                return;
           }

           //Update new values
           Hashtable newValues = new Hashtable();

           newValues["Country"] = (userControl.FindControl("TextBox7") as TextBox).Text;
           newValues["City"] = (userControl.FindControl("TextBox8") as TextBox).Text;
           newValues["Region"] = (userControl.FindControl("TextBox9") as TextBox).Text;
           newValues["HomePhone"] = (userControl.FindControl("HomePhoneBox") as RadMaskedTextBox).Text;
           newValues["BirthDate"] = (userControl.FindControl("BirthDatePicker") as RadDatePicker).SelectedDate.ToString();
           newValues["TitleOfCourtesy"] = (userControl.FindControl("ddlTOC") as DropDownList).SelectedItem.Value;

           newValues["Notes"] = (userControl.FindControl("TextBox1") as TextBox).Text;
           newValues["Address"] = (userControl.FindControl("TextBox6") as TextBox).Text;
           newValues["FirstName"] = (userControl.FindControl("TextBox2") as TextBox).Text;
           newValues["LastName"] = (userControl.FindControl("TextBox3") as TextBox).Text;
           newValues["HireDate"] = (userControl.FindControl("HireDatePicker") as RadDatePicker).SelectedDate.ToString();
           newValues["Title"] = (userControl.FindControl("TextBox4") as TextBox).Text;

           changedRows[0].BeginEdit();
           try
           {
                foreach (DictionaryEntry entry in newValues)
                {
                     changedRows[0][(string)entry.Key] = entry.Value;
                }
                changedRows[0].EndEdit();
                this.Employees.AcceptChanges();
           }
           catch (Exception ex)
           {
                changedRows[0].CancelEdit();

                Label lblError = new Label();
                lblError.Text = "Unable to update Employees. Reason: " + ex.Message;
                lblError.ForeColor = System.Drawing.Color.Red;
                RadGrid1.Controls.Add(lblError);

                e.Canceled = true;
           }
      }

      protected void RadGrid1_InsertCommand(object source, GridCommandEventArgs e)
      {
           UserControl userControl = (UserControl)e.Item.FindControl(GridEditFormItem.EditFormUserControlID);

           //Create new row in the DataSource
           DataRow newRow = this.Employees.NewRow();

           //Insert new values
           Hashtable newValues = new Hashtable();

           newValues["Country"] = (userControl.FindControl("TextBox7") as TextBox).Text;
           newValues["City"] = (userControl.FindControl("TextBox8") as TextBox).Text;
           newValues["Region"] = (userControl.FindControl("TextBox9") as TextBox).Text;
           newValues["HomePhone"] = (userControl.FindControl("HomePhoneBox") as RadMaskedTextBox).Text;
           newValues["BirthDate"] = (userControl.FindControl("BirthDatePicker") as RadDatePicker).SelectedDate.ToString();
           newValues["TitleOfCourtesy"] = (userControl.FindControl("ddlTOC") as DropDownList).SelectedItem.Value;

           newValues["Notes"] = (userControl.FindControl("TextBox1") as TextBox).Text;
           newValues["Address"] = (userControl.FindControl("TextBox6") as TextBox).Text;
           newValues["FirstName"] = (userControl.FindControl("TextBox2") as TextBox).Text;
           newValues["LastName"] = (userControl.FindControl("TextBox3") as TextBox).Text;
           newValues["HireDate"] = (userControl.FindControl("HireDatePicker") as RadDatePicker).SelectedDate.ToString();
           newValues["Title"] = (userControl.FindControl("TextBox4") as TextBox).Text;

           //make sure that unique primary key value is generated for the inserted row
           newValues["EmployeeID"] = (int)this.Employees.Rows[this.Employees.Rows.Count - 1]["EmployeeID"] + 1;
           try
           {
                foreach (DictionaryEntry entry in newValues)
                {
                     newRow[(string)entry.Key] = entry.Value;
                }
                this.Employees.Rows.Add(newRow);
                this.Employees.AcceptChanges();
           }
           catch (Exception ex)
           {
                Label lblError = new Label();
                lblError.Text = "Unable to insert Employees. Reason: " + ex.Message;
                lblError.ForeColor = System.Drawing.Color.Red;
                RadGrid1.Controls.Add(lblError);

                e.Canceled = true;
           }
      }
      protected void RadGrid1_DeleteCommand(object source, GridCommandEventArgs e)
      {
           string iD = (e.Item as GridDataItem).OwnerTableView.DataKeyValues[e.Item.ItemIndex]["EmployeeID"].ToString();
           DataTable employeeTable = this.Employees;
           if (employeeTable.Rows.Find(iD) != null)
           {
                employeeTable.Rows.Find(iD).Delete();
                employeeTable.AcceptChanges();
           }
      }

Is there anything that needs tobe changed? Could you please point me to the right direction?
Thanks,
Felice

[Shinu]

Hi Felice,

The code works fine at my end. Make sure your connection string is correct for proper operation. Make sure your command events fire and use breakpoint to find out if all the values are obtained in the Update/Insert/Delete command. If you have made any changes to the demo code, please let know.

Thanks,
Shinu

[Felice]

Hi Shinu,
sorry to bother you with this again.
Here is my solution.
As already mentioned my main problem is that the changes made in the form do not get saved to the db. The changes or even new records are only visible in the current session. If I rerun the code all changes and new records inserted will disappear.
Another minor problem I have is that the edit form stays open even if I set this line to false:   this.RadGrid1.MasterTableView.Items[1].Edit = false;

You will see in the solution I tried also other methods for insert/update which are commented out but I get even more problems with those.
Your support is really appreciated.
Kind regards
Felice

[Shinu]

Hi Felice,

Basically, the edit/insert form not closing after an operation is a sign that there is an issue with the update/insert logic. I see that you are having Automatic CRUD operation, but along with which you have used OnUpdateCommand/OnInsertCommand events. These events are used only when performing Manual CRUD operations.
Either use only AllowAutomaticInserts="true" AllowAutomaticUpdates="true" AllowAutomaticDeletes="true" for Automatic operation with your SqlDataSource, else you have to do manual updates setting the above properties to false and handling the events in OnUpdateCommand/OnInsertCommand events. Please try and let me know if any concern.

Thanks,
Shinu

[Felice]

Shinu, thanks again for your continuous support.
I have reproduced the demo without any modification, rather then my connection string and a SqlDataSource necessary for the connection to the db. Here is the linked project.
The problem about the edit form not closing is solved.
But still the data are not written in the db. New records or modified once exist only in memory.
Help!
Could you please provide me the changes necessary to make this working (I mean to save changes and records to the db)?
Thanks a million,
Felice

[Shinu]

Hi Felice,

You can try the following code snippet to Update values to db. Similarly you can do for Insert.

ASPX.CS:

public static DataTable dtTable;
SqlConnection SqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
public SqlDataAdapter SqlDataAdapter = new SqlDataAdapter();
public SqlCommand SqlCommand = new SqlCommand();
protected void RadGrid1_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
{
    dtTable = new DataTable();
    SqlConnection.Open();
    try
    {
        string selectQuery = "SELECT * FROM Employees";
        SqlDataAdapter.SelectCommand = new SqlCommand(selectQuery, SqlConnection);
        SqlDataAdapter.Fill(dtTable);
        RadGrid1.DataSource = dtTable;
    }
    finally
    {
        SqlConnection.Close();
    }
}

protected void RadGrid1_UpdateCommand(object source, GridCommandEventArgs e)
{
    string drpTitleOfCourtesy, txtFirstName, txtLastName, radpickerHireDate, txtTitle;
    GridEditableItem editedItem = e.Item as GridEditableItem;
    string Employeeid = editedItem.OwnerTableView.DataKeyValues[editedItem.ItemIndex]["EmployeeID"].ToString();
    UserControl userControl = (UserControl)e.Item.FindControl(GridEditFormItem.EditFormUserControlID);

    drpTitleOfCourtesy = (userControl.FindControl("ddlTOC") as DropDownList).SelectedItem.Value;
    txtFirstName = (userControl.FindControl("TextBox2") as TextBox).Text;
    txtLastName = (userControl.FindControl("TextBox3") as TextBox).Text;
    radpickerHireDate = (userControl.FindControl("HireDatePicker") as RadDatePicker).SelectedDate.ToString();
    txtTitle = (userControl.FindControl("TextBox4") as TextBox).Text;
    try
    {
        SqlConnection.Open();
        string updateQuery = "UPDATE Employees SET TitleOfCourtesy =@0,FirstName=@1, LastName=@2, HireDate=@3,Title=@4 where EmployeeID=@5 ";
        SqlCommand.CommandText = updateQuery;
        SqlCommand.Connection = SqlConnection;
        SqlCommand.Parameters.AddWithValue("@0", drpTitleOfCourtesy);
        SqlCommand.Parameters.AddWithValue("@1", txtFirstName);
        SqlCommand.Parameters.AddWithValue("@2", txtLastName);
        SqlCommand.Parameters.AddWithValue("@3", radpickerHireDate);
        SqlCommand.Parameters.AddWithValue("@4", txtTitle);
        SqlCommand.Parameters.AddWithValue("@5", Employeeid);
        SqlCommand.ExecuteNonQuery();
        SqlConnection.Close();
    }
    catch (Exception ex)
    {
        Label lblError = new Label();
        lblError.Text = "Unable to update Employees. Reason: " + ex.Message;
        lblError.ForeColor = System.Drawing.Color.Red;
        RadGrid1.Controls.Add(lblError);
        e.Canceled = true;
    }
}
protected void RadGrid1_DeleteCommand(object source, GridCommandEventArgs e)
{
    string Employeeid = (e.Item as GridDataItem).OwnerTableView.DataKeyValues[e.Item.ItemIndex]["EmployeeID"].ToString();
    try
    {
        SqlConnection.Open();
        string deleteQuery = "DELETE FROM Employees where EmployeeID=@0 ";
        SqlCommand.Connection = SqlConnection;
        SqlCommand.CommandText = deleteQuery;
        SqlCommand.Parameters.AddWithValue("@0", Employeeid);
        SqlCommand.ExecuteNonQuery();
        SqlConnection.Close();
    }
    catch (Exception ex)
    {
        Label lblError = new Label();
        lblError.Text = "Unable to update Employees. Reason: " + ex.Message;
        lblError.ForeColor = System.Drawing.Color.Red;
        RadGrid1.Controls.Add(lblError);
        e.Canceled = true;
    }
}

Thanks,
Shinu

[Felice]

Thank you Shinu,
your snippet has been of help. It does the job.

Kind regards
Felice