Visual Basic Tips #37


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

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

Proudly presents:
Visual Basic

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


*1. DON'T USE RESERVED WORDS                   
          
A reader recently passed on a reminder to the rest of us to not use 
reserved words. While we know this in Visual Basic--since VB will 
generally catch that type of syntax error--the suggestion was to make 
sure you're not doing it in your database tables. The story he related 
was that one of his fields was named ALL, which is a reserved word in 
SQL. There are some less-used keywords in SQL that can trip you up 
like this. If SQL starts complaining about a table or query, first 
check to make sure your fields and table names aren't using keywords. 
The syntax highlighting provided by Enterprise Manager, for instance, 
can help you detect these bugs.


*2. WORKING WITH MP3 FILES                   
          
If you're like me, you probably have a few music files that are in the 
MP3 format. A number of people have asked me recently about using this 
format to write an encoder or decoder. The best source of information 
is the original source of the MP3 format: the Motion Picture Experts 
Group. I would also recommend the more unofficial MPEG.org site. Both 
sites offer lots of code listings that you can adapt for use within 
Visual Basic to read and write MP3 files. 

Motion Picture Experts Group 
http://www.cselt.it/mpeg/ 

MPEG.org 
http://www.mpeg.org/


*3. SEARCHING FOR APOSTROPHES                   
          
This question comes up a lot: How do you search within SQL for a name 
like O'Brien? The problem is that the single quote character is used 
to delimit strings within SQL. The solution is to search for two 
single quotes. If you put this in your query, your database will 
translate those two single quotes into the single quote for which 
you're searching. This is a common problem in SQL Server, Oracle, and 
many other databases.


*4. ADDING CARRIAGE RETURNS                   
          
When writing code that exports to a plain text file, it's often 
necessary to embed special characters, such as carriage returns, line 
feeds, and tab characters. Visual Basic has special constants defined 
for many of these characters: 

VbTab--Tab character 
VbLf--Line feed 
VbCr--Carriage Return 
VbCrLf--Carriage return/line feed combination 

These constants are available in VB 6 and, I believe they are also 
available in VB 5. Prior to that, I'm not sure. However, you can 
easily replace the control code using the Chr (or Chr$) function: 

vbTab = Chr(9) 
vbLf = Chr(10) 
vbCr = Chr(13) 
vbCrLf = Chr(13) & Chr(10)


*5. BUILDING A TEXT EDITOR                   
          
I'm currently writing an article series at the Ask the VB Pro site. 
These articles show you how to build a text editor and add all the 
common features found in many other applications. As of this writing, 
the first two parts are available, and four more are under way. If 
you're looking for instruction on how to work with basic file 
operations, menus, and so on, take a look at these articles: 

Building a Text Editor, Part I 
http://www.inquiry.com/techtips/thevbpro/10_minute_solutions/10min0400es.asp 

Building a Text Editor, Part II 
http://www.inquiry.com/techtips/thevbpro/10_minute_solutions/10min0500es.asp


*6. USING VARIANTS IN COM COMPONENTS                   
          
If you're building COM components for your Web applications, remember 
that VBScript knows only one data type: Variant. This has two 
implications for COM components. The first involves parameters you 
might have to supply to a function or subroutine. If your COM 
component is set to accept a Long, for instance, you'll have to use 
the CLng function on the input value before passing it. This is fine, 
but an easier way to handle this is to accept a Variant and do any 
conversions after the value is inside the COM component. This has the 
added benefit of keeping all the conversion code in one place. Also, 
remember to do any necessary validation on the input.  

The other implication involves return values from functions. While you 
might want to return an ADODB.Recordset object to your code, for 
instance, you should use a return type of Variant. You'll still be 
able to return the object, and VBScript will know how to handle the 
object, since you're using the Set statement to store the result in a 
Variant variable.


*7. PACKAGING AND DEPLOYMENT INSTALLATION ERRORS                   
          
A number of people have reported problems with the Packaging and 
Deployment Wizard under Windows 2000. The good news is that some of 
these errors have been corrected via the Visual Studio Service Pack. 
The current service pack for VS is SP 4, and it's available at this 
URL: 

Visual Studio Service Pack 4 
http://msdn.microsoft.com/vbasic 

I'd recommend at least checking out what SP4 fixes--it might take care 
of any problems you're having.


*8. HANDLING DUPLICATE FIELDS IN A TABLE                   
          
A user asked me how best to handle the following database table 
structure that he had created: 

   Table: Invoices 
   PartNumber - 1 to 15 
   Quantity - 1  to 15 
   Description - 1 to 15 

The user wasn't sure whether he should use a few arrays or some other 
structure to maintain the data. 

The quick answer is that a table like this is not the best design for 
a relational database table. The idea of a relational database is to 
eliminate duplicate fields from a table. Instead of holding space open 
for 15 possible rows of data, it's better to break the data into two 
tables: The first contains the "singular" information about the 
invoice, such as the number, date, shipper, etc.; the second contains 
each of the line items for the invoice. That table might look like 
this: 

   Table: InvoiceLines 
   PartNumber 
   Quantity 
   Description 

This structure has the benefit of not wasting space for invoices that 
have less than 15 rows and gives the ability to have more than 15 rows 
for invoices that require the extra space.


*9. PROCEDURE TOO LARGE MESSAGE                   
          
Well, this is a new one for me: A user said that he had a procedure 
with approximately 4,000 ListBox AddItem lines in it and was getting 
an error from Visual Basic about the procedure being too long. 

I personally don't remember VB having a limit on the number of lines 
in a procedure, but this guy found it. The first thing I would suggest 
is that the data (entered manually) be put into a database. The 
developer could then query the database and return as many rows as 
necessary for the application. 

The point of this is that if you're manually entering more than a few 
data values, that data should be put in some sort of permanent storage 
mechanism OUTSIDE the application. This includes files, databases, XML 
files, or whatever you want--just don't put it directly in the 
application.


*10. INT VS. CINT                   
          
You're probably familiar with the Int() function, which returns the 
integer portion of a value. Similarly, the CInt() function does much 
the same thing. The main difference between the two functions is the 
data type of the value it returns:

- Int() returns the same data type as the value it's passed. 
- CInt() always returns an Integer data type. 

In addition, CInt rounds the value given to it, while Int truncates 
the decimal portion.
