Visual Basic Tips #22


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

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

Proudly presents:
Visual Basic

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


*1. 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 reader 
me 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 SQL Server 
can.


*2. PASSING PARAMETERS                   
          
There's always some confusion about passing parameters to subroutines 
and functions in Visual Basic. Whenever you pass a parameter to a 
function, it is (by default) passed by reference. This means that if 
you change the value of the parameter within the subroutine or 
function, it changes the original variable as well. This has good and 
not-so-good uses: If you have a function that needs to update more 
than one parameter (you can use a function to return one value), you 
want to pass the variables by reference. However, if you want to 
prevent this from happening, define the parameter as ByVal, as shown 
here: 

Private Sub Test(ByVal testvar As String) 

You don't have to use ByVal when you call the function--just put it in 
the definition. That will prevent any inadvertent omissions of this 
critical keyword.


*3. WRITING SIMPLE CONDITIONS                   
          
One thing I like to do is to write simple conditions. A student in a 
class I taught recently put it best as "Don't code negatively." A 
perfect example is this statement: 

Do While Not rs.EOF 

I probably typed this a hundred times before I realized I was breaking 
my own rule. This code is more easily read as 

Do Until rs.EOF 

In general, I try to avoid using the Not keyword as much as possible. 
If you have a Not keyword and a condition, you can reverse the 
condition like this: 

Not (x < 5)  is the same as x >= 5 
Not (x > 5) is the same as x <= 5 

Conditions that don't use the Not keyword are typically easier to read 
and don't take as much work to explain to other programmers.


*4. GREAT SITE FOR API CODE                   
          
If you're looking for examples that use the Windows API to do cool 
stuff in Visual Basic, take a look at Karl Peterson's One Stop Source 
Shop: 

http://www.mvps.org/vb/ 

You'll find lots of samples for questions that I get often, such as 
how to get the user's login ID, use the common dialog API calls, and 
so on.


*5. SORTING LISTVIEW DATA NUMERICALLY                   
          
The ListView control (part of the Windows Common Controls) has the 
ability to sort by a particular column in either ascending or 
descending order. While this works great for text, it doesn't work so 
well for numeric data. For instance, if you wanted to show the price 
of products in the Northwind Traders database, the system would sort 
the prices according to alphabetical order, not numeric. You can see 
another example of this when you look at Windows Explorer in Detail 
mode. The files in a directory can be sorted according to size, and 
they work properly.  

A trick I came up with is to left-pad the numbers with spaces. Once 
you do that, they sort properly. Here's an example that has a ListView 
control, in Report View (View property), on a form called frmProducts. 
I'm going to show the product name (with product ID as the key), unit 
price, and units in stock: 

Private Sub Form_Load() 
   Dim rsData As ADODB.Recordset 
   Dim objItem As ListItem 
   Dim strTemp As String 
   
   Set m_dcnDB = New ADODB.Connection 
   m_dcnDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _ 
      & "Data Source=C:\Visual Studio\VB98\NWind.MDB" 
   m_dcnDB.Open 
   Set rsData = m_dcnDB.Execute("SELECT * FROM PRODUCTS ORDER BY 
ProductName") 
   
   With lvwProducts.ColumnHeaders 
      .Add , "ProductName", "Product Name", 2880 
      .Add , "UnitPrice", "Unit Price ($)", 1440, lvwColumnRight 
      .Add , "UnitsInStock", "Units In Stock", 1440, lvwColumnRight 
   End With 
   
   Do Until rsData.EOF 
      Set objItem = lvwProducts.ListItems.Add(, "P" & 
rsData("ProductID"), _ 
         rsData("ProductName")) 
      strTemp = Format(rsData("UnitPrice"), "###,###,##0.00") 
      objItem.SubItems(1) = String(14 - Len(strTemp), " ") & strTemp 
      
      strTemp = Format(rsData("UnitsInStock"), "###,###,##0") 
      objItem.SubItems(2) = String(11 - Len(strTemp), " ") & strTemp 
      
      rsData.MoveNext 
   Loop 
   rsData.Close 
   m_dcnDB.Close 
   
End Sub 

The formatting for the price will handle a far bigger value than the 
database will allow. The length of the maximum format is 14 
characters, so that's what we use to left-pad the result. A dollar 
value of 5.50 will get 10 spaces in front of it. The same thing 
happens for the quantity, which doesn't have a decimal component. You 
can now specify a sort on the unit price or units in stock columns and 
all the data sorts properly, based on the numeric values.


*6. SORTING A LISTVIEW CONTROL                   
          
When using a ListView control in Report mode, users have the ability 
to click on a column header to designate the sort order in Windows 
Explorer. Here's the code you need to make this work: 

Private Sub lvwProducts_ColumnClick(ByVal ColumnHeader As 
MSComctlLib.ColumnHeader) 
   lvwProducts.SortKey = ColumnHeader.Index - 1 
   lvwProducts.SortOrder = lvwAscending 
End Sub 

This code works on the assumption that the ListView control is named 
lvwProducts and that the Sorted property has been properly set. If 
this property isn't set, nothing will happen when you click a column 
header, even with this code in place.


*7. REVERSE SORTING A LISTVIEW CONTROL                   
          
A handy feature of Windows Explorer is the ability to click twice on a 
column header to sort that column's data in reverse order. If you used 
our previous tip on handling a simple sort, changing the code to 
handle a reverse sort is easy. The key is to check the column by which 
the data is currently sorted: If it's sorted by the same column that 
the user just clicked on, you simply reverse the sort. Here's the code 
to use: 

Private Sub lvwProducts_ColumnClick(ByVal ColumnHeader As 
MSComctlLib.ColumnHeader) 

   If lvwProducts.SortKey = ColumnHeader.Index - 1 Then 
      If lvwProducts.SortOrder = lvwAscending Then 
         lvwProducts.SortOrder = lvwDescending 
      Else 
         lvwProducts.SortOrder = lvwAscending 
      End If 
   Else 
      lvwProducts.SortKey = ColumnHeader.Index - 1 
      lvwProducts.SortOrder = lvwAscending 
   End If 
   
End Sub 

Since users are accustomed to having this feature in Windows Explorer, 
it's a good idea to add it to your applications (those that use the 
TreeView control, anyway) as well.


