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 StatementSQL 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.
Syntax
insert into table_name (field1, field2, field3) values ('value1',
'value2', 'value3';
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.
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.
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 ObjectIt 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%
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 ObjectRecordset 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.
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.
