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

Question

I am getting the error above saying that I cannot explicitly define the identity column without doing these things however, I am not trying to set the value of the identity column. I am merely trying to execute a basic insert statement using user input. I'm really stuck and need to get past this. Thoughts?

 Public Sub ExecuteSQL(ByVal sql As String)

    Using myConnection As New Data.SqlClient.SqlConnection(connString)
        myConnection.Open()
        Using cdmDatabaseCommand As New Data.SqlClient.SqlCommand(sql, myConnection)
            cdmDatabaseCommand.ExecuteNonQuery()
        End Using
    End Using
End Sub

  Dim insertSpray As String = ""
  insertSpray = "INSERT INTO Spray VALUES ('" & sprayDate & "','" & timeStart & "','" & timeFinish & "','" & tankVolumeStart & "','" & tankVolumeFinish & "','" & comment & "','" & vehicleId & "','" & vehicleTime & "','" & siteType & "','" & area & "','" & applicatorOneId & "','" & applicatorTwoId & "','" & sprayLocationFeature & "','" & sprayStartLocationDescription & "','NULL','NULL','NULL','NULL','" & sprayEndLocationDescription & "');"

  ExecuteSQL(insertSpray)

Well, you did not specify which columns and in which order you want to insert those values, so `sprayDate ` is likely trying to squeeze in to Identity column. You should also use SQL Parameters. Everything is not string/text is it? — Ňɏssa Pøngjǣrdenlarp, Jul 21 '16 at 20:22
This is very bad practice not to include `insert_list` into the `insert` statement. Any change in the table schema would crash the app. — Alex Kudryashev, Jul 21 '16 at 20:52
How will this code work if your `comment` variable contains an apostrophe? (Hint: It won't). — Chris Dunaway, Jul 22 '16 at 13:34

score 2 · Accepted Answer · edited May 23 '17 at 12:14

2

You're not specifying a column-list, so SQL Server assumes you want to insert into all columns, in table-order.

You need to change your syntax to specify the names of the columns you're inserting into (and leave out the identity column, obviously):

insert into Spray (SprayDate, TimeStart, ...) 
values (...

Also note that your code is vulnerable to SQL injection attacks. You should look into using parameterized queries.

edited May 23 '17 at 12:14

Community

1
1

answered Jul 21 '16 at 20:23

Blorgbeard

101,031
48
228
272

Thank you for the quick answer. I'm not sure why I didn't think of doing that. Also thank you for the warning about SQL injection. I'm looking into parameterized queries now. – MtheBass Jul 21 '16 at 20:45

score 0 · Answer 2 · edited Jul 21 '16 at 21:15

0

Try to explicitly make the columns of table Spray without the identity column.

Something like this:

insertSpray = "INSERT INTO Spray(Column1, Column2,...) VALUES (Value1,Value2,..)"

edited Jul 21 '16 at 21:15

buczek

2,011
7
29
40

answered Jul 21 '16 at 20:26

Sergio Camillo Jr

1

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

2 Answers2