title: SCOPE_IDENTITY is not working in asp.net?
body: <p>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.</p>
  
  <pre><code>Object cannot be cast from DBNull to other types
  </code></pre>
  
  <p>My code is as below: (I don't want to use sql stored procedures)</p>
  
  <pre><code>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 &gt; 0 &amp;&amp; lastInsertedVideoId &gt; 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();
              }
          }
      }
  </code></pre>
  
  <p>And also if possible, please check is the above code coded well or we can optimize it more for improve performance?</p>
  
  <p>Thanks</p>