ADO Add Record
We can use the SQL INSERT INTO command to add records to a database table.
To add a record in a database table
We want to add a new record to the Customers table in the Northwind database. We first want to create a form, the form contains the data we want to collect input fields:
<body>
<form method="post" action="demo_add.html">
<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 OK button, the form will be sent to a file called "demo_add.asp" of. File "demo_add.asp" can contain the code to add a new record to the Customers table:
<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
When you use the INSERT command command, please note the following:
- If the table contains a primary key, make sure to add value to the primary key field is unique and non-empty (otherwise, provider will not append the records, or that an error occurs)
- If the table contains an AutoNumber field, do not involve this field in the INSERT command (the value of the field is determined by the provider responsible)
No data on field
In MS Access database, if you set the AllowZeroLength property is set to "Yes", you can text, hyperlinks and notes field to enter a zero-length string ( "").
Note: Not all databases support zero-length string, which may cause an error when you add records with blank fields. So check that you are using a database supported data types is very important.