?
Solved

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

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

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 brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

777 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