Identity and SqlDataSource Question

I'm trying to update my e-commerce approach from ADO-heavy code to a more modern approach based on the SqlData Source. I also want to move away from Stored Procedures for the moment, if I can, though I may return to them later (mainly for educational purposes at the moment).

In the past I used @.@.Identity in a stored procedure to return an ID# which I passed on to the end user as their "order number". Unfortunately this approach seems to only apply to SPs.

In short, what's the best way to handle this using SqlDataSource and minimal ADO code?

Just to add a bit more detail, I've got basically three tables, in a fairly obvious relationship. Orders stores the main order info (Customer's name and address, total, etc), OrderDetails lists the line items, and Products contains detail on the items.

(Put another way, the auto-generated tags in the SqlDataSource object either don't include the OrderID param because it's toggled for Identity in the database, or if I toggle Identity off then I don't know how to trigger it to toggle the next number in sequence. And either way I don't know how to feed that information back to the program.)

Thanks!

Well no sooner than I posted the above than I found one way to do it, which is using the SQL function Ident_Current, which sends back the last used Identity number in the table, like this:

Select Ident_Current('Orders')

But it requires writing several lines of ADO code, e.g.:

Dim conGetInvNum As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MyDB").ConnectionString)
Dim cmdInvoiceNumber As New System.Data.SqlClient.SqlCommand("Select Ident_Current('Orders')", conGetInvNum)
conGetInvNum.Open()
OrderNumber = cmdInvoiceNumber.ExecuteScalar
conGetInvNum.Close()

Which is not exactly laborious, but it does mean writing ADO code. Is there an easier, more 2.0-oriented/SqlDataSource-oriented way to do this?

|||

I think that is dangerous to use in a multi-user application.

From the SQL Documentation:

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
|||

I appreciate the reply; that greatly clarified things.

Unfortunately that would remove the Insert handling from the SqlDataSource. I couldn't see a way to do that within the purvue of the DS. I tried this (note bolded text:

<asp:SqlDataSource ID="DSOrders" runat="server" ConnectionString="<%$ ConnectionStrings:SimpsonsDB %>" DeleteCommand="DELETE FROM [Orders] WHERE [OrderID] = @.OrderID"InsertCommand="INSERT INTO [Orders] ([UserID], [Total], [OrderDate]) VALUES (@.UserID, @.Total, {fn NOW()}); SELECT SCOPE_IDENTITY();" SelectCommand="SELECT [OrderID], [UserID], [Total], [OrderDate] FROM [Orders]" UpdateCommand="UPDATE [Orders] SET [UserID] = @.UserID, [Total] = @.Total, [OrderDate] = @.OrderDate WHERE [OrderID] = @.OrderID">
<DeleteParameters>
<asp:Parameter Name="OrderID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="UserID" Type="String" />
<asp:Parameter Name="Total" Type="Decimal" />
<asp:Parameter Name="OrderDate" Type="DateTime" />
<asp:Parameter Name="OrderID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="UserID" Type="String" />
<asp:Parameter Name="Total" Type="Decimal" />
<asp:Parameter Name="OrderDate" Type="DateTime" />
</InsertParameters>
</asp:SqlDataSource>

And then launched it with this:

OrderNumber = DSOrders.Insert()

I realize that's not an "ExecuteScalar" but that Method isn't available for the DS, and unsurprisingly I got an error that suggested that it wasn't running the command. This suggests to me that this approach just isn't accomodated. (In other words, there's no way to return a value from an Insert command using SqlDataSource.) But that still seems unlikely to me -- it's a fairly obvious thing for them to have included. So I must be missing something.

Any further thoughts would be appreciated.