Getting ID of new record after insert
I'm just getting my head around insert statements today after getting sick of cheating with Dreamweaver's methods to do this for so long now (please don't laugh).
One thing I'm trying to figure out is how to get the ID value of a newly inserted record so I can redirect the user to that page if successful.
I have seen some examples which talk about stored procedures, but they're double dutch for me at the moment and I'm yet to learn about these, let alone how to use these from within my web pages.
Summary
How do I, using my code below retrieve the record ID for what the us开发者_运维问答er has just inserted.
Workflow
Using a HTML form presented on an ASP page (add.asp), a user will submit new information which is inserted into a table of a database (treebay_transaction).
On pressing submit, the form data is passed to another page (add_sql.asp) which takes the submitted data along with additional information, and inserts it into the required table.
If the insert is successful, the id value of the new record (stored in the column treebay_transaction_id) needs to be extracted to use as part of a querystring before the user is redirected to a page showing the newly inserted record (view.asp?id=value).
Sample code - add_sql.asp
<!--#include virtual="/Connections/IntranetDB.asp" -->
...
<html>
<body>
<%
set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = MM_IntranetDB_STRING
conn.Open ConnectionString
...
sql="INSERT INTO treebay_transaction (treebay_transaction_seller,treebay_transaction_start_date,treebay_transaction_expiry_date,treebay_transaction_title,treebay_transaction_transaction_type,treebay_transaction_category,treebay_transaction_description,treebay_transaction_status)"
sql=sql & " VALUES "
sql=sql & "('" & CurrentUser & "',"
sql=sql & "'" & timestampCurrent & "',"
sql=sql & "'" & timestampExpiry & "',"
sql=sql & "'" & Request.Form("treebay_transaction_title") & "',"
sql=sql & "'" & Request.Form("treebay_transaction_transaction_type") & "',"
sql=sql & "'" & Request.Form("treebay_transaction_category") & "',"
sql=sql & "'" & Request.Form("xhtml1") & "',"
sql=sql & "'3')"
on error resume next
conn.Execute sql,recaffected
if err<>0 then
%>
<h1>Error!</h1>
<p>
...error text and diagnostics here...
</p>
<%
else
' this is where I should be figuring out what the new record ID is
recordID = ??
' the X below represents where that value should be going
Response.Redirect("index.asp?view.asp?id='" & recordID & "'")
end if
conn.close
%>
</body>
</html>
Run this after your execute statement and before you close your connection:
lsSQL = "SELECT @@IDENTITY AS NewID"
Set loRs = loConn.Execute(lsSQL)
llID = loRs.Fields("NewID").value
I pulled it from here: http://www.kamath.com/tutorials/tut007_identity.asp
Build your sql variable as you have been. Let's make one trip to the DB instead of two. We'll use SCOPE_IDENTITY() right in the same statement(s) as the INSERT to avoid many trips to the database.
Add this when building your SQL statement:
sql=sql & "; SELECT SCOPE_IDENTITY() As NewTreebayTransactionID"
'now execute the insert and receive the ID in one Execute statement.
set newTransactionResults = conn.Execute(sql)
'here is our new ID.
recordID = newTransactionResults("NewTreebayTransactionID")
As soon as that's done:
- sanitize your data inputs from your user
- use parameterized statements
Set objConn = CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")
objConn.Open "DSN=connectionName"
rs.CursorLocation = 3
rs.Open "treebay_transaction", objConn, 3, 3
rs.AddNew fieldlist,values 'see link bellow to see how to fill this
rs.Update
bookmark = rs.absolutePosition ' First, store the location of you cursor
rs.Requery ' Next, update your recordset with the data from the database
rs.absolutePosition = bookmark ' Finally, change your cursor back
recordID = rs("ID")
rs.AddNew documentation: http://www.w3schools.com/ado/met_rs_addnew.asp
It depends on the database you are using, in SQL Server you can get the @@IDENTITY or SCOPE_IDENTITY() see: http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/
But one thing I want to warn you, the code above has SEVERE security vulnerabilities, namely SQL Injection attack, please stay away from concatenating strings that are coming from users, you should use command paramaters.
look to @@IDENTITY, SCOPE_IDENTITY or IDENT_CURRENT
This makes the assumption that your ID field is an IDENTITY INSERT field. Also consider ramifications of the various options listed, as each one acts and performs slightly differently.
http://sqlserverpedia.com/wiki/Functions_-_@@IDENTITY,_SCOPE_IDENTITY,_IDENT_CURRENT
With the same transaction :
Const adUseServer = 2
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "DSN=connectionName"
Set oRS = Server.CreateObject("ADODB.RecordSet")
oRS.CursorLocation = aduseserver
oRS.CursorType = adopenkeyset
oRS.LockType = adlockoptimistic
oRS.Open "treebay_transaction", oConn
if oRS.eof then
oRS.AddNew
oRS("treebay_transaction_seller") = CurrentUser
...
oRS.Update
recordID = oRS("treebay_transaction_id")
end if
oRS.Close
set oRS = nothing
oConn.Close
Set oConn = Nothing
加载中,请稍侯......
精彩评论