[Solved] Insert/Update failed

[Jaimie]

Can anyone tell whats wrong with the following code? The inert / update failed with message " Cannot insert null value to database, etc" Thanks

Asp.net:

    <asp:SqlDataSource ID="LessonHardStudentDescDataSource" runat="server"
       ConnectionString="<%$ ConnectionStrings:EnglishAGoGoConnectionString %>"
       SelectCommand="select * from LESSONHARD_STUDENTDESCRIPTION where LessonHard_ID = @SelectLessonHard_ID"
       InsertCommand="INSERT INTO LESSONHARD_STUDENTDESCRIPTION (LessonHard_ID, LessonHard_Name_Student, LessonHard_Description_Student, ResourceLanguage_ID) VALUES (@InsertLessonHard_ID, @InsertLessonHard_Name_Student, @InsertLessonHard_Description_Student, @InsertResourceLanguage_ID)"
       UpdateCommand="Update LESSONHARD_STUDENTDESCRIPTION SET LessonHard_Name_Student=@UpdateLessonHard_Name_Student, LessonHard_Description_Student=@UpdateLessonHard_Description_Student, ResourceLanguage_ID=@UpdateResourceLanguage_ID">
        <InsertParameters>
            <asp:Parameter DbType="Guid" Name="InsertLessonHard_ID" />
            <asp:Parameter DbType="String" Name="InsertLessonHard_Name_Student" />
            <asp:Parameter DbType="String" Name="InsertLessonHard_Description_Student" />
            <asp:Parameter DbType="Int32" Name="InsertResourceLanguage_ID" />
        </InsertParameters>
        <UpdateParameters>
            <asp:Parameter DbType="String" Name="UpdateLessonHard_Name_Student" />
            <asp:Parameter DbType="String" Name="UpdateLessonHard_Description_Student" />
            <asp:Parameter DbType="Int32" Name="UpdateResourceLanguage_ID" />
        </UpdateParameters>
    </asp:SqlDataSource>

