[RESOLVED] Scope_identity Query returning two different numbers - Can someone explain?

[StacyOW]

I'm using SQL2008 R2. I am executing the following query from VS2010.

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.


Thanks,
Stacy

[techgnome]

From the last line of your sproc:

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

[StacyOW]

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

[techgnome]

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

[StacyOW]

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

[StacyOW]

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?

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

[FunkyDexter]

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.

[StacyOW]

So at the moment you're half way between two approaches.

It doesn't surprise me I am between two different 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.

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

[digitalShaman]

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:

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.