Results 1 to 13 of 13

Thread: [RESOLVED] IDENTITY_INSERT using asp.net MVC code

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Oct 2008
    Location
    India, Kerala, Calicut
    Posts
    242

    Resolved [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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: IDENTITY_INSERT using asp.net MVC code

    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.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Oct 2008
    Location
    India, Kerala, Calicut
    Posts
    242

    Re: IDENTITY_INSERT using asp.net MVC code

    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?

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: IDENTITY_INSERT using asp.net MVC 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?

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Oct 2008
    Location
    India, Kerala, Calicut
    Posts
    242

    Re: IDENTITY_INSERT using asp.net MVC code

    sorry..i forget to mention that..

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Oct 2008
    Location
    India, Kerala, Calicut
    Posts
    242

    Re: IDENTITY_INSERT using asp.net MVC code

    and the error is

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

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: IDENTITY_INSERT using asp.net MVC code

    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.

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: IDENTITY_INSERT using asp.net MVC code

    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??

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Oct 2008
    Location
    India, Kerala, Calicut
    Posts
    242

    Re: IDENTITY_INSERT using asp.net MVC code

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

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: IDENTITY_INSERT using asp.net MVC code

    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).

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #11
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: IDENTITY_INSERT using asp.net MVC code

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: IDENTITY_INSERT using asp.net MVC code

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    Oct 2008
    Location
    India, Kerala, Calicut
    Posts
    242

    Re: IDENTITY_INSERT using asp.net MVC code

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width