Author |
Topic |
sparkadelic
Starting Member
5 Posts |
Posted - 2004-12-16 : 16:33:40
|
I'm trying to insert data into a table but unsuccessful because I get this message - An SQLException occurred: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot insert explicit value for identity column in table 'EmpNames' when IDENTITY_INSERT is set to OFF.As you can see I'm using JDBC. Does anybody know how to turn the IDENTITY_INSERT to ON?Or do you think it's a JDBC problem? |
|
jhermiz
3564 Posts |
Posted - 2004-12-16 : 16:41:37
|
Check that field make sure that the identity is set to yes (you can do this in enterprise manager). |
|
|
sparkadelic
Starting Member
5 Posts |
Posted - 2004-12-16 : 16:43:31
|
quote: Originally posted by jhermiz Check that field make sure that the identity is set to yes (you can do this in enterprise manager).
I checked before I posted the problem but I can't find anything. Can you tell me where to look in Enterprise Manager? |
|
|
jhermiz
3564 Posts |
Posted - 2004-12-16 : 16:46:15
|
Right click your table goto design view. Click on the row of that field..look at the bottom where the properties are..there is a "IDENTITY" property. Set it to yes. |
|
|
sparkadelic
Starting Member
5 Posts |
Posted - 2004-12-16 : 16:58:09
|
Well I tried but it's greyed out. What can I do now? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-16 : 17:05:51
|
Well, let's first start with the basic stuff. Do you even want the IDENTITY property turned on? Do you want SQL Server to automatically generate values for your ID column? If you do, then that Identity option in EM must be set to Yes and then you must NOT send values to it on inserts from your application. So you exclude that column in your column list.Tara |
|
|
sparkadelic
Starting Member
5 Posts |
Posted - 2004-12-16 : 17:12:10
|
Well yes I want it turned on if I can get rid of the error that's listed. It's complaining about the the IDENTITY_INSERT so I would like to turn it on but it's grayed out. Also the ID is set to yes but the other column is not. |
|
|
sparkadelic
Starting Member
5 Posts |
Posted - 2004-12-16 : 17:18:06
|
Ok it's working now. I had to use Integer.parseInt in the java code. |
|
|
jhermiz
3564 Posts |
Posted - 2004-12-16 : 17:19:03
|
Be careful with parseInt, since the data type doesnt quite correspond with bigints in sql.What is this fields type ? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-16 : 17:19:23
|
Identity set to Yes is what you want. Now the problem is with your application. Are you doing your queries through stored procedures or embedded t-sql? Please post the queries. You need to exclude your ID column from the query so that SQL Server handles the data for it. Also, post the name of the ID column unless of course if it's just ID which isn't a very good name if that's what you have chosen, best to use TableName_ID or something like that.Tara |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-16 : 17:20:17
|
So what does this Integer.parseInt do? Doesn't sound like it excludes a column.Tara |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2004-12-16 : 17:38:27
|
It appears you have a table with an IDENTITY column and you're trying to INSERT an explicit value into that column. Usually when you have an IDENTITY column you don't insert that value and SQL Server supplies the next value in sequence. You can SELECT back that value by doing a SELECT SCOPE_IDENTITY() right after the INSERT.If you truly do want to INSERT an explicit value into an IDENTITY column do it like this.SET IDENTITY_INSERT EmpNames ON... INSERT statement here ...SET IDENTITY_INSERT EmpNames OFFAfter you've INSERTed an explicit value into an identity column you'll probably need to correct the see as docemented here: http://www.sqlteam.com/item.asp?ItemID=8003.I usually only insert explicit values into an identity when I'm importing data. If you're doing during your regular processing I'd reconsider whether you really want an identity column.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|