locked
using ObjectDataSource with Dataset, retreive last inserted ID RRS feed

  • Question

  • User2068700433 posted

    I've done a lot of searching on this here but I can't seem to find anyone who has done this. I'm sure it's a common problem but I can't figure out the right keywords to search for the solution so I'm sorry if this is a duplicate.

    I have a dataset where I've used the Generate Insert, Update, and Delete statements as well as the Refresh the Data Table options so that I can retrieve the last inserted ID when I'm using an ObjectDatasource.

    however, when I'm handling the ObjectDataSource-Inserted method, I'm getting a 1 as a return value, which I assume is the affected rows. I don't want that, I want the ID so that I can use it in my code.

    I looked at the generated code for the insert method and it is doing a SELECT after inserting, but it returns the whole row not the ID, so how can I get at this row to retrieve the value?

    Sunday, January 23, 2011 6:56 PM

Answers

  • User-2139489267 posted

    however, when I'm handling the ObjectDataSource-Inserted method, I'm getting a 1 as a return value, which I assume is the affected rows. I don't want that, I want the ID so that I can use it in my code.

    http://forums.asp.net/t/1325405.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 24, 2011 6:37 AM

All replies

  • User-693248168 posted

    Hi,


    Can you show us the code that you are using to implement the functionality? Please post your datasource code and complete code behind.

    Monday, January 24, 2011 6:31 AM
  • User-2139489267 posted

    however, when I'm handling the ObjectDataSource-Inserted method, I'm getting a 1 as a return value, which I assume is the affected rows. I don't want that, I want the ID so that I can use it in my code.

    http://forums.asp.net/t/1325405.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 24, 2011 6:37 AM
  • User2068700433 posted

    thanks for the replies. I ended up solving this myself by adding a new custom insert query to the dataset that does the same thing as the default insert but adds the scope_identity call afterwards.

    This is also the solution suggested by the link above, and it does work.

    However I find it RIDICULOUS. why doesn't this work by default? I thought that was the whole POINT of the "Refresh Table" option, as it retrieves the last inserted item by default, so why in the heck doesn't the objectdatasource have access to this item? 

    Monday, January 24, 2011 10:59 AM
  • User-2139489267 posted

    thanks for the replies. I ended up solving this myself by adding a new custom insert query to the dataset that does the same thing as the default insert but adds the scope_identity call afterwards.

    This is also the solution suggested by the link above, and it does work.

    Mark the thread as resolved, and if post had helped you then mark the post as an answer :)

    Monday, January 24, 2011 12:47 PM
  • User2068700433 posted

    thanks, done and done...

    however I'd still like to know more about why this doesn't work as expected. I thought the whole point of objectdatasource and dataasets was to do this stuff for you, and this is such a common scenario that I can't image it's something that was ommitted, but rather that I'm doing something wrong...

    any ideas? 

    Monday, January 24, 2011 1:03 PM
  • User-2139489267 posted

    however I'd still like to know more about why this doesn't work as expected

    As it's by design, it will never give you the recently added record Id, either you use ObjectDatasource/SQLDataSource or code for that. You've to do it explicitly.

    Monday, January 24, 2011 11:25 PM