title: SQL - INSERT with Scope_Identity() - getting the record id
body: <p>I have an ASP.NET page written in VB.NET that gets the items into a <code>GridView</code> by using a <code>SELECT</code> statement with <code>INNER JOIN</code> and also allows you to add an item to the invoice.</p>
  
  <p><code>INNER JOIN</code> that gets data from <code>items</code> and <code>project_items</code>.</p>
  
  <pre><code>SELECT Items.item_id, Items.item_name, Items.item_cost, project_items.item_quantity
  FROM Items 
  INNER JOIN project_items
  ON items.item_id = project_items.item_id 
  WHERE project_items.project_id = @parameter
  </code></pre>
  
  <p><code>@parameter is</code> <code>Session("ProjectID")</code></p>
  
  <p>(There is a foreign key <code>project_items.item_id -&gt; items.item_id</code>.)</p>
  
  <p>I have an trying to use an SQL statement in VB.NET to try and INSERT into two tables simultaneously. What I tried is I tried to get the <code>item_id</code> of the last record created and insert into another table (<code>project_items</code>) by using that data. However, data is only being entered into the first table.</p>
  
  <p>Any idea what I can do?</p>
  
  <p>This is the code:</p>
  
  <pre><code>Protected Sub btnAddItem_Click(sender As Object, e As EventArgs) Handles btnAddItem.Click
  
          Dim conn As New SqlConnection("Data Source=BRIAN-PC\SQLEXPRESS;Initial Catalog=master_db;Integrated Security=True")
          Dim addItemComm As String = "SELECT item_id FROM project_items WHERE project_id=@ProjectID"
          Dim user_id_select As New Integer
  
          Dim addItemSQL As New SqlCommand
  
          conn.Open()
  
          addItemSQL = New SqlCommand(addItemComm, conn)
          addItemSQL.Parameters.AddWithValue("@ProjectID", Convert.ToInt32(Session("ProjectID")))
  
  
          Dim datareader As SqlDataReader = addItemSQL.ExecuteReader()
  
  
          datareader.Close()
          conn.Close()
  
          Dim AddNewItemComm As String = "INSERT INTO Items (item_name,  item_cost, item_code) VALUES (@ItemName, @ItemCost, @ItemCode); SELECT SCOPE_IDENTITY()"
          Dim AddNewItem2Comm As String = "INSERT INTO project_items (item_id, project_id, item_quantity) VALUES (@ItemID, @ProjectID, @ItemQuantity) "
          Dim AddNewItemSQL As New SqlCommand
  
  
          conn.Open()
  
          AddNewItemSQL = New SqlCommand(AddNewItemComm, conn)
          AddNewItemSQL.Parameters.AddWithValue("@ItemName", txtItemName.Text.Trim)
          AddNewItemSQL.Parameters.AddWithValue("@ItemCost", Convert.ToInt32(txtItemCost.Text))
          AddNewItemSQL.Parameters.AddWithValue("@ItemCode", txtItemCost.Text.ToString.ToUpper)
  
          Dim ItemId As Integer
  
          ItemId = AddNewItemSQL.ExecuteScalar()
  
          AddNewItemSQL.ExecuteNonQuery()
  
          conn.Close()
  
          conn.Open()
  
          AddNewItemSQL = New SqlCommand(AddNewItem2Comm, conn)
  
          AddNewItemSQL.Parameters.AddWithValue("@ItemID", ItemId)
          AddNewItemSQL.Parameters.AddWithValue("@ProjectID", Convert.ToInt32(Session("ProjectID")))
          AddNewItemSQL.Parameters.AddWithValue("@ItemQuantity", Convert.ToInt32(txtItemQuantity.Text))
  
          AddNewItemSQL.ExecuteNonQuery()
  
          conn.Close()
  
  
  
      End Sub
  </code></pre>