Error - IDENTITY_INSERT is set to OFF

[rickjamesb]

I've been spending the entire day writing sql code, stored procedures, and a pretty big visual basic program so that i could insert typed in user values from vb into rows of a sql table. I could insert rows into other tables, but the table i needed to use would never work. I thought there was a problem with my vb or sql code, and almost gave up.

So i went back to square one, and tried the very basic sql code to insert a row into a table:

INSERT INTO Employees (EmployeeID, LastName, FirstName)  
VALUES (20, 'Jordan', 'Nick')

And i get this error:
Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Employees' when IDENTITY_INSERT is set to OFF.

Whats the fix to this? EmployeeID is an incremental value, is this the root of the problem? Later on in my project, the EmployeeID is supposed to be a computer generated random number, i can't even get my own input to work! How can i assign it to be random, when its supposed to be incremental!

 

[gmmastros]

Here's my suggestion.

Add another column called EmployeeNumber. Let this be the random number. Keep Id's hidden from the user. It will only confuse them. Trust me. Then, when you make other tables that link to the employee table, you use the EmployeeID (not the employee number).

Identity columns are good. You just need to know how to work with them.

When inserting a record in to a table that has an identity column, you don't insert the identity value. But, you can retrieve the value that was just entered. Here's how.

Create Procedure InsertEmployee
  @EmployeeNumber Integer,
  @LastName VarChar(50),
  @FirstName VarChar(50)
AS
SET NOCOUNT ON

INSERT INTO Employees (EmployeeNumber, LastName, FirstName)  
VALUES (@MployeeNumber, @LastName, @FirstName)

Select Scope_Identity() As EmployeeId

When calling this stored procedure from vb, you would call it the same way you would when you expect a recordset to come back from the database. In this case, there will be 1 record with 1 field (named employeeId). The value of that field will be the newly inserted EmployeeId.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rickjamesb]

Thanks that makes sense. You answer very fast, your good

Instead of a random number, what if EmployeeID was supposed to be an "autonumber". How does one insert rows into a table when one of the columns is supposted to be an autonumber?

 

[gmmastros]

In that case, the user wouldn't be choosing the autonumber field (the database would). In the example I show above, just remove the EmployeeNumber stuff.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom