Adding records to the database with ASP

Overview
No matter what kind of site you have got, you will need your ASP pages to access databases. ASP makes it easier to work with databases by providing support for ADO ( Activex Data Objects ). You already know how to build DSN ( Data Source Name ) and then access database, if you don't then you might want to check out my tutorial on Accessing Databases via ASP.

In this article I will build on that tutorial to show you how to add records to the database. We will begin by exploring the insert statement provided by SQL ( Structured Query Language ). After that we will see the two basic ways we can use to add records to the database.

I will assume here that you have read the Accessing Databases via ASP tutorial and are comfortable creating DSNs and simple Access Databases. I will also assume that you have got access to Microsoft Access Database, PWS / IIS with a notepad to write ASP pages.

SQL Insert Statement
SQL is the standard language to deal with databases World wide. It provides us with select, insert, delete and update statements to show, add, delete and update the records in the database respectively. We will only study the insert statement since we are only dealing with adding records to the database here. It will be very helpful to us when we are adding records, as we'll see later.

The insert statement as you have seen above is very simple to understand. It takes three arguments; table, fields and values. table_name is the name of the table in the database into which you want to add records. fieldn are the names of the columns in that table into which you want to add records. valuen are the values which will be inserted into specific fields. Note field names and values can be one or more than one but the table name will always be one.

When run the above query results in the insertion into two fields of table books, author and title values Faisal Khan and Add Records.

Now after you are familiar with the insert statement and have seen how it works, it is time to move forward to see the two ways by which we can easily add records to our database via ASP.

We manipulate databases in ASP through ADO ( Activex Data Objects ). ADO is a set of pre made data components which makes things a lot easier for us when it comes to accessing data stores. Wondering why did I say data stores and not databases ? well, database is only one of the data stores and ADO can help us access more than that e.g. XML. We'll not go into what ADO can do for us, instead we'll restrain ourselves to the discussion of adding records to database via ASP.

There are two ways to add records to the database. We'll discuss each of them now.

Via Connection Object
It is the easiest and fastest way to add records to the database. Here is how we add records with Connection Object :

The above results in the creation of Connection Object which opens the database and inserts the records into specific fields of the table according to the SQL insert statement. See, didn't I say before it was going to be easy.

Via Recordset Object
Recordset is another very useful Object which allows us to select, add, update and delete records without using SQL statements. Here is how we add records with Recordset Object.

So what should you use ? Connection or Recordset Object, for adding records. Well Connection Object is fast and uses less server resources while Recordset Object is resource heavy. So if you have to add records to the database then Connection Object is usually the preferred way. Whichever you choose is up to you.

Syntax

insert into table_name (field1, field2, field3)
	values ('value1', 'value2', 'value3';

Example

insert into books (author, title) values
	('Faisal Khan', 'Add Records';

When run the above query results in the insertion into two fields of table books, author and title values Faisal Khan and Add Records.

		Table Name = books
		Fields			Values
		author			Faisal Khan
		title			Add Records

Now after you are familiar with the insert statement and have seen how it works, it is time to move forward to see the two ways by which we can easily add records to our database via ASP.

There are two ways to add records to the database. We'll discuss each of them now.

Via Connection Object
It is the easiest and fastest way to add records to the database. Here is how we add records with Connection Object :

<%
	' Setting variables
	Dim con, sql_insert, data_source

	data_source = myDSN
	sql_insert = "insert into books (author, title) values " & _
		"('Faisal Khan', 'Adding Records')"

	' Creating the Connection Object and opening the database
	Set con = Server.CreateObject("ADODB.Connection")
	con.Open data_source

	' Executing the sql insertion code
	con.Execute sql_insert

	' Done. Now Close the connection
	con.Close
	Set con = Nothing
%>

Via Recordset Object
Recordset is another very useful Object which allows us to select, add, update and delete records without using SQL statements. Here is how we add records with Recordset Object.

<%
	' Setting variables
	Dim rs, data_source

	data_source = myDSN

	' Creating Recordset Object and opening the database
	Set rs = Server.CreateObject("ADODB.Recordset")

	' Lets open books table
	rs.Open "books", data_source

	rs.AddNew
	' Now adding records
	rs("author") = "Faisal Khan"
	rs("title") = "Adding Records"
	rs.Update

	' Done. Now Close the Connection
	rs.Close
	Set rs = Nothing
%>

We didn't use any SQL insert statement here but added the records.