In order to insert a row into the database and get the newly inserted ID, you need to execute the following steps:
1. Use
cmd.ExecuteNonQuery()
to execute insert operation (Instead of
ExecuteScaler()
.
2. Execute another SQL statement "
SELECT SCOPE_IDENTITY()
" and use
ExecuteScaler()
to get the newly inserted ID.
I would recommend you to create a Stored Procedure to carry out the overall task. Following is a very simple stored procedure which does that:
CREATE PROCEDURE dbo.SAVE_TEMPLATE(
@Id INT
, @Name varchar(255)
, @GeneratedID INT OUTPUT
)
AS
BEGIN
INSERT INTO dbo.Table (Name)
VALUES(@Name);
SET @GeneratedID = SCOPE_IDENTITY();
END
Hope this helps.