locked
Asp.Net Stored procedure error RRS feed

  • Question

  • Alter PROCEDURE Feedback
    @FeedbackID int,
    @Feedback Varchar(200),
    @UserID Varchar(50)
    AS
    Begin
    insert into Feedback values (@FeedbackID,@Feedback,@UserID);
    End

    When I save this Stored procedure I get an error "An explict value for the identity column in table 'Feedback' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    please help me in solving this error.

    Sunday, March 2, 2014 7:18 PM

Answers

  • "An explict value for the identity column in table 'Feedback' can only be specified when a column list is used and IDENTITY_INSERT is ON.


    As the error message already say, one of the columns (I guess FeedbackID) is of type "IDENTITY" and you can't insert a specific value, instead an auto generated ID will be used; see IDENTITY

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Naomi N Sunday, March 2, 2014 7:39 PM
    • Marked as answer by Elvis Long Tuesday, March 11, 2014 10:06 AM
    Sunday, March 2, 2014 7:22 PM
  • SInce FeedBackID is identity column the value will get generated automatically upon insert so you can ignore it in your insert statement

    You can make stored procedure like below

    Alter PROCEDURE FeedbackInsert
    @Feedback Varchar(200),
    @UserID Varchar(50),
    @FeedbackID int OUTPUT=0
    AS
    Begin
    insert into Feedback (Feedback,UserID)
    values  (@Feedback,@UserID)
    
    SET @FeedBackID = SCOPE_IDENTITY()
    End
    
    then call it like below
    
    DECLARE @NewFeedbackID int,@UserID varchar(50),@Feedback varchar(200)
    
    SELECT @UserID=<your userid value>,
    @Feedback=<your feedback value>
    
    EXEC FeedbackInsert @Feedback,@UserID,@NewFeedbackID OUT
    
    SELECT @NewFeedbackID

    this will give you back generated id value which you may use for any of further operations

    also see

    http://www.sqlteam.com/article/stored-procedures-returning-data


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    • Edited by Visakh16MVP Sunday, March 2, 2014 7:42 PM
    • Proposed as answer by Elvis Long Friday, March 7, 2014 6:28 AM
    • Marked as answer by Elvis Long Tuesday, March 11, 2014 10:06 AM
    Sunday, March 2, 2014 7:41 PM

All replies

  • "An explict value for the identity column in table 'Feedback' can only be specified when a column list is used and IDENTITY_INSERT is ON.


    As the error message already say, one of the columns (I guess FeedbackID) is of type "IDENTITY" and you can't insert a specific value, instead an auto generated ID will be used; see IDENTITY

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Naomi N Sunday, March 2, 2014 7:39 PM
    • Marked as answer by Elvis Long Tuesday, March 11, 2014 10:06 AM
    Sunday, March 2, 2014 7:22 PM
  • Hi rafip, change the code to below...

    insert into Feedback(feedbackcolname, useridcolumnname) values (@Feedback,@UserID);

    Since you already have a identity defined on feedbackid, you don't need to enter it.. A quick question... This is a simple insert statement.. Why are you using a Stored Procedure instead of just the insert...

    Please mark as answer, if this has helped you solve the issue.

    Good Luck :) .. visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

    Sunday, March 2, 2014 7:34 PM
  • SInce FeedBackID is identity column the value will get generated automatically upon insert so you can ignore it in your insert statement

    You can make stored procedure like below

    Alter PROCEDURE FeedbackInsert
    @Feedback Varchar(200),
    @UserID Varchar(50),
    @FeedbackID int OUTPUT=0
    AS
    Begin
    insert into Feedback (Feedback,UserID)
    values  (@Feedback,@UserID)
    
    SET @FeedBackID = SCOPE_IDENTITY()
    End
    
    then call it like below
    
    DECLARE @NewFeedbackID int,@UserID varchar(50),@Feedback varchar(200)
    
    SELECT @UserID=<your userid value>,
    @Feedback=<your feedback value>
    
    EXEC FeedbackInsert @Feedback,@UserID,@NewFeedbackID OUT
    
    SELECT @NewFeedbackID

    this will give you back generated id value which you may use for any of further operations

    also see

    http://www.sqlteam.com/article/stored-procedures-returning-data


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    • Edited by Visakh16MVP Sunday, March 2, 2014 7:42 PM
    • Proposed as answer by Elvis Long Friday, March 7, 2014 6:28 AM
    • Marked as answer by Elvis Long Tuesday, March 11, 2014 10:06 AM
    Sunday, March 2, 2014 7:41 PM