Jump to content

Insert and Retrieve record ID


Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...