get the id of the last inserted row in

Question

I saw few solutions here but none worked. I tried SCOPE_IDENTITY() didn't wotk. the id is autoincrement.

This is my stored procedure

CREATE PROCEDURE [dbo].[uploadVid]
    @video varbinary(MAx),
    @vidTitle varchar(50),
    @vidCategory varchar(50),
    @vidDate date,
    @vidDescription varchar(Max),
    @vidName varchar(50),
    @vidSize bigint
AS

INSERT INTO Video(video, vidTitle, vidCategory, vidDate, vidDescription, vidName, vidSize)
VALUES (@video, @vidTitle, @vidCategory, @vidDate, @vidDescription, @vidName, @vidSize)

& in the back end I tried

Object i = register.ExecuteScalar();

&

int newId = (Int32)register.ExecuteScalar();

I put a break point and it gave me a value of null or 0. any help appreciated

Thanks

Consider using an `output parameter` to get back the new id, as return value is used for error handling : http://stackoverflow.com/questions/3142444/stored-procedure-return-identity-as-output-parameter-or-scalar — Dude Pascalou, Oct 09 '13 at 20:03
A stored procedure call from ADO.NET sample here : http://msdn.microsoft.com/en-us/library/59x02y99 — Dude Pascalou, Oct 09 '13 at 20:11

th1rdey3 · Accepted Answer · 2013-10-09T18:47:32.707

try this

CREATE PROCEDURE [dbo].[uploadVid]
    @video varbinary(MAx),
    @vidTitle varchar(50),
    @vidCategory varchar(50),
    @vidDate date,
    @vidDescription varchar(Max),
    @vidName varchar(50),
    @vidSize bigint
AS
begin

declare @id as int --assuming your identity column is int

INSERT INTO Video(video, vidTitle, vidCategory, vidDate, vidDescription, vidName, vidSize)
VALUES (@video, @vidTitle, @vidCategory, @vidDate, @vidDescription, @vidName, @vidSize)

set @id = scope_identity()
select @id --return the value for executescaler to catch it
end

Steve · Answer 2 · 2013-10-09T20:19:23.523

0

Probably you don't execute the commands in the correct sequence: (supposing you are using an SQL Server DB)

SqlCommand cmd = new SqlCommand("uploadVid", connnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(.....)
......
// After adding the parameters you execute the command to insert the new row....
int rowsInserted = cmd.ExecuteNonQuery();
if(rowsInserted > 0)
{

    SqlCommand cmd1 = new SqlCommand("SELECT SCOPE_IDENTITY()", connection);
    int newID = (int)cmd1.ExecuteScalar();
}

ExecuteScalar returns only the value of the first row in the first column of the query executed. In case of an insert this value is meaningless. You need an ExecuteNonQuery that returns the rows inserted by the command. After that run a new command with the SELECT SCOPE_IDENTITY() command text with the ExecuteScalar. This, of course, if you cannot modify the SP, the answer from th1rdey3 is better if you could change the proc because it avoids a run-trip to the database.

edited Oct 09 '13 at 20:19

answered Oct 09 '13 at 18:24

Steve

213,761
22
232
286

No need to execute another SqlCommand, returning SCOPE_IDENTITY() from the stored procedure should do the trick. See th1rdey3's answer. – Dude Pascalou Oct 09 '13 at 19:54
@DudePascalou Does my answer gives a wrong result? Did you know if he could change the SP? While the other answer could be better I don't think that mine deserve downvoting. Well that's your choice. – Steve Oct 09 '13 at 20:05
He did not tell us if he can modify the SP. If he cannot, you're right, that is a way to do. And I am sorry about the downvoting. But if he can, this way is not recommended, because you can return the id in only one command. – Dude Pascalou Oct 09 '13 at 20:15

get the id of the last inserted row in

2 Answers2