how to retrive identity value of sql in textbox of C# application

Arvind Yadav

i am genrating identity value in sql . this value i want to show in my textbox or label of the application of C#. how to retrive identity value into textbox to label of the the application. and what is the code of retriving identity value into textbox or label.

5 Replies

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

Senthilkumar14y ago
Hi,

There are three ways to get the identity value from the sql server.

@@SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session.
However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

Instead of writing separate logic to read the identity value after inserting it can returns the stored procedure with output parameter.

CREATE PROC sp_getIdentity @id int output,@name varchar(20)
as

INSERT INTO t1 (name) VALUES (@name);
select @id = SCOPE_IDENTITY()
go

Then call the stored procedure in a sql command:

SqlCommand cmd = new SqlCommand("sp_getIdentity", connection);
connection.Open();
SqlParameter sp= new SqlParameter("@id",SqlDbType.Int);
sp.Direction = ParameterDirection.Output;
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(sp);

int id=sp.Value.ToString();

For more info:

http://www.dotnetfunda.com/articles/article1547-how-to-retrieve-identity-auto-incremented-value-using-output-paramter-thr.aspx

http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record

http://ramanisandeep.net/tag/how-to-retrieve-the-identity-value/
+1
Dorababu Meka14y ago
complete code looks as follows

sqlconnection con=new sqlconnection("your connection string");
sqlcommand cmd=new sqlcommand("select id from tblname",con);
con.open();
dataset ds=new dataset();
dataadapter da =new dataadapter();
da.selectcommand=cmd;
da.fill(ds):
con.close();
label1.text= ds.tables[0].rows[0]["id"].tostring();
0
Dorababu Meka14y ago
Then follow the way i said and assign the value as follows

label.text=ds.tables[0].rows[0]["yourcol"].tostring();
0
Arvind Yadav14y ago
only single column which is the subject id column in my application, that i want to retrive from identity to my textbox or label. so pls write code how to retrive identity value into textbox or label.
0
Dorababu Meka14y ago
If that is a column in a table you can retrieve in a general way

open the connection
write your query in command object as follows
Select Identitycolumn from yourtable
fill to dataset using dataadapter

da.fill(ds);

But if you have multiple ID's it is hard to show the required. So post what you are exactly doing

0