ASP.net Scope_Identity () equivalent for mysql, LAST_INSERT_ID()

I've looked all over for this answer and had to resort to several resources.  I wanted to post this here because it was so hard for me to find a simple example.  If it exist other places then please excuse my post and put references to it on a reply.

So, How do I return the Primary Key Identity when I click the control with the 'Insert' command on a FormView? (With very least minimal code)  get the ID - the primary key - of the newly inserted row when adding a

new record to the database using a FormView Control on a web page

With SQL Server we do it like this : (Add new insertparameter ID with direction output)

              <asp:SqlDataSource ID="SqlDataSource1" runat="server" 

            ConnectionString="<%$ ConnectionStrings:entertrackConnectionString %>" 

             ProviderName="<%$ ConnectionStrings:entertrackConnectionString.ProviderName %>" 

            InsertCommand="INSERT INTO training_ppm ( descr, organizer, dt_st, dt_end, venue, residential, fee , detail, dt_last, uploadby, file, approver ) values ( @descr, @organizer , @dt_st, @dt_end, @venue, @residential, @fee , @detail, @dt_last, @uploadby, @file, @approver) ; SELECT @ID = Scope_Identity()"

            >

         <InsertParameters>

                <asp:Parameter Name="pid" Type="Int32" />

                <asp:Parameter Name="descr" Type="String" />

                <asp:Parameter Name="organizer" Type="String" />

                <asp:Parameter Name="dt_st" Type="DateTime" />

                <asp:Parameter Name="dt_end" Type="DateTime" />

                <asp:Parameter Name="venue" Type="String" />

                <asp:Parameter Name="residential" Type="String" />

                <asp:Parameter Name="fee" Type="String" />

                <asp:Parameter Name="detail" Type="String" />

                <asp:Parameter Name="dt_last" Type="DateTime" />

                 <asp:Parameter Name="uploadby" Type="String" />

                 <asp:Parameter Name="file" Type="String" />

                <asp:Parameter Name="approver" Type="String" />

             <asp:Parameter Name="ID" Direction="Output" Type="Int32" />

            </InsertParameters>

        </asp:SqlDataSource>

Code Behind:

Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted

        Dim cmd As System.Data.Common.DbCommand = e.Command

        Dim strID As String

        SqlDataSource1.InsertParameters("ID").DefaultValue = cmd.Parameters("@ID").Value.ToString()

        strID = SqlDataSource1.InsertParameters("ID").DefaultValue

        ifdisplay.Attributes("src") = "http://191.254.1.42/cmg/upload.php?folder=training&fileid=" & strID

        ifdisplay.Visible = True

        ModalPopupExtender1.Show()

    End Sub

The Mysql equivalent would be like this (using LAST_INSERT_ID()) : No need to add new insertparameter instead use executescalar in code behind:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 

            ConnectionString="<%$ ConnectionStrings:entertrackConnectionString %>" 

             ProviderName="<%$ ConnectionStrings:entertrackConnectionString.ProviderName %>" 

           InsertCommand="INSERT INTO training_ppm ( descr, organizer, dt_st, dt_end, venue, residential, fee , detail, dt_last, uploadby, file, approver ) values ( @descr, @organizer , @dt_st, @dt_end, @venue, @residential, @fee , @detail, @dt_last, @uploadby, @file, @approver) ; SELECT LAST_INSERT_ID()"

                     >

Code Behind:

Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted

        Dim cmd As System.Data.Common.DbCommand = e.Command

      Dim strID As String

        strID = cmd.ExecuteScalar 

        ifdisplay.Attributes("src") = "http://191.254.1.42/cmg/upload.php?folder=training&fileid=" & strID

        ifdisplay.Visible = True

        ModalPopupExtender1.Show()

    End Sub

Vinod Kotiya

www.vinodkotiya.com