Solved

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

Posted on 2009-04-08
4
372 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

746 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

13 Experts available now in Live!

Get 1:1 Help Now