troubleshooting Question

Insertion of Details view results in an error

Avatar of Ru1995
Ru1995Flag for United States of America asked on
C#ASP.NET.NET Programming
4 Comments1 Solution406 ViewsLast Modified:
I'm trying to insert a menu item into a database.  Whenever I do the insert it "results in a System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'CategoryId', table 'ParishPDX.dbo.Product'; column does not allow nulls. INSERT fails.
The statement has been terminated. "

However, when I check the database and if I go back and refresh the page the item I inserted appears.  

How do I prevent the error from appearing and just return to a refreshed version of the page i was working on?  Please help

Here is the code for my details view

 <asp:DetailsView ID="dvMenu" runat="server" Height="50px" Width="125px"   
        DefaultMode="Insert"
        AutoGenerateRows="False" DataKeyNames="ProductId"   
        DataSourceID="sqlNewProduct" oniteminserting="dvMenu_ItemInserting" 
        onmodechanging="dvMenu_ModeChanging" Visible="False" >
        <Fields>
          
            <asp:TemplateField HeaderText="Category" SortExpression="Category">
               
                <InsertItemTemplate>
                    <asp:DropDownList ID="ddlCategory" runat="server" DataSourceID="sqlCategory" 
                        DataTextField="Category" DataValueField="CategoryId">
                    </asp:DropDownList>
                    <asp:SqlDataSource ID="sqlCategory" runat="server" 
                        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
                        SelectCommand="SELECT * FROM [Category]" ></asp:SqlDataSource>
                </InsertItemTemplate>
                <ItemTemplate>
                    <asp:DropDownList ID="ddlCategory" runat="server" DataSourceID="sqlCategory" 
                        DataTextField="Category" DataValueField="CategoryId" 
                        SelectedValue='<%# Bind("CategoryId") %>'>
                    </asp:DropDownList>
                    <asp:SqlDataSource ID="sqlCategory" runat="server" 
                        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
                        SelectCommand="SELECT * FROM [Category]"></asp:SqlDataSource>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Menu Name" SortExpression="productName">
                <%--<EditItemTemplate>
                    <asp:TextBox ID="txtProductName" runat="server" Text='<%# Bind("productName") %>'></asp:TextBox>
                </EditItemTemplate>--%>
                <InsertItemTemplate>
                    <asp:TextBox ID="txtProductName" runat="server"></asp:TextBox>
                    <asp:RequiredFieldValidator ID="rfvProductName" runat="server" 
                        ControlToValidate="txtProductName" ErrorMessage="Please insert a menu name"></asp:RequiredFieldValidator>
                </InsertItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("productName") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Menu Image" SortExpression="productImage">
               
                <InsertItemTemplate>
                    <asp:Label ID="lblPicture" runat="server" Text="Menu Image"></asp:Label>
                    <asp:FileUpload ID="FileUpload2" runat="server" />
                </InsertItemTemplate>
                <ItemTemplate>
                     <asp:Label ID="lblPicture" runat="server" Text="Menu Image"></asp:Label>
                    <asp:FileUpload ID="FileUpload2" runat="server" />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Description" SortExpression="Description">
               <%-- <EditItemTemplate>
                    <asp:TextBox ID="txtDescription" runat="server" Text='<%# Bind("Description") %>'></asp:TextBox>
                </EditItemTemplate>--%>
                <InsertItemTemplate>
                    <asp:TextBox ID="txtDescription" runat="server"></asp:TextBox>
                </InsertItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="txtDescription" runat="server" Text='<%# Bind("Description") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Price" SortExpression="Price">
               <%-- <EditItemTemplate>
                    <asp:TextBox ID="txtPrice" runat="server" Text='<%# Bind("Price") %>'></asp:TextBox>
                </EditItemTemplate>--%>
                <InsertItemTemplate>
                    <asp:TextBox ID="txtPrice" runat="server" 
                        Height="16px" Width="71px"></asp:TextBox>
                    <asp:RequiredFieldValidator ID="rfvPrice" runat="server" 
                        ControlToValidate="txtPrice" 
                        ErrorMessage="Please insert a price for your menu item"></asp:RequiredFieldValidator>
                </InsertItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="txtPrice" runat="server" Text='<%# Bind("Price") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Active" SortExpression="Active">
                <ItemTemplate>
                    <asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("Active") %>' 
                        Enabled="false" />
                </ItemTemplate>
                
                <InsertItemTemplate>
                    <asp:CheckBox ID="chkActive" runat="server" Checked='<%# Bind("Active") %>' />
                </InsertItemTemplate>
            </asp:TemplateField>
            <asp:CommandField ShowInsertButton="True"  />
        </Fields>
    </asp:DetailsView>
        <asp:SqlDataSource ID="sqlNewProduct" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
       
        
        
        
      
        InsertCommand="spMenuInsert" InsertCommandType="StoredProcedure" 
        SelectCommand="SELECT CategoryId, productName, productImage, Description, Price, Active FROM Product">
            <InsertParameters>
                <asp:Parameter Name="CategoryId" Type="Int32" ConvertEmptyStringToNull="false"  />
                <asp:Parameter Name="productName" Type="String" ConvertEmptyStringToNull="false" />
                <asp:Parameter Name="productImage" Type="String"  />
                <asp:Parameter Name="Description" Type="String"  />
                <asp:Parameter Name="Price" Type="Int32" ConvertEmptyStringToNull="false" />
                <asp:Parameter Name="Active" Type="Boolean" ConvertEmptyStringToNull="false" />
            </InsertParameters>
    </asp:SqlDataSource>
        <br />

