Advertisement.

TransHimalaya.in

Everest Helicopter Tour for a day

Book exciting packages at www.transhimalaya.in

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


Comments

Gatesben said…
well said...pointable...
Hire ASP .NET
Programmers

Most Viewed Post

solution for facebook static.ak.fbcdn.net waiting

Connect Sony XPLOD MEX-BT2500 Bluetooth CD Receiver with ur mobile

Crystal Report : Show data horizontally (Left to right) i.e. columns as rows

Google+ Followers