Did you find this useful?
Socialize it today.


ADO Inserting Records in a Database

Friday, September 28, 2012

tags ADO

In previous articles in this series, we have been focusing on how to use the Connection, Command, and Recordset object to query a data source such as a database. In this tutorial, we will build on that and take a look at an example on how to insert new records in a database table using ASP and ADO. We need to understand how to use the SQL INSERT INTO statement and how to integrate it with ADO.


SQL INSERT INTO Statement

Structured Query Language also known as 'SQL' is the standard language used with most modern database applications. SQL provides us with a mechanism to select, insert, update and modify records in a database. In this summary, we are going to focus on the SQL INSERT INTO statement. For more in-depth information about SQL, please visit the SQL section of this site. There are dozens of tutorials to help you better understand the SQL language.

Syntax

INSERT INTO tableName (field1, field2, field3)
VALUES ('value1', 'value2', 'value3')

OR

INSERT INTO tableName VALUES ('value1', 'value2', 'value3')

In the previous example, the INSERT INTO statement inserts three values into three fields. If you insert the values out of order, you will need to use the first example. However, if you do not specify the field names in the statement, the values are inserted in the order that they are presented as in the second example. So the next step is to build from our previous examples in this series and take a look at how we incorporate the SQL INSERT INTO statement into our ASP/ADO code.

Using the Connection Object

<% 
Dim oConn, ds, sql
ds = "Driver={MySQL ODBC 3.51 Driver};SERVER=db-hostname;DATABASE=db-name;UID=userID;PWD=password"
sql = "INSERT INTO employees (empName, empTitle) VALUES ('Josh Walker', 'Manager')"

Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open ds
oConn.Execute sql

oConn.close
Set oConn=nothing
%>

The previous example simply creates the Connection object which opens the database and inserts the records into specific fields of the table according to the SQL INSERT INTO statement.

Add New Form

A common approach to inserting information into your database is to use a form on a web page. The user fills out the form and submits it. You send the form information to an ASP page that reads in the form values and inserts data into the database. Keep in mind that you should validate your user's input on the source page before inserting information into your database. Here is an example of an ASP page used to collect the information within a form.

<!DOCTYPE html>
<html>
<head>
  <title>Employee Insert</title>
</head>
<body>
<form method="post" action="employeeInsert.asp">
<table>
  <tr>
    <td>Employee Name:</td>
    <td><input name="empName"></td>
  </tr>
  <tr>
    <td>Employee Title:</td>
    <td><input name="empTitle"></td>
  </tr>
</table>
<br /><br />
<input type="submit" value="Add New">
<input type="reset" value="Cancel">
</form>
</body>
</html>


insert

A user simply needs to visit the request form page, fill out the information and click on the 'Add New' button. The user will be redirected to the employeeInsert.asp page where the form information will be collected and a new record will be inserted into the database. Here is are some examples of the ASP/ADO code for the employeeInsert.asp page.

<% 
Dim oConn, ds, sql, qName, qTitle
qName = Request.Form("empName")
qTitle = Request.Form("empTitle")
ds = "Driver={MySQL ODBC 3.51 Driver};SERVER=db-hostname;DATABASE=db-name;UID=userID;PWD=password"
sql = "INSERT INTO employees (empName, empTitle) VALUES ('" & qName & "', '" & qTitle & "')"

Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open ds

oConn.Execute sql
If err<>0 then
  Response.Write("There was an error!")
Else
  Response.Write("Record Added!")
End if

oConn.close
Set oConn=nothing
%>

Using Parameters

When inserting data into a database, it is always a good practice to use parameters so that the information provided by the user input is treated as a literal. This is so that you can prevent a SQL Injection attack by a user that manipulates the Form data and inludes unwanted SQL code.

<% 
Dim oConn, oCmd, ds, sql, qName, qTitle
qName = Request.Form("empName")
qTitle = Request.Form("empTitle")
ds = "Driver={MySQL ODBC 3.51 Driver};SERVER=db-hostname;DATABASE=db-name;UID=userID;PWD=password"
sql = "INSERT INTO employees (empName, empTitle) VALUES (?,?)"

Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open ds

Set oCmd = Server.CreateObject("ADODB.Command") 
oCmd.ActiveConnection = oConn
oCmd.CommandText = sql
oCmd.CommandType = 1
oCmd.Parameters(0) = qName
oCmd.Parameters(1) = qTitle
oCmd.Execute()
If err<>0 then
  Response.Write("There was an error!")
Else
  Response.Write("Record Added!")
End if

oConn.close
Set oConn=nothing
%>

Please help us spread the word by socializing it today!

email contact us

Did you find something wrong with the information on this page? Please take a moment to report it to us so that we can continue to improve the quality of the information on this site. Click here to report an issue with this page.



Recommended Books & Training Resources

ADO Programmers Reference ADO: ActiveX Data Objects ADO Programming for Dummies