none
Primary key not refreshing after to the Select after an Update RRS feed

  • Question

  •  I have read numerous posts and thought I was doing this as described but obviously I am missing something.

    I have table where XrefID is the PK. I have the following insert commend in a tableadapter:

    INSERT INTO xrefPricingProducts
               (projID, prodTypeID, APSiteID, percentDelivery, Amount, Margin, Price, percentOps, opsAmount, opsMargin, opsPrice, opsStaticAmount, trainingCost, 
               trainingMargin, trainingPrice, trainingPercent, xrefSiteID)
    VALUES   (@projID,@prodTypeID,@APSiteID,@percentDelivery,@Amount,@Margin,@Price,@percentOps,@opsAmount,@opsMargin,@opsPrice,@opsStaticAmount,@trainingCost,@trainingMargin,@trainingPrice,@trainingPercent, @xrefSiteID);
    
    SELECT  tblProductType.ProductType, xrefPricingProducts.projID, xrefPricingProducts.APSiteID, xrefPricingProducts.prodTypeID, 
               xrefPricingProducts.trainingPercent, xrefPricingProducts.trainingPrice, xrefPricingProducts.trainingMargin, xrefPricingProducts.trainingCost, 
               xrefPricingProducts.opsStaticAmount, xrefPricingProducts.opsPrice, xrefPricingProducts.opsMargin, xrefPricingProducts.opsAmount, 
               xrefPricingProducts.percentOps, xrefPricingProducts.Price, xrefPricingProducts.Margin, xrefPricingProducts.Amount, 
               xrefPricingProducts.percentDelivery, xrefPricingProducts.xrefID
    FROM     xrefPricingProducts INNER JOIN
               tblProductType ON xrefPricingProducts.prodTypeID = tblProductType.ProdTypeID
    WHERE xrefPricingProducts.xrefID = SCOPE_IDENTITY()
    

    Later in the code I use the following statement to execute the insert:

    ppt.InsertProducts(.projID, .prodTypeID, .APSiteID, .percentDelivery, .Amount, .Margin, .Price, .percentOps, .opsAmount, .opsMargin, .opsPrice, _
                      .opsStaticAmount, .trainingCost, .trainingMargin, .trainingPrice, .trainingPercent, APSite.xrefID)
    

    I would expect that after the insert I should be able to look at the xrefID of the inserted record and see the identity of the inserted row but it is always the autoincremented value assigned locally (autoincrement defined in the datatable).

    I'm just trying to mimic the Refresh function that can be selected when creating a tableadapter. Can this be done with a custom insert command?

    Thanks,

    Rick

    Thursday, March 24, 2011 12:28 AM

Answers

  • Alan,

    I'm afraid the suggestion is not what I was looking for. I can indeed get the newly inserted record's id using the @scope and return that as part of the insert call. But what I wanted to do was implement the refresh of the record. When you create a tableadapter using the dataset designer, if the Select is from a single table, the tableadapter will create a Select statement on Inserts and Updates to "select back" newly inserted records. This way you can get the record with the new id.

    I was tring to duplicate what I saw happening in the tableadapter and make it happen myself. I've seen a lot of posts asking the same thing and everybody just points to getting the id by itself. I ended up doing it and inserting the new id into the local record myself.

    Thanks,

    Rick

    Tuesday, March 29, 2011 3:10 PM

All replies

  • Hi Rick,

    Welcome.

    You can use Store Procedure to return the identity value, and you can configure TableAdapter, here is a link about it:

    http://forums.asp.net/t/990365.aspx/1?Get+identity+after+insert+using+default+using+Text+Sql+Commands

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, March 25, 2011 3:31 AM
    Moderator
  • Hi Rick,

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 
     
    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, March 29, 2011 2:41 PM
    Moderator
  • Alan,

    I'm afraid the suggestion is not what I was looking for. I can indeed get the newly inserted record's id using the @scope and return that as part of the insert call. But what I wanted to do was implement the refresh of the record. When you create a tableadapter using the dataset designer, if the Select is from a single table, the tableadapter will create a Select statement on Inserts and Updates to "select back" newly inserted records. This way you can get the record with the new id.

    I was tring to duplicate what I saw happening in the tableadapter and make it happen myself. I've seen a lot of posts asking the same thing and everybody just points to getting the id by itself. I ended up doing it and inserting the new id into the local record myself.

    Thanks,

    Rick

    Tuesday, March 29, 2011 3:10 PM
  • Hi Rick,

    Welcome back!

    Oh, My bad link! I think it is hard to use custom command, I'm glad to hear you have got a way to work.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 30, 2011 5:59 AM
    Moderator