title: How to get the insert ID from this ADODB.Recordset?
body: <p>I'm trying to avoid using straight SQL queries in my web app. I looked around and have come to the conclusion that ADO <code>Recordset</code>s would be the best or at least safest tool for the job. I need to insert records into a database table. Unfortunately I'm at a loss as to how to get the identity value for the record which was just inserted. Here's a reduction of what I've got now:</p>
  
  <pre><code>&lt;%
  dim insertID, rs
  set rs = Server.CreateObject("ADODB.Recordset")
  rs.Open "my_table_name", conn, adOpenForwardOnly, adLockOptimistic
  
  rs.AddNew()
  
  Call m_map_values_to_rs(rs)
  
  rs.Update()
  
  insertID = rs("id")
  
  rs.Close()
  rs = Nothing
  %&gt;
  </code></pre>
  
  <p>The code I have is successfully inserting the record, but I can't for the life of me figure out how to get the id field of the <code>Recordset</code> to update after the insert. How can I get the identity column value back from this <code>Recordset</code>?</p>
  
  <p><strong>UPDATE</strong> - Here's the solution with regard to the code above.</p>
  
  <p>I had to change the cursor type to <code>adOpenKeyset</code> instead of <code>adOpenForwardOnly</code>. After I did this the record is automatically updated with the "auto number" field's new value after the insert. However it is <strong><em>not</em></strong> what you think it is. The value of <code>rs("id")</code> doesn't become an integer or even a variant. It becomes some sort of <code>Automation</code> type and cannot be evaluated as a number. Nor can CInt() be used directly on that type for some reason. So what you must do is to convert the value to a string and then convert it to an <code>Int</code>. Here's how I managed that:</p>
  
  <pre><code>insertID = CInt( rs("id") &amp; "" )
  </code></pre>
  
  <p>Thanks to Dee for their answer. It helped immensely.</p>