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:

INSERT INTO TableName(column1, column2, ...) VALUES(value1, value2, ...)

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:

strSQL = "INSERT INTO person(FirstName,LastName,Phone,BirthDate) VALUES('Cousin','Gus','99887766 ','20-04-1964')"
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.

Specifying data types and zero length in Design view

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

<< Lesson 19: Retrieve data from a database

Lesson 21: Delete data from a database >>