Problem with Identity field when inserting

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with Identity field when inserting

Author

Topic

zbibliophile
Starting Member

2 Posts

Posted - 2010-09-19 : 22:53:15

An Access database was converted to SQL Server 2005 Express using the DTS utility. The old AutoNumber fields were automatically converted to int identity not null fields. However, when attempting to INSERT a row, such as the following ASP code,

Conn.Execute("INSERT INTO tblAdmin3 (fldA3Username,fldA3TimeIn,fldA3Date) VALUES ('" & var5 & "','" & Time() & "','" & Date() & "')")

the error

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'fldA3Num', table 'mhdsg.dbo.tblAdmin3'; column does not allow nulls. INSERT fails.
/mhds/login.asp, line 113

is received. The database works properly except when adding new rows. The SQL Server Management Studio Express program shows that the column properties for fldA3Num are:
Data Type = int
System Type = int
Primary key = true
Allow nulls = false
Identity = true
Identity seed = 1
Identity increment = 1
etc. I must be overlooking something simple, but I can't see it. This is my first SQL project. Any ideas?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-09-19 : 23:32:03

Not fully sure but i feel that Identity insert property may be on.
Try it setting to off by using

Syntax:
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

Set Identity_Insert <yourtablename> off

zbibliophile
Starting Member

2 Posts

Posted - 2010-09-20 : 01:35:40

Thanks for the quick response pk_bohra. I placed the line:

Conn.Execute("Set Identity_Insert tblAdmin3 off")

immediately before the Conn.Execute("INSERT INTO ...") line. Got the error

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Table 'tblAdmin3' does not have the identity property. Cannot perform SET operation.
/mhds/login.asp, line 113

Goggle tells me that this error message appears when you try to use the SET IDENTITY_INSERT setting for a table that does not contain a column, for which the IDENTITY property was declared. However, the SQL Server Management Studio Express program says that the fldA3Num column is set as Identity. So am still puzzled.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources