?
Solved

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

Posted on 2009-04-08
4
Medium Priority
?
383 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 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

830 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