1 Attachment(s)
[RESOLVED] Scope_identity Query returning two different numbers - Can someone explain?
I'm using SQL2008 R2. I am executing the following query from VS2010.
Code:
USE [MT]
GO
/****** Object: StoredProcedure [dbo].[POToSparts] Script Date: 01/19/2016 15:12:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROC [dbo].[POToSparts]
(
@Component Varchar(150),
@QtyonHand numeric,
@EquipmentID Integer,
@VendorID Integer,
@PartNum VarChar(150),
@Price decimal,
@ordqty decimal,
@CompID Integer = NULL OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
/*DECLARE @CompID INT*/
INSERT INTO sparts
(Component, Priority, QtyonHand, PointofReorder, OrderedAmnt, Status)
VALUES (@Component, '3',@QtyonHand, '0', @ordqty, 'Active');
SET @CompID = SCOPE_IDENTITY()
INSERT INTO compequip (ComponentID, EquipmentID ) VALUES (@CompID, @EquipmentID)
INSERT INTO spartven (ComponentID , Component, VendorID , PartNum , Price, PrimaryVendor) VALUES (@CompID, @Component, @VendorID, @PartNum, @Price, 'True')
SELECT SCOPE_IDENTITY ()
END
It is returning two different values. Where is it getting that first value of 4050. The value I'm expecting is the 3411. I fear I am not understanding something here.
Attachment 134403
Thanks,
Stacy
Re: Scope_identity Query returning two different numbers - Can someone explain?
From the last line of your sproc:
Code:
INSERT INTO spartven (ComponentID , Component, VendorID , PartNum , Price, PrimaryVendor) VALUES (@CompID, @Component, @VendorID, @PartNum, @Price, 'True')
SELECT SCOPE_IDENTITY ()
END
It's dutifully doing what you told it to do... and the last thing the sproc does is select the identity from the last insert.
-tg
Re: Scope_identity Query returning two different numbers - Can someone explain?
Thanks techgnome for your reply.
My sproc does eventually return my @CompID of 3411, but my question is where is it getting the first returned value of 4050 because that is what it is putting in my datagridview cell. It returns that value and as far as I can tell I'm not asking for it and it just says (No Column Name), whatever that means.
Thanks,
Stacy
Re: Scope_identity Query returning two different numbers - Can someone explain?
You did ask for it... using the SELECT statement... it's the ID of the last insert. It comes up with "No Column Name" because you didn't give it one.
You assinged the FIRST Scope Identity to your variable... that's where your second value comes from ... but inside your sproc, you told it to SELECT the scope identity from the second insert... so it does...
-tg
Re: Scope_identity Query returning two different numbers - Can someone explain?
Ahhh light bulb! I was assuming since I assigned it after the first query that it would only use that value.
Thanks techgnome - that was driving me nuts.
Stacy
Re: Scope_identity Query returning two different numbers - Can someone explain?
I thought it would work after I took out the last Select, but alas it is now returning the 0 before the 3411. This is how I am executing the proc and getting the return value. Do I need to do something different so I just get the @CompID returned to VS?
Code:
Try
Using connection As New SqlConnection(My.Settings.MTConnectionString)
Using command As New SqlCommand("POToSparts", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(New SqlParameter("@Component", SqlDbType.VarChar)).Value = gridrow.Cells(3).Value
command.Parameters.Add(New SqlParameter("@QtyonHand", SqlDbType.Decimal)).Value = zqty
command.Parameters.Add(New SqlParameter("@EquipmentID", SqlDbType.Int)).Value = gridrow.Cells(0).Value
command.Parameters.Add(New SqlParameter("@VendorID", SqlDbType.Int)).Value = ComboBox4.SelectedValue
command.Parameters.Add(New SqlParameter("@PartNum", SqlDbType.VarChar)).Value = gridrow.Cells(2).Value
command.Parameters.Add(New SqlParameter("@Price", SqlDbType.Decimal)).Value = gridrow.Cells(8).Value
command.Parameters.Add(New SqlParameter("@ordqty", SqlDbType.Decimal)).Value = zordqty
connection.Open()
Dim CompID As Integer = CInt(command.ExecuteScalar())
ItemsDataGridView.Rows(e.RowIndex).Cells(12).Value = CompID
End Using
End Using
Catch ex As Exception
End Try
Re: [RESOLVED] Scope_identity Query returning two different numbers - Can someone exp
In the sproc your setting CompID as an output parameter. If you want to access that from VB you need to add the parameter to your command before you execute it and then check the value of the parameter after you execute it.
In your VB you're setting CompID to the value of executeScalar. Execute scalar requires you to select the value in your sproc (it's expecting a single row, single column data table).
So at the moment you're half way between two approaches.
My suggestion would be to remove the CompID parameter from the sproc, declare it as a variable instead, and then select it at the end of the sproc. That way it will be assigned to the result of your ExecuteScalar call. I feel this is probably the most clear approach in it's intent.
Alternatively you could leave your sproc as is and change your client side code instead. You would need to declare an ado parameter called CompID, set it's direction to output, add it to the command and then check it's value after your execute call.
Re: [RESOLVED] Scope_identity Query returning two different numbers - Can someone exp
Quote:
So at the moment you're half way between two approaches.
It doesn't surprise me I am between two different approaches.
Quote:
My suggestion would be to remove the CompID parameter from the sproc, declare it as a variable instead, and then select it at the end of the sproc. That way it will be assigned to the result of your ExecuteScalar call. I feel this is probably the most clear approach in it's intent.
I'm not sure I'm understanding the difference between a parameter and variable.
I'm trying to follow your instructions to fix my query. If I remove the @CompID (parameter/variable?) from the sproc how will I get the Identity value from the first query? Don't I have to assign it to the @Something? Currently I am getting the Scope_Identity from the last query, but I want the Scope_Identity from the first insert. I don't know what the value is until after the 1st insert query.
Thanks,
Stacy
Re: [RESOLVED] Scope_identity Query returning two different numbers - Can someone exp
i think you have still not understood everything that was said here, maybe consider reading everything again and keep in mind that an output parameter is something different than a return value ;)
to return the value of @CompID you just select it at the end of your sproc:
Code:
ALTER PROC [dbo].[POToSparts]
(
@Component Varchar(150),
@QtyonHand numeric,
@EquipmentID Integer,
@VendorID Integer,
@PartNum VarChar(150),
@Price decimal,
@ordqty decimal,
@CompID Integer = NULL OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
/*DECLARE @CompID INT*/
INSERT INTO sparts
(Component, Priority, QtyonHand, PointofReorder, OrderedAmnt, Status)
VALUES (@Component, '3',@QtyonHand, '0', @ordqty, 'Active');
SET @CompID = SCOPE_IDENTITY()
INSERT INTO compequip (ComponentID, EquipmentID ) VALUES (@CompID, @EquipmentID)
INSERT INTO spartven (ComponentID , Component, VendorID , PartNum , Price, PrimaryVendor) VALUES (@CompID, @Component, @VendorID, @PartNum, @Price, 'True')
SELECT @CompID
END
it currently misuses the @CompID parameter as a local var. as pointed out, the parameter should be removed and @CompID declared within the sproc. i did not change that for simplicity so that you see what the effect this minimal modification has.
