Tech
Forums
Jobs
Books
Events
Interviews
Live
More
Learn
Training
Career
Members
Videos
News
Blogs
Login
Sign Up
Ask Question
3
Reply
Get the ID of a Newly Inserted Row
Hold On
Jan 25 2018 3:54 AM
177
Reply
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.
Upload Source Code
Select only zip and rar file.
Post
Reset
Cancel
Answers (
3
)
Next Recommended Forum
Whatsapp business with asp.net
AjaxControlToolKit not working after publishing