TechnowledgeBase

Part One: Making the ADO Connection

Usually, when a novice programmer hears the word “database” or “network” they break out in hives. They have the mistaken impression that connecting to a database, either locally or over a network, is difficult to do or involves some kind of esoteric commands that only more advanced programmers know how to use. Not true. The various flavors of Visual Basic have made this as easy as… well… reading this blog.

We’ll be using an ActiveX Data Object, which is commonly abbreviated as “ADO”. The process is called “Creating an ADO connection” to a database. Let’s jump right into the code, shall we?

First you declare a variable that we’ll use to refer to the database connection:


Dim objCN


This is no different than declaring something simpler, like an integer. Right after we tell the computer what name we’re going to use (objCN), we initialize it. Since the ADO connection is an OBJECT, we create it with the SET command instead of simply assigning it a value. So:


Set objCN=CreateObject("ADODB.Connection")


So far, so good. We’re about a third of the way to doing something useful.

The next step is to OPEN the connection. ADO objects have an Open method that we’ll be using in a few seconds, but first… THINK about what we’re about to do. We’re going to connect to a database and run a query, right? Does it seem logical that we’ll need some information before we do that? Information like, ohhhhh I dunno:

• The name of the database server?
• The name of the database?
• What KIND of database server it is (Oracle, SQL, an Excel Spreadsheet)?
• A username and password for the database?
• A query that you need to run?

So maybe now would be a good time to gather up all that info, because we’ll need MOST of it in the very next line of code. And here is the very next line of code:


objCN.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=YYY;Initial Catalog=IR34;Data Source=MWASHINGTON"


WHOA!
Okay, don’t panic. The actual command being issued here is just the first part: “objCN.Open” But what’s that long string of gibberish behind it? That’s your connection string; it contains all the information you gathered earlier. Generally, you shouldn’t try to craft these strings by hand if you’re new at this. Either someone should give the string to you, or you can use a simple trick with a text file to get Windows to make it for you. But let’s dissect this madness just so you can see howscary it isn’t:

The string starts off with

Provider=SQLOLEDB.1

That’s just shorthand for “Hey, I want to connect to an SQL server” …as opposed to an Oracle server, or an Access database. If you were connecting to something other than an SQL server, then obviously something else would go here.

Then you have a chunk that says “Persist Security Info=False”
This tells the ADO connection to discard the username and password AFTER it is done with them. Setting this to TRUE means that somewhere, somehow, some other chunk of code has the ability to retrieve that password from the connection object. How to do that is beyond the scope of this tutorial, so just accept the fact that it’s generally good practice to set this baby to FALSE.

Next:
USERID=sa;Password=YYY

Self explanatory. If you are using Windows integrated security, then instead of these two values, you might find something else here, like:

Integrated Security=SSPI

That means that windows will use whatever username/password you logged onto the machine with to connect to the database (assuming the database is set up to accept such a connection). Moving on:

Initial Catalog=IR34;Data Source=MWASHINGTON

These are the name of your database and the name of the database server, respectively.

Remember: Try to get someone to GIVE you a connection string, or use a tool/trick/shortcut to craft one automatically. Creating one by hand is opening yourself up to errors and frustrations that you, as a novice, do not want. Let us move on.

So we’ve used the OPEN command and passed it a long string containing our connection information. If the connection succeeded, then are now able to run queries. Umm, what if it doesn’t succeed. This is where you need to code some kind of error checking. There are lots of ways to do that, however, a simple if statement using err.number can usually do the trick-


objCN.Open connectionstring
if(err.number<>0) then
‘connectionfailed!
else
‘runthequery!
end if


We’ll assume the connection succeeded and move on to running the query… in Part Two!

Popularity: 17% [?]

This entry was posted on Tuesday, June 12th, 2007 at 7:00 am and is filed under VBScript. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.