Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Retrieve the auto-increment value immediately after insert to display in a label

Posted on 2009-04-08
4
Medium Priority
?
382 Views
Last Modified: 2012-05-06
I have an asp page that allows the user to enter data and once they click the submit button, the data is inserted into a database file.  I have a stored procedure that inserts the data.  I want the ability to get the auto incremented value of that inserted record so that i can let the user know what that value is.  
It is a primary key.
How can I get this value and display it on my page?

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
 
        lblError.Visible = False
        lblError.Text = String.Empty
               ' Insert form data into Communications database; alert user to let them know the status of the insert
        Try
            ds1.Insert()
            lblError.Visible = True
            lblError.Text = "Record Successfully Added"
        Catch ex As Exception
            lblError.Visible = True
            lblError.Text = "Problem Adding Record"
        End Try
 
 
Protected Sub ds1_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles ds1.Inserting
        e.Command.Parameters("@TONumber").Value = txtTaskOrder.Text
        e.Command.Parameters("@VisitDate").Value = CalendarControl1.SelectedDate.ToString
        e.Command.Parameters("@VisitAgendaTopics_Scope").Value = txtScope.Text
        e.Command.Parameters("@VisitAgendaTopics_TORevisions").Value = txtTORev.Text
        e.Command.Parameters("@VisitAgendaTopics_Schedule").Value = txtSchedule.Text
        e.Command.Parameters("@VisitAgendaTopics_Resources").Value = txtResources.Text
        e.Command.Parameters("@VisitAgendaTopics_Personnel").Value = txtPersonnel.Text
        e.Command.Parameters("@VisitAgendaTopics_ActionStatusFollowUp").Value = txtFollowUp.Text
        e.Command.Parameters("@VisitAgendaTopics_Deliverables").Value = txtDeliverables.Text
        e.Command.Parameters("@VisitAgendaTopics_OtherCommentsIssues").Value = txtOther.Text
        e.Command.Parameters("@ESTSVisitor1").Value = drpESTSRep.SelectedValue
        e.Command.Parameters("@ESTSVisitor2").Value = ddlESTSRep2.SelectedValue
        e.Command.Parameters("@ESTSVisitor3").Value = ddlESTSRep3.SelectedValue
        e.Command.Parameters("@Customer1").Value = drpCustName.SelectedValue
        e.Command.Parameters("@Customer2").Value = ddlCustomer2.SelectedValue
        e.Command.Parameters("@Customer3").Value = ddlCustomer3.SelectedValue
        e.Command.Parameters("@BranchCodes").Value = ddlBranchCode.SelectedValue
 
    End Sub
 
Here's the ASP Code:
 
<asp:SqlDataSource ID="ds1" runat="server" ConnectionString="<%$ ConnectionStrings:Communications_dbConnectionString %>"
            InsertCommand="insertVisitData" InsertCommandType="StoredProcedure" SelectCommand="Select * from Communications">
            <InsertParameters>
                <asp:Parameter Name="TONumber" Type="String" />
                <asp:Parameter Name="VisitDate" Type="DateTime" />
                <asp:Parameter Name="VisitAgendaTopics_Scope" Type="String" />
                <asp:Parameter Name="VisitAgendaTopics_TORevisions" Type="String" />
                <asp:Parameter Name="VisitAgendaTopics_Schedule" Type="String" />
                <asp:Parameter Name="VisitAgendaTopics_Resources" Type="String" />
                <asp:Parameter Name="VisitAgendaTopics_Personnel" Type="String" />
                <asp:Parameter Name="VisitAgendaTopics_ActionStatusFollowUp" Type="String" />
                <asp:Parameter Name="VisitAgendaTopics_Deliverables" Type="String" />
                <asp:Parameter Name="VisitAgendaTopics_OtherCommentsIssues" Type="String" />
                <asp:Parameter Name="ESTSVisitor1" Type="String" />
                <asp:Parameter Name="ESTSVisitor2" Type="String" />
                <asp:Parameter Name="ESTSVisitor3" Type="String" />
                <asp:Parameter Name="Customer1" Type="String" />
                <asp:Parameter Name="Customer2" Type="String" />
                <asp:Parameter Name="Customer3" Type="String" />
                <asp:Parameter Name="BranchCodes" Type="String" />
            </InsertParameters>
        </asp:SqlDataSource>

