locked
Cannot insert explicit value for identity column in table 'UserDetails' when IDENTITY_INSERT is set to OFF. RRS feed

  • Question

  • User1111434469 posted

    Hi,

    I have error:

    Cannot insert explicit value for identity column in table 'UserDetails' when IDENTITY_INSERT is set to OFF.

    I'm trying to insert a record through a BLL (Business Logic Layer) class with an 'Insert' button attached to the FooterTemplate of a GridView2. My code:

     

                <asp:GridView ID="GridView2" runat="server" AllowPaging="True"
                    AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="idUser" 
                    DataSourceID="SqlDataSource1" EnableViewState="True" ShowFooter="False" 
                    OnRowDataBound="GridView2_RowDataBound" 
                    OnRowCommand="GridView2_OnRowCommand1" >
                    <Columns>
                        <asp:CommandField ShowSelectButton="True" ShowDeleteButton="True" 
                            ShowEditButton="True" />
                        <asp:BoundField DataField="idUser" HeaderText="idUser" ReadOnly="True" 
                            SortExpression="idUser" />
                          <asp:TemplateField HeaderText="Country">
                            <ItemTemplate>
                                <%# Eval("Country")%>
                            </ItemTemplate>                    
                            <EditItemTemplate>
                                <asp:TextBox ID="txtCountry" runat="Server" Text='<%# Bind("Country") %>'>
                                </asp:TextBox>
                            </EditItemTemplate>
                            <FooterTemplate>
                                <asp:TextBox ID="txtCountry" runat="Server">
                                </asp:TextBox>
                            </FooterTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Browser">
                            <ItemTemplate>
                                <%# Eval("Browser")%>
                            </ItemTemplate>                    
                            <EditItemTemplate>
                                <asp:TextBox ID="txtBrowser" runat="Server" Text='<%# Bind("Browser") %>'>
                                </asp:TextBox>
                            </EditItemTemplate>
                            <FooterTemplate>
                                <asp:TextBox ID="txtBrowser" runat="Server">
                                </asp:TextBox>
                            </FooterTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>

     

            protected void AddNewRecord(object sender, EventArgs e) 
            { 
                GridView2.ShowFooter = true; 
            }
    
            protected void GridView2_OnRowCommand1(object sender, GridViewCommandEventArgs e)
            {
                if (e.CommandName.Equals("Insert"))
                {
                    TextBox txtcountry = GridView2.FooterRow.FindControl("txtCountry") as TextBox;
                    TextBox txtbrowser = GridView2.FooterRow.FindControl("txtBrowser") as TextBox;
                    new UserDetailsBLL().AddProduct(txtcountry.Text, txtbrowser.Text);
                }
            }
    
    

    BLL layer code:

     

            [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)]
            public bool AddProduct(string country, string browser)
            {
                // Create a new MyResultsRow instance
                MyResults.UserDetailsDataTable userdetails = new MyResults.UserDetailsDataTable();
                MyResults.UserDetailsRow userdetail = userdetails.NewUserDetailsRow();
    
                userdetail.Country = country;
                userdetail.Browser = browser;
                // Add the new product
                userdetails.AddUserDetailsRow(userdetail);
                int rowsAffected = Adapter.Update(userdetails);
    
                // Return true if precisely one row was inserted, otherwise false
                return rowsAffected == 1;
            }

     

    The field 'idUser' is autoincremented. When I use SqlDataSource with a DetailsView, add option it adds a record and autoincrements the 'isUser'.

     

     

     

    Thursday, March 3, 2011 4:43 AM

Answers

  • User1111434469 posted

    I finally managed to deal with the problem. The method I used is not recommended but still i'll post my solution:

    Ctrl+F, 'Find', Entire Solution - "INSERT INTO [dbo].[UserDetails]" and in xsd typed dataset manually changed (removed) all fields 'idUser' associated with the INSERT command.

    I suppose my xsd schema wasn't refreshed when i changed 'idUser' field to AutoIncrement. I also suppose that in time the xsd schema won't refresh and replace my manual editing.

    I have a question about xsd schema - how to refresh the xsd or the tableadapter when you make a change such as make a field autoincrement?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 3, 2011 10:53 AM

All replies

  • User-1313990200 posted

    Make sure that IDENTITY_INSERT is ON when you are creating the table.

    Thursday, March 3, 2011 5:17 AM
  • User-1011137159 posted

    To Resolve this issue...

    First run this

    SET

    IDENTITY_INSERT <tablename> ON

    AFter then in sql run your query without providing autogenrated ID because that is automatically generate by sql.

    If still this not work then please give me sql query which you are using for insert and also sent me that tables created script.

     

    Please mark as answer if this helps

     

    Thursday, March 3, 2011 5:40 AM
  • User-610330605 posted

    By default IDENTITY_INSERT is set to OFF.

    Therefore you have to turn it ON before trying make an INSERT which includes a value for the IDENTITY column.

    Thursday, March 3, 2011 5:53 AM
  • User1111434469 posted

    How to turn on (run) this query? What steps should i make?

    In 'Server Explorer' when I go to my table and click 'Open Table Definition' and I point to 'idUser' field, on the properties below i have:

    Idenitity Specification = Yes

    (Is Identity) = Yes

    Identity Increment = 1

    Identity Seed = 1

    I tested inserting a record with DetailsView control / SqlDataSource and the field 'idUser' is successfully incremented, the record is successfully inserted.

    More info about the error:

    Line 4186:        [global::System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")]
    Line 4187:        public virtual int Update(FlightResults.UserDetailsDataTable dataTable) {
    Line 4188:            return this.Adapter.Update(dataTable);
    Line 4189:        }
    Line 4190:       
    Source File: C:\Inetpub\wwwroot\Views1\CodeClasses\DAL\MyResults.Designer.cs    Line: 4188

    Thursday, March 3, 2011 6:09 AM
  • User-1313990200 posted

    Open management studio and open your table design then run this statement.

    Thursday, March 3, 2011 7:54 AM
  • User1111434469 posted

    This database is not in the MS SQL Management Studio, it's only in a local App_Data folder. How to enable this IDENTITY_INSERT from Visual Studio 2008?

    In Server Explorer, when I right click with mouse on the table and choose 'New Query' and i write code:

    set INDENTITY_INSERT UserDetails ON
    insert into X(idUser) VALUES(111)

    I get error: The Set SQL construct or statement is not supported.

     

    Thursday, March 3, 2011 9:51 AM
  • User1111434469 posted

    I finally managed to deal with the problem. The method I used is not recommended but still i'll post my solution:

    Ctrl+F, 'Find', Entire Solution - "INSERT INTO [dbo].[UserDetails]" and in xsd typed dataset manually changed (removed) all fields 'idUser' associated with the INSERT command.

    I suppose my xsd schema wasn't refreshed when i changed 'idUser' field to AutoIncrement. I also suppose that in time the xsd schema won't refresh and replace my manual editing.

    I have a question about xsd schema - how to refresh the xsd or the tableadapter when you make a change such as make a field autoincrement?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 3, 2011 10:53 AM