Visual Basic Tips #24


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

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

Proudly presents:
Visual Basic

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


*1. READ PROPERTIES SPARINGLY                   
          
If you're looking at property values of objects, such as COM or ADO 
components, store the values in variables so that you don't have to 
read them more than once. Each property value read is expensive, in 
terms of computing resources. If you're doing it frequently, you're 
eating CPU cycles that could be better used elsewhere. A temporary 
variable allows you to reuse the property values without having to 
reread the object.


*2. BUILDING SQL STATEMENTS                   
          
If you are building, within your Visual Basic code, SQL statements 
that have data that may contain single quote characters, be sure to 
replace every single quote within two single quotes. This is really 
easy to do with the new Replace function. Simply run the Replace 
function on each data value you're appending to your SQL query, as 
shown in this example: 

strName = "Mrs. O'Leary" 
strSQL = "SELECT * FROM Emp WHERE Name = '" & Replace(strName, "'", 
"''") & "'" 

Both Access and SQL Server will properly handle the single quote as 
long as it is marked in this manner. Remember that single quotes have 
to surround the text literal you're using in your SQL statement, so be 
sure not to just replace all the single quotes in your SQL statement 
with two single quotes--you'll end up with a mess.


*3. WRAP LONG LITERALS                   
          
If you're building a long string, remember that you can't simply wrap 
your string onto multiple lines. You can, however, use the line 
continuation character to break a long line into multiple, smaller 
lines, as shown here: 

strLongString = "This is a long string that goes on and on without any 
end in sight." 

strShorterPieces = "This is a long string" _ 
      & " that goes on and on" _ 
      & " without any end in sight." 

Be sure to include spaces either at the end or beginning of sections, 
because the continuation character won't add them for you 
automatically. (Breaking a long string like this makes it easier to 
read since the user doesn't have to scroll horizontally.)


*4. EXECUTING STORED PROCEDURES                   
          
If you have an Oracle or SQL Server stored procedure that you want to 
execute, the quickest way is to use the Execute method of the ADO 
Connection object. Here's some sample code: 

Dim dcnDB As New ADODB.Connection 
dcnDB.ConnectionString = "your connection string goes here" 
dcnDB.Open 
dcnDB.Execute "sp_DoSomethingStoredProcedure" 

This particular stored procedure does not return values; rather, it 
simply runs and exits. 


*5. CUSTOM ERROR NUMBERS                   
          
When defining your own error numbers in Visual Basic applications or 
COM components, remember to add the constant vbObjectError. Adding 
this value to your error values will ensure you stay clear of the 
reserved numbers that Microsoft products are already using. You can 
either use the built-in messages for errors lower than this number or 
create your own messages for errors higher than this value. 


*6. ODBC and ADO                   
          
A couple of readers have asked how the ODBC Control Panel applet 
figures into using ADO. The short answer is that for most databases, 
it isn't necessary. ADO can talk directly to many types of databases, 
including Oracle, SQL Server, Access, and more, without requiring any 
changes to ODBC on each machine. ODBC can be used if you have an ODBC 
driver for your database but don't have an OLE DB provider. In this 
case, you'll use the OLE DB provider for ODBC, which in turn can talk 
to your ODBC database. You'll need to make the appropriate ODBC 
entries in this case, but not under normal circumstances.


*7. REMEMBER TO SET COMMANDTYPE                   
          
Recently, I ran into a problem involving an ADO Command object. I was 
trying to use a stored procedure with SQL Server, but after setting 
everything--including the parameters--I was getting no results. Of 
course, the error messages didn't make any sense, either. After 
reviewing the code, I realized I hadn't set the CommandType property 
of the Command object. Once I set that, things worked properly. Here's 
the complete code: 

Set cmdQuery = New ADODB.Command 
With cmdQuery 
   Set .ActiveConnection = dcnDB 
   .CommandType = adCmdStoredProc 
   .CommandText = "sp_KeywordSearch" 
   Set parItem = .CreateParameter("Keyword", adVarChar, _ 
      adParamInput, 255, txtKeywords.Text) 
   .Parameters.Append parItem 
End With 
Set rsData = New ADODB.Recordset 
rsData.Open cmdQuery, , adOpenStatic 

In this case, the stored procedure is named sp_KeywordSearch and takes 
a parameter called Keyword. The parameter is created and then appended 
to the Command object. When you open the recordset object, instead of 
specifying a SQL string you supply a Command object.


*8. FINDING OLE DB PROVIDERS                   
          
When you're using ADO, you have to use an OLE DB provider to access 
the database. If you're using a database that doesn't have an OLE DB 
provider, you can get one from several companies. Microsoft maintains 
a site with all of the third-party OLE DB providers that are 
available. Here's the URL: 

http://www.microsoft.com/data/partners/products.htm


*9. CLEANING YOUR SQL DATA                   
          
One big problem many people encounter when writing SQL within Visual 
Basic is dealing with data supplied by users. For example, you may 
have some code that looks like this: 

Dim strSQL 
strSQL = "SELECT * FROM Customers WHERE CompanyName = '" _ 
   & Request.Form("Keywords") & "'" 

This code will return all the customers where the CompanyName field is 
equal to whatever was input via the Keywords field on a form. Simple 
enough, you'd think. But a problem arises when you have a name with a 
single quote in it, such as the name O'Reilly. The single quote 
character breaks the SQL statement. 

In a situation like this, the easiest thing to do is "clean" the input 
data before putting it into the SQL statement. Here's a quick function 
you can use, which in turn employs the SQ constant that holds a single 
quote. 

Const SQ = "'" 
Function Clean(strData As String) As String 
   Clean = Replace(strData, SQ, SQ & SQ) 
End Function 

The original chunk of code looks like this with the Clean function in 
place: 

Dim strSQL As String 
strSQL = "SELECT * FROM Customers WHERE CompanyName = '" _ 
   & Clean(Request.Form("Keywords")) & "'"


*10. DETERMINING HOW MANY RECORDS ARE AFFECTED                   
          
Using the ADO Connection object, you can use the Execute method to run 
queries. However, it's often helpful to know how many new records were 
created, updated, or deleted. Fortunately, you can do this by simply 
passing a variable into the Execute method as the second parameter, in 
which case ADO will fill that variable with the number of records that 
were touched. Here's an example: 

Dim cnDB As New ADODB.Connection 
Dim lngRecords As Long 
cnDB.ConnectionString = "some connection string" 
cnDB.Open 
cnDB.Execute "UPDATE Products SET UnitPrice = UnitPrice * 1.05", 
lngRecords 

The variable lngRecords will hold the number of records that were 
updated.
