Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Introduction to the ADO Connection Object

By Andy Warren, (first published: 2001/11/08)

ADO (Active Data Objects) is really the way to access data right now. It's pretty much replaced RDO and DAO for new projects. ADO.Net is on the way, but not quite here yet. Whether you're a DBA or a developer, ADO is something you need to know. This week I'm going to explore the Connection object, in upcoming articles I'll also provide some introductory coverage of both the Recordset and Command objects.

If you have VB or Access, you'll find those environments are great productivity boosters - the strongly typed variables, syntax checking, online help, all help you work faster and learn faster. If you're using VBScript, you can still make it work, it may just take you a bit longer to get there.

If you have VB/Access, set a reference to 'Microsoft Active Data Objects 2.6'. If you don't see it, you may see older versions like 2.5 or even 2.1. I'd encourage you to apply the latest MDAC to get the bug fixes, but it's not critical to the examples that follow. 

As the name implies, you'll be working with objects, which means using the set statement. Here is how to begin using the most basic object in ADO, the connection object, using VB:

dim oConnection as adodb.connection

set oConnection = new adodb.connection

Or in VBScript:

dim oConnection

set oConnection=createobject("adodb.connection")

You've just "instantiated" the object. Before you can actually do anything with it, you need to "open" it, like this:

oconnection.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=eg;Application Name=TestApp"

New developers often find getting the connection string correct the hardest part of using ADO. Just looking at it, you'll see the 'provider' is the SQLOLEDB provider - you'll definitely want to use this one when connecting to SQL. Initial Catalog is the same concept as default database. Setting the application name is a great best practice - ever run sp_who2 and see a blank program name? That's because the programmer didn't take the time to add this to the connection string. The order of the items in the connection string doesn't matter, the only time case matters is for the password if you're providing one or the server/database is case sensitive.

Still, aside from doing the copy and paste thing, it's a lot to remember when you're trying to right a quick 10 minute utility app. Try this trick to always get it right. Create a empty Notepad file on your desktop called connection.txt. Rename it to connection.udl. Double click it. You'll be in the UDL designer, possibly even better than sliced bread. Click on the provider tab to get started, then select 'Microsoft OLE DB Provider for SQL Server'.


Now click on the connection tab. Either type in your server name or pick it from the drop down list. Move down and select how you want to connect - NT if you're using a trusted connection (lan login) or the specific name/password if you're using a SQL login. If you're using the SQL login option, go ahead and check the box called 'Allow Saving Password'. Move on to step #3 and pick the database, and then click Test Connection - you should get a 'OK' if it worked.

Now skip the advanced tab (you can explore this later!) and move to the All tab. Highlight application name and click the Edit Value button. For this article I'm using the value of TestApp.

Click the OK button. Rename the connection.udl file to connection.txt, then double click to open. You should see something like this:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=eg;Application Name=TestApp

Look familiar? All you have to do is cut and paste that line into your code. No syntax errors, no trying to figure out if the password is wrong. Now let's actually DO something with this connection!

Connection objects can only execute "action" queries (insert, update, and delete). Here are some examples:

oConnection.execute "Delete from Customers_History"

oConnection.execute "Truncate table Customers_History"

oConnection.execute "Insert Into SomeTable (CategoryID, CategoryName) values (1,'ADO Articles')

oConnection.execute "Update SomeTable set CategoryName='ADO Articles & Tips' where CategoryName='ADO Articles'

You can also do cross database actions just like you would in Query Analyzer:

oConnection.execute "Truncate table Pubs.dbo.Publishers"

You can also execute stored procedures, in this example passing the parameter '5' to the custOrderHist stored procedure:

oConnection.execute "custOrderHist 5"

When you're done, you always want to do a good clean up to avoid memory leaks and to release the connection.

oConnection.close

set oConnection = nothing

Not too bad so far is it? Next week we'll talk about how to begin using the recordset object which will leverage what we covered in this article. As you test these, please do so in a test environment! As always, I'd love to hear any comments or questions you have about the article - just click the 'Your Opinion' tab below.

Total article views: 30956 | Views in the last 30 days: 11
 
Related Articles
FORUM

DB@ Linked Server - Cannot initialize the data source object of OLE DB provider "IBMDASQL"

Cannot initialize the data source object of OLE DB provider "IBMDASQL"

FORUM

SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - C...

BLOG

Clicking on Links in Email

Every security awareness presentation makes the warning about opening attachments or clicking on lin...

FORUM

provider: TCP Provider, error: 0 ...

provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote

Tags
ado    
miscellaneous    
programming    
stored procedures    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones