Microsoft KB Archive/299980

= How To Hand Code an ADO Data Connection in ASP =

Article ID: 299980

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Active Server Pages 4.0

-



This article was previously published under Q299980





IN THIS TASK
SUMMARY
 * The Connection String
 * Putting the Connection String to Work
 * Code Explanation
 * Troubleshooting

REFERENCES



SUMMARY
This step-by-step procedure demonstrates how to hand code an ActiveX Data Objects (ADO) data connection in an Active Server Pages (ASP) page.

For the purposes of this article, the sample to follow uses the Northwind sample database and establishes an ADO connection to both Microsoft Access and Microsoft SQL Server 7.0 or 2000.

back to the top

The Connection String
The connection string is simply a set of string values that contain your database connection information. For example, the following string points to the Access database: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Mydatabase\Northwind.mdb where &quot;C:\Mydatabase\Northwind.mdb&quot; represents the source path, which you should modify to reflect the correct path information to the Northwind database.

The following string points to SQL Server: Provider=SQLOLEDB;Server=Your_Server_Name;Database=Northwind; & _ UID=your_user_account;PWD=your_password; where you should modify the Server, UID, and PWD parameters to reflect the correct server name, user name, and password respectively for the database.

back to the top

Putting the Connection String to Work
This section presents sample code to get a better idea of how this works.

 From the Windows Start menu, point to Programs, point to Accessories, and then click Notepad.  Highlight the following code, right-click the code, and then click Copy. In Notepad, click Paste on the Edit menu to paste the following code into Notepad:   ADO Open Method  

ADO Open Method   </li> In Notepad, click Save on the File menu. Save the document with the file name Adopage.asp to the default Web site on your server, which is typically located at C:\InetPub\Wwwroot.</li> To view the page, start your Web browser, and type the HTTP location of the page in the Address bar. If you saved the file in the above-mentioned location, type http://<servername>/Adopage.asp in the Address bar.</li></ol>

back to the top

Code Explanation
<ul>  In the preceding sample code, note the following code: src = &quot;C:\Database\Northwind.mdb&quot; sConnStr = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & src ' Uncomment the next two lines and comment the above line to switch between Access and SQL. 'sConnStr = &quot;Provider=SQLOLEDB;Server=Your_Server_Name;Database=Northwind;&quot; & _ '&quot;UID=your_user_account;PWD=your_password;&quot; This code is currently set to connect to an Access database. To connect to a SQL Server database, comment out the line for the Access provider and uncomment the SQL Server lines. To comment out a line, insert an apostrophe (') at the beginning of the line. To uncomment a line, remove the apostrophe from the beginning of the line. The revised code appears as follows: src = &quot;C:\Database\Northwind.mdb&quot; 'sConnStr = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & src ' Uncomment the next two lines and comment the above line to switch between Access and SQL. sConnStr = &quot;Provider=SQLOLEDB;Server=Your_Server_Name;Database=Northwind;&quot; & _ &quot;UID=your_user_account;PWD=your_password;&quot; </li>  In the following line of code, you actually begin the process of creating the ADO connection: Set OBJdbConn = Server.CreateObject(&quot;ADODB.Connection&quot;) Then, you execute the Open command and pass your connection string as follows: OBJdbConn.Open sConnStr </li></ul>

back to the top

Troubleshooting
When you use ADO in ASP, make sure that you: <ul>  Always close recordsets and connections. For example: rs.close set rs=nothing conn.close set conn=nothing For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

176056 INFO: ADO/ASP Scalability FAQ

</li> Open late, and close early. Open ADO objects just before they are needed, and close them right after you are done. This frees resources while other logic is processing.</li> Do not create ADO objects in session variables. This effectively bypasses MTX connection and thread pooling. If threads are not pooled, each object that is created for each user can tie up a thread. Also, if the object is not specifically closed, a session object variable can live and tie up a thread for the life of a session (the default is 20 minutes after last click).</li> Do not pass parameters to the Command object in the execute statement.</li> Instantiate objects with the Server.CreateObject method if you are not marshalling data through a firewall. Server tells Microsoft Transaction Server (MTS) to create the object in a transaction server package so that resources are pooled.

NOTE: This does not apply to Microsoft Internet Information Services (IIS) 5.0 or Microsoft COM+.</li> Do not re-use Recordset or Command variables; create new ones.For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

197449 PRB: Problems Reusing ADO Command Object on Multiple Recordsets

</li></ul>

back to the top

<div class="references_section">