Execute Insert command and return inserted Id in SQL Server using C#

Question

I am inserting some values into SQL Server table using C#. Actually I want to insert values and return the 'ID' of last inserted record. I use the following code:

string name = txt_name.Text.Trim();
string gender = Gender.Text.Trim();
string citizen = Citizen.Text.Trim();
int IdNo = Convert.ToInt32(idnumber.Text);

BUSSINESSACCESS.SENDMESSAGES send = new BUSSINESSACCESS.SENDMESSAGES();
DataSet dt = send.insertvoucher(name, gender, citizen, IdNo);

Business layer

public DataSet insertvoucher(string name, string gender, string citizen, int IdNo)
{
    SqlParameter[] par = {
                             new SqlParameter("@Name", name),    
                             new SqlParameter("@Citizen", citizen),
                             new SqlParameter("@Emiratesidno", IdNo),
                             new SqlParameter("@gender", gender),
                         };

    var rowCount = SHJCSQLHELPER.ExecuteScalar(CONNECTIONSTRING, CommandType.StoredProcedure, "[INSERTVOUCHER]", par);

    return rowCount;
    // return SHJCSQLHELPER.ExecuteDataset(CONNECTIONSTRING, CommandType.StoredProcedure, "[INSERTVOUCHER]", par);
    //string mn = "";
}

Stored procedure

-- Insert statements for procedure here
Insert into Usertable(Name, Citizen, Emiratesidno, gender) 
values (@Name, @Citizen, @Emiratesidno, @gender)

SELECT scope_identity() AS int

I am getting the value in the variable rowcount but I cannot return the variable rowcount because it is saying

Cannot implicitly convert type 'object' to 'system.data.dataset'

Type of return value of method insertvoucher is DataSet but you returning object. I think you need change return type to int and then cast rowCount to integer return (int)rowCount; — Fabio
– Fabio, Commented Apr 17, 2017 at 15:28
And name of rowCount is misleading, since you expecting id — Fabio
– Fabio, Commented Apr 17, 2017 at 15:29
ExecuteScalar does not return a DataSet, so you need to change your method signature DataSet insertvoucher to int insertvoucher. Also cast rowCount to (int)rowCount during the return — R Quijano
– R Quijano, Commented Apr 17, 2017 at 15:29

Chirag Rupani · Accepted Answer · 2017-04-17 15:35:09Z

3

You are not casting, you are aliasing column name to int,

Use: SELECT CAST(scope_identity() AS int)
rowCount is boxed integer. change return statement and return type. Example:

return (int)rowCount;

This would be your required ID.

Ref: SqlCommand.ExecuteScalar MSDN

edited Apr 17, 2017 at 15:35

answered Apr 17, 2017 at 15:29

Chirag Rupani

1,7152 gold badges18 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mad Myche · Accepted Answer · 2017-04-17 15:54:20Z

1

Prepend your Execute command with (Int32)

var rowCount =  (Int32)SHJCSQLHELPER.ExecuteScalar(CONNECTIONSTRING, CommandType.StoredProcedure, "[INSERTVOUCHER]", par);

scope_identity() returns an INT by default, no need to cast it.

You could also setup the stored procedure to use an OUT parameter and assign the Scope_Identity() to that and adjust your C# to utilize this

Correction scope_identity() returns a decimal and not an int. If your identity column is an int the conversion is automatic and no casting is needed.
I am a little rusty on this as I am utilizing output parameters in the majority of my Stored Procedures and not returning a select statement

edited Apr 17, 2017 at 15:54

answered Apr 17, 2017 at 15:32

Mad Myche

1,0751 gold badge7 silver badges15 bronze badges

3 Comments

Chirag Rupani Over a year ago

Actually Scope_Identity() returns numeric(38,0)

Riya Mathew Over a year ago

do i have to change the dataset to int

Mad Myche Over a year ago

Yes on changing the type of the return, and I have corrected to reflect correct numeric type

Collectives™ on Stack Overflow

Execute Insert command and return inserted Id in SQL Server using C#

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related