Microsoft ActiveX Data ObjectsMicrosoft ActiveX Data Objects*
*Contents  *Index  *Topic Contents

AddNew Method Example

This example uses the AddNew method to create a new record with the specified name. 

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

	' Open a connection.
	Set cnn1 = New ADODB.Connection
	strCnn = "driver={SQL Server};server=srv;" & _
		"uid=sa;pwd=;database=pubs"
	cnn1.Open strCnn
		
	' Open Employee table.
	Set rstEmployees = New ADODB.Recordset
	rstEmployees.CursorType = adOpenKeyset
	rstEmployees.LockType = adLockOptimistic
	rstEmployees.Open "employee", cnn1, , , adCmdTable

	' Get data from the user.
	strID = Trim(InputBox("Enter employee ID:"))
	strFirstName = Trim(InputBox("Enter first name:"))
	strLastName = Trim(InputBox("Enter last name:"))

	' Proceed only if the user actually entered something
	' for both the first and last names.
	If (strID <> "") And (strFirstName <> "") _
		And (strLastName <> "") Then

		rstEmployees.AddNew
		rstEmployees!emp_id = strID
		rstEmployees!fname = strFirstName
		rstEmployees!lname = strLastName
		rstEmployees.Update
		booRecordAdded = True

		' Show the newly added data.
		MsgBox "New record: " & rstEmployees!emp_id & " " & _
			rstEmployees!fname & " " & rstEmployees!lname

	Else
		MsgBox "Please enter an employee ID, " & _
			"first name, and last name."
	End If
		
	' Delete the new record because this is a demonstration.
	cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strID & "'"
		
	rstEmployees.Close
	cnn1.Close

End Sub

VBScript Version

The following is the same example written in VBScript to be used in an Active Server Page (ASP). To view this example, you need to create a system Data Source Name (DSN) called AdvWorks for the Microsoft Access database file AdvWorks.mdb, which is installed with Microsoft Internet Information Server and located at C:\InetPub\ASPSamp\AdvWorks. Locate the file adovbs.inc and place it in the directory you plan to use. Cut and paste the following code to Notepad or another text editor and save it as AddNew.asp. You can view the result in any client browser.

To exercise the example, add a new fictional record in the HTML Form. Click the Add New Button. See the Delete method example to remove unwanted records. 

<!-- #Include file="ADOVBS.INC" -->
<% Language = VBScript %>
<HTML><HEAD><TITLE>ADO 1.5 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-->
<% 
'Create and Open Connection Object
Set OBJdbConnection = Server.CreateObject("ADODB.Connection") 
OBJdbConnection.Open "AdvWorks" 
'Create and Open Recordset Object
Set RsCustomerList = Server.CreateObject("ADODB.Recordset")
RsCustomerList.ActiveConnection = OBJdbConnection
RsCustomerList.CursorType = adOpenKeyset
RsCustomerList.LockType = adLockOptimistic
RsCustomerList.Source = "Customers"
RsCustomerList.Open

%>
<!-- If this is the first time the page is opened, the Form collection will be empty when data is entered 

run AddNew method-->
<% 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>

<!-- BEGIN column header row for Customer Table-->

<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>
<!--Display ADO Data from Customer Table One row on each pass through recordset-->
<% 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 and add to row html table-->
<% 
RScustomerList.MoveNext 
Loop 
%>

</TABLE><HR>
<!-- Form to enter new record posts variables back to this page -->
<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>

Up Top of Page
© 1997 Microsoft Corporation. All rights reserved. Terms of Use.