*8. KEY PROPERTY CANNOT BE NUMERIC                   
          
When adding ListItem objects to a ListView or Node objects to a 
TreeView, remember that the Key property cannot be just numeric. The 
value must begin with one letter, at a minimum. When I build 
applications using either of these controls, I typically use the 
primary key of the database table as part of the key. I then prefix 
that value with a letter (or two letters), indicating the table from 
which the data comes. I do this because the key has to be unique 
throughout the control's data collection (whichever collection is 
being used).


*9. CALLING EVENT HANDLERS AS SUBROUTINES                   
          
When you're writing a Visual Basic program, you create event handlers 
to deal with events such as a form loading or a user clicking a 
button. These event handlers are simply additional subroutines in your 
forms, which means they can be called like other subroutines. 
Therefore, if you have code in a button's Click event, you can invoke 
that code from elsewhere in the form by calling Button1_Click, for 
instance. 

However, I prefer to put this type of reused code in a subroutine 
within the form and call it from both places. This technique makes the 
code easier to explain to other programmers, and makes it a bit easier 
to move or change the code at a later time.


*10. WRITING A SHORTCUT FORMATTING FUNCTION                   
          
In a previous tip, we discussed formatting and left-padding numeric 
data so that it will sort properly in a ListView control, which only 
sorts alphabetically. Here's a shortcut function that does all this in 
one fell swoop: 

Private Function LVFormat(varExpression As Variant, strFormat As 
String) 
   Dim strTemp As String 
   
   strTemp = Format(varExpression, strFormat) 
   LVFormat = String(Len(strFormat) - Len(strTemp), " ") & strTemp 
End Function 

Accordingly, to add data to a ListView control from the Northwind 
Traders database, you can use this code: 

Private Sub Form_Load() 
   Dim rsData As ADODB.Recordset 
   Dim objItem As ListItem 
   
   Set m_dcnDB = New ADODB.Connection 
   m_dcnDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _ 
      & "Data Source=C:\Visual Studio\VB98\NWind.MDB" 
   m_dcnDB.Open 
   Set rsData = m_dcnDB.Execute("SELECT * FROM PRODUCTS ORDER BY 
ProductName") 
   
   With lvwProducts.ColumnHeaders 
      .Add , "ProductName", "Product Name", 2880 
      .Add , "UnitPrice", "Unit Price ($)", 1440, lvwColumnRight 
      .Add , "UnitsInStock", "Units In Stock", 1440, lvwColumnRight 
   End With 
   
   Do Until rsData.EOF 
      Set objItem = lvwProducts.ListItems.Add(, "P" & 
rsData("ProductID"), _ 
         rsData("ProductName")) 
      objItem.SubItems(1) = LVFormat(rsData("UnitPrice"), 
"###,###,##0.00") 
      objItem.SubItems(2) = LVFormat(rsData("UnitsInStock"), 
"###,###,##0") 
      
      rsData.MoveNext 
   Loop 
   rsData.Close 
   m_dcnDB.Close 
   
End Sub
