Get the ID of a Newly Inserted Row

Hi everyone, I need to get the ID of a newly inserted row. And I was able to create an SP from the SQL Server. Please see below:

ALTER PROCEDURE [dbo].[spInser_CategoryHeader]
-- Add the parameters for the stored procedure here
(
@SchoolYear nvarchar(50),
@Term nvarchar(50),
@GradeLvl nvarchar(50),
@SubjectCode nvarchar(50),
@TotalCategoryPercentage decimal(5,2),
@SchoolCode nvarchar(50),
@getUser nvarchar(50),
@CategoryHeaderID int OUTPUT
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
BEGIN TRANSACTION
IF NOT EXISTS (SELECT CategoryHeaderID FROM tblCategoryHeader WHERE CategoryHeaderID=@CategoryHeaderID)
BEGIN
INSERT INTO [dbo].[tblCategoryHeader]
([SchoolYear]
,[Term]
,[GradeLvl]
,[SubjectCode]
,[TotalCategoryPercentage]
,[SchoolCode]
,[getUser])
VALUES
(@SchoolYear,
@Term,
@GradeLvl,
@SubjectCode,
@TotalCategoryPercentage,
@SchoolCode,
@getUser)
SELECT @CategoryHeaderID = SCOPE_IDENTITY()
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Duplicate record found. All entries are reverted to it original state.',16,1)
END
END

In my c# application, I am using a 3 tier architecture. I was able to get my objective if I will write my ADO code in my .cs file. Please see below:

private void InsertGradeCategory()
{
using (SqlConnection con = DBCS.GetConString())
{
con.Open();
SqlTransaction sqlTran = con.BeginTransaction();
SqlCommand cmd = con.CreateCommand();
cmd.Transaction = sqlTran;
try
{
using (SqlConnection cons = DBCS.GetConString())
{
SqlCommand cmds = new SqlCommand("spInser_CategoryHeader", cons);
cmds.CommandType = CommandType.StoredProcedure;
cmds.Parameters.AddWithValue("@SchoolYear", ddlSchoolYear.SelectedValue);
cmds.Parameters.AddWithValue("@Term", ddlTerm.SelectedValue);
cmds.Parameters.AddWithValue("@GradeLvl", ddlGradeLvl.SelectedValue);
cmds.Parameters.AddWithValue("@SubjectCode", ddlSubjects.SelectedValue);
cmds.Parameters.AddWithValue("@TotalCategoryPercentage", lblTotalPercentage.Text);
cmds.Parameters.AddWithValue("@SchoolCode", lblSchoolCode.Text);
cmds.Parameters.AddWithValue("@getUser", lblUsername.Text);
SqlParameter outputParameter = new SqlParameter();
outputParameter.ParameterName = "@CategoryHeaderID";
outputParameter.SqlDbType = SqlDbType.Int;
outputParameter.Direction = ParameterDirection.Output;
cmds.Parameters.Add(outputParameter);
cons.Open();
cmds.ExecuteNonQuery();
string empId = outputParameter.Value.ToString();
hfCategoryHeaderId.Value = empId;
}
}
}
}

But I prefer writing my ADO from my DataAccess code. Here's what I started:

public int InsertCategoryHeader(CategoryHeaderTable head)
{
using (SqlConnection con = DBCS.GetConString())
{
SqlCommand cmd = new SqlCommand("spInser_CategoryHeader", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
try
{
cmd.Parameters.AddWithValue("@SchoolYear", head.SchoolYear);
cmd.Parameters.AddWithValue("@Term", head.Term);
cmd.Parameters.AddWithValue("@GradeLvl", head.GradeLvl);
cmd.Parameters.AddWithValue("@SubjectCode", head.SubjectCode);
cmd.Parameters.AddWithValue("@TotalCategoryPercentage", head.CategoryPercentage);
cmd.Parameters.AddWithValue("@SchoolCode", head.SchoolCode);
cmd.Parameters.AddWithValue("@getUser", head.getUser);
SqlParameter outputParameter = new SqlParameter();
outputParameter.ParameterName = "@CategoryHeaderID";
outputParameter.SqlDbType = SqlDbType.Int;
outputParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outputParameter);
return cmd.ExecuteNonQuery();
//string studId = outputParameter.Value.ToString(); //problem here
}
catch (Exception)
{
throw;
}
}
}

Thanks in advance for any help.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Dharmraj Thakur8y ago
Accepted answer
1. In your stored procedure:
SELECT @CategoryHeaderID = @@IDENTITY() as CategoryHeaderID
2. No need of output parameter and use ExecuteScalar() instead of ExecuteNonQuery():
using (SqlConnection cons = DBCS.GetConString())
{
    SqlCommand cmds = new SqlCommand("spInser_CategoryHeader", cons);
    cmds.CommandType = CommandType.StoredProcedure;

    cmds.Parameters.AddWithValue("@SchoolYear", ddlSchoolYear.SelectedValue);
    cmds.Parameters.AddWithValue("@Term", ddlTerm.SelectedValue);
    cmds.Parameters.AddWithValue("@GradeLvl", ddlGradeLvl.SelectedValue);
    cmds.Parameters.AddWithValue("@SubjectCode", ddlSubjects.SelectedValue);
    cmds.Parameters.AddWithValue("@TotalCategoryPercentage", lblTotalPercentage.Text);
    cmds.Parameters.AddWithValue("@SchoolCode", lblSchoolCode.Text);
    cmds.Parameters.AddWithValue("@getUser", lblUsername.Text);

    cons.Open();

    string empId = cmds.ExecuteScalar();
    hfCategoryHeaderId.Value = empId;
}
That's It brother.... Cheers!
Gautam Parmar8y ago
use Executescaler inseadof executeNonQuery()
int NewlycreatedEntryId=cmd.ExecuteScalar();
Gajendra Jangid8y ago
use @@IDENTITY to get newly inserted row