<telerik:RadGrid ID="RadGridResource" runat="server" AllowAutomaticDeletes="True"
                AllowAutomaticInserts="True" AllowAutomaticUpdates="True"
                AllowFilteringByColumn="True" AllowPaging="True" AllowSorting="True"
            AutoGenerateColumns="False" GridLines="None"
                onitemdatabound="RadGridResource_ItemDataBound" OnItemDeleted="RadGridResource_ItemDeleted"
            OnItemInserted="RadGridResource_ItemInserted"
                OnItemUpdated="RadGridResource_ItemUpdated"
                DataSourceID="LessonHardStudentDescDataSource"
                onitemcommand="RadGridResource_ItemCommand">
            <MasterTableView CommandItemDisplay="Top" DataKeyNames="LHSD_ID"
                DataSourceID="LessonHardStudentDescDataSource">
                <RowIndicatorColumn>
                    <HeaderStyle Width="20px" />
                </RowIndicatorColumn>
                <ExpandCollapseColumn>
                    <HeaderStyle Width="20px" />
                </ExpandCollapseColumn>
                <Columns>
                    <telerik:GridEditCommandColumn>
                    </telerik:GridEditCommandColumn>
                    <telerik:GridBoundColumn DataField="LessonHard_ID" HeaderText="LessonID" UniqueName="LessonHard_ID"
                        Visible="False">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn AutoPostBackOnFilter="True" CurrentFilterFunction="Contains"
                        DataField="LessonHard_Name_Student" HeaderText="Name" ShowFilterIcon="False"
                        SortExpression="LessonHard_Name_Student" UniqueName="LessonHard_Name_Student">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn AutoPostBackOnFilter="True" CurrentFilterFunction="Contains"
                        DataField="LessonHard_Description_Student" HeaderText="Description"
                        ShowFilterIcon="False" SortExpression="LessonHard_Description_Student"
                        UniqueName="LessonHard_Description_Student">
                    </telerik:GridBoundColumn>
                    <telerik:GridDropDownColumn DataField="ResourceLanguage_ID" DataType="System.Int32" HeaderText="Type" DataSourceID="ResourceLangDataSource"
                        SortExpression="ResourceLanguage_ID" UniqueName="ResourceLanguage_ID"
                        ListTextField="ResourceLanguageName" ListValueField="ResourceLanguageID">
                        <FilterTemplate>
                            <telerik:RadComboBox ID="RadComboBox1" DataSourceID="ResourceLangDataSource" DataTextField="ResourceLanguageName"
                                DataValueField="ResourceLanguageID" AppendDataBoundItems="true"
                                SelectedValue='<%# ((GridItem)Container).OwnerTableView.GetColumn("ResourceLanguage_ID").CurrentFilterValue %>'
                                runat="server" OnClientSelectedIndexChanged="SelectedIndexChanged1">
                                <Items>
                                    <telerik:RadComboBoxItem />
                                </Items>
                            </telerik:RadComboBox>
                            <telerik:RadScriptBlock ID="RadScriptBlock1" runat="server">
                                <script type="text/javascript">
                                    function SelectedIndexChanged1(sender, args) {
                                        var tableView = $find("<%# ((GridItem)Container).OwnerTableView.ClientID %>");
                                        tableView.filter("ResourceLanguage_ID", args.get_item().get_value(), "EqualTo");
                                    }
                                </script>
                             </telerik:RadScriptBlock>
                        </FilterTemplate>
                    </telerik:GridDropDownColumn>
                    <telerik:GridButtonColumn ButtonType="LinkButton" CommandName="Delete" ConfirmDialogType="RadWindow"
                        ConfirmText="Delete this resource?" ConfirmTitle="Delete" Text="Delete"
                        UniqueName="DeleteColumn">
                    </telerik:GridButtonColumn>
                </Columns>
                <EditFormSettings EditFormType="Template">
                    <FormTemplate>
                        <table id="Table3" border="0" cellpadding="1" cellspacing="1" width="250">
                            <tr>
                                <td>Lesson ID: </td>
                                <td>
                                    <asp:TextBox ID="txtLessonHardID" runat="server" Text='<%# Bind("LessonHard_ID") %>'></asp:TextBox>
                                </td>
                            </tr>
                            <tr>
                                <td>Name:</td>
                                <td>
                                    <asp:TextBox ID="TextBox7" runat="server" CssClass="form100" Rows="5" Text='<%# Bind("LessonHard_Name_Student") %>' TextMode="MultiLine">
                                    </asp:TextBox>
                                </td>
                            </tr>
                            <tr>
                                <td>Description:</td>
                                <td>
                                    <asp:TextBox ID="TextBox8" runat="server" CssClass="form100" Rows="5" TabIndex="1" Text='<%# Bind("LessonHard_Description_Student") %>' TextMode="MultiLine">
                                    </asp:TextBox>
                                </td>
                            </tr>
                            <tr>
                                <td>Resource Language:</td>
                                <td>
                                    <telerik:RadComboBox ID="RadComboBox2" runat="server" AppendDataBoundItems="true" DataSourceID="ResourceLangDataSource" DataTextField="ResourceLanguageName" DataValueField="ResourceLanguageID"
                                        SelectedValue='<%# Bind("ResourceLanguage_ID") %>'>
                                        <Items>
                                            <telerik:RadComboBoxItem />
                                        </Items>
                                    </telerik:RadComboBox>
                                </td>
                            </tr>
                            <tr>
                                <td align="right" colspan="2">
                                    <asp:Button ID="btnUpdate" Text='<%# (Container is GridEditFormInsertItem) ? "Insert" : "Update" %>'
                                        runat="server" CommandName='<%# (Container is GridEditFormInsertItem) ? "PerformInsert" : "Update" %>'>
                                    </asp:Button>
                                    &nbsp;
                                    <asp:Button ID="btnCancel" Text="Cancel" runat="server" CausesValidation="False" CommandName="Cancel"></asp:Button>
                                </td>
                            </tr>
                         </table>
                     </FormTemplate>
                 </EditFormSettings>
             </MasterTableView>
                <GroupingSettings
                CaseSensitive="False" />
         </telerik:RadGrid>

 

 

CS:

 

using System;
using System.Web.Security;
using System.IO;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

using Telerik.Web.UI;

using ORM = EAGG.ORM;
using BLL = EAGG.BLL;
using Utilities = EAGG.Utilities;
using System.Data;

public partial class Lessons_LessonEditor : System.Web.UI.Page
{
    Guid LessonId;
    Boolean IsDeleted;


