how to display identity value of sql in text box.

Arvind Yadav

Sir,
i am generating value in sql using identity. this value i want to display in my application in Text box or label. how to display this value in TextBox or Label.

3 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/
Gohil Jayendrasinh14y ago
using LINQ
MyBDDDataContext db = new MyBDDDataContext();
record newRecord = new record();

newRecord.record_title = newRecordBDD.Title;
newRecord.record_text = newRecordBDD.Text;
newRecord.record_date = DateTime.Now;

db.records.InsertOnSubmit(newRecord);

try
{
    db.SubmitChanges();
    int id = newRecord.ID; // you can access your newRecord to get the ID
}
catch (Exception)
{
    throw;
}

If it's helpful to you then please check as Accepted Answers

Hemant Kumar14y ago

If you want to get the Identity Value of the row try to do like this

// Use @@IDENTITY to work out the primary key of 
// the new row and display it
command.CommandText = "SELECT @@IDENTITY";
id = Convert.ToInt32(command.ExecuteScalar());

txtbxIdentity.Text=id.ToString();