Visual Basic Tips #21


----------------------------------------------

TipWorld - http://www.tipworld.com
The Internet's #1 Source for Computer Tips, News, and Gossip

Proudly presents:
Visual Basic

----------------------------------------------


*1. CREATING ALTERNATE CURRENCY FORMATS                   
          
A user from India asked me about creating a currency value using the 
Indian format, which looks like this: 99,99,99,999.99. This is easy to 
do using the Format statement. Remember that a pound sign in the 
format represents a digit that will be shown if it is 
significant--that is, not a leading or trailing zero. A zero in the 
formatting expression represents a digit that must always be shown. 
For his particular format, here's how it can be done: 

Debug.Print Format(99999123123.123123, "##,##,##,##,##,##,##,##0.00") 

This statement prints out the value: 99,99,91,23,123.12. If you're 
using standard US notation, you'd use this statement: 

Debug.Print Format(99999123123.123123, "###,###,###,###,##0.00") 

If you have larger numbers, be sure to add format characters to handle 
the length of the value.


*2. WRITING YOUR OWN MMC SNAP-INS                   
          
The Microsoft Management Console, used for managing SQL Server, IIS, 
and most other BackOffice applications, now has a designer available 
for it that lets you build your own snap-ins for this platform. 
There's a new book out from Microsoft Press about it, and there's an 
article at this DevX.com site on the same topic:  

http://www.pcworld.com/r/tw/1%2C2061%2Ctw-vb5-18%2C00.html  

The book covers doing it in both VB and VC, and from the looks of it, 
they've done a great job with the MMC Snap-In Designer for VB. All of 
the commonly used functions are supported by the designer, and best of 
all, the designer is free with the Windows 2000 Platform SDK.  

Microsoft Management Console Design and Development Kit  
Microsoft Press (editors), May 2000  
http://www.amazon.com/exec/obidos/ASIN/073561038X/tipworld


*3. LIST BOX VS. DROP-DOWN LIST                   
          
A common question I get is when to use a drop-down list box versus 
when to use a list box. There are a few considerations to take into 
account. If you have to pick more than one item from the list, a list 
box control is required. If you have more than 50 items, scrolling 
through them in a drop-down list can get tedious and should be 
avoided. If you have a small number of items, it really comes down to 
how much screen real estate you have available. You can create a 
drop-down list and save space, or show a list box and display more 
items. It's really up to you, and there isn't any particular guideline 
other than those already mentioned.


*4. FILTERING A RECORDSET                   
          
If you have a recordset already created and want to filter it by some 
criteria, you can use the Filter property of the recordset to do so. 
Just change the recordset's Filter property to a WHERE clause and then 
set the recordset object to another recordset variable, like this: 

Dim dcnDB As New ADODB.Connection 
Dim objRS As New ADODB.Recordset 
Dim objFilter As ADODB.Recordset 

dcnDB.Open "Some connection string" 
Set objRS = Server.CreateObject("ADODB.Recordset") 
objRS.Open "SELECT * FROM tblOrders", dcnDB, adOpenStatic 
objRS.Filter = "CustomerName Like %Smith%" 
Set objFilter = objRS 

Depending on how big the recordset is, you may want to just do another 
query from the database. It will probably run faster, especially if 
you create a stored procedure to do the work for you. Also, make sure 
you have indexes on the fields that you're using in your filters. 

Remember that you also have the option, with ADO recordsets, to use 
the Save method to save the recordset to disk. Using this makes 
filtering more useful since you can keep a somewhat static recordset 
on disk and use the filtering option to retrieve only the records you 
really need.


*5. REGISTERING MTS COMPONENTS                   
          
In a previous tip, I said that MTS components have to be registered 
before use. As several readers pointed out, this is not entirely 
necessary: You can drag a new, unregistered component into an MTS 
package and the component will be registered for you. If you want to 
add a component to the package, you can select it from a list if it's 
registered. Dragging and dropping is often quicker since you don't 
have to figure out which component is yours.


*6. WORKING WITH STRINGS                   
          
In the following article at Dev-Center.com, you can learn more about 
string processing using Visual Basic. Here's the URL to visit: 

http://www.pcworld.com/r/tw/1%2C2061%2Ctw-vb5-19%2C00.html 

In particular, you'll find some interesting techniques for encrypting 
text in your application.


*7. STACK RECORDSET RETURNS                   
          
If you're getting a bunch of data for your application (such as for 
decodes for drop-down lists), you can retrieve all the data through a 
single stored procedure and through a single call. Here's what your 
stored procedure might look like: 

CREATE PROCEDURE sp_GetAllTheData AS 
SELECT * FROM tblStates ORDER BY StateCode 
SELECT * FROM tblCountries ORDER BY CountryName 
SELECT * FROM tblTitles ORDER BY TitleName 

When you retrieve this, you'll get three recordsets back at the same 
time. Simply open the query as a static recordset, and then use the 
NextRecordset method to assign each recordset to a different variable, 
as shown here: 

Dim dcnDB As New ADODB.Connection 
Dim rsStates As ADODB.Recordset 
Dim rsCountries As ADODB.Recordset 
Dim rsTitles As ADODB.Recordset 

dcnDB.Open "Some connection string" 
rsStates.Open "sp_GetAllTheData", dcnDB, adOpenStatic 
Set rsCountries = rsStates.NextRecordset 
Set rsTitles = rsCountries.NextRecordset 

Because it's more time consuming to do another COM operation than it 
is to just pass more data, this is a more efficient way to pass back a 
large batch of data.


*8. WHICH ORACLE DRIVER TO USE                   
          
In a previous tip, I talked about the Oracle driver that had been 
created by Microsoft when ADO was first released. At the time, it had 
better performance than any driver that Oracle had created. However, 
this situation has since changed. According to several readers, the 
Oracle OLE DB provider has improved dramatically and is now the 
recommended driver for accessing Oracle databases through OLE DB/ADO. 
Some users estimate a 30 percent performance improvement over the 
Microsoft Oracle driver.


*9. HELP WITH HTML HELP                   
          
If you're like most help developers, HTML Help is giving you trouble. 
If you need help with creating it, you can visit the HTML Help Center 
at this URL: 

http://mvps.org/htmlhelpcenter/ 

There are lots of tips and some examples of how to build HTML Help 
format files for Visual Basic. While I'm not a big fan of this format, 
Microsoft is putting its weight behind the format, which means it will 
likely be around for a while.


*10. AVOID THE ADO ADDNEW AND DELETE METHODS                   
          
If you're looking to coax some performance from your application, be 
sure that you're using stored procedures or SQL statements to handle 
all your record additions and deletions. While you can use the AddNew 
and Delete methods of the ADO Recordset, they're quite a bit slower 
than the alternate versions that just use plain SQL or stored 
procedures. Stored procedures that do INSERT statements get additional 
performance since they have to update indexes when the new records are 
created. Accordingly, the execution plan includes this as part of the 
required work.
