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

[deedv]

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:

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):

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:

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)

[techgnome]

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

[deedv]

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

[techgnome]

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

[deedv]

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.

[deedv]

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

[techgnome]

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:

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

        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

[deedv]

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.

    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:

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

[techgnome]

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

[deedv]

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.


Here are the other two tables that are part of the inner joins:


Here is my vb code in the aspx.vb file:

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:

<FooterTemplate>
                        <asp:Button ID="ftrInsert" runat="server" Text="Add" onclick="ftrInsert_Click" CommandName="ftrInsert" BackColor="Maroon" ForeColor="White" >
                        </asp:Button>
                    </FooterTemplate>

Stored Procedure:

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