在厌倦了Dreamweaver这么长时间以来的作弊之后(请不要笑),我今天只是想弄明白insert语句。
我想弄清楚的一件事是如何获得新插入记录的ID值,这样如果成功,我就可以将用户重定向到该页面。
我已经看到了一些关于存储过程的例子,但目前它们对我来说是双重荷兰语,我还没有学习到这些,更不用说如何在我的网页中使用它们了。
摘要
如何使用下面的代码检索用户刚插入的内容的记录ID。
工作流
使用在ASP页(add.asp)上呈现的超文本标记语言表单,用户将提交插入到数据库(treebay_transaction)的表中的新信息。
按submit时,表单数据被传递到另一个页面(add_sql.asp),该页面接受提交的数据和附加信息,并将其插入到所需的表中。
如果插入成功,则在将用户重定向到显示新插入的记录(view.asp?id=value)的页面之前,需要提取新记录(存储在列treebay_transaction_id
中)的id值,以用作查询字符串的一部分。
示例代码- 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>
发布于 2010-07-07 12:41:58
在您的execute语句之后,在关闭连接之前运行以下命令:
lsSQL = "SELECT @@IDENTITY AS NewID"
Set loRs = loConn.Execute(lsSQL)
llID = loRs.Fields("NewID").value
我从这里拉出来的:http://www.kamath.com/tutorials/tut007_identity.asp
发布于 2010-07-07 13:07:28
像以前一样构建您的sql
变量。让我们只访问一次数据库,而不是两次。我们将在与INSERT
相同的语句中使用SCOPE_IDENTITY()
,以避免多次访问数据库。
在构建SQL语句时添加以下内容:
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")
一旦完成:
发布于 2010-07-07 17:16:13
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文档:http://www.w3schools.com/ado/met_rs_addnew.asp
https://stackoverflow.com/questions/3191953
复制相似问题