i'm putting values into SQL Server using a Stored Procedure. The Procedure will add an ID to the row that is added. I need to get this ID back to my code.
Currently I can get the I see the output id in the OUTPUT window of Visual Studio, but can't seem to capture it in my code. Here is a summarized version of the proc:
SQL:
CREATE PROCEDURE dbo.DoSomething
(
@var1 INT = NULL,
@var2 INT = NULL,
@var3 DATE = NULL
)
AS
BEGIN
INSERT INTO atable
(
vara,
varb,
varc
)
VALUES
(
@var1,
@var2,
@var3
)
RETURN SCOPE_IDENTITY()
END
C#:
int result = 0;
/// create command
SqlCommand cmd = new SqlCommand("DoSomething", this.OpenSqlConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@var1", thing.value1);
cmd.Parameters.AddWithValue("@var2", thing.value2);
cmd.Parameters.AddWithValue("@var3", thing.value3);
/// send data to db
result = (int)cmd.ExecuteScalar();
So I'm getting an error: Object reference not set to an instance of an object. when it gets to the (int)cmd.ExecuteScalar().
Any ideas?
...
SELECT SCOPE_IDENTITY()
The RETURN value actually comes back as an special OUTPUT parameter. SELECT gives a result set for ExecuteScalar.
You can use the OUTPUT clause instead of a separate SELECT too:
...
AS
BEGIN
INSERT INTO atable
(
vara,
varb,
varc
)
OUTPUT INSERTED.IDCol
VALUES
(
@var1,
@var2,
@var3
)
END
GO
This will work for multiple rows too.