     //<summary>
     //Get the License_ID from session
     //</summary>
     //<returns>Guid LicenseID</returns>
    protected String GetLicenseID()
    {
        String LicenseID;
        if (Session["License_ID"] != null)
        {
            LicenseID =  Session["License_ID"].ToString();
        }
        else
        {
            LicenseID = "";
        }
        return LicenseID;
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        RadAjaxManager AjaxManager = (RadAjaxManager)this.Page.Master.FindControl("RadAjaxManager1");
        AjaxManager.AjaxSettings.AddAjaxSetting(RadMultiPage1, RadMultiPage1);
        AjaxManager.AjaxSettings.AddAjaxSetting(RadMultiPage1, RadTabStrip1);
        AjaxManager.AjaxSettings.AddAjaxSetting(RadGridResource, RadGridResource);

        Guid myID;

        string sID = Request.QueryString.Get("Id");
        string sDeleted = Request.QueryString.Get("Deleted");

        if (string.IsNullOrEmpty(sID))
        {
            myID = Guid.Empty;
        }
        else
        {
            myID = new Guid(sID);
        }

        LessonId = myID;

        if (LessonId != Guid.Empty)
        {
            //LessonHardDataSource.CommandText = "select * from LessonHardStudentDescription where LessonHardID='" + LessonId + "'";
            LessonHardStudentDescDataSource.SelectParameters.Clear();
            LessonHardStudentDescDataSource.SelectParameters.Add("SelectLessonHard_ID", System.Data.DbType.Guid, LessonId.ToString());

            IsDeleted = Boolean.Parse(sDeleted);

            if (IsDeleted == true)
            {
                RadTabStrip1.Enabled = false;
                RadMultiPage1.Enabled = false;
            }
            else
            {
                lblDeleted.Visible = false;
                btnRestore.Visible = false;
                btnRestore.Enabled = false;
            }
        }

        if (!IsPostBack)
        {
            //when page loads for the first time...
            if (LessonId == Guid.Empty) //if there is no Id passed through...
            {
                AddOrEditMode1.SetMode("create");
                DetailsView1.ChangeMode(DetailsViewMode.Insert);
                lblDeleted.Visible = false;
                btnRestore.Visible = false;
            }
            else
            {
                //we have an Id
                AddOrEditMode1.SetMode("edit");
            }
            RadTabStrip1.SelectedIndex = 0;
            RadMultiPage1.SelectedIndex = 0;
        }
    }
    protected void DetailsView1_ItemCommand(object sender, DetailsViewCommandEventArgs e)
    {
        if (e.CommandName == "Cancel")
        {
            Response.Redirect("~/Lessons/Lesson.aspx?message=Your request has been cancelled.");
        }
    }
    protected void DetailsView1_ItemInserted(object sender, DetailsViewInsertedEventArgs e)
    {
        //try
        //{
        //    Guid newLessonID;
        //    ORM.Lessonhard newlh = (ORM.Lessonhard)e.Entity
        //    newLessonID = newlh.LessonHardID;

        //    Response.Redirect("~/Lesson/LessonEditor.aspx?Id=" + newLessonID + "&" + "Deleted=False");
        //    //Master.ResultMessage = "Command completed.";

        //}
        //catch (SqlException ex)
        //{
        //    // Master.ErrorMessage = ex.Message;
        //    //Master.ErrorMessage = "Cannot get product data.";
        //}
        RadGridResource.Rebind();
    }
    protected void DetailsView1_ItemUpdated(object sender, DetailsViewUpdatedEventArgs e)
    {
        lblMessage.Text = "Information has been updated.";
    }

