Topic
  • 3 replies
  • Latest Post - ‏2010-01-21T13:19:32Z by DGawron
rk128
rk128
9 Posts

Pinned topic How to retrieve the ID from last insert into DB?

‏2009-10-12T15:55:57Z |
I am trying to retrieve the ID from last DB insert into SQL Server 2005. The following insert statement works fine but I’m not sure how to set up the return parameter in the SQL Call builder. Would it be after all the Input parms and configured as Output? What should the Value or Set/Cast Method be? I’m using automatic Parameter Binding with Create XML Variable. What I’m trying to accomplish is I have an input form which generates new entry in the DB but the user may continue to make additional changes to the data in the form and continue clicking on the save button. So I’d like to capture the primary key from the first insert and use update SQL Call builder afterwards. Also how would my Input Form builder have to change to support this flexibility of using either Insert SQL Call or Update SQL Call?

INSERT INTO growth.dbo.Opportunity
(state
,name
,businessgroup)
VALUES
(?
,?
,?)
SELECT NEWID = SCOPE_IDENTITY()
Updated on 2010-01-21T13:19:32Z at 2010-01-21T13:19:32Z by DGawron
  • DGawron
    DGawron
    251 Posts

    Re: How to retrieve the ID from last insert into DB?

    ‏2009-10-13T15:35:59Z  
    SQL Call sees the statement as a straight INSERT so it will not try to pull back any results including generated keys. I'd recommend using a stored procedure to perform the INSERT. The procedure would take 3 inputs and return a single output, the generated key.
  • cdub
    cdub
    9 Posts

    Re: How to retrieve the ID from last insert into DB?

    ‏2010-01-21T13:06:15Z  
    • DGawron
    • ‏2009-10-13T15:35:59Z
    SQL Call sees the statement as a straight INSERT so it will not try to pull back any results including generated keys. I'd recommend using a stored procedure to perform the INSERT. The procedure would take 3 inputs and return a single output, the generated key.
    Would the stored procedure need to be created on the database side or can it be created in WPF somehow? What does a stored procedure look like if I were to attempt to create one?
  • DGawron
    DGawron
    251 Posts

    Re: How to retrieve the ID from last insert into DB?

    ‏2010-01-21T13:19:32Z  
    • cdub
    • ‏2010-01-21T13:06:15Z
    Would the stored procedure need to be created on the database side or can it be created in WPF somehow? What does a stored procedure look like if I were to attempt to create one?
    You can use SQL Call to create a stored procedure or you can use your DB admin tool for that. Each DB has a unique syntax for defining stored procedures so your best bet is to look at the docs for your specific DB.