CODEWITHSUNDEEP
vb.netsql-server-2005
Thanzeem
Thanzeem

Reputation: 133

Error showing while updating database with Identity Column

Please help me... i am trying to update my database from VB.net. It shows error. My code is given below....

Try
            getConnect()

            Dim strSQL As String
            strSQL = " UPDATE DEPARTMENT SET [DEP_ID]=@DEP_ID,[DEPART]=@DEPART,    [DEP_DSCRPTN]=@DEP_DSCRPTN WHERE [DEP_ID] = @DEP_ID"
            Dim cmd As New SqlCommand(strSQL, Conn)
            cmd.Parameters.AddWithValue("@DEP_ID", CInt(Me.DEPID.Text))
            cmd.Parameters.AddWithValue("@DEPART", SqlDbType.VarChar).Value =     CMBDEPT.Text
            cmd.Parameters.AddWithValue("@DEP_DSCRPTN", SqlDbType.VarChar).Value =     TXTDESC.Text
            Conn.Open()
            cmd.ExecuteNonQuery()
            MsgBox("Update Complete!", MsgBoxStyle.Information, "Update")
        Catch ex As Exception
            MsgBox("ERROR: " + ex.Message, MsgBoxStyle.Information, "Update")
        Finally
            Conn.Close()
            BTNCLEAR.PerformClick()
        End Try

And the error is:

ERROR: Cannot update identity column 'DEP_ID'

Upvotes: 0

Views: 579

Answers (2)

Steve
Steve

Reputation: 216313

The error message is clear, remove the Set DEP_ID = @DEP_ID part.

Try
    getConnect()

    Dim strSQL As String
    strSQL = "UPDATE DEPARTMENT SET [DEPART]=@DEPART," + 
             "[DEP_DSCRPTN]=@DEP_DSCRPTN WHERE [DEP_ID] = @DEP_ID"
    Dim cmd As New SqlCommand(strSQL, Conn)
    cmd.Parameters.AddWithValue("@DEP_ID", CInt(Me.DEPID.Text))
    cmd.Parameters.AddWithValue("@DEPART", SqlDbType.VarChar).Value =     CMBDEPT.Text
    cmd.Parameters.AddWithValue("@DEP_DSCRPTN", SqlDbType.VarChar).Value =     TXTDESC.Text
    Conn.Open()
    cmd.ExecuteNonQuery()
    MsgBox("Update Complete!", MsgBoxStyle.Information, "Update")
Catch ex As Exception
    MsgBox("ERROR: " + ex.Message, MsgBoxStyle.Information, "Update")
Finally
    Conn.Close()
    BTNCLEAR.PerformClick()
End Try

If you find yourself in need to change an Identity column then I think you need to analyze better your database schema.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453598

Remove [DEP_ID]=@DEP_ID, from the SET. It makes no sense to try and set it to the value already ensured by the WHERE anyway so it is clearly redundant and it is not permitted to update IDENTITY columns.

UPDATE DEPARTMENT
SET    [DEPART] = @DEPART,
       [DEP_DSCRPTN] = @DEP_DSCRPTN
WHERE  [DEP_ID] = @DEP_ID

Upvotes: 2

Related Questions