ADO Add Records
We may use the SQL INSERT INTO command to add a record to a
table in a database.
Add a Record to a Table in a Database
We want to add a new record to the Customers table in the Northwind database.
We first create a form that contains the fields we want 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 submit button the form is sent to a file called "demo_add.asp".
The "demo_add.asp" file contains the code that will 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
If you use the SQL INSERT command be aware of the following:
- If the table contains a primary key, make sure to append a unique,
non-Null value to the primary key field (if not, the provider may not append the
record, or an error occurs)
- If the table contains an AutoNumber field, do not include this field in
the SQL INSERT command (the value of this field will be taken care of
automatically by the provider)
What about Fields With no Data?
In a MS Access database, you can enter zero-length strings ("") in Text,
Hyperlink, and Memo fields IF you set the AllowZeroLength property to
Yes.
Note: Not all databases support zero-length strings and may cause an
error when a record with blank fields is added. It is important to check what
data types your database supports.
Click here to design a Stunning Flash Website for Free
Wix is a revolutionary web design tool that provides anyone with the possibility to create professional and beautiful websites for free.
With e-commerce features, search engine visibility and many more professional tools, Wix is the ultimate solution for creating a spectacular site while saving tons of money.
|
