Insert New Record to Database ASP Script

Posted by admin

May 7, 2008

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.

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 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
%>

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.

<%
‘ 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.

Faisal Khan

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.