@@IDENTITY of recently added record using SqlDataSource | MS Acccess ASP.NET VB

[Pipo Dyer]

Hello Everybody,
I am Using @@IDENTITY from MS Access (YES-It is possible) using SqlDataSource -NO STORE PROC- in ASP.NET {VB CODE} to retrieve the ID of a inserted record.

Please keep in mind I am using a form page an code behing, and I have currently my ConnectionString within my form. Therefore http://www.mikesdotnetting.com/Article.aspx?ArticleID=54 was close but not exactly what I need at this point.

So far I have done the following (Without positive results):
<asp:SqlDataSource ID="AddProductDataSource" runat="server"
ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=C:\~WEBSITES\insurance\App_Data\Insurance.m db"
InsertCommand="INSERT INTO Leads (firstname, lastname) VALUES (@firstname, @lastname); SET @ID = @@IDENTITY"
ProviderName="System.Data.OleDb">

<InsertParameters>
<asp:ControlParameter ControlID="txt_firstName" Name="firstname" PropertyName="Text" />
<asp:ControlParameter ControlID="txt_lastName" Name="lastname" PropertyName="Text" />
<asp:Parameter Direction="Output" Name="ID" Size="100" Type="Double" />
</InsertParameters>
</asp:SqlDataSource>


In addition, I have in my code behind:

Protected Sub AddProductDataSource_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEv entArgs) Handles AddProductDataSource.Selecting
'Read the value of the @Identity OUTPUT parameter
Dim sID
sID = e.Command.Parameters("@ID").Value.ToString()
'Display(New ID)
Response.Write("ID:" + sID)
End Sub


After some suggestions I changed my code a little as follows:

Protected Sub AddProductDataSource_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles AddProductDataSource.Inserting

However now that I have the following code, I get a different error message:
Protected Sub AddProductDataSource_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEven tArgs) Handles AddProductDataSource.Inserting
Dim query As String = "SELECT @@IDENTITY"
Dim cmd As New System.Data.OleDb.OleDbCommand(query, e.Command.Connection)
Dim newid As Integer = cmd.ExecuteScalar()
Response.Write("ID:" + newid.ToString())
End Sub

But I get an error message in line
Dim newid As Integer = cmd.ExecuteScalar()


I have also changed SqlDataSourceCommandEventArgs for SqlDataSourceStatusEventArgs, but still not working...

Thanks in advance for your suggestions.
PD

[Pipo Dyer]

Found the answer:

Protected Sub AddProductDataSource_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs) Handles AddProductDataSource.Inserted
Dim query As String = "SELECT @@IDENTITY"
Dim cmd As New System.Data.OleDb.OleDbCommand(query, e.Command.Connection)
'Dim newid As Integer = e.Command.Parameters("@NewId").Value
Dim newid As Integer = cmd.ExecuteScalar()
Response.Write("ID:" + newid.ToString())
End Sub