none
Anyway to get the key from ExecuteNonQuery()? RRS feed

  • Question

  •  

    Hello,

     

    I have a database table which looks like this.

     

    Customers

    |

    |

     ------ ID (KEY, Autoincrement)

     ------ FirstName

     ------ LastName

     ------ Address

     ------ City

     ------ State

     ------ ZipCode

     ------ PhoneNumber

     

    The ID is used as the customer number. When I run an ExecuteNonQuery to add a new record for a new customer I would like the application to receive back the ID which was assigned to the customer. I have some post-processing on the account that needs to be run against other tables which reference the ID. At this time I have been running the ExecuteNonQuery then generating a SqlDataReader and passing all the same field data to get the ID. I was wondering if there was an easier way to do this. It just seems tedious to have to go back to the database and recall all the fields a second time for the ID. Any help is appreciated.

     

    Thanks.

     

    Monday, December 10, 2007 3:03 PM

Answers

  •  Quilnux wrote:

    First, thanks for taking the time to respond to my post.

     

    Yeah it runs Microsoft SQL Server 2005. When you say "same connection session", your talking about not closing the connection after ExecuteNonQuery and running a second query with the same connection object, right?

    Correct.

    I will start researching this using the information you provided to see the if I can get the syntax. I will post back once I find it. If someone else finds/know it first then that will help.

    I did a quick google search for SELECT IDENTITY and found this, which should help. Or do a similar search and find other articles.

    http://www.sqlteam.com/article/alternatives-to-identity-in-sql-server-2000

    And yes, I am trying to avoid the issue of returning the wrong value. luckily we haven't had this problem yet (there are not many of us pinging the database but things are planning to change soon) but I know it will happen eventually. i want to avoid that now so I don't have to worry about it later.

    Using @@IDENTITY or the related flavors like the SCOPE_IDENTITY() SQL function are thread safe by design.

    Thanks,

     

    BTW, Have a happy holiday!

    Same to you!
    Monday, December 10, 2007 4:15 PM

All replies

  • It's database dependent. If this is SQL Server, then you just need to execute another simple query on the same connection session to get the last IDENTITY value used (which is safe from returning an incorrect value if for instance another thread/process executed another insert statement inbetween). I don't remember the exact syntax of the query so this is just mainly a hint for right now to give you a nudge in the right direction. Maybe I'll post a follow-up later.

     

    Another alternative is to create a stored procedure to perform the insert and have it return the key back to you. The implementation of the stored procedure would be very similar to what you could do in code:

    1) execute the insert statement

    2) execute the query to get the last identity value used, and return this value

     

    If this is a different kind of database, then the technique may be a bit different. For instance, in Oracle databases, you would need to first execute a query to give you the next SEQUENCE value for a table (which reserves that value so it is protected from multi-threaded / multi-process scenarios), and then you would use the returned value in your insert statement to set the key.

     

    Monday, December 10, 2007 3:13 PM
  • First, thanks for taking the time to respond to my post.

     

    Yeah it runs Microsoft SQL Server 2005. When you say "same connection session", your talking about not closing the connection after ExecuteNonQuery and running a second query with the same connection object, right?

     

    I will start researching this using the information you provided to see the if I can get the syntax. I will post back once I find it. If someone else finds/know it first then that will help.

     

    And yes, I am trying to avoid the issue of returning the wrong value. luckily we haven't had this problem yet (there are not many of us pinging the database but things are planning to change soon) but I know it will happen eventually. i want to avoid that now so I don't have to worry about it later.

     

    Thanks,

     

    BTW, Have a happy holiday!

     

    Monday, December 10, 2007 3:40 PM
  •  Quilnux wrote:

    First, thanks for taking the time to respond to my post.

     

    Yeah it runs Microsoft SQL Server 2005. When you say "same connection session", your talking about not closing the connection after ExecuteNonQuery and running a second query with the same connection object, right?

    Correct.

    I will start researching this using the information you provided to see the if I can get the syntax. I will post back once I find it. If someone else finds/know it first then that will help.

    I did a quick google search for SELECT IDENTITY and found this, which should help. Or do a similar search and find other articles.

    http://www.sqlteam.com/article/alternatives-to-identity-in-sql-server-2000

    And yes, I am trying to avoid the issue of returning the wrong value. luckily we haven't had this problem yet (there are not many of us pinging the database but things are planning to change soon) but I know it will happen eventually. i want to avoid that now so I don't have to worry about it later.

    Using @@IDENTITY or the related flavors like the SCOPE_IDENTITY() SQL function are thread safe by design.

    Thanks,

     

    BTW, Have a happy holiday!

    Same to you!
    Monday, December 10, 2007 4:15 PM
  • Thanks for the reply. The information was very useful!

     

    Have a happy holiday!

    Tuesday, December 11, 2007 1:28 PM