-1

I'm trying to insert data into fingerprint table which has four columns - ID (primary key) - StudentID (foreign key) linked to student table - description - Template

But the following error keeps coming up. I can't turn off the IDENTITY for ID as I want it to increment automatically. I also have a student table to store information. What I want to achieve is that after entering student details, I want to copy the studentID that was generated before onto the fingerprint table - StudentID column. the code I have for this is shown below. 

private void btnSave_Click(object sender, EventArgs e)
        {
            fgrTemplate template = new fgrTemplate();
            template.StudentID = std.StudentID;
            template.Description = fngDes.Text;
            template.Template = m_StoredTemplate;
            if (upload.InsertTemplate(template))
            {
                MessageBox.Show("Student Successfully Added!");
            }
            else
            {
                MessageBox.Show("Student Not Successfully Added!");
            }

using (SqlCommand com = new SqlCommand("INSERT INTO tblFingerprint (StudentID, Description, Template) values ('" + template.StudentID + "' ,'" + template.Description + "' ,@Template)", cn))

this is what I have on my web service. However it gives me the error

user3144368
  • 1
  • 1
  • 3
  • Don't add the ID column as part of the Insert. `Insert Table (strcolumn1, strcolumn2) values (1,2)` Table(id int, strcolumn1 varchar(1), strcolumn2 varchar(2)) – Jaques Jan 31 '14 at 13:46
  • You are passing `ID` value in your INSERT statement, `ID` being an identity type should get an auto increment number. See the [linked question](http://stackoverflow.com/questions/1334012/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity) and also post your related code in the question. – Habib Jan 31 '14 at 13:47

1 Answers1

4

When issuing an INSERT statement for a table with an IDENTITY column, don't include the identity column in the fields and values list. For example, the following statement would fail:

INSERT INTO tbl (ID, StudentID) VALUES (1, 1)

but this statement will succeed:

INSERT INTO tbl (StudentID) VALUES (1)

and insert the next available identity value into the ID column.

Now, if you do want to insert an actual identity, do this:

SET IDENTITY_INSERT tbl ON
INSERT INTO tbl (ID, StudentID) VALUES (1, 1)
SET IDENTITY_INSERT tbl OFF
Mike Perrenoud
  • 63,395
  • 23
  • 143
  • 222