    protected void DetailsView1_DataBound(object sender, EventArgs e)
    {
        if (DetailsView1.CurrentMode == DetailsViewMode.Insert)
        {
            DetailsView view = (DetailsView)sender;
            DetailsViewRowCollection rows = view.Rows;
            //Set the value for LicenseID from Sesson
            DetailsViewRow row = rows[2];
            Label licenseLabel = (Label)row.FindControl("lblLicenseID");
            licenseLabel.Text = GetLicenseID();
            //Set the value for AdministratorID from Sesson
            DetailsViewRow row1 = rows[1];
            Label adminIDLabel = (Label)row.FindControl("lblAdminID");
            adminIDLabel.Text = Session["Administrator_ID"].ToString();
        }

    }
    protected void LessonHardDataSource_Inserted(object sender, Telerik.OpenAccess.OpenAccessDataSourceChangedEventArgs e)
    {
         try
        {
            Guid newLessonID;
            ORM.Lessonhard newlh = (ORM.Lessonhard)e.Entity;
            newLessonID = newlh.LessonHardID;

            //LessonHardDataSource.Sel.Where = "LessonHardID='" + newLessonID.ToString() + "'";

            //LessonHardStudentDescDataSource.Where = "LessonHardID = '" + newLessonID + "'";
            //RadGridResource.DataSource = LessonHardStudentDescDataSource;
            //RadGridResource.Rebind();

            RadGridResource.Rebind();

            Response.Redirect("~/Lessons/LessonEditor.aspx?Id=" + newLessonID + "&" + "Deleted=False");
            //Master.ResultMessage = "Command completed.";


        }
        catch (SqlException ex)
        {
            // Master.ErrorMessage = ex.Message;
            //Master.ErrorMessage = "Cannot get product data.";
        }
    }
    protected void LessonHardDataSource_Inserting(object sender, Telerik.OpenAccess.OpenAccessDataSourceChangingEventArgs e)
    {

    }
    protected void RadGridResource_ItemDeleted(object source, GridDeletedEventArgs e)
    {
        if (e.Exception != null)
        {
            e.ExceptionHandled = true;
            DisplayMessage(true, "Resource " + e.Item.OwnerTableView.DataKeyValues[e.Item.ItemIndex]["LHSD_ID"] + " cannot be deleted. Reason: " + e.Exception.Message);
        }
        else
        {
            DisplayMessage(false, "Resource " + e.Item.OwnerTableView.DataKeyValues[e.Item.ItemIndex]["LHSD_ID"] + " deleted");
        }


    }
    protected void RadGridResource_ItemInserted(object source, GridInsertedEventArgs e)
    {
        if (e.Exception != null)
        {
            e.ExceptionHandled = true;
            e.KeepInInsertMode = true;
            DisplayMessage(true, "Resource cannot be inserted. Reason: " + e.Exception.Message);
        }
        else
        {
            DisplayMessage(false, "Resource inserted");
        }


    }
    protected void RadGridResource_ItemUpdated(object source, GridUpdatedEventArgs e)
    {
        if (e.Exception != null)
        {
            e.KeepInEditMode = true;
            e.ExceptionHandled = true;
            DisplayMessage(true, "Resource " + e.Item.OwnerTableView.DataKeyValues[e.Item.ItemIndex]["LSHD_ID"] + " cannot be updated. Reason: " + e.Exception.Message);
        }
        else
        {
            DisplayMessage(false, "Resource " + e.Item.OwnerTableView.DataKeyValues[e.Item.ItemIndex]["LSHD_ID"] + " updated");
        }


    }

    private void DisplayMessage(bool isError, string text)
    {
        RadGridResource.Controls.Add(new LiteralControl(string.Format("<span style='color:red'>{0}</span>", text)));
    }

    protected void RadGridResource_ItemDataBound(object sender, GridItemEventArgs e)
    {
        if (e.Item is GridEditFormItem && e.Item.IsInEditMode)
        {
            GridEditFormItem eeditFormItem = e.Item as GridEditFormItem;

            TextBox txtLessonHardID = (TextBox)editFormItem.FindControl("txtLessonHardID");
            txtLessonHardID.Text = LessonId.ToString();

        }
    }
    protected void RadGridResource_ItemCommand(object source, GridCommandEventArgs e)
    {
        if (e.CommandName == RadGrid.InitInsertCommandName) //"Add new" button clicked
        {
            GridEditCommandColumn editColumn = (GridEditCommandColumn)RadGridResource.MasterTableView.GetColumn("EditCommandColumn");
            editColumn.Visible = false;
        }
        else if (e.CommandName == RadGrid.RebindGridCommandName && e.Item.OwnerTableView.IsItemInserted)
        {
            e.Canceled = true;
        }
        else
        {
            GridEditCommandColumn editColumn = (GridEditCommandColumn)RadGridResource.MasterTableView.GetColumn("EditCommandColumn");
            if (!editColumn.Visible)
                editColumn.Visible = true;
        }


    }
}

Thanks

 

 

 

[Shinu]

Hello,

Can you check whether you have any field which does not allow null and not bound with grid. Also check whether the LHSD_ID is Auto-incremented or not.

-Shinu.

[Jaimie]

The code failed for both (insert  and update).

Those are all the required fields. It seems the values just don't save for update or insert.

Resource cannot be updated. Reason: Cannot insert the value NULL into column 'LessonHard_Name_Student', table 'C:\USERS\JAIMIE\DOCUMENTS\VISUAL STUDIO 2008\PROJECTS\EAGG\CMS\APP_DATA\ENGLISH.MDF.dbo.LESSONHARD_STUDENTDESCRIPTION'; column does not allow nulls. UPDATE fails. The statement has been terminated.

Any concerns / suggestions?

Thanks