Solved

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

Posted on 2009-04-08
4
373 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
  • 2
  • 2
4 Comments
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query to Add Late Tolerance 10 60
How can I exclude some wording in a like statement? 39 65
SQL Login 17 38
IIS Authorization for Web Service 2 21
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now