In this tutorial, we will take a look at an example of 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 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>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 literal. This is so that you can prevent a SQL Injection attack by a user that manipulates the Form data and includes 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
%>
