ADO Add Record

We can use the SQL INSERT INTO command to add records to a table in the database.

Add records to the table in the database

We hope to add a new record to the Customers table in the Northwind database. First, we need to create a form that contains the input fields we need to collect data from:

<html>
<body>
<form method="post" action="demo_add.asp">
<table>
<tr>
<td>CustomerID:</td>
<td><input name="custid"></td>
</tr><tr>
<td>Company Name:</td>
<td><input name="compname"></td>
</tr><tr>
<td>Contact Name:</td>
<td><input name="contname"></td>
</tr><tr>
<td>Address:</td>
<td><input name="address"></td>
</tr><tr>
<td>City:</td>
<td><input name="city"></td>
</tr><tr>
<td>Postal Code:</td>
<td><input name="postcode"></td>
</tr><tr>
<td>Country:</td>
<td><input name="country"></td>
</tr>
</table>
<br /><br />
<input type="submit" value="Add New"> 
<input type="reset" value="Cancel">
</form>
</body>
</html>

When the user presses the confirm button, this form will be sent to a file named "demo_add.asp". The file "demo_add.asp" contains code that can add a new record to the Customers table:

<html>
<body>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"
sql="INSERT INTO customers (customerID,companyname,"
sql=sql & "contactname,address,city,postalcode,country)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("custid") & "',"
sql=sql & "'" & Request.Form("compname") & "',"
sql=sql & "'" & Request.Form("contname") & "',"
sql=sql & "'" & Request.Form("address") & "',"
sql=sql & "'" & Request.Form("city") & "',"
sql=sql & "'" & Request.Form("postcode") & "',"
sql=sql & "'" & Request.Form("country") & "')"
on error resume next
conn.Execute sql,recaffected
if err<>0 then
  Response.Write("No update permissions!")
else 
  Response.Write("<h3>" & recaffected & " record added</h3>")
end if
conn.close
%>
</body>
</html>

Important Matters

When using the INSERT command, please note the following:

  • If the table contains a primary key, make sure that the value added to the primary key field is unique and not empty (otherwise, the provider will not append this record, or an error may occur).
  • If the table contains an auto-number field, do not include this field in the INSERT command (the value of this field is handled by the provider).

About Null Data Fields

In the MS Access database, if you set the AllowZeroLength property to 'Yes', you can enter zero-length strings ("") in text, hyperlinks, and memo fields.

Note:Not all databases support zero-length strings, so an error may occur when adding a record with a blank field. Therefore, it is very important to check the data types supported by the database you are using.