Insert and Retrieve record ID

[prichardson]

How can i in a sql statement insert a record then retrieve the id of the record so i could use that id to be assigned to the subject of outlook email - all happens when the users clicks on the submit form button

[Yahweh]

How can i in a sql statement insert a record then retrieve the id of the record so i could use that id to be assigned to the subject of outlook email - all happens when the users clicks on the submit form button

It depends on what database and technology you're using.If you're using ASP.Net and SQL Server, then you can create a stored procedure like this:

CREAT PROCEDURE your_stored_procedure(...,@return_value output)ASINSERT INTO some_table (somecols...) VALUES (somevalues...);@return_value = scope_identity()

Then, in your code behind, you can access the variable like this:

	Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted		dim returnValue as integer = cint(e.Command.Parameters.Item("@return_value").ToString)	End Sub

[prichardson]

thanks Yahweh,how would i do it in asp and access and i probably later on put it in sql. Would be great if i knew both.

[Yahweh]

thanks Yahweh,how would i do it in asp and access and i probably later on put it in sql. Would be great if i knew both.

Imagine I had a table called "articles" with the following setup:- ID (primary key, autoincrement)- Title- Article- DateFirst, make sure you have these ADO constants included on your page. So, in ASP, it looks like this:

Dim conn, rs, sql, LastPostIDSet conn = Server.CreateObject("ADODB.Connection")Set rs = Server.CreateObject("ADODB.Recordset")sql = "Select ID, Title, Article, Date from Articles where 1 = 0"RS.CursorLocation = adUseClient  '<-- that line of code retains the value of the inserted IDRS.open sql, conn, 1, 3	RS.AddNew	RS("Title") = Title	RS("Article") = Article	RS("Date") = Now	RS.Update	LastPostID = RS("ID")   '<-- this line of code retrieves the IDRS.close