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

    Question

  •  

    Hello.. i m a new with asp.net so please help me.

    My Stored procedure is working properly but when i m trying to insert the data in the form at that time the error occurs "Cannot insert explicit value for identity column in table 'Qualification1_mast' when IDENTITY_INSERT is set to OFF."

    • Stored Procedure:
    ALTER PROCEDURE [dbo].[insert_qualification]
    (
        @Qualification_id numeric(5,0)=0,
        @Qualification_name varchar(30)=null,
        @Description varchar(100)=null
    )
    AS
    INSERT INTO Qualification1_mast
    (
        Qualification_id,
        Qualification_name,
        Description
    )
    VALUES
    (
        @Qualification_id,
        @Qualification_name,
        @Description
    )
    • C# code:
    using System;
    using System.Collections;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;

    public partial class City_Master : System.Web.UI.Page
    {
        QualificationBean obj = new QualificationBean();
        DBHelper db = new DBHelper();
        DataTable dt = new DataTable();
        DataRow dr;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                if (Request.QueryString.ToString() != "")
                {
                    getdata(Request.QueryString.ToString());
                }
            }
        }


        protected void btnSave_Click(object sender, EventArgs e)
        {
            bool status;

            if (Request.QueryString.ToString() != "")
            {
                string id = Request.QueryString.ToString();
                string[] arInfo = new string[4];

                char[] spliter = { '?' };

                arInfo = id.Split(spliter);

                int id1 = 0;
                for (int x = 0; x < arInfo.Length; x++)
                {
                    id1 = Convert.ToInt32(arInfo[x]);
                }

                obj.Qualification_id = id1;
                obj.Qualification_name = txtQname.Text;
                obj.Description = txtDesc.Text;

                status = obj.UpdateData();
            }
            else
            {
                obj.Qualification_name = txtQname.Text;
                obj.Description = txtDesc.Text;

                status = obj.InsertData();
            }

            txtQname.Text = "";
            txtDesc.Text = "";

            Response.Redirect("Qualification_Grid.aspx");
        }

        void getdata(string id)
        {
            dt = db.GetTable("Select * from Qualification1_mast where Qualification_id='" + Request.QueryString.ToString() + "'");
            dr = dt.Rows[0];

            txtQname.Text = dr["Qualification_name"].ToString();
            txtDesc.Text = dr["Description"].ToString();
        }
    }
    • ASP.NET Code:
    <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
       
        <td>
        <table width="100%" border="0" cellpadding="0" cellspacing="0" id="middle">
          <tr>
            <td align="left" valign="top" style="padding-left:40px;" class="style4">
            <table border="0" cellspacing="0" cellpadding="0" style="width: 61%">
              <tr>
                <td align="left" valign="middle" class="red-title"> <span class="____-link"><img src="images/clear.gif" width="6" height="10" /></span>My
                    Home Page &gt; Home </td>
                <td height="50" align="left" valign="middle"><img src="images/icon.jpg" alt="Icon" width="287" height="31" /></td>
              </tr>
              <tr>
                <td colspan="2" align="left" valign="top" class="mid-bg">
                <table border="0" cellspacing="10" cellpadding="10"
                        style="height: 246px; width: 100%;">
                 <tr>
                      <td align="left" valign="top"
                          style="vertical-align: middle; text-align: center" class="style6" >
               <table border="0" cellpadding="5" cellspacing="0"
               style="border: thin solid #E1E1E1; width: 100%; clip: rect(1px, 1px, 1px, 1px); height: 296px;"
               rules="all">
       
                <tbody><tr style="height: 25px;">
       
                <td class="style3" align="right" style="background-color: #EAEAEA; color: #FF0000;">
                    *<asp:Label ID="lblQname" runat="server" Font-Bold="True" Text="Qualification:  "
                        ForeColor="#666666"></asp:Label>
                    </td>
                <td class="style1" align="left" style="background-color: #FDFDFD">
                    &nbsp;&nbsp;
                    <asp:TextBox ID="txtQname" runat="server" style="margin-bottom: 0px"></asp:TextBox>
                </td>
                </tr>
                                                                        <tr style="height: 25px;">
       
                <td class="style3" align="right" style="background-color: #EAEAEA; color: #FF0000;">
                    <asp:Label ID="lblDesc" runat="server" Font-Bold="True" ForeColor="#666666"
                        Text="Description:  "></asp:Label>
                    </td>
                <td class="style1" align="left" style="background-color: #FDFDFD">
                    &nbsp;&nbsp;
                    <asp:TextBox ID="txtDesc" runat="server"></asp:TextBox>
                </td>
                </tr>
                    </tbody></table></td>
                    </tr>
                   
                    </table></td>
              </tr>
            </table>
            <table>
            <tr>
            <td>
                <asp:Button ID="btnSave" runat="server" Text="Save" TabIndex="18"
                    Font-Bold="True" ForeColor="#666666" onclick="btnSave_Click"
                    style="height: 26px" />
            </td>
            <td>
                &nbsp;
             <asp:Button ID="btnCancel" runat="server" Text="Cancel" TabIndex="19"
                    Font-Bold="True" ForeColor="#666666" />
            </td>
            </tr>
            </table>
            </td>

            <td width="15%" align="left" valign="top">&nbsp;</td>
          </tr>
          <tr>
            <td align="left" valign="top" style="padding-left:40px;" class="style4">&nbsp;</td>
            <td align="left" valign="top">&nbsp;</td>
          </tr>
        </table>
        </td>
    </tr>
    </asp:Content>
    • Moved by Bob BeaucheminMVP, Editor Thursday, May 06, 2010 2:03 PM Moved to a more relevent forum (From:.NET Framework inside SQL Server)
    Thursday, May 06, 2010 7:09 AM

Answers

  • Hello Neha,

    If you have a column with IDENTITY the id are generate automatically in a sequence and in common you don't (shouldn't) insert the value explict. If you want to insert a value, you have to set the flag Indentity_Insert to ON. But take care, if you insert a id higher then the next to be generated automatically it will crash when the id comes up to that value => unique key violation.

    SET

     

    IDENTITY_INSERT ON


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    • Marked as answer by KJian_ Thursday, May 13, 2010 2:23 AM
    Thursday, May 06, 2010 7:20 AM