Link to home
Start Free TrialLog in
Avatar of Ru1995
Ru1995Flag for United States of America

asked on

Insertion of Details view results in an error

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

Avatar of rajeeshmca
rajeeshmca
Flag of India image

Hi,

just keep a breakpoint in the "dvMenu_ItemInserting" event and check whether all values (Parameters) are  getting passed correctly.


If everything is ok, then just execute the procedure "spMenuInsert" from the backend (Sql) by passing the parameters directly like

spMenuInsert 1, 'Prodname' 'ImageBin'................

and see if there is any issue. if any then u will need to correct it in the Procedure.

Regards
Rajeesh
ASKER CERTIFIED SOLUTION
Avatar of Rajar Ahmed
Rajar Ahmed
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ru1995

ASKER

I tried both suggestions and it didn't work the items keep getting inserted in the database and everything seems to work correctly but for some reason it seems to want to go back and maybe reinsert the item
Avatar of Ru1995

ASKER

Not sure if this is correct solution just finishing up other projects