Lesson 20: Insert data into a database
When you have to insert new data in your Access database, you could download the entire database, manually entering data and then uploading the entire database again. But there is an easier way. In this lesson, we look at how you can insert data into the database directly from your ASP scripts.
Insert data using SQL
You retrieve data from a database in the same way that you use SQL queries to insert data into the database. The syntax of the SQL statement is:
As you can see, you can update multiple columns in the SQL statement by specifying them in a comma-separated list. But of course, it is also possible to specify just one column and one value. The columns that are not mentioned in the SQL statement will just be empty.
Example: Insert a new person in the table
Again, we use the database from lesson 17 . Let's say we want to update the database with an additional person. It could be the person Gus Goose with the phone number 99887766 and 20-04-1964 as the date of birth.
The SQL statement would then look like this:
Conn.Execute(strSQL)
As you can see, SQL statements can get quit long, and you can easily lose track. Therefore, it can be an advantage to write the SQL statement in a slightly different way:
strSQL = "INSERT INTO people (" strSQL = strSQL & "FirstName, " strSQL = strSQL & "LastName, " strSQL = strSQL & "Telephone, " strSQL = strSQL & "birth) " strSQL = strSQL & "VALUES (" strSQL = strSQL & "'cousin', " strSQL = strSQL & "'Gus', " strSQL = strSQL & "'99887766', " strSQL = strSQL & "'20-04-1964')" Conn.Execute(strSQL)
This way, the SQL statement is built up by splitting the sentence up into small parts and then putting those parts together in the variable strSQL.
In practice, it makes no difference which method you choose, but once you start working with larger tables, it's crucial that you always keep track.
Try running the following code to insert Gus Goose into the database:
<html> <head> <title>Enter data into database</title> </head> <body> <% ' Database connection - remember to specify path to your database Set Conn = Server.CreateObject("ADODB.Connection") DSN = "DRIVER = {Microsoft Access Driver (*. mdb)}; " DSN = DSN & "DBQ=" & Server.MapPath("/cgi-bin/database.mdb") Conn.Open DSN ' The SQL statement is built strSQL = "INSERT INTO people (" strSQL = strSQL & "FirstName, " strSQL = strSQL & "LastName, " strSQL = strSQL & "Phone, " strSQL = strSQL & "BirthDate) " strSQL = strSQL & "VALUES(" strSQL = strSQL & "'Cousin', " strSQL = strSQL & "'Gus', " strSQL = strSQL & "'99887766', " strSQL = strSQL & "'20-04-1964')" ' The SQL statement is executed Conn.Execute (strSQL) ' Close the database connection Conn.Close Set Conn = Nothing %> <h1>The database is updated!</h1> </body> </html>
Save user input into a database
Often you want to save user input in a database.
As you've probably already figured out, this can be done by creating a form as described in lesson 11 - where the values from the form fields can be inserted in the SQL statement. Suppose you have a simple form like this:
<form action="insert.asp" method="post"> <input type="text" name="FirstName" /> <input type="submit" value="Save" /> </form>
The form submits to the file insert.asp where you, as shown in lesson 11, can get the user's input by requesting the form content. In this particular example, an SQL statement could look like this:
strSQL = "INSERT INTO people (FirstName) values('" & Request.Form ("FirstName") & "')"
In the same way, it is possible to retrieve data from cookies, session, QueryString, etc.
Most common beginner mistakes
In the beginning, you will probably get a lot of error messages when you try to update your databases. There is no room for the slightest inaccuracy when you work databases. A misplaced comma can mean the database is not being updated, and you get an error message instead. Below, we describe the most common beginner mistakes.
The field doesn't allow zero length
In Design View (see screenshot below), you can set the parameter "Allow Zero Length" for each column. If this parameter is set to "No", and you attempted to insert a null value in this column, it would not be possible to insert the record in the database.
Wrong data types
It is important that there is consistency between the type of data and column. Each column can be set to a data type. The screenshot below shows the data types for table "people" in our example.
An error occurs if you, for example, attempt to insert text or numbers in a date field. Therefore, try to set the data types as precisely as possible.
Below is the most common data types listed:
Setting | Data Type | Size |
---|---|---|
Text | (Default) Text or combinations of text and numbers. Can also be used for numbers that are not used in calculations (e.g. phone numbers). | Up to 255 characters - or the length defined in the "Field Size" |
Memo | Longer pieces of text, or combinations of text and numbers. | Up to 65.535 characters. |
Number | Numerical data for mathematical calculations. | 1, 2, 4, or 8 bytes. |
Date/Time | Dates and times. | 8 bytes. |
Currency | Currency values. | 8 bytes. |
AutoNumber | A unique number (incrementing by 1) inserted by Microsoft Access whenever a new record is added to the database. AutoNumber fields cannot be updated. | 4 bytes |
Yes/No | Fields that can contain only one of two values, such as Yes/No, True/False, On/Off. | 1 bits. |
OLE Object | An object (e.g. a Microsoft Excel spreadsheet, a Microsoft Word document, graphics, audio, or other binary data). | Up to 1 gigabyte (limited by the available disk space) |
SQL statements with single quotes ( ' )
If you allow users to type some text in a form, and this text contains the character ' (single quote), the record cannot be inserted into the database. The solution is to replace a single quote (') with double quote ('').
This can be done with the function Replace this way:
<% strTekst = Request.Form ("TextField") strTekst = Replace(strTekst, "'", "''") %>
All ' characters will be replaced with double ' characters, which will be interpreted as single quotes by the database.