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

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

mmtcunningham1Asked:
Who is Participating?
 
mmtcunningham1Connect With a Mentor Author Commented:

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
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
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
 
mmtcunningham1Author Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.