Update database when using RadGrid and user control to edit record...

[Koen Hoebeek]

Dear all,

I'm following this demo : http://demos.telerik.com/aspnet-ajax/grid/examples/dataediting/usercontroleditform/defaultcs.aspx
I can simulate this in a working environment. But the database is not updated, only the in memory datatable.

How can I update the real database?  Is there a method that can do or do you have working example that works directly on the SQLDatasource? 

Please help!

Koen

[Elliott]

update these two methods to:
instantiate a SQL connection and SQL command object, along with all the relevant SQL parameter objects
open the connection
invoke a method of the SQL command object (ExecuteNonQuery or ExecuteScalar)
close the connection
dispose of the objects
well, that's how to do it in ADO.NET
there is a way to update the database directly off the DataSet

        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)
        {
            GridEditableItem editedItem = e.Item as GridEditableItem;
            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();
            }
        }
}
}

[Koen Hoebeek]

Thanks for your answer.

You say : "there is a way to update the database directly off the DataSet "

How to do this? You have simple example of it? Should be very usefull for me and many others.

Thanks,

Koen

[Elliott]

look at this
http://support.microsoft.com/kb/307587

I use classic ADO.NET because it works, and the objects needed only exist as long as they need to update the database
I only use a data adapter to Fill a dataset, which is then passed back to a business layer

you're using Microsoft SQL Server, right?