How to Get the New ID number to pass using Stored Procedure and SCOPE_IDENTITY-VBForums
Results 1 to 10 of 10

Thread: How to Get the New ID number to pass using Stored Procedure and SCOPE_IDENTITY

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Sep 2015
    Posts
    18

    How to Get the New ID number to pass using Stored Procedure and SCOPE_IDENTITY

    I need help! I am able to get this code to work, but it passes the previous Meeting ID, not the newly added Meeting ID. I am still somewhat new to VB and Asp.net so I cannot figure out where the problem is. Maybe the ordering of my code is keeping the newly ID from being passed.

    Here is my vb code snippet:
    Code:
    Partial Class _NewMeetings
        Inherits System.Web.UI.Page
        Dim WithEvents ftrInsert As New Button
    
        Protected Sub GridView2_RowCommand(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs)
            If e.CommandName = "ftrInsert" Then
                'call the insert sub (sender is the gridview that called the rowCommand, 
                gvQuickInsert(sender, SqlDataSource2)
            End If
        End Sub
    
        Protected Sub gvQuickInsert(ByRef gv As GridView, ByRef ds As SqlDataSource)
            Dim paramsArr As ArrayList = New ArrayList
            Dim controlsArr As ArrayList = New ArrayList
            Dim col As Integer = 0
            Dim footrow As GridViewRow = gv.FooterRow
    
            Try
                Dim ix As Integer = 0
                For ix = 0 To gv.Columns.Count
                    Dim d As DataControlField = gv.Columns(ix)
                    If d.InsertVisible = True Then
                        If footrow.Cells(ix).Controls(1) IsNot Nothing Then
                            paramsArr.Add(d.SortExpression.ToString)
    687:                    controlsArr.Add(footrow.Cells(ix).Controls(1))
                        End If
                    End If
                Next
            Catch ex As Exception
            End Try
    
            ds.InsertParameters.Clear()
    
            Dim ii As Integer = 0
            For Each contr As Object In controlsArr
                If contr.GetType.Name.ToLower.Contains("textbox") Then
                    ds.InsertParameters.Add(paramsArr(ii), TryCast(contr, TextBox).Text)
                ElseIf contr.GetType.Name.ToLower.Contains("dropdownlist") Then
                    ds.InsertParameters.Add(paramsArr(ii), _
                  TryCast(contr, DropDownList).SelectedValue)
                ElseIf contr.GetType.Name.ToLower.Contains("checkbox") Then
                    If TryCast(contr, CheckBox).Checked Then
                        ds.InsertParameters.Add(paramsArr(ii), 1)
                    Else
                        ds.InsertParameters.Add(paramsArr(ii), 0)
                    End If
                End If
                ii = ii + 1
            Next
            ds.Insert()
            gv.DataBind()
        End Sub
    
        Protected Sub ftrInsert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ftrInsert.Click
            Using conn As New SqlConnection("Data Source=c3digpssql2;Initial Catalog=ServiceLine_Committee;Integrated Security=True;")
                Using cmd As New SqlCommand("InsertVtg", conn)
                    cmd.CommandType = CommandType.StoredProcedure
                    'cmd.Parameters.AddWithValue("@CID", CID.Text)
                    'cmd.Parameters.AddWithValue("@Member", Member.Text)
                    cmd.Parameters.Add("@MID", SqlDbType.Int, 0, "ID")
                    cmd.Parameters("@MID").Direction = ParameterDirection.Output
                    conn.Open()
                    cmd.ExecuteNonQuery()
                    ID = cmd.Parameters("@MID").Value
                End Using
            End Using
        End Sub
    Here is my stored procedure (InsertVtg):

    Code:
    ALTER PROCEDURE [dbo].[InsertVtg]
    (
    @MID INT OUTPUT
    
    )
    AS
    BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO ServiceLine_Committee.dbo.tblVotings (Meeting_ID, Committee_ID, Member_Name)
    SELECT M.ID, C.ID, CM.FULL_NAME
    FROM ServiceLine_Committee.DBO.tblCommitteeName C 
      inner join ServiceLine_Committee.DBO.tblMeetings M on M.ContractCategory = C.Contract_Category AND M.Committee = C.Committee_Name
      inner join ServiceLine_Committee.DBO.tblCommitteeMembers CM on CM.Committee = M.Committee
    SET @MID = SCOPE_IDENTITY()
    RETURN @MID
    END
    My SQL DataSource Code for inserting to tblMeetings table:

    Code:
    INSERT INTO [tblMeetings] ([Meeting_Date], [Committee], [Meeting_Type], [Voting_Item], [Motioned_By], [Seconded_By], [Comments], [ContractCategory], [ContractVendor], [Submitted_By], [Part_Vendor], [Recomm_Vendor]) VALUES (@Meeting_Date, @Committee, @Meeting_Type, @Voting_Item, @Motioned_By, @Seconded_By, @Comments, @ContractCategory, @ContractVendor, @Submitted_By, @Part_Vendor, @Recomm_Vendor)
    Last edited by deedv; Oct 5th, 2015 at 04:04 PM.

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,111

    Re: How to Get the New ID number to pass using Stored Procedure and SCOPE_IDENTITY

    That's because you're selecting the max, which gives the ID of the previous record, then inserting it...


    If you look in the Database FAQ & Tutorials thread, you'll find how to use scope identity to properly pull back the new ID.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Sep 2015
    Posts
    18

    Re: How to Get the New ID number to pass using Stored Procedure and SCOPE_IDENTITY

    Quote Originally Posted by techgnome View Post
    That's because you're selecting the max, which gives the ID of the previous record, then inserting it...


    If you look in the Database FAQ & Tutorials thread, you'll find how to use scope identity to properly pull back the new ID.

    -tg
    I understand about the SCOPE IDENTITY, but I have also tried the below stored procedure and get the same result of it pulling back the previous ID and not the new:

    ALTER PROCEDURE [dbo].[InsertVtg]
    (
    @MID INT OUTPUT

    )
    AS
    BEGIN
    SET NOCOUNT ON;

    INSERT INTO ServiceLine_Committee.dbo.tblVotings (Meeting_ID, Committee_ID, Member_Name)
    SELECT M.ID, C.ID, CM.FULL_NAME
    FROM ServiceLine_Committee.DBO.tblCommitteeName C
    inner join ServiceLine_Committee.DBO.tblMeetings M on M.ContractCategory = C.Contract_Category AND M.Committee = C.Committee_Name
    inner join ServiceLine_Committee.DBO.tblCommitteeMembers CM on CM.Committee = M.Committee
    SET @MID = SCOPE_IDENTITY()
    RETURN @MID
    END

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,111

    Re: How to Get the New ID number to pass using Stored Procedure and SCOPE_IDENTITY

    1) Post the relevant SQL
    2) Post the relevant code
    3) USE [code][/code] tags around both code samples... it'll preserve the formatting and make it easier to reasd.
    4) DO NOT use bold tags around the code. It just makes it harder.

    To be honest, I can't tell what you're trying to do... I see you clear your parametersm then add in parameters in a loop... but the sproc only takes one. In one vbersion you insert one row using variable, in another you're adding using a select statement...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Sep 2015
    Posts
    18

    Re: How to Get the New ID number to pass using Stored Procedure and SCOPE_IDENTITY

    I fixed the code samples so that they are more readable. All that I am trying to do is have an "Add" button so that users can add a record by clicking the Add button in which the record gets added to the tblMeetings table. I then have an "ftrInsert_Click" command as the last vb code to run, to pass the ID that was added to the tblMeetings, to insert into the tblVotings after that. I have all of this working the way I want, except that I cannot figure out why it is not passing the new ID.

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Sep 2015
    Posts
    18

    Re: How to Get the New ID number to pass using Stored Procedure and SCOPE_IDENTITY

    Ugghhhh! I guess nobody on here can help!!! :-(

  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,111

    Re: How to Get the New ID number to pass using Stored Procedure and SCOPE_IDENTITY

    well your code doesn't make sense... I asked you to post the current post and you went back and edited the original post... it still doesn't make sense... you've got Parameters.Add in multiple places and inside a loop... and yet your sproc only takes one parameter.... normally it's done like this:
    Code:
    create sproc doSomething(@ID int output, @PAram1 varchar(10), @Param2 varchar(10))
    as
    insert into sometable(Field1, Field2) values (@Param1, @Param2)
    set @ID = scope_identity()
    go
    Code:
            Dim newRecID As Integer
            Using myConn As New SqlClient.SqlConnection("connectionstring")
                Using myCmd As New SqlClient.SqlCommand("DoSomething", myConn)
                    myCmd.Parameters.Add("ID", SqlDbType.Int).Direction = ParameterDirection.Output
                    myCmd.Parameters.AddWithValue("Param1", Textbox1.Text)
                    myCmd.Parameters.AddWithValue("Param2", Textbox2.Text)
    
                    myCmd.ExecuteNonQuery()
    
                    newRecID = myCmd.Parameters("ID").Value
                End Using
            End Using
    Andthat's it... there's nothing complicated about it.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Sep 2015
    Posts
    18

    Re: How to Get the New ID number to pass using Stored Procedure and SCOPE_IDENTITY

    By following your code example I now get an error "Cannot insert the value NULL into column 'Meeting_ID', table 'ServiceLine_Committee.dbo.tblVotings'; column does not allow nulls. INSERT fails.
    ". Also, my parameters are not coming from a textbox, but from the stored procedure.

    Code:
        Protected Sub ftrInsert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ftrInsert.Click
            Dim newRecID As Integer
            Using conn As New SqlConnection("Data Source=c3digpssql2;Initial Catalog=ServiceLine_Committee;Integrated Security=True;")
                Using cmd As New SqlCommand("InsertVtg", conn)
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.Parameters.Add("@MID", SqlDbType.Int).Direction = ParameterDirection.Output
                    cmd.Parameters.AddWithValue("@CID", SqlDbType.Int)
                    cmd.Parameters.AddWithValue("@Member", SqlDbType.NVarChar)
                    conn.Open()
                    cmd.ExecuteNonQuery()
                    newRecID = cmd.Parameters("@MID").Value
                End Using
            End Using
        End Sub
    Stored Procedure:
    Code:
    ALTER PROCEDURE [dbo].[InsertVtg]
    (
    @MID INT OUTPUT,
    @CID INT,
    @Member nvarchar(100)
    )
    AS
    BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO ServiceLine_Committee.dbo.tblVotings (Committee_ID, Member_Name)
    SELECT @CID, @Member
    FROM ServiceLine_Committee.DBO.tblCommitteeName C 
      inner join ServiceLine_Committee.DBO.tblMeetings M on M.ContractCategory = C.Contract_Category AND M.Committee = C.Committee_Name
      inner join ServiceLine_Committee.DBO.tblCommitteeMembers CM on CM.Committee = M.Committee
    SET @MID = SCOPE_IDENTITY()
    return @MID
    END

  9. #9
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,111

    Re: How to Get the New ID number to pass using Stored Procedure and SCOPE_IDENTITY

    I am so confused... every time you post code, it's different...

    so let's start at the beginning - the table you're inserting into ... what is the table definition look like? What are the fields? What are their types? What is an identity column and what isn't?

    I think there might be some misunderstanding of some basic concepts here.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Sep 2015
    Posts
    18

    Re: How to Get the New ID number to pass using Stored Procedure and SCOPE_IDENTITY

    Quote Originally Posted by techgnome View Post
    I am so confused... every time you post code, it's different...

    so let's start at the beginning - the table you're inserting into ... what is the table definition look like? What are the fields? What are their types? What is an identity column and what isn't?

    I think there might be some misunderstanding of some basic concepts here.

    -tg
    Firstly, I really appreciate you helping me.

    Here are the table structures for the two tables that need inserting.
    Name:  tables.jpg
Views: 54
Size:  96.1 KB

    Here are the other two tables that are part of the inner joins:
    Name:  othertables.jpg
Views: 59
Size:  131.0 KB

    Here is my vb code in the aspx.vb file:
    Code:
    Partial Class _NewMeetings
        Inherits System.Web.UI.Page
        Dim WithEvents ftrInsert As New Button
    
        Protected Sub GridView2_RowCommand(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs)
            If e.CommandName = "ftrInsert" Then
                'call the insert sub (sender is the gridview that called the rowCommand, 
                gvQuickInsert(sender, SqlDataSource2)
            End If
        End Sub
    
        Protected Sub gvQuickInsert(ByRef gv As GridView, ByRef ds As SqlDataSource)
            Dim paramsArr As ArrayList = New ArrayList
            Dim controlsArr As ArrayList = New ArrayList
            Dim col As Integer = 0
            Dim footrow As GridViewRow = gv.FooterRow
    
            Try
                Dim ix As Integer = 0
                For ix = 0 To gv.Columns.Count
                    Dim d As DataControlField = gv.Columns(ix)
                    If d.InsertVisible = True Then
                        If footrow.Cells(ix).Controls(1) IsNot Nothing Then
                            paramsArr.Add(d.SortExpression.ToString)
    687:                    controlsArr.Add(footrow.Cells(ix).Controls(1))
                        End If
                    End If
                Next
            Catch ex As Exception
            End Try
    
            ds.InsertParameters.Clear()
    
            Dim ii As Integer = 0
            For Each contr As Object In controlsArr
                If contr.GetType.Name.ToLower.Contains("textbox") Then
                    ds.InsertParameters.Add(paramsArr(ii), TryCast(contr, TextBox).Text)
                ElseIf contr.GetType.Name.ToLower.Contains("dropdownlist") Then
                    ds.InsertParameters.Add(paramsArr(ii), _
                  TryCast(contr, DropDownList).SelectedValue)
                ElseIf contr.GetType.Name.ToLower.Contains("checkbox") Then
                    If TryCast(contr, CheckBox).Checked Then
                        ds.InsertParameters.Add(paramsArr(ii), 1)
                    Else
                        ds.InsertParameters.Add(paramsArr(ii), 0)
                    End If
                End If
                ii = ii + 1
            Next
            ds.Insert()
            gv.DataBind()
        End Sub
    
        Protected Sub ftrInsert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ftrInsert.Click
            Dim newRecID As Integer
            Using conn As New SqlConnection("Data Source=c3digpssql2;Initial Catalog=ServiceLine_Committee;Integrated Security=True;")
                Using cmd As New SqlCommand("InsertVtg", conn)
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.Parameters.Add("@MID", SqlDbType.Int).Direction = ParameterDirection.Output
                    cmd.Parameters.AddWithValue("@CID", SqlDbType.Int)
                    cmd.Parameters.AddWithValue("@Member", SqlDbType.NVarChar)
                    conn.Open()
                    cmd.ExecuteNonQuery()
                    newRecID = cmd.Parameters("@MID").Value
                End Using
            End Using
        End Sub
    Here is the code from the aspx page for the Add button that has the commands for the aspx.vb page:
    Code:
    <FooterTemplate>
                            <asp:Button ID="ftrInsert" runat="server" Text="Add" onclick="ftrInsert_Click" CommandName="ftrInsert" BackColor="Maroon" ForeColor="White" >
                            </asp:Button>
                        </FooterTemplate>
    Stored Procedure:
    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[InsertVtg]
    (
    @MID INT OUTPUT,
    @CID INT,
    @Member nvarchar(100)
    )
    AS
    BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO ServiceLine_Committee.dbo.tblVotings (Committee_ID, Member_Name)
    SELECT @CID, @Member
    FROM ServiceLine_Committee.DBO.tblCommitteeName C 
      inner join ServiceLine_Committee.DBO.tblMeetings M on M.ContractCategory = C.Contract_Category AND M.Committee = C.Committee_Name
      inner join ServiceLine_Committee.DBO.tblCommitteeMembers CM on CM.Committee = M.Committee
    SET @MID = SCOPE_IDENTITY()
    return @MID
    END
    Last edited by deedv; Oct 6th, 2015 at 12:29 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.