Weird problem.

Discussion in 'ASP .Net' started by Tom P., Sep 19, 2008.

  1. Tom P.

    Tom P. Guest

    I have a webpage that takes user data and sends it to a stored
    procedure that inserts a row in a table then returns the ID of that
    row for display.

    The stored procedure does an insert then it uses IDENT_CURRENT to get
    the identity of the row that was just inserted and selects the row
    back out to display.

    The dataset is not getting populated. The dataset has a DataTable that
    gets created and the DataTable has the columns named correctly
    according to the final select but there's no data in the table.

    I've tried SET NOCOUNT ON, I've tried READ UNCOMMITTED, I've tried
    COMMITing after the insert...

    I've tried just limiting the data to return the ID and I'll do another
    select later but I can't even get the ID out.

    I just don't get it.

    If anyone has any ideas please let me know. If there's anything else
    you need to know just ask.

    Tom P.
     
    Tom P., Sep 19, 2008
    #1
    1. Advertising

  2. Well,
    obviously (or perhaps not so) there is something wrong with your code. Also,
    I'm not familiar with "IDENT_CURRENT" - @@IDENTITY will do this for SQL
    Server. Do you want to post a short-but-complete code sample so that you can
    get some help?
    Peter

    "Tom P." <> wrote in message
    news:...
    >I have a webpage that takes user data and sends it to a stored
    > procedure that inserts a row in a table then returns the ID of that
    > row for display.
    >
    > The stored procedure does an insert then it uses IDENT_CURRENT to get
    > the identity of the row that was just inserted and selects the row
    > back out to display.
    >
    > The dataset is not getting populated. The dataset has a DataTable that
    > gets created and the DataTable has the columns named correctly
    > according to the final select but there's no data in the table.
    >
    > I've tried SET NOCOUNT ON, I've tried READ UNCOMMITTED, I've tried
    > COMMITing after the insert...
    >
    > I've tried just limiting the data to return the ID and I'll do another
    > select later but I can't even get the ID out.
    >
    > I just don't get it.
    >
    > If anyone has any ideas please let me know. If there's anything else
    > you need to know just ask.
    >
    > Tom P.
     
    Peter Bromberg [C# MVP], Sep 21, 2008
    #2
    1. Advertising

  3. Tom P.

    leeeyre

    Joined:
    Sep 21, 2008
    Messages:
    2
    Location:
    Northamptonshire
    Apparently the IDENT_CURRENT returns the last identity created by the server for any connection, bit pointless really, so you should use the @@IDENTITY.

    If you post the SP I will take a look for you.
     
    leeeyre, Sep 21, 2008
    #3
  4. Tom P.

    Tom P. Guest

    On Sep 21, 2:57 am, "Mark Rae [MVP]" <> wrote:
    > "Tom P." <> wrote in message
    >
    > news:...
    >
    > > I have a webpage that takes user data and sends it to a stored
    > > procedure that inserts a row in a table then returns the ID of that
    > > row for display.

    >
    > > The stored procedure does an insert then it uses IDENT_CURRENT to get
    > > the identity of the row that was just inserted and selects the row
    > > back out to display.

    >
    > And there's your problem. Use SELECT @@IDENTITY or, even better, SELECT
    > SCOPE_IDENTITY()...
    >
    > http://msdn.microsoft.com/en-us/library/ms190315(SQL.90).aspx
    >
    > --
    > Mark Rae
    > ASP.NET MVPhttp://www.markrae.net


    Except I don't want just some random IDENTITY value I want the last
    IDENTITY value that was inserted into this specific table. And what
    would that have to do with the data being returned?

    In any case this is the stroed Proc that is executing but not
    returning rows:

    CREATE PROC [IMAGING\PadillaH].[spCreateAcquisition]
    @FileUploadName varchar (50),
    @AcquisitionID int
    AS

    SET NOCOUNT ON
    --SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    -- Variable Declarations
    DECLARE @InternalAcquisitionID int;

    --BEGIN TRAN

    -- Insert initial values into table
    INSERT INTO
    Acquisition(
    AcquisitionID,
    [Filename],
    AcquisitionStatusCode
    )
    VALUES
    (
    @AcquisitionID,
    @FileUploadName,
    'U' );

    --COMMIT

    -- Get the Identity value that was last used in this table
    SET @InternalAcquisitionID = IDENT_CURRENT('Acquisition');

    --Select the information back out for the entire row
    SELECT
    InternalAcquisitionID,
    AcquisitionID,
    ServicerID,
    '',
    FileUploadDate,
    TotalLoanCount,
    LoansAssigned,
    TotalLoanCount - LoansAssigned AS LoansUnassigned,
    TotalIndexes,
    UnmatchedIndexes,
    TotalIndexes - UnmatchedIndexes AS MatchedIndexes,
    TotalProductCodes,
    UnmatchedProductCodes,
    TotalProductCodes - UnmatchedProductCodes AS MatchedProductCodes,
    LastProcessedTime,
    Acquisition.AcquisitionStatusCode,
    [Description],
    [Filename]
    FROM
    Acquisition
    INNER JOIN AcquisitionStatusCode
    ON Acquisition.AcquisitionStatusCode =
    AcquisitionStatusCode.AcquisitionStatusCode
    WHERE
    InternalAcquisitionID = @InternalAcquisitionID


    The insert happens, I've verified that. The select layout gets back to
    the code, I've verified that. But there is no data row that gets back
    to the code. The SQLServer is on a different box than the code is
    running on. I have a page before this that is getting rows back from
    this table so it CAN happen.

    I've tried setting dirty reads (it's commented out above). I've tried
    setting transactions and then commit (also commented out). I can't get
    the data to come back from this procedure.

    Tom P.
     
    Tom P., Sep 22, 2008
    #4
  5. Tom P.

    Tom P. Guest

    On Sep 22, 7:22 am, "Mark Rae [MVP]" <> wrote:
    > "Tom P." <> wrote in message
    >
    > news:...
    >
    > >>> The stored procedure does an insert then it uses IDENT_CURRENT to get
    > >>> the identity of the row that was just inserted and selects the row
    > >>> back out to display.

    >
    > >> And there's your problem. Use SELECT @@IDENTITY or, even better, SELECT
    > >> SCOPE_IDENTITY()...

    >
    > >>http://msdn.microsoft.com/en-us/library/ms190315(SQL.90).aspx

    >
    > > Except I don't want just some random IDENTITY value I want the last
    > > IDENTITY value that was inserted into this specific table.

    >
    > In which case, you're using the wrong function - read the docs...
    >
    > > SET @InternalAcquisitionID = IDENT_CURRENT('Acquisition');

    >
    > SET @InternalAcquisitionID = SCOPE_IDENTITY();
    >
    > --
    > Mark Rae
    > ASP.NET MVPhttp://www.markrae.net


    OK, someone's gonna have to explain that to me.

    First off, it worked, thanks. (Dear God, thanks) But all that did is
    confuse me even more.

    IDENT_CURRENT was returning the correct identity. This is only now in
    DEV so there are no others hitting the table to get confused with.
    Besides, even if it wasn't returning the correct identity it should
    have returned incorrect data, not nothing at all.

    Does SCOPE_IDENTITY imply something about the transaction? Is that
    what was happening? IDENT_CURRENT was abandonig the transaction or
    something? But the identity it returned was correct... I've read both
    articles and I don't get the difference. Please, if you could explain
    better than MS I'd appreciate it.

    And thanks again for the persistence in helping me.

    Tom P.
     
    Tom P., Sep 22, 2008
    #5
  6. Tom P.

    SAL Guest

    The docs for IDENT_CURRENT:
    Returns the last identity value generated for a specified table or view in
    any session and any scope

    That sounds like trouble to me but then, who am I right?

    The docs say this for @@SCOPE_IDENTITY():
    Returns the last identity value inserted into an identity column in the same
    scope. A scope is a module: a stored procedure, trigger, function, or batch.
    Therefore, two statements are in the same scope if they are in the same
    stored procedure, function, or batch

    Wow, sounds much safer to me...

    S


    "Tom P." <> wrote in message
    news:...
    On Sep 22, 7:22 am, "Mark Rae [MVP]" <> wrote:
    > "Tom P." <> wrote in message
    >
    > news:...
    >
    > >>> The stored procedure does an insert then it uses IDENT_CURRENT to get
    > >>> the identity of the row that was just inserted and selects the row
    > >>> back out to display.

    >
    > >> And there's your problem. Use SELECT @@IDENTITY or, even better, SELECT
    > >> SCOPE_IDENTITY()...

    >
    > >>http://msdn.microsoft.com/en-us/library/ms190315(SQL.90).aspx

    >
    > > Except I don't want just some random IDENTITY value I want the last
    > > IDENTITY value that was inserted into this specific table.

    >
    > In which case, you're using the wrong function - read the docs...
    >
    > > SET @InternalAcquisitionID = IDENT_CURRENT('Acquisition');

    >
    > SET @InternalAcquisitionID = SCOPE_IDENTITY();
    >
    > --
    > Mark Rae
    > ASP.NET MVPhttp://www.markrae.net


    OK, someone's gonna have to explain that to me.

    First off, it worked, thanks. (Dear God, thanks) But all that did is
    confuse me even more.

    IDENT_CURRENT was returning the correct identity. This is only now in
    DEV so there are no others hitting the table to get confused with.
    Besides, even if it wasn't returning the correct identity it should
    have returned incorrect data, not nothing at all.

    Does SCOPE_IDENTITY imply something about the transaction? Is that
    what was happening? IDENT_CURRENT was abandonig the transaction or
    something? But the identity it returned was correct... I've read both
    articles and I don't get the difference. Please, if you could explain
    better than MS I'd appreciate it.

    And thanks again for the persistence in helping me.

    Tom P.
     
    SAL, Sep 26, 2008
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. dorayme
    Replies:
    1
    Views:
    630
    richard
    Jan 21, 2011
  2. richard
    Replies:
    0
    Views:
    592
    richard
    Jan 21, 2011
  3. richard
    Replies:
    0
    Views:
    621
    richard
    Jan 21, 2011
  4. Beauregard T. Shagnasty

    Re: A Weird Appearance for a Weird Site

    Beauregard T. Shagnasty, Jan 21, 2011, in forum: HTML
    Replies:
    1
    Views:
    446
    Captain Paralytic
    Jan 21, 2011
  5. will
    Replies:
    6
    Views:
    425
    Phrogz
    Dec 27, 2006
Loading...

Share This Page