SCOPE_IDENTITY is not working in asp.net?

Question

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

Answer 1

The SCOPE_IDENTITY() should be extracted from the first command (SELECT, RETURN or OUT) and passed into the next command. By that, I mean that the SELECT_IDENTITY() should be at the end of the first command. In SQL 2008 there is additional syntax for bring values back as part of the INSERT, which makes this simpler.

Or more efficiently: combine the commands into one to avoid round-trips.

Answer 2

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.