Here is my code behind

protected void dvMenu_ItemInserting(object sender, DetailsViewInsertEventArgs e)
{
    DropDownList ddlCategoryInsert = (DropDownList)dvMenu.FindControl("ddlCategory");
    TextBox txtproductName = (TextBox)dvMenu.FindControl("txtproductName");
    TextBox txtDescription = (TextBox)dvMenu.FindControl("txtDescription");
    TextBox txtPrice = (TextBox)dvMenu.FindControl("txtPrice");
    Label lblPicture = (Label)dvMenu.FindControl("lblPicture");
    FileUpload FileUpload2 = (FileUpload)dvMenu.FindControl("FileUpload2");
    CheckBox chkActive = (CheckBox)dvMenu.FindControl("chkActive");
    
     if (FileUpload2.HasFile)
        {
            if (CheckFileRequirements() == true)
            {
                //insert data into database

                String picUrl = "../Images/MenuImages/" + Path.GetFileNameWithoutExtension(FileUpload2.FileName) + Path.GetExtension(FileUpload2.FileName);
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
                conn.Open();
                SqlCommand cmd = new SqlCommand("MenuInsertion", conn);
                
                cmd.CommandType = CommandType.StoredProcedure;

                try
                {
                    cmd.Parameters.AddWithValue("CategoryId", ddlCategoryInsert.SelectedValue);
                    cmd.Parameters.AddWithValue("productName", txtproductName.Text);
                    cmd.Parameters.AddWithValue("productImage", picUrl);
                    cmd.Parameters.AddWithValue("Description", txtDescription.Text);
                    cmd.Parameters.AddWithValue("Price", txtPrice.Text);
                    cmd.Parameters.AddWithValue("Active", chkActive.Checked);
                    //upload file to path
                    FileUpload2.SaveAs(Server.MapPath(picUrl));

                    cmd.ExecuteNonQuery();
                    conn.Close();
                    lblError.Text = "You have successfully inserted a new menu item";
                    gvMenu.DataBind();
                    gvMenu.Visible = true;

                }

                catch (Exception er)
                {
                    lblError.Text = "An error occurred please try again. " + er.Message;
                }

            }
            
        }

        else
        {

            String picUrl = "";
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand("spMenuInsert", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("CategoryId", ddlCategoryInsert.SelectedValue);
                cmd.Parameters.AddWithValue("productName", txtproductName.Text);
                cmd.Parameters.AddWithValue("productImage", picUrl);
                cmd.Parameters.AddWithValue("Description", txtDescription.Text);
                cmd.Parameters.AddWithValue("Price", txtPrice.Text);
                cmd.Parameters.AddWithValue("Active", chkActive.Checked);

            }
          catch (Exception er)
                {
                    lblError.Text = "An error occurred please try again. " + er.Message;
                }
            
        }
}
ASKER CERTIFIED SOLUTION
Rajar Ahmed
Consultant

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros