Cannot INSERT data into table with identity column

95 pts.
Tags:
INSERT statement
SQL Server 2005
SQL Server 2005 Express Edition
VB.NET 2005
In my program I have an EmployeeRegistry table which holds data of each employee's work attendance. Every attendance record is uniquely distinguished by an IDENTITY COLUMN called "AttendanceID" which has an IDENTITY SEED of 1 and an IDENTITY increment of 1. However when I try to insert an attendance record to the table via my VB.NET program. it gives an error saying:

"An explicit value for the identity column in table 'EmployeeRegistry' can only be specified when a column list is used and IDENTITY_INSERT is ON." I tried to insert data into the table using an SQL query via the SQL management studio and it inserted the data without a problem. What might be wrong?

Here's the coding:



Public Sub SaveRecord()       Dim objSys As New SpringsSystem       Dim objCommand As New SqlCommand()       Dim objReader As SqlDataReader         Try              objSys.Connect()              objCommand.Connection = objSys.objConnection              objCommand.CommandText = "SELECT AttendanceID FROM   EmployeeRegistry WHERE AttendanceID = '" & Me.AttendanceNumber & "'"              objReader = objCommand.ExecuteReader()

             If objReader.HasRows = False Then                     objReader.Close()                     objCommand.CommandText = "INSERT INTO  EmployeeRegistry VALUES('" & Me.EmployeeID & "', '" & Me.AttendanceDate & "', '" & Me.WorkArrivalTime & "', '" & Me.WorkLeaveTime & "', '" & Me.ShortLeaveTime & "', '" & Me.ReturnTime & "', Null)"                     objCommand.ExecuteNonQuery()

             Else                     objReader.Close()                     objCommand.CommandText = "UPDATE EmployeeRegistry  SET EmployeeID = '" & Me.EmployeeID & "', AttendanceDate = '" & Me.AttendanceDate & "', WorkArrivalTime = '" & Me.WorkArrivalTime & "', WorkLeaveTime = '" & Me.WorkLeaveTime & "', " & _  "ShortLeaveTime = '" & Me.ShortLeaveTime & "', ReturnTime = '" & Me.ReturnTime & "', ExpenseID = Null WHERE AttendanceID = '" & Me.AttendanceNumber & "'"                     objCommand.ExecuteNonQuery()              End If

      Catch ex As Exception              MessageBox.Show(ex.Message, "Error saving Attendance Data Recrod", MessageBoxButtons.OK, MessageBoxIcon.Error)

      Finally              objSys.objConnection.Close()              objSys = Nothing              objCommand.Dispose()              objReader = Nothing       End Try End Sub




Software/Hardware used:
Visual Basic .NET 2005, SQL Server 2005 Express edition.

Answer Wiki

Thanks. We'll let you know when a new response is added.

You should specify the column names in the insert statement. The SQL Server thinks that you are attempting to insert the employee ID into the AttendanceID column. Simply put the column names as a comma seperated list before the values like this.

<pre>INSERT INTO YourTAble
(Column1, Column2, Column3)
VALUES
(‘Value1′, ‘Value2′, ‘Value3′)</pre>

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Dotnet9vs
    even i have this problem i entered Column names and values as mentioned above in my sp still iam not able to insert .... and i abserved that this thing occurs only when my table is new and waiting for first record to insert but when one record is inserted then no problem occurs it goes on just like that is there any solution for this ????
    10 pointsBadges:
    report
  • carlosdl
    Dotnet9vs, can you post your code and your table's structure ?
    69,920 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following