No way to pass identity back to SqlDataSource?

Is there no way to pass identity info back through SqlDataSource? You can only do that with ADO.NET code?

In other words, if I want to run a complex INSERT statement to a table that uses Identity, I can't take that key value back through something like SCOPE_IDENTITY() and use it?

I know how to do this with ADO code, but I can't figure out how to do it purely with SqlDataSource. I was hoping to do this without having to write a new Insert statement -- just using the one that's already in the SqlDataSource control. But there doesn't seem to be any facility for Identity in there. I tried embedding a separate select statement after the insert statement and a semi-colon, but that didn't seem to do anything.

Thanks!

Check out my reply on this post:http://forums.asp.net/thread/1284900.aspx

This is how I successfully got an output parameter back from my stored procedure using the sqldatasource, for instance the Identity after I inserted a record.

Hope this helps.

|||

I appreciate the reply, but I just don't see how that can work without a stored procedure. The problem is that I can't sneak the SCOPE_IDENTITY event into the Insert transaction through SqlDataSource. I can declare it separately in ADO code during the same (SqlDataSource-based) Insertion loop, but SQL Server sees that as a separate transaction and returns a DBNULL -- the scope is different, so no identity gets returned.

I can define the entire Insert event in ADO code, but then that would remove the function from the SqlDataSource, which was a design goal. Or I can dump the whole thing in a Stored Procedure, but that would also violate a design goal. (I realize this is a fairly academic exercise, but I've become curious at this point and really want to find the correct answer.)

In short, I'm just a-boggle that there's no way to return Identity entirely within the confines of SqlDataSource. It seems a fairly obvious thing to include. They obviously want us using this thing for Insertions, and it's a fairly obvious thing to do when inserting, but it does not appear to be allowed.

Surely I must just be missing something.

|||

Does anybody have any further thoughts on this? It doesn't appear there's any way to pass Identity back through SqlDataSource without moving the Insert statement out to ADO code or using a Stored Procedure. SqlDataSource doesn't appear to have any facility to handle the returning parameter.

|||

I did try the following and it works fine:

InsertCommand="INSERT INTO [table]([fieldName]) VALUES(@.value); Select @.myId = @.@.Identity;"

Add a additional parameter to the InsertParameters of the SQLDataSource:

<InsertParameters><asp:Parameter Name="name" Type="type"/><asp:Parameter Direction="output" Name="myId" Size=4 Type=int64/></InsertParameters>

Handle the inserted event of de sqldatasource:

Protected Sub SqlDataSource_Inserted(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SqlDataSource.InsertedDim idAs Integerid = e.Command.Parameters("@.myId").ValueEnd Sub

|||

Thank you so much! I had tried the same thing but couldn't getit to work -- turns out I left out the @. symbol after the semi-colon inthe follow-on select statement within the SqlDataSource. Once Istuck that in there it worked perfectly!

Much obliged!