Display id after insert in stored procedure

Question

How to display the id after inserting the data with the use of stored procedure, my sample stored procedure is like this..

CREATE PROCEDURE [dbo].[insertResidence]
@Fname varchar(50),
@Lname varchar(50),
@Mname varchar(50),
@Bdate varchar(50),
@BirthPlace varchar(50),
@CivStat varchar(50),
@Purok varchar(50),
@Religion varchar(50),
@Gender varchar(50),
@CitShip varchar(50)

AS
    Begin tran one;
    Declare @resId INT;
    INSERT INTO [tbl_Residence] (resFname, resLname, resMname, resBdate, resBirthPlace, resGender) VALUES (@Fname,@Lname,@Mname,@Bdate,@BirthPlace,@Gender);
    Select @resId = SCOPE_IDENTITY();
    NSERT INTO [tbl_Religion] (resId, religion) VALUES (@resId,@Religion);
    INSERT INTO [tbl_Purok] (resId,pur_sit) VALUES (@resId,@Purok);
    INSERT INTO [tbl_Citizenship] (resId,citship) VALUES (@resID,@Purok);
    Select resId from tbl_Residence WHERE resId = @resId;
    commit tran;
RETURN

After inserting the data I want to display the id in the label or textboxt.

I use ExecuteNonQuery() in the transaction.. This is the code of inserting data in transaction

public int insertRes(Informations info)
    {

        //Insert Residence Informations
        SqlCommand cmd = new SqlCommand("InsertResidence");
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@FName", SqlDbType.VarChar).Value = info.Fname;
        cmd.Parameters.AddWithValue("@LName", SqlDbType.VarChar).Value = info.Lname;
        cmd.Parameters.AddWithValue("@MName", SqlDbType.VarChar).Value = info.Mname;
        cmd.Parameters.AddWithValue("@BDate", SqlDbType.VarChar).Value = info.Bdate;
        cmd.Parameters.AddWithValue("@BirthPlace", SqlDbType.VarChar).Value = info.BirthPlace;
        cmd.Parameters.AddWithValue("@CivStat", SqlDbType.VarChar).Value = info.CivStat;
        cmd.Parameters.AddWithValue("@Purok", SqlDbType.VarChar).Value = info.purok;
        cmd.Parameters.AddWithValue("@Religion", SqlDbType.VarChar).Value = info.Religion;
        cmd.Parameters.AddWithValue("@Gender", SqlDbType.VarChar).Value = info.Gender;
        cmd.Parameters.AddWithValue("@CitShip", SqlDbType.VarChar).Value = info.CitShip;
        return db.ExeNonQuery(cmd);
    }

UI layer :

Business layer:

[get last id](https://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id) — CodingYoshi, Jul 31 '17 at 02:15

BlackSpy · Accepted Answer · 2017-07-31T02:36:24.890

1

ExecuteNonQuery will only return you the number of rows effected by your command. Use ExecuteScalar, which will return you resId.

To display:

int resId = (int)cmd.ExecuteScalar();

Then in your UI:

lblMyLabel.Text = resId.ToString();

edited Jul 31 '17 at 02:36

answered Jul 31 '17 at 02:22

BlackSpy

5,563
5
29
38

but how do i display it in the label with the use of my transaction – Cooper Jul 31 '17 at 02:31
Have updated answer to give a code snippet – BlackSpy Jul 31 '17 at 02:33
do i need to add this in my parameters? cmd.Parameters.AddWithValue("@resId", SqlDbType.VarChar).Value = info.Id; – Cooper Jul 31 '17 at 02:44
or replace my ExecuteNonQuery() to ExecuteScalar()? – Cooper Jul 31 '17 at 02:48
You shouldn't need to change your query at all - as long as your command only returns 1 value ExecuteScalar will return that value – BlackSpy Jul 31 '17 at 02:50
Sir i have updated my post, the first picture is my UI layer and the second is the Business logic layer – Cooper Jul 31 '17 at 02:58
In your Business layer, ExecuteNonQuery needs to be ExecuteScalar. You can then set the text of your label to the value returned from that method. – BlackSpy Jul 31 '17 at 03:09

Display id after insert in stored procedure

1 Answers1