Building a database connection using ASP
Connecting your
database to the remote server and displaying it on the screeen is coded in a
number of steps.
The example code below from car333.asp uses ASP VB Scripting with SQL commands
to query the database for display. The MDAC uses ADO components
| Initiate the scripting dialogue | |
| <% (close at end of scripting with %>) | |
| 'Dimension variables - creates variables and dimensions the arrays | Sets up the variables and initializes them - not essential |
| dim conn, strsql, rsuser, strMDBPath | |
| 'Create and open an ADO database connection object | |
| set conn=server.createobject("ADODB.Connection") | |
| 'Create an ADO recordset object | |
| set rsuser=server.createobject("ADODB.Recordset") | Sses the ruser variable set up earlier |
| 'Set an active connection to the Connection object using a DSN-less connection | connection string used rather than DSN |
| strMDBpath
= Server.MapPath("..\private\car.mdb") conn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strMDBPath |
MapPath method gives absolute route to the database |
| 'Initialise the strsql variable with an SQL statement to query the database | |
| strsql = "SELECT (Make),(Model),(Price),(Mileage),(Colour),(Year),(Description) FROM Cars" | Include here all the fields you want to display |
| 'Open the recordset using current connection | |
rsuser.open
strsql,conn,1,2 |
|
| Using a 'Do While' loop we can loop through the recordset | |
| Do While not rsuser.EOF | |
| 'Write the HTML to display the current record in the recordset | Unless you do this you will not see the results of query |
| Response.Write
("<br>") Response.Write (rsuser("Make")) Rem: Do the same for each field |
Repeat the write statement for each field queried to output results |
| Close the recordset, reset the server objects | |
| rsuser.Close Set rsuser = Nothing Set adoCon = Nothing |