@@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 ConnectionStrin g within my form. Therefore http://www.mikesdotnet ting.com/Article.aspx?Ar ticleID=54 was close but not exactly what I need at this point.

So far I have done the following (Without positive results):
<asp:SqlDataSou rce ID="AddProductD ataSource" runat="server"
ConnectionStrin g="Provider=Mic rosoft.Jet.OLED B.4.0;Data Source=C:\~WEBS ITES\insurance\ App_Data\Insura nce.mdb"
InsertCommand=" INSERT INTO Leads (firstname, lastname) VALUES (@firstname, @lastname); SET @ID = @@IDENTITY"
ProviderName="S ystem.Data.OleD b">

<InsertParamete rs>
<asp:ControlPar ameter ControlID="txt_ firstName" Name="firstname " PropertyName="T ext" />
<asp:ControlPar ameter ControlID="txt_ lastName" Name="lastname" PropertyName="T ext" />
<asp:Paramete r Direction="Outp ut" Name="ID" Size="100" Type="Double" />
</InsertParameter s>
</asp:SqlDataSour ce>


In addition, I have in my code behind:

Protected Sub AddProductDataS ource_Inserted( ByVal sender As Object, ByVal e As System.Web.UI.W ebControls.SqlD ataSourceSelect ingEventArgs) Handles AddProductDataS ource.Selecting
'Read the value of the @Identity OUTPUT parameter
Dim sID
sID = e.Command.Param eters("@ID").Va lue.ToString()
'Display(New ID)
Response.Write( "ID:" + sID)
End Sub


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

Protected Sub AddProductDataS ource_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.W ebControls.SqlDataSourceCo mmandEventArgs) Handles AddProductDataS ource.Inserting

However now that I have the following code, I get a different error message:
Protected Sub AddProductDataS ource_Inserting (ByVal sender As Object, ByVal e As System.Web.UI.W ebControls.SqlD ataSourceComman dEventArgs) Handles AddProductDataS ource.Inserting
Dim query As String = "SELECT @@IDENTITY"
Dim cmd As New System.Data.Ole Db.OleDbCommand (query, e.Command.Conne ction)
Dim newid As Integer = cmd.ExecuteScal ar()
Response.Write( "ID:" + newid.ToString( ))
End Sub

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


I have also changed SqlDataSourceCo mmandEventArgs for SqlDataSourceSt atusEventArgs, but still not working...

Thanks in advance for your suggestions.
PD

[Pipo Dyer]

Found the answer:

Protected Sub AddProductDataS ource_Inserted( ByVal sender As Object, ByVal e As SqlDataSourceSt atusEventArgs) Handles AddProductDataS ource.Inserted
Dim query As String = "SELECT @@IDENTITY"
Dim cmd As New System.Data.Ole Db.OleDbCommand (query, e.Command.Conne ction)
'Dim newid As Integer = e.Command.Param eters("@NewId") .Value
Dim newid As Integer = cmd.ExecuteScal ar()
Response.Write( "ID:" + newid.ToString( ))
End Sub