Solved

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

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Finding Events logs for IIS website that restarts 2 14
tempdb log keep growing 7 32
SSRS Deployment problem 5 63
Parse this column 6 23
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.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

792 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