[RESOLVED] IDENTITY_INSERT using asp.net MVC code

Can anybody tell me how to set

Code:

---

SET IDENTITY_INSERT tablename ON

---

using asp.net code..plsss

That's SQL code but it's not a query so you execute it the same way you would any other SQL code that isn't a query, i.e. create a command object and call its ExecuteNonQuery method. It's not really anything to do with MVC because it would be done exactly the same way regardless of the type of application.

I have done this way
sqlQueryID = "SET IDENTITY_INSERT tppl ON";
IQuery queryPrimON = session.CreateSQLQuery(sqlQueryID);

using session.CreateSQLQuery but still its throwing the same error.
Should i add anything after these lines to the code?

Um, I have no idea what 'session.CreateSQLQuery' is. I just did a quick web search and it appears to be an NHibernate thing. Are you using NHibernate but you thought that it would be a good idea to keep it a secret? Also, you say that it's still throwing the same error. Did you also think it would be a good idea to keep it a secret what that error is? Do you go to the doctor and make him guess what your symptoms are?

sorry..i forget to mention that..

and the error is

Code:

---

Cannot insert explicit value for identity column in table 'tppl' when IDENTITY_INSERT is set to OFF.

---

I don't use NHibernate but, from what you've told us, there's no indication that you're actually executing the SQL code. I would think that you'd have to do something extra to execute the query you create.

Just to be sure...

You are trying to put your own value into that identity field with the INSERT - right?? In other words - you DO NOT want the SQL engine to determine the next IDENTITY value...

Can you use a STORED PROCEDURE or are you limited to INSERT's with MVC??

Yes am trying to insert my own values. Now am limited with MVC

Well - the error clearly tells you that you are in fact NEVER EXECUTING that SET statement (or you are getting an error in the attempt).

I bet it does execute... but it's isolated in a transaction or something, and so as a result, gets "undone"... which is fine, because identity insert isn't something you normally want off all the time, otherwise what would be the point.... ok... now I got to ask, WHYyou trying to turn it off in the first place? something doesn't feel right. the ONLY, I mean the ONLY time I've ever used it is part of a like a data conversion, and then it's part of a larger script that's being run, and not part of the normal daily operations.
Example:
SET IDENTITY_INSERT tppl ON
-do bunch of data conversion type stuff
SET IDENTITY_INSERT tppl OFF
GO

If tppl is like a temp table that you've created on the fly for data processing, and you know you're going to be inserting the ID value, then it shouldn't have an identity column in the first place.

-tg

To further what TG is saying...

Although it might be an effort to "retrieve" the IDENTITY VALUE used by an INSERT - that is the way you are supposed to do it.

If you are making your own values to get around the effort required to retrieve the value SQL uses - then reconsider what you are doing.

I did some changes in my nhibernate file to save the Primary key

I changed the Generator class to native
<class name="tppl" table="tppl" batch-size="10">
<id name="UserID" column="UserID">
<generator class="native"></generator>

Now its accepting the Primary key values

Thank you all for your time and effort :)