How to Grab ID of a recently "inserted item"

rsearing · February 15th, 2007, 11:01 AM

I am wondering--I have a form that adds "shift" information into a table, but I need to add the auto-generated "shiftID" into another relationship table that tracks that shift per the event it relates to. In other words, I have a event table, a shift table, and an event_shift table. An event can have multiple shifts. So, I have a dropdown that captures the eventID--but I don't know (obviously) up front, the ID of the shift..because it hasn't been inserted yet...so...I am trying to write code in the "formview_Iteminserting" method (that is inserting the data into the shift table) to insert the eventID and shiftID into the event_shift table...but I don't know how to access the auto-generated shiftID that is being inserted.

So, I have a method:

Protected Sub FormView2_ItemInserting(ByVal sender As Object, .....)
Dim sh, ev as Integer
ev = dropdownlist1.selectedvalue
sh = ???
eventstruct.InsertShiftToEvent(sh, ev)
End Sub

Where "InsertShiftToEvent" takes the event id (from the drop down list) and the shift ID--that which is created in the "Iteminserting" phase and inserts into a different table to relate the shift to the event.

*****
EDIT
*****

What I am trying to do, simply, is insert into a table:

SHIFT
*****
shiftID = int
description = nvarchar(250)
hour = int
minute = int

The problem is that shiftID is a autogen. key. How can I capture shiftID after I have inserted?

I have the method above, and am trying to retrieve the shiftID so that I can use it to plug into another table.

Imar · February 15th, 2007, 02:43 PM

Hi Rob,

Assuming your business method returns the ID of your object, you can catch the value with e.ReturnValue in the ObjectDataSource's Inserted event.

E.g.

Code:

Public Function InsertShift(WhatEver) As Integer
  ' Whatever you need to do here
  Return NewIdOfShift
End Function

You can then catch this new ID with the following ODS:

Code:

Protected Sub ObjectDataSource1_Inserted( _
  ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceStatusEventArgs) _
  Handles ObjectDataSource1.Inserted

    Dim shiftId As Integer = Convert.ToInt32(e.ReturnValue)

End Sub

Hope this helps,

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

rsearing · February 15th, 2007, 02:57 PM

I try that and I get a 0...however, the row is inserted..??

I didn't think about trying to use the objectdatasource--originally I was using the formview_inserted method.

Imar · February 15th, 2007, 03:01 PM

The ID isn't available until the Inserted event, The Inserting event fires before the item is actually inserted in the data source.

Is your method returning the correct ID? How does the code look like for the method?

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

rsearing · February 15th, 2007, 03:10 PM

Protected Sub KnightDataSource4_Inserted(bla...) Handles KnightsDataSource4.Inserted

Dim sh, ev as Integer
ev = dropdownlist1.selectedvalue
sh = Convert.ToInt32(e.ReturnValue)
eventStruct.InsertShiftToEvent(sh, ev)
End Sub

Note..I debug at the eventstruct.insert.... line. ev = the correct eventID from the dropdown, when I rest the cursor on "sh" it has a value of "0"

-Rob

Imar · February 15th, 2007, 03:14 PM

I meant the code in your business object that inserts the object and returns the value. E.g. the method for the InsertMethod of the ODS.

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

rsearing · February 15th, 2007, 03:19 PM

Sorry:

Public Sub InsertShift(byval description as string, byval hour as integer, byval minute as integer)

Dim insertstring as string = "InsertShift"
Dim knightsDBConn as new sqlconnectionstring(conString)
Dim sqlCmd as new sqlCommand(insertString, knightsDBConn)
sqlCmd.commandtype = commandtype.storedprocedure
sqlcmd.parameters.addwithvalue("@description", description)
sqlcmd.parameters.addwithvalue("@hour", hour)
sqlcmd.parameters.addwithvalue("@minute", minute)
knightsDBConn.open()
sqlCmd.executenonquery()
sqlCmd=Nothing
End Sub

Imar · February 15th, 2007, 03:27 PM

Right, I see.

This is a sub and returns nothing. So, the Inserted event will always see the value of 0.

To make this work, your database needs to insert the new ID of the record you just inserted.

You can change your Stored Procedure for this. At the end of the procedure, you'd return:

RETURN SCOPE_IDENTITY()

and catch that value with a return value parameter in your code. My Instant Results book shows how that works. The InsertUpdateBug method of the BugManager class in the App_Code\DataAccess folder has an exact example of this.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

rsearing · February 15th, 2007, 03:29 PM

Thanks...so, if I understand, correctly, just change my insert sub, to a function and add RETURN SCOPE_IDENTITY()
to the function? Is that what returns the ID of the identity that was just inserted? That was my next question, is how to pull the auto-inserted ID from the DB from within my function.

rsearing · February 15th, 2007, 03:31 PM

AN..in the stored procedure...if I have that in the stored procedure..how do I capture that return from the stored procedure in my function?

Thanks,
Rob