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)
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
Re: How to Get the New ID number to pass using Stored Procedure and SCOPE_IDENTITY
Quote:
Originally Posted by
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
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
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
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.
Re: How to Get the New ID number to pass using Stored Procedure and SCOPE_IDENTITY
Ugghhhh! I guess nobody on here can help!!! :-(
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
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
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
2 Attachment(s)
Re: How to Get the New ID number to pass using Stored Procedure and SCOPE_IDENTITY
Quote:
Originally Posted by
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
Firstly, I really appreciate you helping me.
Here are the table structures for the two tables that need inserting.
Attachment 131041
Here are the other two tables that are part of the inner joins:
Attachment 131043
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
