Identity Insert issue

Transact-SQL
1

The below code is in a VB NET form and worked fully as intended with no issues. Earlier this year we had a server failure and I had to recreate my tables from backups. I am now receiving the following error:

The explicit value for the Identity column in State_Miles can only be specified when a column list is used and IDENTITY_INSERT is turned 'ON'

I double checked my table and the ID column is indeed set as the Primary Key and set as follows:
ID INT NOT NULL AUTO_INCREMENT,

I never had to account for the ID column prior but this seems to be the requirement now. I tried:
Set Identity_Insert State_Miles On; but that did not work for me.

I'm obviously missing something (possibly in the table setup area) but unknown as to what.

Insert into State_Miles
(STATE, MONTH, YEAR, MILES, GALLONS)
values (@v1, @v2, @v3, @v4, @v5,
CONVERT(DateTime, '" & cbYear.Text & "' + '-" & (cbMonth.SelectedIndex + 1) & "' + '-01'))"

                    cmd.Parameters.AddWithValue("@v1", cbState.Text)
                    cmd.Parameters.AddWithValue("@v2", cbMonth.SelectedIndex + 1)
                    cmd.Parameters.AddWithValue("@v3", cbYear.Text)
                    cmd.Parameters.AddWithValue("@v4", tbMiles.Text)
                    cmd.Parameters.AddWithValue("@v5", tbGallons.Text)
2

Insert into State_Miles
(STATE, MONTH, YEAR, MILES, GALLONS)
values (@v1, @v2, @v3, @v4, @v5,
CONVERT(DateTime, '" & cbYear.Text & "' + '-" & (cbMonth.SelectedIndex + 1) & "' + '-01'))"

Insert into 5 columns but you are providing 6 values?

Would highly recommend you use stored procedures instead

1 Like