MtheBass MtheBass - 1 year ago 111 Question

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."

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)
Using cdmDatabaseCommand As New Data.SqlClient.SqlCommand(sql, myConnection)
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 & "');"


Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download