Open in new window

0
Comment
Question by:mmtcunningham1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 1000 total points
ID: 24101219
In your Stored procedure after inserting record into that table issue the statement

set @val = SCOPE_IDENTITY;

declare @val as an Output parameter of datatype int in your procedure.

Your stored procedure will then return the last inserted key.

Hope this helps
0
 

Author Comment

by:mmtcunningham1
ID: 24101338
Do I have to put something in my code or better yet, where would I let it be known that the stored procedure is returning a value???
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24102005
If you change your procedure to accommodate the output column to return a value along with above mentioned modifications, then it would suffice.

I am not familiar in ASP.Net and hence I can't guide you on your Front end code on how to retrieve output values from Stored Procedure.
0
 

Accepted Solution

by:
mmtcunningham1 earned 0 total points
ID: 24142096

Stored Procedure:

 ALTER PROCEDURE [dbo].[insertVisitData]
      -- Add the parameters for the stored procedure here
      @TONumber nchar(15),
      @VisitDate smalldatetime,
      @VisitAgendaTopics_Scope char(1),
      @VisitAgendaTopics_TORevisions char(1),
      @VisitAgendaTopics_Schedule char(1),
      @VisitAgendaTopics_Resources char(1),
      @VisitAgendaTopics_Personnel char(1),
      @VisitAgendaTopics_ActionStatusFollowUp char(1),
      @VisitAgendaTopics_Deliverables char(1),
                     @VisitAgendaTopics_Recruiting char(1),
      @VisitAgendaTopics_OtherCommentsIssues char(1),
      @ESTSVisitor1 varchar(40),
      @ESTSVisitor2 varchar(40),
      @ESTSVisitor3 varchar(40),
      @Customer1 varchar(40),
      @Customer2 varchar(40),
      @Customer3 varchar(40),
      @BranchCodes varchar(10),
      @VisitSummary varchar(max),
      @CommunicationStatus char(1),
      @PK_New int OUTPUT
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      insert into Communications(TONumber, VisitDate, VisitAgendaTopics_Scope,VisitAgendaTopics_TORevisions,
      VisitAgendaTopics_Schedule,
      VisitAgendaTopics_Resources,
      VisitAgendaTopics_Personnel,
      VisitAgendaTopics_ActionStatusFollowUp,
      VisitAgendaTopics_Deliverables,
      VisitAgendaTopics_Recruiting,
      VisitAgendaTopics_OtherCommentsIssues,
      ESTSVisitor1,
      ESTSVisitor2,
      ESTSVisitor3,
      Customer1,
      Customer2,
      Customer3,
      BranchCodes,
                     VisitSummary,
                     CommunicationStatus)
      values(@TONumber,@VisitDate,@VisitAgendaTopics_Scope,@VisitAgendaTopics_TORevisions,
      @VisitAgendaTopics_Schedule,
      @VisitAgendaTopics_Resources,
      @VisitAgendaTopics_Personnel,
      @VisitAgendaTopics_ActionStatusFollowUp,
      @VisitAgendaTopics_Deliverables,
      @VisitAgendaTopics_Recruiting,
      @VisitAgendaTopics_OtherCommentsIssues,
      @ESTSVisitor1,
      @ESTSVisitor2,
      @ESTSVisitor3,
      @Customer1,
      @Customer2,
      @Customer3,
      @BranchCodes,
    @VisitSummary,
    @CommunicationStatus)
      set @PK_New = @@IDENTITY
END

VB Code:
 Protected Sub ds1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles ds1.Inserted
        Dim communicationsID As String
        communicationsID = e.Command.Parameters("@PK_New").Value.ToString()
        lblCommunications.Visible = True
        lblCommunications.Text = "Your new commuications id number is " & communicationsID
 Protected Sub ds1_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles ds1.Inserting
        Dim insertedKey As SqlParameter
 insertedKey = New SqlParameter("@PK_New", Data.SqlDbType.Int)
        insertedKey.Direction = Data.ParameterDirection.Output
        e.Command.Parameters.Add(insertedKey)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question