Here we look at the steps to allow a new record to be added to a Recordset.
Basically, we first create an entry form consisting of the set of fields we need to collect data from.
Whilst there may be ways to dynamically generate form fields based on the fields and data type from the Recordset, I opted to code the form by hand here for several reasons.
Generating form fields on-the-fly are useful only when all fields need user input, and that all inputs are of type="text", unless you define a custom function to provide for various input types to contain different data types. Plus, with that, you are limited to using the Recordset's field names for input labels.
<form method="post" onsubmit="return validateForm( this )">
<input type="hidden" name='msgDate' value='<%= ( month( now ) & _
"/" & day( now ) & "/" & year( now ) & " " & time( ) )%>'>
<table width=85% cellspacing=1 cellpadding=3 border=0>
<col width=40% align="right">
<col width=60%>
<tr>
<td>Your Name:</td>
<td><input name='msgFrom'></td></tr>
<tr>
<td>Your email address:</td>
<td><input name='msgEmail'></td></tr>
<tr>
<td>Your subject:</td>
<td><input name='msgSubject'></td></tr>
<tr>
<td>Your message:</td>
<td><textarea name='msgBody' rows=5 cols=35></textarea></td></tr>
</table>
<p><input type="submit" value="Add New">
<input type="reset" value="Cancel"></p>
</form>
When the form is sent, the script gathers the field names and values in the form's data set, and sends these back to the server where it is added to the table as a new record, using either of two ways: ADO AddNew method or SQL INSERT INTO command.
The AddNew method creates a new record for an updateable Recordset object. Typical syntax is
recordset.AddNew Fields, Values
The arguments Fields and Values are optional.
The behavior of the AddNew method depends on the updating mode of the Recordset object ( batch or immediate ) and whether or not you pass the Fields and Values arguments.
Calling the AddNew method without arguments sets the Recordset's EditMode property to adEditAdd, and the provider caches any field value changes locally. The provider writes changes to the underlying data source ( posts the new record to the database and resets the EditMode property to adEditNone ) only when you call the Update method.
' add a new record
rsGuests.AddNew
' gather db field values
rsGuests( "MessageDate" )=Request.Form( "msgDate" )
rsGuests( "MessageFrom" )=Request.Form( "msgFrom" )
rsGuests( "Email" )=Request.Form( "msgEmail" )
rsGuests( "MessageSubject" )=Request.Form( "msgSubject" )
rsGuests( "MessageBody" )=Request.Form( "msgBody" )
' update the Recordset
rsGuests.Update
If you move from the record you are adding before calling the Update method, ADO will automatically call Update to save the changes.
In immediate update mode, if you pass the Fields and Values arguments, ADO immediately posts the new record to the database and no Update call is necessary.
In batch update mode, if you pass the Fields and Values arguments, ADO sends the new record to the provider for storage in a cache; you need to call the UpdateBatch method to post the new record to the database.
Fields are passed either as a single name or an array of names or ordinal positions of the FIELDS in the new record, while Values are passed as a single value or an array of VALUES for the fields in the new record.
If Fields is an array, Values must also be an array with the same number of members; otherwise, an error occurs. The order of field names must match the order of field values in each array.
' store db field names in fldsArray
fldsArray=array ( "MessageDate", "MessageFrom", _
"Email", "MessageSubject", "MessageBody" )
' store form field values into valsArray
valsArray=array ( Request.Form( "msgDate" ), _
Request.Form( "msgFrom" ), Request.Form( "msgEmail" ), _
Request.Form( "msgSubject" ), Request.Form( "msgBody" ) )
' update the Recordset
rsGuests.AddNew fldsArray, valsArray
In this example, we use the array option to pass the database field names and form values to AddNew. When using this method, ensure that the order of field names MATCHES the order of field values, otherwise data type mismatch errors can occur.
While not needed ( and thereby omitted ) in the sample, you may have to use the Supports method with adAddNew to verify whether you can add records to the current Recordset object.
After the AddNew method is invoked, the new record becomes the current record. Depending on your cursor type, and whether the Recordset object supports bookmarks, you may need to call the Requery method to make the new record accessible.
The sample also shows how to output the posted entry on the same page, to notify the user that the data has been accepted. This uses the generic list-view described in Part II: Displaying information from a database.
<h5>Thank you for sharing your comments ... </h5>
<table width=90% cellspacing=1 cellpadding=5 border=0>
<col width=35% align="right">
<% for each field in rsGuests.Fields %>
<tr>
<td><%= field.Name %></td>
<td><%= field.Value %></td>
</tr>
<% next %>
</table>
Optionally, you can provide to view the Recordset itself, to verify that the entries indeed have been added to the table. For this, the sample uses the generic table-view described in detail in Part II: Displaying information from a database.
There is one other way to add a new record to a table using the SQL INSERT INTO statement.
SQL INSERT INTO creates an append query to add one or more records to a given table.
In this case, you specify the name of each of the fields that a value is to be assigned to, and a value for that field.
INSERT INTO tableName ( field1, field2, ... ) VALUES ( value1, value2, ... )
The INSERT INTO statement has these parts:
| Part |
Description |
| tableName |
The name of the table or view to append records to. |
| field1, field2 |
Names of the FIELDS to append data to. |
| value1, value2 |
The VALUES to insert into the specific fields of the new record. Each value is inserted into the field that corresponds to the value's position in the list: value1 is inserted into field1 of the new record, value2 into field2, and so on. |
Separate multiple field names and values with a comma "," and enclose text field values in single quote marks ( 'strValue' ).
Omitting the FIELDS list denotes ALL fields. In that case, the VALUES clause must include a value for every field in the table; otherwise, the INSERT operation will fail.
On the other hand, when not all fields are specified, the default value or Null is inserted for unspecified columns.
Records are added to the end of the table.
When using INSERT INTO, the command is applied as a parameter of the Execute method of the Connection object, in contrast with the ADO AddNew method, which is applied to a Recordset.
'specify the provider
strProvider="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath( "/aspxtreme/shared/guests.mdb" );
'create and open a connection
set cnGuests=Server.CreateObject( "ADODB.Connection" )
cnGuests.Open strProvider
'gather form field values
msgdate=Request.Form( "msgDate" )
msgFrom=Request.Form( "msgFrom" )
msgEmail=Request.Form( "msgEmail" )
msgSubject=Request.Form( "msgSubject" )
msgBody=Request.Form( "msgBody" )
'define the append query
strInsert="INSERT INTO messages _
( MessageDate, MessageFrom, Email, MessageSubject, MessageBody ) _
VALUES ( '"&msgDate&"', '"&msgFrom&"', _
'"&msgEmail&"', '"&msgSubject&"', '"&msgBody&"' )"
'execute the insert
cnGuests.Execute ( strInsert )
If you need to show the added record for confirmation, you can open the Recordset and use the MoveLast method to position the pointer to the new record.
'create and open the recordset
set rsGuests=Server.CreateObject( "ADODB.Recordset" )
rsGuests.Open "messages", cnGuests, adCmdTable
rsGuests.MoveLast %>
If the destination table contains a primary key, make sure you append unique, non-Null values to the primary key field or fields; if not, the data source provider may not append the record, or provide Null values that may not be accepted by the underlying table.
If you append a record to a table with an AutoNumber field, do not include the AutoNumber field in the INSERT. The provider uses the default value.
Which to use and when? For adding single records, you can use either ADO AddNew or SQL INSERT, but SQL INSERT has the added functionality for use in append queries involving multiple records.
A zero-length string is a string containing no characters. It is typically used to indicate that there is no value for a field in query strings.
The value of a blank HTML Input type="text" form field is equivalent to a zero-length string, but you can specify a zero-length string by typing two double quotation marks with no space between them ( "" ).
In a Microsoft Access database, you can enter zero-length strings in Text, Memo, or Hyperlink fields if you set the AllowZeroLength property for the field to Yes.
So in the example above, when gathering the form's data set to be passed in the AddNew call, we can use blank or zero-length fields, as long as the AllowZeroLength property for those fields have been set to Yes when the table was created.
Note, though, that NOT ALL databases support zero-length strings and may cause a run-time error when a new record with blank fields is added. In some database servers, each form field must have some kind of value depending on the data type of the database field it is bound to.
It is imperative to understand what data types your database supports. Refer to the fields structure of the particular table to determine which data type to use.
