CODEWITHSUNDEEP
c#asp.net
Serah Math
Serah Math

Reputation: 19

How to store scope_identity value to a variable using datareader

objcon.GetReader(" insert into product(name,price)Values('" + txt_name.Text + "','" + txt_price.Text + "') select scope_identity() ");

if (objcon.dr.Read())
{
    var id = objcon.dr[0].ToString();
}

objcon.dr.Close();

I want to store the scope_identity value into a variable. The above code is working. But I am not getting the value in variable id. Can someone please help?

Upvotes: 0

Views: 575

Answers (1)

Mikal Schacht Jensen
Mikal Schacht Jensen

Reputation: 547

It has been a while since I last worked directly with ADO.Net, so this might not be 100% accurate.

var cmd = new SqlCommand(@"
    insert into product(name,price) Values(@name, @price);
    select scope_identity();", objCon);
cmd.Parameters.AddWithValue("@name", txt_name.Text);
cmd.Parameters.AddWithValue("@price", Convert.ToInt32(txt_price.Text));
var newId = (int)cmd.ExecuteScalar();

The SqlCommand object is ideal for performing queries that returns nothing, or a single value

ExecuteNonQuery() returns the number of affected rows. F.ex. how many row was actually updated, inserted, or deleted (note: triggers can affect this value)

ExecuteScalar() returns the first column of the first row, in the result, and ignores the rest. It's perfect to retrieving a newly inserted ID or a count, or a sum, etc. The returned type is object, but can be cast to the expected type.

Upvotes: 3

Related Questions