ADO Add Records

From http://www.w3schools.com (Copyright Refsnes Data)

# 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.

From http://www.w3schools.com (Copyright Refsnes Data)