none
Update record failure using "ID" (C#) Visual Studio 2013 RRS feed

  • Question

  • I'm trying to get my application to update when I click on the button but I keep getting errors regarding the ID which has datatype int

    The error I get is "Cannot update identity column 'Id'"

    Here is my code:

    string Query = "update Patient set Id='" + this.Id_txt.Text + "', PatientFirstName='" + this.patientfirstname_txt.Text + "', PatientLastName='" + this.patientlastname_txt.Text + "', GuardianName='" + this.guardianname_txt.Text + "', DateOfBirth='" + this.dob_txt.Text + "', [Address Line 1]='" + this.address1_txt.Text + "', [Address Line 2]='" + this.address2_txt.Text + "', County='" + this.county_txt.Text + "', Postcode='" + this.postcode_txt.Text + "', Notes='" + this.notes_txt.Text + "' where Id='" + this.Id_txt.Text + "' ;";

    Saturday, August 25, 2018 12:45 PM

Answers

  • You will receive better advice if you ask in the appropriate language forum.
    Saturday, August 25, 2018 4:34 PM
  • This is actually a SQL Server question, even though you expressed it in C#.

    Since you asked in the Training and Certification forum, I'll tell you that the course that teaches this kind of Skills is SQL Course 20461 or its successor.

    Anyway, the explanation to your problem, inferred from the error message, is that the Id column is declared as IDENTITY in your table schema. An Identity column should normally not be updated; instead, you keep the ID fixed and you change the rest of the fields in the Row. In fact, given your "Update" query, you are indeed keeping Id fixed, because the value you use in the Set is the same that you have un the Where. So the remedy is to just omit the Set for Id:

    string Query = "update Patient set PatientFirstName='" + this.patientfirstname_txt.Text + "', PatientLastName='" + this.patientlastname_txt.Text + "', GuardianName='" + this.guardianname_txt.Text + "', DateOfBirth='" + this.dob_txt.Text + "', [Address Line 1]='" + this.address1_txt.Text + "', [Address Line 2]='" + this.address2_txt.Text + "', County='" + this.county_txt.Text + "', Postcode='" + this.postcode_txt.Text + "', Notes='" + this.notes_txt.Text + "' where Id='" + this.Id_txt.Text + "' ;";

    If you ever really need to change an Identity value in a table, you can do it by first setting the option "set identity_insert... ON". But this should occur in very rare cases, and this particular statement is not one of them.

    A piece of warning about your query: It appears that you are concatenating user input into a SQL statement. You should avoid doing this. It has several drawbacks, the most severe of which is allowing SQL injection attacks against your code. The remedy is to parameterize the query instead of building it by concatenation. But this is a different subject and is unrelated with the error that you are seeing.


    Sunday, August 26, 2018 2:53 PM

All replies

  • You will receive better advice if you ask in the appropriate language forum.
    Saturday, August 25, 2018 4:34 PM
  • This is actually a SQL Server question, even though you expressed it in C#.

    Since you asked in the Training and Certification forum, I'll tell you that the course that teaches this kind of Skills is SQL Course 20461 or its successor.

    Anyway, the explanation to your problem, inferred from the error message, is that the Id column is declared as IDENTITY in your table schema. An Identity column should normally not be updated; instead, you keep the ID fixed and you change the rest of the fields in the Row. In fact, given your "Update" query, you are indeed keeping Id fixed, because the value you use in the Set is the same that you have un the Where. So the remedy is to just omit the Set for Id:

    string Query = "update Patient set PatientFirstName='" + this.patientfirstname_txt.Text + "', PatientLastName='" + this.patientlastname_txt.Text + "', GuardianName='" + this.guardianname_txt.Text + "', DateOfBirth='" + this.dob_txt.Text + "', [Address Line 1]='" + this.address1_txt.Text + "', [Address Line 2]='" + this.address2_txt.Text + "', County='" + this.county_txt.Text + "', Postcode='" + this.postcode_txt.Text + "', Notes='" + this.notes_txt.Text + "' where Id='" + this.Id_txt.Text + "' ;";

    If you ever really need to change an Identity value in a table, you can do it by first setting the option "set identity_insert... ON". But this should occur in very rare cases, and this particular statement is not one of them.

    A piece of warning about your query: It appears that you are concatenating user input into a SQL statement. You should avoid doing this. It has several drawbacks, the most severe of which is allowing SQL injection attacks against your code. The remedy is to parameterize the query instead of building it by concatenation. But this is a different subject and is unrelated with the error that you are seeing.


    Sunday, August 26, 2018 2:53 PM