We help IT Professionals succeed at work.
Get Started

Insertion of Details view results in an error

Ru1995
Ru1995 asked
on
405 Views
Last Modified: 2012-10-03
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 />

Open in new window


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;
                }
            
        }
}

Open in new window

Comment
Watch Question
Consultant
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE