We are going to create 2 pages. The first will display a simple HTML form and the second will be an ASP page (known as the action page) which will process the data sent by the form and insert it into our Access database. We will be using a DSN-less connection.
Create a page and name it 'form.html'. Copy the following code into it.
<title>Form to Database</title>
<!-- comment - start the HTML form and use a HTML table for formatting-->
<form name="form1" action="add_to_database.asp" method="post">
<table width="80%" border="0">
<td><input type="text" name="name"></td>
<td> <input type="text" name="email"></td>
<td><input type="submit" value="submit details" name="submit"></td>
<!-- end the HTML form-->
The above code creates a simple form asking for name, email and
The form knows which page to post the details too i.e. action="add_to_database.asp"
Now onto the ASP action page, i.e. the page that will receive the details sent from the form and process them.
Create a new ASP page called 'add_to_database.asp'. Keep it in the same folder as 'form.html'. Copy and paste the following ASP code into 'add_to_database.asp'.
<%@ Language="VBScript" %>
<% Option Explicit %>
<title>Form to database</title>
'declare your variables
Dim name, email, comments
Dim sConnString, connection, sSQL
'Receiving values from Form, assign the values entered to variables
name = Request.Form("name")
email = Request.Form("email")
'declare SQL statement that will query the database
sSQL = "INSERT into users_tbl (name, email, comments) values ('" & _
name & "', '" & email & "', '" & comments & "')"
'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("Users.mdb")
'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")
'Open the connection to the database
'execute the SQL
response.write "The form information was inserted successfully."
'Done. Close the connection object
Set connection = Nothing
Here's how to create a simple Access database to work with this tutorial.
We will create a simple Access database with just one table 'users_tbl' containing four fields, 'id', 'name', 'email', 'comments' respectively. Creating an access database is pretty straightforward. Open Microsoft Access and create a database 'users.mdb' and save it in the same directory where you are creating other HTML and ASP files for this tutorial. In 'design view' of Microsoft Access our 'users_tbl' table will look like :
Notice at the bottom that we have set the 'Allow Zero Length' to Yes, this will allow the form to pass empty strings, i.e. not entering a value for any of the textfields.
Run the example submitting your form details and it will insert those values into our Access database. Just note that if you enter the ' (an apostrophe) it will not work. We will come to dealing with that a little later on.
Well it's as simple as that, but now you'll want to see what you have entered. In our next tutorial we will create a page that will display the records that you have just entered.
|Previous: SELECT a Random Record|