Retrieving Identity Value after using DataAdapter.Update on DataTable

I am trying to retrieve the Identity Value on an Inserted Table Row.. However, I am inserting this row by creating a new DataRow, inserting it into the DataTable, and using SqlDataAdapter.Update (which would then auto-create the insertion string, insert, and then close the connection ).

I want to retrieve the Identity Value of what I just inserted.. I tried using a "SELECT @.@.IDENTITY", but that returned null.. I think its because @.@.IDENTITY only works for a connection session, and the SqlDataAdapter closes the connection after it inserts..

Any ideas / workarounds would be welcome! Thanks!

I posted this to the MySQL forum by accident too Sorry! :\Write your own insert function & attach it into SqlDataAdapter.InsertCommand property.|||How will writing my own insertion string allow me to retrieve the Identity value after its inserted?|||1st of all: try to use stored procedures. It's more robust & more elegant solution than inline SQL statements. Example statement (for Northwind database):

CREATE PROCEDURE CategoryInsert
(
 @.CategoryName nvarchar(15)
)
AS
 INSERT INTO Categories (CategoryName) VALUES (@.CategoryName )
 SELECT @.@.IDENTITY
<code>
And in code-behind:
<code>
SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
conn.Open();
SqlCommand sqlInsert = new SqlCommand("CategoryInsert", conn);
sqlInsert.CommandType = CommandType.StoredProcedure;
SqlParameter paramCategoryName = new SqlParameter("@.CategoryName", SqlDbType.NVarChar, 15);
paramCategoryName.Value = "New category";
sqlInsert.Parameters.Add(paramCategoryName);
object o = sqlInsert.ExecuteScalar();
if(o != null)
lblFeedback.Text = o.ToString();
conn.Close();