locked
How to assign the newly created record id to a variable? RRS feed

  • Question

  • User1700504264 posted

    I am using this sub to insert a new record in a table. I need to assign the newly created ID to the variable (result) and display it for the user

    Private Sub btnCreateFile_Click(sender As Object, e As EventArgs) Handles btnCreateFile.Click
            Try
                FilesSQLDataSource.InsertParameters.Clear()
                FilesSQLDataSource.InsertParameters.Add("name", txtTitle.Text)
                FilesSQLDataSource.InsertParameters.Add("startDate", txtStartDate.Text)
                FilesSQLDataSource.InsertParameters.Add("endDate", txtEndDate.Text)
                FilesSQLDataSource.InsertParameters.Add("type", ddlType.SelectedValue)
                Dim result As Integer
                result = FilesSQLDataSource.Insert()
                If result > 0 Then
                    ltrlFiles.Text = "<div class='alert alert-success alert-dismissible' role='alert'><button type='button' class='close' data-dismiss='alert' aria-label='Close'><span aria-hidden='True'>&times;</span></button><strong>" & GetLocalResourceObject("success") & "</strong> " & GetLocalResourceObject("successdetails") & result & GetLocalResourceObject("successdetails2") & "</div>"
                Else
                    ltrlFiles.Text = "<div class='alert alert-warning alert-dismissible' role='alert'><button type='button' class='close' data-dismiss='alert' aria-label='Close'><span aria-hidden='True'>&times;</span></button><strong>" & GetLocalResourceObject("sorry") & "</strong> " & GetLocalResourceObject("sorrydetails") & "</div>"
                End If
                ClearForm()
    
            Catch ex As Exception
                ltrlFiles.Text = "<div class='alert alert-warning alert-dismissible' role='alert'><button type='button' class='close' data-dismiss='alert' aria-label='Close'><span aria-hidden='True'>&times;</span></button><strong>" & GetLocalResourceObject("sorry") & "</strong> " & GetLocalResourceObject("sorrydetails2") & "</div>"
            End Try
        End Sub

    But the assigned value is (-1) all the time although it inserts successfully

    This is the markup <g class="gr_ gr_373 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="373" data-gr-id="373">i</g> used

    <asp:SqlDataSource ID="FilesSQLDataSource" runat="server"
                            ConnectionString="<%$ ConnectionStrings:DefaultConnection %>"
                            SelectCommand="SELECT f.[ID] as Code, f.[title] as filename, f.[startDate] as startDate, f.[endDate] as endDate, t.[title] as type, f.[counter] as counter
                            FROM [File] f
                            INNER JOIN [Type] t ON f.[Type]=t.[ID]
                            ORDER BY f.[ID] Desc" 
                            InsertCommandType="StoredProcedure" 
                            InsertCommand="spCreateFile">
    
                            <InsertParameters>
                                <asp:Parameter Direction="Output" Type="Int16" Name="newFileID" DefaultValue="" />
                            </InsertParameters>
                        </asp:SqlDataSource>

    and this is the stored procedure <g class="gr_ gr_477 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="477" data-gr-id="477">i</g> used <g class="gr_ gr_484 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="484" data-gr-id="484">for</g> insert

    USE [VALUATOR]
    GO
    /****** Object:  StoredProcedure [dbo].[spCreateFile]    Script Date: 11/12/2018 7:12:59 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[spCreateFile] 
    	-- Add the parameters for the stored procedure here
    	@Name nvarchar(max),
    	@startdate date,
    	@enddate date,
    	@type int,
    	@newFileID bigint = NULL 
    	
    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 [File] (title,startDate,endDate,[type]) VALUES (@name,@startdate,@enddate,@type)
    	SELECT SCOPE_IDENTITY()
    	SET @newFileID=SCOPE_IDENTITY()
    	RETURN @newFileID
    END
    

    Anyone can help, please?

    Monday, November 12, 2018 5:53 PM

Answers

  • User61956409 posted

    Hi 0591666959,

    You can try the following stored procedure and code snippet.

    ALTER PROCEDURE [dbo].[spCreateFile] 
    	-- Add the parameters for the stored procedure here
    	@Name nvarchar(max),
    	@startdate date,
    	@enddate date,
    	@type int,
    	@newFileID bigint = NULL OUTPUT
    	
    AS
    BEGIN
        -- Insert statements for procedure here
    	INSERT INTO [File] (title,startDate,endDate,[type]) VALUES (@name,@startdate,@enddate,@type)
    	SELECT @newFileID = SCOPE_IDENTITY()
    END

    In btnCreateFile_Click:

    Protected Sub btnCreateFile_Click(sender As Object, e As EventArgs)
        FilesSQLDataSource.InsertParameters.Clear()
        FilesSQLDataSource.InsertParameters.Add("name", txtTitle.Text)
        FilesSQLDataSource.InsertParameters.Add("startDate", txtStartDate.Text)
        FilesSQLDataSource.InsertParameters.Add("endDate", txtEndDate.Text)
        FilesSQLDataSource.InsertParameters.Add("type", ddlType.SelectedValue)
    
        Dim newFileIDParam As New Parameter("newFileID", DbType.Int16, "")
        newFileIDParam.Direction = ParameterDirection.Output
    
        FilesSQLDataSource.InsertParameters.Add(newFileIDParam)
    
        Dim result As Integer
        result = FilesSQLDataSource.Insert()
    End Sub

    In FilesSQLDataSource_Inserted event:

    Protected Sub FilesSQLDataSource_Inserted(sender As Object, e As SqlDataSourceStatusEventArgs) Handles FilesSQLDataSource.Inserted
    
        'your code logic here
    
        Dim newFID As String = e.Command.Parameters("@newFileID").Value
    
        ltrlFiles.Text = newFID
    End Sub

    With Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 13, 2018 7:07 AM

All replies

  • User61956409 posted

    Hi 0591666959,

    You can try the following stored procedure and code snippet.

    ALTER PROCEDURE [dbo].[spCreateFile] 
    	-- Add the parameters for the stored procedure here
    	@Name nvarchar(max),
    	@startdate date,
    	@enddate date,
    	@type int,
    	@newFileID bigint = NULL OUTPUT
    	
    AS
    BEGIN
        -- Insert statements for procedure here
    	INSERT INTO [File] (title,startDate,endDate,[type]) VALUES (@name,@startdate,@enddate,@type)
    	SELECT @newFileID = SCOPE_IDENTITY()
    END

    In btnCreateFile_Click:

    Protected Sub btnCreateFile_Click(sender As Object, e As EventArgs)
        FilesSQLDataSource.InsertParameters.Clear()
        FilesSQLDataSource.InsertParameters.Add("name", txtTitle.Text)
        FilesSQLDataSource.InsertParameters.Add("startDate", txtStartDate.Text)
        FilesSQLDataSource.InsertParameters.Add("endDate", txtEndDate.Text)
        FilesSQLDataSource.InsertParameters.Add("type", ddlType.SelectedValue)
    
        Dim newFileIDParam As New Parameter("newFileID", DbType.Int16, "")
        newFileIDParam.Direction = ParameterDirection.Output
    
        FilesSQLDataSource.InsertParameters.Add(newFileIDParam)
    
        Dim result As Integer
        result = FilesSQLDataSource.Insert()
    End Sub

    In FilesSQLDataSource_Inserted event:

    Protected Sub FilesSQLDataSource_Inserted(sender As Object, e As SqlDataSourceStatusEventArgs) Handles FilesSQLDataSource.Inserted
    
        'your code logic here
    
        Dim newFID As String = e.Command.Parameters("@newFileID").Value
    
        ltrlFiles.Text = newFID
    End Sub

    With Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 13, 2018 7:07 AM
  • User1700504264 posted

    You can try the following stored procedure and code snippet.

    Thanks a lot Fei Han

    Wednesday, November 14, 2018 7:12 PM