ADO Add Record

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

Add a record to a table in the database

We want 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 from which we need to collect data:

<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 clicks the confirm button, the form is 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 Notes

When you use the INSERT command, please note the following:

  • If the table has a primary key, make sure that the values you add to the primary key field are unique and not empty (otherwise, the provider will not append the record, or an error will 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 Zero-Length Fields

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

Note:Not all databases support zero-length strings, so errors may occur when adding records with blank fields. Therefore, it is important to check the data types supported by the database you are using.

Other Extensions