SCOPE_IDENTITY is not working in asp.net?

asp.net, c#, identity, sql

Solution

The call to SCOPE_IDENTITY() is not being treated as being in the same "scope" as the INSERT command that you're executing.

Essentially, what you need to do is change the line:

string sqlQuery = "INSERT INTO videos(" + sqlColumnNames + ") VALUES(" + sqlInsertValues + ");";

to:

string sqlQuery = "INSERT INTO videos(" + sqlColumnNames + ") VALUES(" + sqlInsertValues + "); SELECT SCOPE_IDENTITY() AS [lastInsertedVideoId]";

and then call

int lastVideoInsertedId = Convert.ToInt32(sqlCmd.ExecuteScalar());

instead of .ExecuteNonQuery and the code block following the "//getting last inserted video id" comment.

Problem

I am trying to insert a record and get its newly generated id by executing two queries one by one, but don't know why its giving me the following error. ``` Object cannot be cast from DBNull to other types ``` My code is as below: (I don't want to use sql stored procedures) ``` SqlParameter sqlParam; int lastInsertedVideoId = 0; using (SqlConnection Conn = new SqlConnection(ObjUtils._ConnString)) { Conn.Open(); using (SqlCommand sqlCmd = Conn.CreateCommand()) { string sqlInsertValues = "@Name,@Slug"; string sqlColumnNames = "[Name],[Slug]"; string sqlQuery = "INSERT INTO videos(" + sqlColumnNames + ") VALUES(" + sqlInsertValues + ");"; sqlCmd.CommandText = sqlQuery; sqlCmd.CommandType = CommandType.Text; sqlParam = sqlCmd.Parameters.Add("@Name", SqlDbType.VarChar); sqlParam.Value = txtName.Text.Trim(); sqlParam = sqlCmd.Parameters.Add("@Slug", SqlDbType.VarChar); sqlParam.Value = txtSlug.Text.Trim(); sqlCmd.ExecuteNonQuery(); //getting last inserted video id sqlCmd.CommandText = "SELECT SCOPE_IDENTITY() AS [lastInsertedVideoId]"; using (SqlDataReader sqlDr = sqlCmd.ExecuteReader()) { sqlDr.Read(); lastInsertedVideoId = Convert.ToInt32(sqlDr["lastInsertedVideoId"]); } } } //tags insertion into tag table if (txtTags.Text.Trim().Length > 0 && lastInsertedVideoId > 0) { string sqlBulkTagInsert = ""; string[] tags = txtTags.Text.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries); foreach (string tag in tags) { sqlBulkTagInsert += "INSERT INTO tags(VideoId, Tag) VALUES(" + lastInsertedVideoId + ", " + tag.Trim().ToLowerInvariant()+ "); "; } using (SqlConnection Conn = new SqlConnection(ObjUtils._ConnString)) { Conn.Open(); using (SqlCommand sqlCmd = Conn.CreateCommand()) { string sqlQuery = sqlBulkTagInsert; sqlCmd.CommandText = sqlQuery; sqlCmd.CommandType = CommandType.Text; sqlCmd.ExecuteNonQuery(); } } } ``` And also if possible, please check is the above code coded well or we can optimize it more for improve performance? Thanks

Original source