AddNew 方法范例
内容
AddNew 方法范例
该范例使用 AddNew 方法创建具有指定名称的新记录。
Public Sub AddNewX()
Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim strID As String
Dim strFirstName As String
Dim strLastName As String
Dim booRecordAdded As Boolean
' 打开连接。
Set cnn1 = New ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=;"
cnn1.Open strCnn
' 打开雇员表。
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
rstEmployees.Open "employee", cnn1, , , adCmdTable
' 从用户获取数据，雇员 ID 的格式必须为：
' 名、中间名和姓的三个首字母，
' 五位数字，以及性别标识 M 或 F。
' 例如，Bill Sornsin 的雇员 ID 为：B-S55555M。
strID = Trim(InputBox("Enter employee ID:"))
strFirstName = Trim(InputBox("Enter first name:"))
strLastName = Trim(InputBox("Enter last name:"))
' P只在用户输入姓和名之后进行。
If (strID <> "") And (strFirstName <> "") _
And (strLastName <> "") Then
rstEmployees.AddNew
rstEmployees!emp_id = strID
rstEmployees!fname = strFirstName
rstEmployees!lname = strLastName
rstEmployees.Update
booRecordAdded = True
' 显示新添加的数据。
MsgBox "New record: " & rstEmployees!emp_id & " " & _
rstEmployees!fname & " " & rstEmployees!lname
Else
MsgBox "Please enter an employee ID, " & _
"first name, and last name."
End If
' 删除新记录，因为这只是演示。
cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strID & "'"
rstEmployees.Close
cnn1.Close
End Sub
VBScript 版本
下面是使用 VBScript 编写、并用于 Active Server Page (ASP) 的相同范例。如需查看该完整功能范例，请使用与 IIS 一同安装并位于 C:\InetPub\ASPSamp\AdvWorks 的数据源 AdvWorks.mdb，来创建名为 AdvWorks 的系统“数据源名称”(DSN)。这是 Microsoft Access 数据库文件。请使用“查找”命令定位文件 Adovbs.inc，并将其放入计划使用的目录中。请将以下代码剪切并粘贴到“记事本”或其他文本编辑器中，另存为 AddNew.asp。这样，便可在任何客户端浏览器中查看结果。
如要执行此范例，请按 HTML 格式添加虚构的新记录，单击“添加新记录”。查看 Delete 方法范例可删除不需要的记录。
<!-- #Include file="ADOVBS.INC" -->
<% Language = VBScript %>
<HTML><HEAD><TITLE>ADO Open Method</TITLE>
</HEAD><BODY>
<FONT FACE="MS SANS SERIF" SIZE=2>
<Center><H3>ADO AddNew Method</H3>
<!-- ADO Connection Object used to create recordset-->
<%
'创建并打开 Connection 对象。
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open "AdvWorks"
'创建并打开 Recordset 对象。
Set RsCustomerList = Server.CreateObject("ADODB.Recordset")
RsCustomerList.ActiveConnection = OBJdbConnection
RsCustomerList.CursorType = adOpenKeyset
RsCustomerList.LockType = adLockOptimistic
RsCustomerList.Source = "Customers"
RsCustomerList.Open
%>
<!-- 如果这是第一次打开页面，则输入数据时 Form 集合将为空。
请运行 AddNew 方法-->
<% If Not IsEmpty(Request.Form) Then
If Not Request.Form("CompanyName") = "" Then
RsCustomerList.AddNew
RsCustomerList("CompanyName") = Request.Form("CompanyName")
RsCustomerList("ContactLastName") = Request.Form("LastName")
RsCustomerList("ContactFirstName") = Request.Form("FirstName")
RsCustomerList("PhoneNumber") = Request.Form("PhoneNumber")
RsCustomerList("City") = Request.Form("City")
RsCustomerList("StateOrProvince") = Request.Form("State")
RsCustomerList.Update
RsCustomerList.MoveFirst
End If
End If
%>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>
<!-- Customer 表的 BEGIN 列标头行-->
<TR><TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company Name</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact Name</FONT></TD>
<TD ALIGN=CENTER WIDTH=150 BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Phone Number</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>City</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>State/Province</FONT></TD></TR>
<!--每通过一遍记录集，显示一行 Customer 表的 ADO 数据-->
<% Do While Not RsCustomerList.EOF %>
<TR><TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RSCustomerList("CompanyName")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("ContactLastName") & ", " %>
<%= RScustomerList("ContactFirstName") %>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("PhoneNumber")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("City")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("StateOrProvince")%>
</FONT></TD></TR>
<!-- Next Row = Record Loop 并添加到行 html 表-->
<%
RScustomerList.MoveNext
Loop
%>
</TABLE><HR>
<!-- 输入新记录的窗体将变量返回该页 -->
<Table>
<Form Method = Post Action="AddNew.asp" Name=Form>
<TR><TD><P>Company Name:</TD>
<TD><Input Type="Text" Size="50" Name="CompanyName" Value = ""></P></TD>
<TR><TD><P>Contact First Name:</TD>
<TD><Input Type="Text" Size="50" Name="FirstName" Value = ""></P></TD>
<TR><TD><P>Contact Last Name:</TD>
<TD><Input Type="Text" Size="50" Name="LastName" Value = ""></P></TD>
<TR><TD><P>Contact Phone:</TD>
<TD><Input Type="Text" Size="50" Name="PhoneNumber" Value = ""></P></TD>
<TR><TD><P>City:</TD>
<TD><Input Type="Text" Size="50" Name="City" Value = ""></P></TD>
<TR><TD><P>State / Province:</TD>
<TD><Input Type="Text" Size="5" Name="State" Value = ""></P></TD>
<TR><TD><Input Type="Submit" Value="Add New "><Input Type="Reset" Value="Reset Form">
</Form></Table></Center></FONT>
<%'Show location of DSN data source
Response.Write(OBJdbConnection)
%>
<Script Language = "VBScript">
Sub Form_OnSubmit
MsgBox "Sending New Record to Server",,"ADO-ASP _Example"
End Sub
</Script>
</BODY></HTML>