Visual Basic Tips #41


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

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

Proudly presents:
Visual Basic

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


*1. SEARCHVB.COM

The site formerly known as the VB Web Directory has been reborn
as searchVB.com. This site has an excellent selection of 
resources and articles for Visual Basic and related 
technologies. Check it out: 

searchVB 
http://www.searchvb.com/


*2. USE FORWARD-ONLY RECORDSETS WHENEVER POSSIBLE

If you're doing a lot of data manipulation, try to use 
forward-only, read-only recordsets as much as possible. These 
recordsets are optimized for fast access and don't require the 
resource overhead that either static or dynamic recordsets take.
Also, when you're done, be sure to close the recordset and set 
it to Nothing. This will release the resource back to the 
system. Supposedly, the system will automatically clean up these
references, but I always like to explicitly close my objects, 
just to make sure they're closed.


*3. CHECKING AN OBJECT FOR NOTHING

If you need to check an object variable to determine if it is 
Nothing, don't use the equal sign, like this: 

If objVariable = Nothing Then 
... more code 
End If 

Instead, you need to use the Is operator, like so: 

If objVariable Is Nothing Then 
... more code 
End If 


*4. DOING STRING COMPARISONS 
 
In a previous tip, I talked about comparing strings and how an
uppercase string is not normally equal to its lowercase 
equivalent. I suggested that you use either the Lcase function
or the UCase function to switch the case of both strings. 
However, there's an easier way to do this that I had forgotten 
about: the StrComp function. This function allows you to 
compare two strings in either binary (exact case-sensitive) or
text (case-insensitive) mode. Here's an example: 

Debug.Print StrComp("test", "TEST", vbTextCompare) 

This will return True, since text comparisons are 
case-insensitive. You can also use vbBinaryCompare if you want 
an exact comparison.


*5. SWITCHING FROM ACCESS TO SQL SERVER 
 
Some of you may have Access databases on your Web servers providing 
some sort of dynamic content or storage facilities for data. A 
question I received asked when you should convert to SQL Server. There 
are several reasons to convert to SQL Server: First, SQL Server can 
better handle large amounts of data. Access databases tend to bog down 
when the files get over 50-100 MB, whereas SQL Server databases can 
easily handle that much data and far more. Another reason to convert 
to SQL Server is performance--SQL Server operates in a different 
manner than Access, allowing requests to be handled much faster and 
more efficiently. SQL Server databases can also be backed up without 
having to take down the server--which means you'll get better uptime. 

SQL Server is a more reliable platform for critical applications like 
e-commerce because it has the capability to commit and roll back 
transactions at any point. If your system crashes, you can get all the 
transactions that had committed to that point without losing a great 
deal of data. 

Finally, SQL Server is a lot easier to administer remotely. Instead of 
having to download and upload the entire database every time, SQL 
Server lets you make all your changes via Enterprise Manager. For that 
reason, if nothing else, I prefer SQL Server to Access in almost all 
cases. 

Note to all you Oracle fans: Oracle has all the same capabilities as 
SQL Server mentioned above. In fact, it is still the choice for most 
major e-commerce sites on the Web. SQL is catching up, but Oracle has 
always been able to deal with big databases better than could SQL 
Server. 

In case you can't afford SQL Server, you might want to look into 
Microsoft's Data Engine (MSDE). This offers a combination of SQL 
Server's reliability with a low cost (actually, free). For more 
information on MSDE, you can download from Microsoft's site a white 
paper that discusses the differences between Access and MSDE: 

http://www.microsoft.com/sql/productinfo/Access2000Jet&MSDE.doc


*6. CAN'T PUT A LABEL ON ANOTHER CONTROL 
 
One problem you might run into when using Label controls is that
you can't move a Label control on top of a "windowed" control.
Most controls are considered to be windowed controls. The best 
solution is to use a control like a PictureBox, put the label 
inside it, and then layer the PictureBox as necessary.


*7. FILE FORMAT WEB SITE

Ever wonder what the format of an MP3 file looks like? What 
about an AVI? At the MyFileFormats.com site, you can find this
out, as well as explore hundreds of other file formats that you
may have forgotten. I personally liked the old Commodore 64 file
formats shown on this site... brings back some good memories. 

My File Formats 
http://www.MyFileFormats.com/ 


*8. USING THE END STATEMENT

One statement that you need to avoid using frequently is the 
End statement. The End statement stops your program immediately,
which means that any memory associated with your application is
not properly released. The best way to stop an application is to
unload all your forms, and then in the Form_Unload event of the
last form to be unloaded, use the End statement. That will 
prevent "phantom" programs that have no visible portions but 
are still running.


*9. RESIZING FORMS AND REPOSITIONING CONTROLS

One of the most frequently asked questions about Visual Basic is
how you allow a form to be resized and have all the controls 
adjust themselves automatically. Guess what? You can't do it...
not automatically, anyway. You do, however, have a couple of 
options: The interesting approach is to do it yourself. Using a
simple methodology, it's not hard to do on forms where this 
approach makes sense. (For example, on a form designed for 
writing, such as a "Notes" or "Description" form, it makes sense
to allow resizing.) As the user enlarges the form, you change 
the height and width of the box to fit within the form borders.
Here's a quick bit of code that changes the size of txtNotes to
fit within the form edges, minus a small margin: 

txtNotes.Height = Me.ScaleHeight - (2 * txtNotes.Top) 
txtNotes.Width = Me.ScaleHeight - (2 * txtNotes.Left) 

In this code, Me refers to the form on which this control is 
located. This code uses the margin defined by the upper left-hand
corner as the margin to use on the other side, too. This means 
that if your leftmost point of the text box is at 60 and the form
is 1200 wide, the text will be 1080 wide (1200 - 2 * 60). Same 
thing goes for the height, using the Top property as the 
top margin. 

While this technique is pretty simple, it gets tricky if you 
have other controls, such as command buttons, on the form. For 
example, I like to keep my command buttons centered in the 
bottom part of the form, which means that while the width of the
text box can use the same formula, the height has to account for
the height of the command buttons. Here's how you could change 
the code to handle this scenario: 

txtNotes.Height = Me.ScaleHeight - (2 * txtNotes.Top) - 
cmdOK.Height - txtNotes.Top. 

This code tells the text box that it has to account for three
blank spaces: one above the box, one below the box, and one 
below the command buttons. The command buttons also have to be 
repositioned based on the bottom of the text box, like so: 

cmdOK.Top = txtNotes.Top + txtNotes.Height + txtNotes.Top 

The easier way to do all this is to look into a commercial 
resizer control. They're a little tricky to get set up 
initially, but once you're done, they handle all this work for 
you. Another option is to not allow resizing at all. Most 
windows, such as options dialog boxes and most other dialog 
boxes, don't need to be resized by the user. However, for the 
few that need this capability, one of these options should take 
care of the problem.


*10. BUILDING A DATE VARIABLE 
 
One thing I do frequently is build up date values using string 
concatenation, like so: 

Dim dteNew As Date 

dteNew = CDate(Month(Now) & "/1/" & Year(Now)) 

This creates an entry for the first day of the month and 
converts it to a date. An easier way to do this uses the 
DateSerial function. Here's how it works: 

dteNew = DateSerial(Year(Now), Month(Now), 1) 

You give it a year, month, and day, and this function puts it 
all together into a date variable. It's a bit easier to read and
prevents errors. A similar function exists for creating time 
values: TimeSerial. It takes hours, minutes, and seconds and 
creates a time value in Visual Basic's internal format.
