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
|
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
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] |
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.
Last
week I posted an article
that covered the basics of the main object in
The first thing we need to talk about is when do you use a recordset versus a connection object? You'll use a recordset when you need to return records to the client - any time you need the results of a select statement. You use a connection object (or the command object as we'll cover next week) to execute 'action' queries. Where it may get a little confusing is that for a recordset to work, it has to have a connection! There are two different ways to do this, the first builds on what we did last week, instantiating a connection object first:
|
dim
cn as adodb.connection |
As you can see, our first step is to get the connection object. Next we instantiate the recordset object, then 'open' it by passing a sql select statement as the first parameter and an open connection object as the second parameter. This is the most common way, since normally you will reuse a connection object several times before closing it.
If you just need a recordset, you can use this abbreviated method:
|
dim
rs as adodb.recordset |
Did I mention there are different types of recordsets? Each recordset has a cursor type (static, dynamic, forward only, or keyset). Sounds like our friend the T-SQL cursor! If you understand the good and bad of T-SQL cursors, you're well on your way to deciding which cursor type to use in a recordset. We'll talk about this some more in a minute. While you're deciding on cursor type, you also have to think about the lock type - again, you get four options: optimistic, batch optimistic, pessimistic, and read only. And finally, we have to backtrack just a bit to consider one final, critical option - the cursor location. For each connection or recordset object, you have the option of setting a 'server' cursor or a 'client side' cursor. Here are some captures right from VB showing the options and how they work in the development environment:
I know that's a lot to put in one paragraph. We're not going to cover all the possible variations, I just want you to know they are there. I'm going to offer two configurations to get you started, then you can experiment and do some additional reading as your skill grows.
The first one is that you JUST need to read the data, not make any changes to it. Maybe to add a list of users to a listbox, or display a list of order details in a web page. You should use a cursor type of forward only, a lock type of read only, and a location of server. This will give you the fastest results with the least amount of locking. This is also known as the 'fire hose' cursor since the server just streams the data to you (the recordset) as quickly as it can, then it's done. This is such a common thing that it is actually the default. If you create a recordset and omit the cursor type and cursor location parameters, you get a forward only read only recordset.
|
rs.CursorLocation
= adUseServer |
In the second scenario, you need to update the data. For this, I recommend you use a cursor type of static, a lock type of optimistic, and a cursor location of client. Using these options, all the data matching your query will be pulled over to the client. No locks will be held on the rows you selected and there is no guarantee that someone else will not change them while you're working. This gives you the freedom to browse the recordset, sort it on the client, etc, and place NO load on the server.
|
rs.CursorLocation
= adUseClient |
Now that you know enough to decide how to configure your recordset, let's talk about how to use it. Here is a sample that shows iterating through a recordset and adding all the items to a listbox:
|
Do
Until rs.EOF |
Recordsets have two very important properties, BOF (beginning of file) and EOF (end of file). When you first open a recordset that has one or more records, the BOF property will be true, the EOF property will be false. Once you 'movenext' after looking at the last record, the EOF property will be true. It is absolutely critical that you always check for BOF and EOF. Failure to do so can result in the following error which your users will NOT appreciate:
Here is another example, this time I'm concatenating two values and adding both to the listbox:
|
Do
Until rs.EOF |
There is also an alternate syntax you can use for addressing field names which uses the 'bang' operator:
|
Do
Until rs.EOF |
And finally, you can address field names by using the ordinal position. This is probably the fastest way to address a field, but also the most dangerous and least understandable. Any change in your select statement will cause you a LOT of problems if you don't adjust your ordinals. I'm including this because you may see it in code samples, but please do not use this method!
|
Do
Until rs.EOF |
So far we've just been displaying values, now let's look at how to edit and add records. Here is how we would add a record:
|
Do
Until rs.EOF |
Use the AddNew method sets up a blank record. You then assign values to as many fields as you need. To save it, you can either do so explicitly by using the .Update statement, or by executing any .Movexxx operation. If you just want to add records, here is one trick you'll use a lot - in your select statement, add this as a where clause "where 1=0". No records will match, so very little traffic will be generated, but you still get the data structure that allows you to add records.
Finally when it's time to edit records, it works almost the same way:
|
Do
Until rs.EOF |
Notice
that there no .Edit statement. It's not required in
|
rs.Close |
Once
you use this a couple times, it's comfortable and easy to use. Recordsets have
a ton of features to make your life easier, we'll talk about some of those in
the upcoming months. For now, give
In
two previous articles I've done a very basic introductions to the ADO
Connection and ADO
Recordset objects. This week I'd like to talk about the third main object,
the Command object.
Let's jump right in. Here is a short code sample that shows how to execute an update (or insert or delete) query using a command object. For the duration of the article we'll assume you've already got a connection object open.
|
Dim
cmd As ADODB.Command |
As you can see, it works exactly the same way that using the connection execute method does. You can also use it to execute a stored procedure, again using the same syntax I illustrated earlier with the connection object:
|
cmd.execute "usp_whatever" |
If
the connection object can do it all, why use the command object. Probably the
first reason is that you can give
Nice, but not really a compelling argument? Maybe..but so far we're just sending parameters, not leveraging the ability to declare parameters as output so that we can get a value returned to us. Let's take a look at something where the command object really excels, executing stored procedures with parameters. Using either the connection object or the command object, you can do it like this:
|
Dim dteStart As Date |
We're just passing the parameters in the order that they are expected. In this case both parameters are dates, so I'm surrounding each with single quotes (Access developers remember to use the single quote and not the pound sign for date delimiters!).
Here is a full example showing how to use the parameters collection of a command object:
|
Dim cn as ADODB.Connection |
There are a couple key points in this example. The first is the "params.refresh" call. Using refresh does the work for you of populating the parameters collection - for each parameter, you have to provide the name, data type, whether it's input or output. Refresh just queries the server for the parameter info and sets it up for you. This is good for you because it's easy, but generally considered a bad practice because it generates an extra "trip" to the server. We'll talk about alternatives in a minute. Then the next couple lines are where we start to see how having a parameters collection makes the code more readable:
|
' Specify input parameter
values |
ParentID and Description could be parameters of a sub routine, or even properties in a class. The other thing you may have noticed is this line:
|
' Execute the command |
Passing
the adExecuteNoRecords tells
Now let's take another look at the "right" way to use the parameters collection. This involves building all the parameters up in code. Instead of params.refresh, we'll insert this code:
|
' Define stored procedure
params and append to command. |
It's more work up front, but it's the best way to get the maximum performance. After all, isn't that why you wrote the stored procedure in the first place? Finally, as with all objects, be sure to close them when you're done by setting your command object = Nothing.
Coming
up in the next week or two I'll have an article that combines all three of the
main