Before a database can be accessed from a web page, a database connection has to be established.
Create a DSN-less Database Connection
The easiest way to connect to a database is to use a DSN-less connection. A DSN-less connection can be used against any Microsoft Access database on your web site.
If you have a database called "northwind.mdb" located in a web directory like "c:/webdata/", you can connect to the database with the following ASP code:
| <% set conn=Server.CreateObject("ADODB.Connection") conn.Provider="Microsoft.Jet.OLEDB.4.0" conn.Open "c:/webdata/northwind.mdb" %> |
Note, from the example above, that you have to specify the Microsoft Access database driver (Provider) and the physical path to the database on your computer.
Create an ODBC Database Connection
If you have an ODBC database called "northwind" you can connect to the database with the following ASP code:
| <% set conn=Server.CreateObject("ADODB.Connection") conn.Open "northwind" %> |
With an ODBC connection, you can connect to any database, on any computer in your network, as long as an ODBC connection is available.
Form to database Tutorial in ASP
Download the codeWe 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.
<html>
<head>
<title>Form to Database</title>
</head>
<body>
<!-- comment - start the HTML form and use a HTML table for formatting-->
<form name="form1" action="add_to_database.asp" method="post">
<div align="center">
<table width="80%" border="0">
<tr>
<td>Name :</td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td>Email :</td>
<td> <input type="text" name="email"></td>
</tr>
<tr>
<td>Comments :</td>
<td><textarea name="comments"></textarea></td>
</tr>
<tr>
<td> </td>
<td><input type="submit" value="submit details" name="submit"></td>
</tr>
</table>
</div>
</form>
<!-- end the HTML form-->
</body>
</html>
<head>
<title>Form to Database</title>
</head>
<body>
<!-- comment - start the HTML form and use a HTML table for formatting-->
<form name="form1" action="add_to_database.asp" method="post">
<div align="center">
<table width="80%" border="0">
<tr>
<td>Name :</td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td>Email :</td>
<td> <input type="text" name="email"></td>
</tr>
<tr>
<td>Comments :</td>
<td><textarea name="comments"></textarea></td>
</tr>
<tr>
<td> </td>
<td><input type="submit" value="submit details" name="submit"></td>
</tr>
</table>
</div>
</form>
<!-- end the HTML form-->
</body>
</html>
The above code creates a simple form asking for name, email and comments.
The form knows which page to post the details too i.e. action="add_to_database.asp"
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 %>
<html>
<head>
<title>Form to database</title>
</head>
<body>
<%
'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")
comments =Request.Form("comments")
'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
connection.Open(sConnString)
'execute the SQL
connection.execute(sSQL)
response.write "The form information was inserted successfully."
'Done. Close the connection object
connection.Close
Set connection = Nothing
%>
</body>
</html
<% Option Explicit %>
<html>
<head>
<title>Form to database</title>
</head>
<body>
<%
'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")
comments =Request.Form("comments")
'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
connection.Open(sConnString)
'execute the SQL
connection.execute(sSQL)
response.write "The form information was inserted successfully."
'Done. Close the connection object
connection.Close
Set connection = Nothing
%>
</body>
</html
Call our file 'show_records.asp'.
<%@ Language="VBScript" %>
<% Option Explicit %>
<html>
<head>
<title>Form to database - showing records</title>
</head>
<body>
<%
'declare your variables
Dim connection, recordset
Dim sSQL, sConnString
'declare SQL statement that will query the database
sSQL="SELECT * FROM Users_tbl"
'create an ADO connection and recordset object
Set connection = Server.CreateObject("ADODB.connection")
Set recordset = Server.CreateObject("ADODB.Recordset")
'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")
'Open the connection to the database
Connection.Open sConnString
'Open the recordset object, executing the SQL
Recordset.Open sSQL, Connection
'Looping through the records until the end of the records
Do while Not recordset.eof
Response.Write "Name : " & recordset("name") & "<br>"
Response.Write "Email : " & recordset("email") & "<br>"
Response.Write "Comments : " & recordset("comments") & "<br><br>"
'move on to the next record
recordset.MoveNext
loop
'Now close the recordset and the connection object
recordset.Close
Set recordset = Nothing
connection.Close
Set connection = Nothing
%>
</body>
</html>
<% Option Explicit %>
<html>
<head>
<title>Form to database - showing records</title>
</head>
<body>
<%
'declare your variables
Dim connection, recordset
Dim sSQL, sConnString
'declare SQL statement that will query the database
sSQL="SELECT * FROM Users_tbl"
'create an ADO connection and recordset object
Set connection = Server.CreateObject("ADODB.connection")
Set recordset = Server.CreateObject("ADODB.Recordset")
'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")
'Open the connection to the database
Connection.Open sConnString
'Open the recordset object, executing the SQL
Recordset.Open sSQL, Connection
'Looping through the records until the end of the records
Do while Not recordset.eof
Response.Write "Name : " & recordset("name") & "<br>"
Response.Write "Email : " & recordset("email") & "<br>"
Response.Write "Comments : " & recordset("comments") & "<br><br>"
'move on to the next record
recordset.MoveNext
loop
'Now close the recordset and the connection object
recordset.Close
Set recordset = Nothing
connection.Close
Set connection = Nothing
%>
</body>
</html>
No comments:
Post a Comment