DT 221 3 Internet Application Development Active Server Pages

DT 221/3 Internet Application Development Active Server Pages & Database Connection

Outline of ASP Database Connection Active Server Pages can communicate with databases via ADO (Active. X Data Objects). 1) 2) 3) 4) 5) 6) ADO Object Model Database Connection Record Set Query Example Update & Insertion Example An Application Example

ADO Object Model 1) 2) 3) 4) 5) ADO is a Microsoft technology ADO stands for Active. X Data Objects ADO is a Microsoft Active-X component ADO is automatically installed with Microsoft IIS ADO is a programming interface to access data in a database 6) ADO can be accessed from within your Active Server Pages

Relationship among the Objects

Accessing a Database from an ASP Page The normal way to access a database from inside an ASP page is to: 1) Create an ADO connection to a database 2) Open the database connection 3) Create an ADO recordset 4) Open the recordset 5) Extract the data you need from the recordset 6) Close the recordset 7) Close the connection

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 SQL database on your web site. If you have a database called "northwind " located in your SQL Server, you can connect to the database with the following ASP code:

Database Connection, cont 1) Create a file called “ADOConnection. asp” and type in the code below <% dim connobj dim connstr = "Provider=SQLOLEDB; Data Source=My. Server; Database=Northwind; User Id=North. User; Password=abc; " set connobj = Server. Create. Object("ADODB. Connection") connobj. Open (connstr) connobj. Close set connobj = nothing %> 2) The ADO Connection object is used to create an open connection to a data source. Through this connection, you can access and manipulate a database.

Create an ADO Table Recordset 1. Create the file “ADORecordset. asp” and type in the code below <% dim connobj dim connstr dim recobj connstr = "Provider=SQLOLEDB; Data Source=MYSERVER; Database=Northwind; User Id=North. User; Password=abc; " set connobj = Server. Create. Object("ADODB. Connection") connobj. Open (connstr)'this part create and assign the recordset. set recobj = Server. Create. Object("ADODB. Recordset") 'open the table customer using connobj as the default connection. recobj. Open "Customers", connobj Response. Write("Table Customers opened. ") connobj. Close

Extract Data from the Recordset 1. Create a file called “ADORecordset 2. asp” and type in the code below: <% dim connobj dim connstr dim recobj connstr = "Provider=SQLOLEDB; Data Source=MYSERVER; Database=Northwind; User Id=North. User; Password=abc; " set connobj = Server. Create. Object("ADODB. Connection") connobj. Open (connstr) 'this part create and assign the recordset. set recobj = Server. Create. Object("ADODB. Recordset") 'open the table customer using connobj as the default connection. recobj. Open "Customers", connobj Response. Write("Table Customers opened. ")

Extract Data from the Recordset, cont for each x in recobj. fields response. write(x. name) response. write(" = ") response. write(x. value & " ") next connobj. Close recobj. Close set recobj = nothing set connobj = nothing %>

Closing Recordset and closing Connection 1) After using the recordset and connection object, it is important to properly close and destroy the objects. 2) Below is the 4 lines necessary to perform such operations. 3) <% Recobj. close Connobj. close Set recobj = nothing Set connobj = nothing %>

Database Connection Examples 1) ODBC source connection example: Set my. DBConnection = Server. Create. Object("ADODB. Connection" ) Conn. Str = “DSN=airline db; uid=cis 8490; pwd=test” my. DBConnection. open conn. Str 2) OLE connection example Set my. DBConnection = Server. Create. Object( "ADODB. Connection " ) my. DBConnection. Open “PROVAIDER=SQLOLEDB; DATA SOURCE = yourserver; UID=sa; PWD=secret; DATABASE=Pubs ” 3) ODBC Driver For Trusted Connection security connection example Set my. DBConnection = Server. Create. Object("ADODB. Connection ") my. DBConnection. Open "Driver={SQL Server}; " & "Server=My. Server. Name; " & "Database=my. Database. Name; " & "Uid=; " & "Pwd=; "