54 replies to this topic

[alhakimie]

Does a file exist? It's easy to tell in VB .NET

It's safe to say that VB 5.0 and 6.0's file manipulation options have never been optimal. You either have to use clunky old Open, Write and Close keywords, or the FileSystemObject, which isn't even part of standard VB.

Fortunately, VB .NET abandons both these models in favor of a new, language-independent set of classes, File and FileInfo. These classes, found in the System.IO namespace, make it easy for a developer to determine if a file already exists on the target system.

No matter which of the two you decide to use, you must first import the required namespace to save yourself from typing long, fully qualified names:

CODE

Imports System.File.IO

Then, you can use the shared Exists() method to look for a specific file. With the File class, you pass in the path as a string, like so:

CODE

If File.Exists("c:myfile.txt") Then

   MessageBox.Show("File found.")

Else

   MessageBox.Show("File not found.")

End If

Notice that you don't need to actually create the File class-it's a helper class and its methods are always available.

Next, let's repeat this procedure with the FileInfo class. This time we need to explicitly create the class instance, as seen here:

Dim MyFile As New FileInfo("c:myfile.txt")
If MyFile.Exists() Then
MessageBox.Show("File found.")
Else
MessageBox.Show("File not found.")
End If

This time, the Exists() method didn't require the filename as a parameter because the object itself points to the appropriate file.

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp.

[alhakimie]

[b]IVB Tips: Let users hold down a VB command button to execute repetitive code

Let users hold down a VB command button to execute repetitive code

VB's command buttons are a staple in most VB projects. However, as you know, they only register a single Click() event. Often, it may be advantageous for your project to let the user hold down the button and execute a piece of code over and over again until they release it. While several techniques exist to build the feature, the one we like best involves the Timer control.

In essence, when the user clicks the button the code enables the Timer control, which in turn fires the repetitive code each time the Timer's interval time expires. When the user releases the button, the code disables the Timer, stopping the repetitive execution. To react to the button 'clicks' our technique uses the command button's MouseDown() and MouseUp() events.

To see how this technique works, open a standard Visual Basic project. Drop a label, a command button and a Timer control onto the default form. Next, right-click on the form and select View Code from the short-cut menu. When the IDE displays the Code window, enter the following code:

CODE

Private Sub Command1_MouseDown(Button As Integer, _

Shift As Integer, X As Single, Y As Single)

Timer1.Enabled = True

End Sub



Private Sub Command1_MouseUp(Button As Integer, _

Shift As Integer, X As Single, Y As Single)

Timer1.Enabled = False

End Sub



Private Sub Form_Load()

Timer1.Interval = 100

End Sub



Private Sub Timer1_Timer()

Label1.Caption = Now

End Sub

Now, press [F5] to launch the project. Click the form's button and hold down the mouse button. When you do, the Timer() event continues to update the label with the current time. Now, release the mouse button. Doing so disables the Timer and stops the repetitive code execution.

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp.

[alhakimie]

[b]IVB Tips: Two ways to let [Esc] unload a VB form

Two ways to let [Esc] unload a VB form

In addition to directives from the mouse, you'll often want to let a form accept keyboard commands as well. For instance, one common keyboard requirement is to let users close a form by pressing the [Esc] key. Visual Basic provides two ways to build this feature into your form. Which one you use depends on the needs of your project.

The first technique involves placing a 'Cancel' command button on the form. To do so, drop a standard command button on the form and enter 'Cancel' as its Caption property. In addition, set the control's Cancel property to True. When you do, VB executes this button's Click() event whenever you press the [Esc] key (as well as when you click it). Of course, even though VB executes the Click() event, you'll still need to provide code to unload the form, such as:

CODE

Private Sub cmdCancel_Click()

Unload Me

End Sub

If you project GUI specs for some reason won't allow for a Cancel button, you can still let users close a form by pressing [Esc]. To do so, set the form's KeyPreview property to True. Then, in the Code window, select the Form's KeyPress event and enter the missing section of the code:

CODE

Private Sub Form_KeyPress(KeyAscii As Integer)

If KeyAscii = 27 Then

   Unload Me

End If

End Sub

If you've used the same form with the 'Cancel' button, don't forget to set the button's Cancel property to False before continuing. Next, launch the project and press the [Esc] key. When you do, the form intercepts the KeyPress event and unloads the form.


This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp.

[alhakimie]

[b]Identify VB controls with the TypeOf keyword

Often in your code procedures, you may need to determine what type
of control your code has accessed. For instance, you may want to
alter the text on every command button on a form. Or you may want
to change the properties for several different controls. In such
instances, you have several ways to test for a control type. The
most efficient and fastest method is the TypeOf operator.

Unlike other methods of determining a control's type, such as the
TypeName property, the TypeOf keyword doesn't need several roundtrips
to the Registry to obtain the information it needs. As a result,
it reduces the processing drag necessary for your code.

This keyword must appear in an If...Then statement, like so:

CODE

If TypeOf ctl Is CommandButton Then

'Do something

End If

The following code shows an example of how to use this keyword
in a procedure.

CODE

Private Sub Command1_Click()

Dim ctl As Control

Dim str As String

For Each ctl In Me.Controls

If TypeOf ctl Is CommandButton Then _

 str = "CommandButton"

If TypeOf ctl Is TextBox Then str = "TextBox"

If TypeOf ctl Is OptionButton Then str = _

 "OptionButton"

If TypeOf ctl Is DriveListBox Then str = _

 "DriveListBox"

If TypeOf ctl Is DataCombo Then str = _

 "DataCombo"

MsgBox str

Next ctl  

End Sub

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp.

[alhakimie]

In VB.NET, Try code statements and Catch the errors

In VB.NET, Try code statements and Catch the errors

Like all .NET languages, VB.NET, uses structured exception handling.
This structure avoids the On Error Goto statement's ugliness by using
a modern block structure, like so:

Try
DoSomethingDangerous()
Catch
' Handle error here.
Finally
' Perform final cleanup.
End Try

As you can see, a structured exception-handling block consists of three parts:

--The Try block encapsulates the code that might generate an error.

--The Catch block contains the actual error handling code.

--The optional Finally block includes code that executes when the
Try and Catch blocks conclude. The Finally code always executes, even
if an unrecoverable error occurs that requires your program to end immediately.

When an error occurs, VB.NET throws a special Exception object,
which your application, or VB, catches. This object contains
information about the problem. VB derives every Exception from
the base System.Exception class. In addition to this class, VB.NET
also defines several broad category Exception objects, also based
on the System.Exception object. Once you're familiar with the exception
classes, you can take charge of exception handling and write error
handlers targeted at specific types of problems. To do so, you
specify the error class explicitly in the Catch statement. The
following simple example illustrates how to handle exceptions:

Public Sub SwapNumbers(ByRef BigNumber As Integer, _
ByRef SmallNumber As Int16)

Dim TempNumber As Integer = BigNumber

' The widening conversion always succeeds.
BigNumber = SmallNumber

Try
' This is more risky.
SmallNumber = TempNumber
Catch ErrorObj As OverflowException
' Handle an overflow here.
Catch ErrorObj As DivideByZero
' Handle a division by zero here.
' (Not possible in this code.)
Catch ErrorObj As NullReferenceException
' Handle an error caused by trying to
' use an unitialized object.
' (Not possible in this code.)
End Try
End Sub


This weekly tip is delivered to you as a free service from
Element K Journals. http://www.elementktips.com

[alhakimie]

How to accommodate spaces in database table names in ADO

In general, you should absolutely avoid giving database tables and
fields names with spaces in them, such as

Big Valley

However, we realize that you don't always have control over such
things, especially when dealing with a legacy database. In such
circumstances, you'll need to know how to accommodate these names in ADO.

To use table or field names with spaces in them in ADO, surround the
entire name in brackets ([]), like so

[Big Valley]

You must use brackets even in ADO methods where the name is already
surrounded in quotes. For instance, in a recordset's Open method:

rst.Open "[Big Valley]", conn,,,adCmdTable

The same goes when you call a field or table in a SQL statement:

rst.Open "SELECT [Little Joe] FROM [Big Valley]"



This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp.

[alhakimie]

[b]Should you remove an ADO Filter before applying another one?

Depending on the recordset type, in ADO to filter records, you use
the Filter property, like so:

CODE

rst.Filter = "pub_id ='000132' "

Under these conditions, ADO would filter all the records with a
pub_id of 000132. It's important to remember, however, that ADO
doesn't eliminate the other records that don't match the criteria.
It just doesn't let you access them at this point.

With this in mind, when you set a recordset filter, you don't need
to return the recordset back to its unfiltered state before applying
another filter. ADO automatically applies the new filter to the
*entire* recordset. As a result, the following code is perfectly
valid:

CODE

With rst

For x = 1 to 10

.Filter = "some_id=" & x

If Not .EOF Then MsgBox .Fields("Name")

Next x

End With

Of course, to return the recordset back to its unfiltered state,
you'll still need to remove any filters, with something along
the lines of:

CODE

rst.Filter = adFilterNone

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp.

[alhakimie]

Effortlessly open Microsoft Word documents from VB

Often, your application may have cause to open a Microsoft Word
document. Rather than accessing it via some directory method, such
as the FileSystemObject or Shell, try opening it directly via Word
Automation. This approach will make it that much easier to read
and manipulate the document's properties and content.

To open a Word document via Automation, you must first work your
way down the object hierarchy to the Documents collection, which
is a member of the top-level Application object. As you can guess,
the Documents collection holds all open documents, and exposes
an Open method. To use this method you simply pass in the full
path of the document you wish to open. The Open method in turn
returns a Document object that represents the document you requested.
Once obtained, you can then read and manipulate the file's contents
as you see fit. The following code shows an example:

CODE

Private Sub Command1_Click()

Dim appWord As Word.Application

Dim wrdDoc As Word.Document

Dim strFileName As String

strFileName = "D:ArticlesTitle.doc"

Set appWord = New Word.Application

Set wrdDoc = appWord.Documents.Open(strFileName)

MsgBox wrdDoc.Path & "" & wrdDoc.Name

wrdDoc.Close False

appWord.Quit

Set wrdDoc = Nothing

Set appWord = Nothing

End Sub

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp.

[alhakimie]

Loop through Arrays with For Each...Next

As you know, Visual Basic provides For Each...Next iteration for
all kinds of standard object collections. But did you also know
that it provides this same construct for arrays as well?

Before you start looping madly through every array, however, you'll
need to apply an important rule: You must declare the control variable
used in the iteration as a variant. For instance, normally, when
looping through, say, fields in a recordset, you'd use code similar to

CODE

Dim fld As ADODB.Field

For Each fld In rst.Fields

MsgBox fld.Name

Next fld

When looping through an array, though, Visual Basic requires the
code to look as follows:

CODE

Dim itm As Variant

For Each itm In myArray

MsgBox itm

Next itm

To verify that this behaviors works the way we say it does, launch
a Visual Basic standard project, select View | Code from the menu
bar, and run the following code:

CODE

Dim myArray(1 To 10) As String

Private Sub Form_Load()

myArray(1) = "H"

myArray(2) = "E"

myArray(3) = "L"

myArray(4) = "L"

myArray(5) = "O"

myArray(6) = " "

myArray(7) = "W"

myArray(8) = "O"

myArray(9) = "R"

myArray(10) = "L"

myArray(11) = "D"

myArray(12) = "!"



Dim itm As Variant

Dim msg As String

msg = ""

For Each itm In myArray

   msg = msg + itm

Next itm

Debug.Print msg

End Sub

Press [F5] to run the project. When Visual Basic loads the default
form, it quickly loops through the array, concatenating the letters
into the phrase Hello World! which it displays in the Immediate
Window.

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp.

[alhakimie]

[b]An alternate way to let VB determine odd and even numbers

In a previous tip, we suggested that you use the MOD arithmetic
operator to determine if a number is odd or even. As many of you
pointed out, however, the technique we presented wasn't the most
efficient method; because MOD actually performs a division behind
the scenes. As a result, multiple calls to this operator could
bog down the processor.

As an alternative, you can perform a simple bitwise comparison
against the number you want to test, such as

myNumber And 1

When you do, Visual Basic returns either a 0 or 1--1 for odd
numbers, and 0 for even numbers. You can then use these results
in your code.

For a simple example, launch a standard Visual Basic project and
add a textbox and command button to the default form. Next, in
the command button's Click() event, add the following code:

CODE

Dim blnIsOdd As Boolean

blnIsOdd = CLng(Text1.Text) And 1

MsgBox blnIsOdd

Press [F5] to run the project. Enter a number in the textbox and
click the command button. When you do, Visual Basic tells you
whether the number is odd or not.

[/code]

[alhakimie]

CODE

[b]Capture SQL Server stored procedure output values with ADO[/b]

As you may know, T-SQL, as used by SQL Server, lets you define output parameters in a stored procedure. These parameters let T-SQL pass values back out to the code executing the stored procedure. In general, output parameters are more efficient than recordsets for returning single values because they don't require the additional resources of a cursor, etc.

To retrieve an output parameter via ADO in VB, you first create a Parameter object, indicating that it's an output parameter (or an output/input parameter). You then append this parameter to a Command object, just like you would when passing in an input parameter. After your code executes the Command, SQL Server and ADO fill the Parameter object with the appropriate value.

To see how this works, consider the following T-SQL stored procedure:

CODE

CREATE PROCEDURE [sp_BookList]

(@ISBN as varchar(30),

@TotalDollar as money OUTPUT)

AS



SELECT @TotalDollar = 404.32

GO

Normally, this procedure might return the total dollar amount in inventory for a specific book. To keep things simple, we hard-coded the return value. To retrieve this parameter in VB, you'd use code like this:

CODE

Private Sub Form_Load()

Dim cmd As New ADODB.Command

Dim param1 As New ADODB.Parameter

Dim param2 As New ADODB.Parameter



With cmd

   .CommandText = "sp_BookList"

   .CommandType = adCmdStoredProc

   .ActiveConnection = CONN_STRING

   Set param1 = .CreateParameter("ISBN", adVarChar, adParamInput, 30, "90023433")

   .Parameters.Append param1

   

   Set param2 = .CreateParameter("TotalDollar", adCurrency, adParamOutput)

   .Parameters.Append param2

   

   .Execute Options:=adExecuteNoRecords

   Set .ActiveConnection = Nothing

   Set param1 = Nothing

End With

Notice that in the Execute method, we specified adExecuteNoRecords. Doing so tells ADO not to bother building and populating a recordset just to hold the return value.

Once executed, your code can retrieve the output parameter value one of two ways: either by accessing the Parameters collection, or by using the Parameter object directly. The following code illustrates both methods and concludes the previous code procedure.

CODE

MsgBox FormatCurrency(param2.Value, 2)

MsgBox FormatCurrency(cmd.Parameters("TotalDollar"), 2)



Set param2 = Nothing

Set cmd = Nothing

End Sub

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp.

[alhakimie]

[b]Quickly remove extra whitespace from inside a string

While VB doesn't offer a function that lets you remove extra whitespace inside a string, you can quickly whip up a custom function to do so on your own. In essence, using the Split() function, you can separate the string's component parts and then reconcatenate only those items in the resulting array that contain actual characters.

As you know, the Split() function lets you split a string using specific characters as a delimiter. This function uses the following syntax:

CODE

Split(string_to_split, delimiter)

Split returns a variant array with each item representing the piece of the string between, but not including the delimiter character. So, for instance, the following function call

CODE

Split("A, B, C, D", ",")

would split the provided string into an array of four items--one for each letter, without the commas.

Of course, you can also use a space (" ") as a delimiter. Naturally, a string might have one or more spaces in a row. Under these circumstances, Split() still splits the two spaces, but the item in the resulting array will contain an empty string. We can test for these empty strings in our custom function. The following code shows our custom Pack() function, and how it uses Split() to accomplish its goal.

CODE

Private Sub Form_Load()

MsgBox Pack("Hello    there, world! What   is        going on?")



End Sub

Private Function Pack(str As String) As String

Dim words As Variant

Dim x As Long

Dim temp As String



words = Split(str, " ")

For x = LBound(words) To UBound(words)

   If words(x) <> "" Then

       temp = temp & " " & words(x)

   End If

Next x

Pack = temp

End Function

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp.

[alhakimie]

[b]Validate numbers in a textbox with RegExp

While it's always a good idea to validate user-entered data, doing so is especially important when you use that data later in a VB expression. For instance, suppose your procedure takes a number entered by the user and multiplies it by 5. If the user doesn't enter the number correctly, (9..9 instead of 9.99, for instance) VB will generate an error.

To use regular expressions, set a project reference to the Microsoft VBScript Regular Expressions (5.5) option in Visual Basic's References dialog box. When you do, you'll be free to use the RegExp object. The following code validates numeric entry in a textbox named Text1:

CODE

Dim myReg As RegExp



Private Sub Form_Load()

Set myReg = New RegExp

myReg.IgnoreCase = True

myReg.Pattern = "^d*.{1}d*$"

End Sub



Private Sub Text1_Validate(Cancel As Boolean)

Cancel = Not myReg.Test(Text1)

If Cancel Then

   MsgBox "Please enter a number in the format ####.##" & vbNewLine _

       & "(no commas)"

End If

End Sub

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp.

[alhakimie]

[b]Determining the system OS with the SysInfo control

The Microsoft SysInfo control provides a great way of gathering system information without resorting to API functions. Included in this handy control are three properties (OSBuild, OSPlatform, and OSVersion) that expose information about the operating system. However, if you've ever used these properties, you may have been confused by the results. You see, all three properties return numbers as their values--and the control doesn't expose any constants to describe which numbers map back to which OS.

Fortunately, we've decoded the cipher. By using a combination of the OSPlatform and OSVersion numbers, you can quickly determine the OS, as seen in the following code:

CODE

Dim OS As String



With SysInfo1

   Select Case .OSPlatform

       Case 0: OS = "Win32"

       Case 1:

           Select Case .OSVersion

               Case 4: OS = "Win 95"

               Case 4.1: OS = "Win 98"

           End Select

       Case 2:

           Select Case .OSVersion

               Case 4: OS = "Win NT"

               Case 5: OS = "Win 2000"

               Case 6: OS = "Win XP"

           End Select

   End Select

   

   MsgBox "Build:" & .OSBuild & vbNewLine & _

       "Platform:" & OS & "(" & .OSPlatform & ")" & vbNewLine & _

       "Version:" & .OSVersion

End With

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp.

[alhakimie]

Assign labels when using an array with MSChart

As you know, the MSChart will accept data from an array. However, when you provide data in a single-dimensioned array, you may have wondered how to supply labels for the data series. Fortunately, you can supply this information in the array as well. When building a chart from an array, MSChart will accept the first series of a multi-dimensional array as strings. It will then use these values as series labels. The following code shows how we might accommodate this behavior to display the labels

CODE

Dim ary(1 To 10, 1 To 2) As Variant

With MSChart1

For X = 1 To 10

ary(X, 1) = "Label" & X

ary(X, 2) = X * 2

Next X

.ChartData = ary

End With

Also notice that because the array must contain both string and integer data, we had to declare it as a Variant data type.

Assign labels when using an array with MSChart

As you know, the MSChart will accept data from an array. However, when you provide data in a single-dimensioned array, you may have wondered how to supply labels for the data series. Fortunately, you can supply this information in the array as well. When building a chart from an array, MSChart will accept the first series of a multi-dimensional array as strings. It will then use these values as series labels. The following code shows how we might accommodate this behavior to display the labels

CODE

Dim ary(1 To 10, 1 To 2) As Variant

With MSChart1

For X = 1 To 10

ary(X, 1) = "Label" & X

ary(X, 2) = X * 2

Next X

.ChartData = ary

End With

Also notice that because the array must contain both string and integer data, we had to declare it as a Variant data type.

[alhakimie]

A quick way to programmatically determine if a database table exists

Often, when you code against a database, you may want your code to programmatically determine if a table exists. Of course, there are several ways to accomplish this task, including looking for a table object with the specified name if you're using DAO. However, another way to do so involves simply opening a test recordset based on the table. If the table doesn't exist, then VB will generate an error. Your code then tests for the existence of an error. Naturally it will also have to turn off error-handling for a short time. The following code illustrates this technique.

CODE

Private Sub Command1_Click()

MsgBox TableExists("tblSomeTable")

End Sub



Private Function TableExists(tableName As String) As Boolean

Dim strConn As String

Dim rst As Recordset



strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

   "D:DBDirectorydb1.mdb"

   

Set rst = New Recordset

On Error Resume Next

rst.Open tableName, strConn, adOpenForwardOnly, adLockReadOnly, adCmdTable

TableExists = Not CBool(Err.Number)

On Error GoTo 0  'Or point back to error-handling line label

End Function

Just make sure that your code tests for the error before it returns error-handling control back over to whatever error-handling system was in place previously. Otherwise, VB will have reset the error code back to 0.

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp.

[alhakimie]

Responding to dynamically added control events


In a previous tip, we showed you how to add controls dynamically to a Frame at runtime. As several of you pointed out, however, in addition to adding the control, you may also want to respond to its events. Fortunately, you can do just that with a few tweaks to our original code, as seen here:

CODE

Dim WithEvents txtBox As TextBox

Private Sub Form_Load()

  Set txtBox = Controls.Add("VB.TextBox", "txtBox", Frame1)

  With txtBox

     .Move 150, 240, 1500

     .Visible = True

  End With

End Sub



Private Sub txtBox_GotFocus()

MsgBox "triggered"

End Sub

As you can see, the WithEvents keyword provides the change we need to respond to the TextBox events. We also made txtBox a form-level variable instead of a procedure-level one as before. Once you declare the object variable WithEvents, the VB IDE lets you select the object's events in the Code Window's Event dropdown box.

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp.

[alhakimie]

[b]Avoid the MSChart glitch when displaying ADO Recordset data

When you assign an ADO Recordset to an MSChart's DataSource property, the resulting chart may not contain the first row of data. Normally, when you create a Recordset object, ADO automatically moves the cursor to the first record (assuming the recordset contains records). As a result, there's no need to issue the MoveFirst command. Unfortunately, unless you issue this command, MSChart skips over the first record for some reason. As a result, when you use the MSChart control with an ADO Recordset, always move the cursor to the first record before you assign it to the chart, as seen here:

CODE

Dim rst As ADODB.Recordset

Dim sql As String

Set rst = New ADODB.Recordset

sql = "SELECT * FROM tblChartData"

With rst

.ActiveConnection = "Provider=Microsoft.Jet" & _

".OLEDB.4.0;Data Source=D:DBPathdb1.mdb"

.Open sql, , adOpenStatic

.MoveFirst

End With

Set MSChart1.DataSource = rst

rst.Close

Set rst = Nothing

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp.

[alhakimie]

[b]Use an enumerated type to declare API constants in VB 6.0

Use an enumerated type to declare API constants in VB 6.0
When dealing with API constants, one easy way to declare them is to group related constants in an enumerated type.

For example, you could group the constants for ShowWindow (SW_*) like this:

CODE

' ShowWindow constants

Public Enum enShowWindow

   SW_HIDE = 0

   SW_MAX = 10

   SW_MAXIMIZE = 3

   SW_MINIMIZE = 6

   SW_NORMAL = 1

   SW_OTHERUNZOOM = 4

   SW_OTHERZOOM = 2

   SW_PARENTCLOSING = 1

   SW_PARENTOPENING = 3

   SW_RESTORE = 9

   SW_SHOW = 5

   SW_SHOWDEFAULT = 10

   SW_SHOWMAXIMIZED = 3

   SW_SHOWMINIMIZED = 2

   SW_SHOWMINNOACTIVE = 7

   SW_SHOWNA = 8

   SW_SHOWNOACTIVATE = 4

   SW_SHOWNORMAL = 1

End Enum

You can then alias the API declarations that use these constants to use the enumeration instead, as in:

Declare Function ShowWindowApi Lib "user32" Alias "ShowWindow" (ByVal hwnd As Long, ByVal nCmdShow As enShowWindow) As Long

Now, when you use the API function in your code, the IDE will provide an Intellisense dropdown list of the relevant constants for nCmdShow. In the long run, this behavior can save a great deal of time and effort.

--Submitted by
Duncan Jones
Merrion Computing Ltd
http://www.merrioncomputing.com

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp.

[alhakimie]

[b]Improve ADO action query performance with adExecuteNoRecords

When you want to execute an action query, or a stored procedure that updates, appends, or deletes records, (or some other action), you can improve performance by using ADO's adExecuteNoRecords flag. Doing so informs ADO that the command being run doesn't require a recordset to be built, reducing the time and processing power necessary to execute the query. The following code shows one way to use this constant:

CODE

Private Sub Form_Load()

Dim con As New Connection

Dim recs As Long



With con

   .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

       "Data Source=D:PathToDBdb1.mdb"

   .Open

   .Execute "qryUpdate", recs, adExecuteNoRecords

   .Close

End With

Set con = Nothing

MsgBox recs & " record(s) affected."

End Sub

Notice that you place this constant in the Execute method's *options* parameter. This parameter is also where you tell ADO what type of command you want to execute. To combine options, simply add them together, like so:

CODE

conn.Execute "qryUpdate", recs, adExecuteNoRecords + adCmdStoredProc

[alhakimie]

[b]Print a Microsoft Access report from VB 6.0

As you may know, Microsoft Access provides a much more robust reporting system than Visual Basic. As a result, if you use Access as a back-end to your application, you may want to print Access reports from your VB application. Fortunately, you can do just that with Automation.

The following code shows one way to do so, using late binding:

CODE

Dim objAccess As Object



Private Sub Command1_Click()

Dim dbName As String

Dim rptName As String

Dim Preview As Long

Const acNormal = 0

Const acPreview = 2



dbName = "D:PathToDBdb1.mdb"

rptName = "MyReportName"

Preview = acPreview 'acNormal



With objAccess

   .OpenCurrentDatabase filepath:=dbName

   If Preview = acPreview Then

      .Visible = True

      .DoCmd.OpenReport rptName, Preview

   Else

      .DoCmd.OpenReport rptName

   End If

End With

End Sub



Private Sub Form_Load()

Set objAccess = CreateObject("Access.Application")

End Sub



Private Sub Form_Unload(Cancel As Integer)

On Error Resume Next

objAccess.Quit

On Error GoTo 0

Set objAccess = Nothing

End Sub

[alhakimie]

[b]How to assign icons from an ImageList to a VB 6.0 ListView

As you know, the ListView control offers several different ways to display its items. Two of those options, Icon and SmallIcon, involve adding--you guessed it--icons to each item in the list. Of course, one of the best ways to make the images you'll need available to your application is with the ImageList control. In essence, this control stores images in an easy-to-reference way.

Before you can take advantage of these stored images in the ListView, however, you'll need to assign the ImageList to the ListView. To do so depends on which view you've chosen for the list, Icon or SmallIcon. If you've chosen the Icon view, then you assign the ImageList to the ListView's Icons property. If you've selected the SmallIcon view, then you assign the ImageList to the SmallIcons property. The following code snippet shows how this might work:

CODE

With ListView1

   .View = lvwIcon

   Set .Icons = ImageList1

End With



With ListView2

   .View = lvwSmallIcons

   Set .SmallIcons = ImageList2

End With

At this point, when you add a list item to the ListView, you reference the icon's index within the ImageList, like so:

CODE

ListView1.ListItems.Add Key:="K1", Text:="Item 1", Icon:=1

which would assign the first image in the control to the new ListItem.

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp.

[alhakimie]

[b]Bring a VB 6.0 Image to the front of the z-stack at runtime

Bring a VB 6.0 Image to the front of the z-stack at runtime

As you know, most VB controls let you change their z-order position at design time. To do so, you simply right-click on the control and then select either Send To Back or Bring To Front from the shortcut menu. Naturally, VB 6.0's Image control is no exception to this rule.

However, you may have wondered how to change this control's z-order at runtime. Fortunately, VB 6.0 makes it easy.

Like most controls, the Image exposes the ZOrder property, which as you can guess lets you alter the control's z-order. This method uses the following syntax:

CODE

objImage.ZOrder x

where objImage is the name of the Image control whose property you want to change, and x is either a 0 or 1. A 0 brings the control to the front of the z-stack, while a 1 sends it to the back of the pack.


This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp.

[alhakimie]

Use the Win32 API to display the property sheet for any file on your system from VB

Property sheets tell us all kinds of things about a file: what sort of file it is, who created it and when, how big it is, and more. Wouldn't it be nice if you could automatically display the built-in property sheet for any file on your system from a Visual Basic application? Well, thanks to the power of the API, you can. The following code will do the trick (full documentation is available on the MSDN Library Web site at http://msdn.microsoft.com):

CODE

 Option Explicit



 Private Type SHELLEXECUTEINFO

     cbSize        As Long

     fMask         As Long

     hWnd          As Long

     lpVerb        As String

     lpFile        As String

     lpParameters  As String

     lpDirectory   As String

     nShow         As Long

     hInstApp      As Long

     lpIDList      As Long     'Optional; ignore

     lpClass       As String   'Optional; ignore

     hkeyClass     As Long     'Optional; ignore

     dwHotKey      As Long     'Optional; ignore

     hIcon         As Long     'Optional; ignore

     hProcess      As Long     'Optional; ignore

 End Type



 Private Const SEE_MASK_INVOKEIDLIST = &HC

 Private Const SEE_MASK_NOCLOSEPROCESS = &H40

 Private Const SEE_MASK_FLAG_NO_UI = &H400



 Private Declare Function ShellExecuteEx Lib "shell32" Alias "ShellExecuteExA" (sei As SHELLEXECUTEINFO) As Long



 Private Sub ShowProperties(strFilename As String, hWnd As Long)

     Dim lngReturn As Long

     Dim sei As SHELLEXECUTEINFO



     With sei

         .cbSize = Len(sei)

         .fMask = SEE_MASK_NOCLOSEPROCESS Or SEE_MASK_INVOKEIDLIST Or SEE_MASK_FLAG_NO_UI

         .hWnd = hWnd

         .lpVerb = "properties"

         .lpFile = strFilename

         .lpParameters = vbNullChar

         .lpDirectory = vbNullChar

         .nShow = 0

         .hInstApp = 0

         .lpIDList = 0

     End With

     lngReturn = ShellExecuteEx(sei)

 End Sub



 Private Sub Command1_Click()

     ShowProperties "c:winntexplorer.exe", Me.hWnd

 End Sub

[/b]

[alhakimie]

Show the standard Windows Run and Find dialog boxes from your VB application

You can use the versatile Microsoft Shell Controls and Automation component (exposed in shell32.dll) to bring up the standard Run, Find Files, and Find Computers dialog boxes right from your own Visual Basic program. Just set a reference to the component, then add the following code to your application:

CODE

 Private shlShell As Shell32.Shell

 Private shlFolder As Shell32.Folder



 Private Sub Command1_Click()

     If shlShell Is Nothing Then

         Set shlShell = New Shell32.Shell

     End If

     shlShell.FileRun

     'shlShell.FindFiles

     'shlShell.FindComputer

 End Sub

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp.

[alhakimie]

Leverage the "browse for folder" dialog box from your VB application

You've seen that snazzy dialog that Windows throws up to facilitate browsing for a folder, no doubt. It used to be that you had to resort to some heavy API work to display and respond to that dialog from VB, but thanks to the Microsoft Shell Controls and Automation component (exposed in shell32.dll), you can now use this professional-looking dialog with minimal effort. Set a reference to this component, and then all you need is something like this:

CODE

Private shlShell As Shell32.Shell

 Private shlFolder As Shell32.Folder

 Private Const BIF_RETURNONLYFSDIRS = &H1



 Private Sub Command1_Click()

     If shlShell Is Nothing Then

         Set shlShell = New Shell32.Shell

     End If

     Set shlFolder = shlShell.BrowseForFolder(Me.hWnd, "Select a Directory", BIF_RETURNONLYFSDIRS)

     If Not shlFolder Is Nothing Then

         MsgBox shlFolder.Title

     End If

 End Sub

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp.

[alhakimie]

[b]Determine whether code is running in the VB IDE or as a compiled executable

There are surprisingly many techniques for determining whether your application is running as a compiled executable or from inside the Visual Basic IDE. Some of these rely on tripping errors with the Debug object, an object which is stripped out of your compiled code. Others involve tedious API calls. But here's a very simple one: use a command line argument. Here's how:

Access the Project Properties for your VB project. On the Make tab, enter "RunningFromIDE" in the Command Line Arguments TextBox. This command line argument will be removed when you compile your program to an executable but it will be present when you're running in the IDE. To test for it, add something like this to your project code:

CODE

Private Function RunningFromIDE() As Boolean

     RunningFromIDE = False

     If InStr(Command(), "RunningFromIDE") Then

         RunningFromIDE = True

     End If

 End Function



 Private Sub Form_Load()

     If RunningFromIDE Then

         MsgBox "Running from Inside the Visual Basic IDE"

     End If

     '...

 End Sub

[alhakimie]

[b]Use plain English key names for ImageList images

In a recent tip, we showed you how to associate icons in an ImageList with items in a ListView or TreeView. You might remember that we showed you how to reference the icon's index within the ImageList, like this:

CODE

 ListView1.ListItems.Add Key:="K1", Text:="Item 1", Icon:=1

Here's another tip: instead of a numeric index, it's just as easy to use a plain English key value instead. To do so, right-click on the ImageList control and choose Properties to display the control's custom property page. Then, add the icons to the ImageList as you normally would, but for each one, enter a plain English key name in the Key TextBox on the Images tab. You'll find these key names much easier to remember than arbitrary numeric indexes, especially when you have lots of different icons associated with your ListView or TreeView. Once you've added all the icons to the ImageList and provided each with a key name, refer to them in code like this:

CODE

 ListView1.ListItems.Add Key:="K1", Text:="Joe Blow", Icon:="User Icon"

 ListView1.ListItems.Add Key:="K2", Text:="Administrators", Icon:="Group Icon"

Also note that even though you've given your icons names, you can still refer to them by their numeric indexes as well. What makes this possible is the fact that the Icon and SmallIcon properties of the ListItem object (and the corresponding Image, SelectedImage, and ExpandedImage properties of the Node object for a TreeView) are of Variant type. This means they can hold either the numeric Index property or the String Key property of the corresponding ListImage object you loaded into the ImageList control.

This weekly tip is delivered to you as a free service from
Element K Journals,http://www.elementkjournals.com/tips.asp.[/code]

[alhakimie]

[b]Setting Status Bar text--is it Simple or more complex?


hen you add a StatusBar control to your project, how you set the status bar text depends on the control's Style property. As you know, the property accepts two values: 0-sbrNormal and 1-sbrSimple.

In 'simple' mode, the StatusBar control displays a single panel that extends the full width of the control. To assign text to the status bar, use the SimpleText property, like so:

CODE

SB1.SimpleText = "I am on top of it!"

In 'normal' mode, the StatusBar will contain multiple panels. You can then assign different text and images to each of these panels individually. To assign a caption to a specific panel, you access that StatusBar control's Panels collection, indicating a specific panel object within that collection. Then, you assign the text to the Pane's Text property. The following code shows how this works:

CODE

StatusBar1.Panels(1).Text = "This is the status!"

Note that the Panels collection is 1-based, as opposed to 0-based like other VB collections. As a result, the panel index numbers start with 1.

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp

[alhakimie]

[b]Dynamically add VB menu items at runtime

VB's Menu editor is a great way to create, at designtime, a menu for your project. Often, however, you may want to add menu items at runtime. Fortunately, Visual Basic makes it easy to do so. In essence, VB menu items are nothing more than special control arrays. And just as with any control array, if you want to dynamically add menu items at runtime, you must place at least one control on your project at designtime. Once you do so, you can then use the Load command to create new objects. The following code shows an example of how this might work. For this example, we added an invisible menu item named mnuShortCut to a form, and then added one submenu item to it, named mnuSubItem. We gave this submenu item an index of 0 and set its Caption to "Menu Item 1".

CODE

Private Sub Form_Load()

Dim x As Integer

For x = 1 To 4

   Load mnuSubItem(x)

   mnuSubItem(x).Caption = "Menu Item " & x + 1

   mnuSubItem(x).Visible = True

Next x

End Sub



Private Sub Form_MouseDown(Button As Integer, Shift As Integer, x As Single, Y As Single)

If Button = vbRightButton Then

   Me.PopupMenu mnuShortCut

End If

End Sub



Private Sub mnuSubItem_Click(Index As Integer)

MsgBox Index

End Sub

Notice that to display the shortcut menu, the code initiates the PopupMenu method during the form's MouseDown event. After testing to see if the right mouse button was pushed, the code then calls the shortcut menu, which our code created in the Load() event

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp

[alhakimie]

7 Ways you can improve your application performance

This article summaries 7 best practice techniques used by experts that are essential for improving your data access from code. The following practices are highlighted and explained -

1. Use stored procedures
2. Use a data access wrapper
3. Pool database connections
4. Use alternative transport mechanisms
5. Use field binding
6. Use parameterized queries
7. Never use cursors


[b]1. Use stored procedures

Never use inline queries to get data from your database when you can create a stored procedure. Microsoft has been saying it for years and if I have to explain that to one more programmer bunny I'll pop. To summarize:

1. Stored procedures can be changed without needing to recompile

[b]2. Procedures cache the query execution plan (QEP) so subsequent calls are faster

[b]3. Less chance of security holes by 'building' the query on the fly

[b]4. Less network overhead, only the procedure name and parameters are transmitted

[size=18][b]2. Use a data access wrapper

This site has a whole article explaining the purpose of a data access wrapper, but in essence this boils down to a design issue - it is a far better design than embedding data access code - other benefits include

1. Improved code reusability, and insulation from future changes to ADO or ADO.NET

[b]2. Guarantees consistent use of the best data access practices

[b]3. Simplifies data access for developers less familiar with ADO

[b]4. An object based wrapper allows easy deployment in COM+, and connection pooling


[size=18][b]3. Use connection pooling

Connection pooling will give your multi-user application a huge performance improvement. Believe it or not, but it can take longer for a connection to be established than it can to run the query or procedure itself!! By pooling the connections to your database via a transaction system like COM+, you can maintain several open connections to the database and each running application can simply make use of one of an already open connection rather than creating a new connection.


[size=18]4. Alternate transport methods

Many programmers use ADO to return recordsets from their data access layers or wrapper functions; but ADO provides two other alternate methods for accessing and returning data. Each method has it’s own disadvantages and advantages so you must choose carefully which method to use in each situation. If your not updating data, consider using an array as your transport mechanism, it allows easy random access to any record/field but can be 50% faster than a recordset!

CODE

  'Method #1 – ADO Recordsets

  Dim RS As ADODB.Recordset

  Set RS = CreateObject("ADODB.Recordset")



  'Method #2 - GetArray

  Dim RS As ADODB.Recordset

  Set RS.GetArray



  'Method #3 - GetString

  Dim RS As ADODB.Recordset

  Set RS.GetString

[size=18]5. Field Binding

You can access data from your recordset object in several ways

CODE

  'Method #1

  sCustomerName = RS(0)

Method #1 is fastest but is obviously less maintainable. If you must access fields by ordinal reference (ie you require the highest possible performance) then at least use a constant.

CODE

  'Method #2

  sCustomerName = RS("CustomerName")

Method #2 has to be the most commonly used and this is unfortunate as it is not necessarily the most efficient as ADO must find the fieldname inside the collection every time you reference it. It helps to remember that because visual basic provides default properties the method actually looks more like:

CODE

  'Method #2

  sCustomerName = RS.Fields("CustomerName").Value

CODE

  'Method #3

  Dim oField As ADODB.Field



  Set oField = RS("CustomerName")



  sCustomerName = oField.Value

Method #3 should be used in circumstances where you are looping though records, perhaps adding to a grid or listview. You only have to bind your field object to the recordset once, and then you can access it much faster. In recordsets that are wide or long the performance increase can be quite refreshing – web based development suffers much from overuse of methods one and two.


[size=18]6. Use parameterized queries

You can run queries on your database either by embedding the query in the application code or by placing the query inside a stored procedure on the server and calling the stored procedure. Your application will perform better and be more maintainable if you use a stored procedure instead of embedding the query. Either way, the best technique for calling procedures or running direct access queries on your database is to use the parameters collection. The example below shows you how to build a parameters collection for your command object (you can not build a stand alone parameters collection unfortunately).

CODE

'Build query



sQuery = "INSERT INTO Customer( CustomerID, CustomerName ) VALUES (?, ?)" OR



sQuery = "CUSP_InsertCustomerNameProcedure"







'Command properties



cmdUpdate.CommandText = sQuery



cmdUpdate.CommandType = adCmdText







'Build parameter list in code (one step)



cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("CustomerID", adInteger, adParamInput, 0, lCustomerID)







cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("CustomerName", adVarChar, adParamInput, 255, sCustomerName)







'Run query



cmdUpdate.Execute

You can also build parameters collection and then set the values in two steps, this can be useful if you are calling the same query over and over but with different values in the parameters:

CODE

'Build parameter list in code (two steps)



cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("CustomerID", adInteger, adParamInput)







cmdUpdate.Parameters.Append cmdUpdate.CreateParameter("CustomerName", adVarChar, adParamInput)







‘Now set parameter properties



cmdUpdate.Parameters("CustomerID ") = lCustomerID



cmdUpdate.Parameters("CustomerName") = sCustomerName

In the case of stored procedures, you can ask ADO to read the database and build the parameters collection for you automatically - as demonstrated below, however there is extra overhead in the round trip to the server to read the parameters, so never be seduced into using this technique. It’s easy, but costly.

CODE

‘Tell database we want the parameters for a stored procedure



cmdUpdate.CommandType = adCmdStoredProc



cmdUpdate.CommandText = “GetCustomers”







‘Get parameter properties from database



cmdUpdate.Parameters.Refresh







‘Set parameter properties



cmdUpdate.Parameters("CustomerID ") = lCustomerID



cmdUpdate.Parameters("CustomerName") = sCustomerName

Using parameter collections in conjunction with Microsoft SQL Server 2000 will also help the database engine to optimize and cache the execution plan for you queries. This is because the execution plan for each query is likely to be the same – only the parameters change. This way users running the same queries across the enterprise will benefit from the reuse of the query execution plan (QEP).



To help promote the use of this technique I suggest you building a data access wrapper – this will help your team standardize and streamline the way you run queries on the database. Using a data access wrapper will ensure your application always uses the latest techniques for accessing and updating data in your system. Click here to read about data access wrappers.



[size=18]7. Never use cursors

What can I say, databases are built for batch or 'set' orientated programming - selecting, updating, deleting multiple records at a time but unfortunately it seems humans have a tendency to want to deal with one record at a time, generally looping though them. Server side cursors which work on a row by row processing scheme offer the worst possible performance imaginable. So many times I've managed to impress people by removing their cursor and rewriting it as separate update statements, or using temporary tables and getting a 10 fold improvement in performance. Just don't use cursors, please..


url http://www.vbdatabase.com/Articles/DataAccess1.htm

[alhakimie]

How to simplify you data access with wrappers

When Data Access Wrappers (DAW) are discussed they are often confused by developers as being synomous with the popular term “Data Access Layer” (DAL). This article highlights the difference and goes on to suggest why you must access your code using a data access wrapper rather than directly through ADO or ADO.NET.

[size=18][b]What is the difference between a wrapper and the data-access layer?

First lets just the different between the DAW and the DAL. As figure #1 indicates you can see the DAW is infact normally a part of the data services layer in a tiered application. Of course, you don’t have to have a data access layer in your software just to have a wrapper –the argument to build a DAW is very strong regardless of whether you choose to use a data access layer or not.



[size=18][b]What is the wrapper?

The data access wrapper is simply that – a wrapper around the calls to ADO. A simple example (lacking some code!) would be to encapsulate the call to the ADO execute statement with your own statement, for example:

CODE

Public Sub Execute( sQuery As String ) As Boolean







  'Execute query



  Cmd.Execute sQuery







End Sub

Each time you need to execute your query rather than creating a command object and calling the execute function; you use your wrapper function.


[size=18][b]Are there performance issues with wrapping?

Pleasantly, no – the performance “overhead” of calling the wrapped function is very, very negligible. If you want to test this for yourself, you’ll need to use a high-resolution timer and multiple repeated calls to get a true evaluation of the difference!

Using a wrapper can in fact help improve performance by ensuring your database calls user the latest and greatest techniques – in this case parameterised queries. More importantly, it makes it easier to place your wrapper in COM+ (or MTS for those still using NT), and reap the benefits of object and connection pooling. On a recent real-life large system we recieved a [b]57% increase in performance when we did this!

[size=18]What does a real wrapper look like?

Before we look a the benefits of using a data access wrapper lets look at what a real wrapper looks like – the one shown here is loosely based on the Fitch & Mathers used by Microsofts scalability site; although we have made some improvements for even more performance.

[size=18][b]As you can see the wrapper performs the following functions:

[size=12][b]1. Creates the command object

[size=12]2. Evaluates the parameters for your query and builds a parameters collection


[size=12][b]3. Connects to the database (or uses an existing connection)


[size=12][b]4. Sets some general connection properties


[size=12][b]5. Executes the query


[size=12][b]6. Clean up


[size=12][b]7. Checks for errors occurring in the query execution




If you would like a copy of our data access layer then either download it separately here or download the CodeGenie utility and as it builds the data layer for you.

[size=18][b]What are the benefits of wrapping calls?

Building a database wrapper can help in many ways. First of all it will [b]simplify and standardise the way your application accesses the database. Immediate benefits can be realised if all your projects and team members are using the same standard. The wrapper helps to simplify calling code so that a query with parameters would look similar to:

CODE

'Update database



  Call oData.Execute("SP_UpdateCustomer", "", QO_UseStoredProc, _



           Array("@CustomerID", adInteger, 0, lCustomerID), _



           Array("@CustomerName", adWChar, 30, sCustomerName), _



           Array("@CustomerBalance", adCurrency, 0, cCustomerBalance))

This example also illustrates how you can more easily call stored procedures with parameters taking advantage of a helper function in the DAW to create the parameters collection. The same technique and DAW can be used even if you are calling ‘direct access’ queries from code:

CODE

 Dim sQuery







 sQuery = “Update Customer SET CustomerName = ?, CustomerBalance = ? WHERE “ &



          “CustomerID = ?”







 'Update database



 Call oData.Execute( sQuery, "", QO_UseStoredText, _



           Array("@CustomerBalance ", adCurrency, 0, cCustomerBalance), _



           Array("@CustomerName", adWChar, 30, sCustomerName), _



           Array("@CustomerID", adInteger, 0, lCustomerID))

This method not only executes the query faster, but makes life much simpler and secure than trying to code the query inline – you don’t have to fiddle around with quotes for starters! You can see how the wrapper also insulates your code somewhat from future changes to ADO. For COM+/MTS users drop your wrapper into COM+/MTS and you will immediately enable connection pooling and recive significantly improved performance.

[size=18]Security Issues Building Queries On the Fly


Just a note on security – in some circumstances it is possible for advanced end users or hackers to execute their own queries on your database if you are building queries on the fly and executing them! For example, imagine building a typical query to changethe customer name:

CODE

sQuery = “UPDATE Customer SET CustomerName = ‘” & sCustomerName & “’ WHERE CustomerID = “ & lCustomerID

It is possible for your user to enter something like (off the top of my head, it may not be totally accurate!) ‘;DELETE FROM Customer; SELECT `Toasty!



Different databases will act in different ways however with a limited amount of energy a hacker could effectively modify your query into three separate queries:

CODE

UPDATE Customer SET CustomerName = ``



DELETE FROM Customer



SELECT ‘Toasty` WHERE CustomerID = 10

This is a common problem on the web where programmers have mistakenly coded queries directly into the ASP web page where there are limited validations on the length or type of data the user enters. Sure, the user must know the table names they want to access but unsecured master databases are good targets, as are tablenames like Users, Accounts, Logins..

[size=18][b]Should I wrap with a class, or is a .bas module OK?

Simple answer to this – while you could create your wrapping functions inside a VB module, I recommend sticking with the object-orientated approach for this, in fact this is essential if you are not using COM+ (or MTS) and wish to persist your connections.

[b]Final Word


Other benefits of wrapping your ADO calls can include performance and of course the obvious abstraction. Because all the calls you do use the same mechanism, its much easier to use the latest techniques (for example: parameter collections) as a standard for your entire application.



Introducing a layer between ADO and your application will also help future proof your application for changes to core data access routines. Those developers that already use data access wrappers had much less difficulty in moving from DAO to ADO, in many cases it was simply a few lines of code, not a rewrite.



Finally, creating a DAW can assist you to more seamlessly move between COM+ (or Microsoft Transaction Server - MTS) and non-COM+ environments - by using code stubs in an appropriate fashion to abstract you from the details of managing transactions.

[alhakimie]

[b]How you can improve performance on the server

Do not be deceived by the apparent fast performance in your development environment. Unless you have a great testing and staging environment - and most people don't - performance problems often do not manifest themselves until you reach the production environment, and by then it's too late. Once the problem has reached the customer it costs at least 100 times as much to fix, so use techniques covered in this article to help prevent the problem in the first instance.

[b][size=18]Indexes

It is very important your index supports the way your query accesses the table. In the example below, the table is keyed on CustomerID only with no secondary indexes. If you look up an individual record by CustomerID performance will be optimal, however if you access the data any other combination of columns then your database performance is going to be relatively poor, no matter what techniques you employ when programming your components.

CODE

SELECT * FROM Customers WHERE City = 'London'



SELECT * FROM Customers WHERE CompanyName = 'Around the Horn'

Both example queries above force the database engine to search though every row in the table to find the record(s) matching the criteria as there are no indexes on the CompanyName or City columns. Even though the second query would only ever return one matching company name, the database engine does not know this so it searches the entire table to see if there are more matching records. This searching process is called a table scan and is reflected in the query plan shown below:



SQL Server has two types of indexes: clustered and nonclustered. A clustered index keeps the data rows of a table physically stored in index order. Because there is only one physical order to the rows in a table, there can be a maximum of one clustered index per table. All other indexes on a table are nonclustered.

[b][size=18]Clustered Indexes

A clustered index is usually the most efficient method for finding a group of rows in a table, so choosing the column(s) by which to define the clustered index is an important part of application tuning. Be careful creating clustered indexes arbitrarily as each non-clustered index automatically inherits the keyed columns from the clustered index – this can make your non-clustered indexes very large – try to minimise your use of wide clustered indexes, especially if you have multiple non-clustered indexes on the same table.



You may choose to put the clustered index on a foreign key field where you wish to quickly scan a range of values - for example a particular batch, site, or customer, rather than jumping around the table retrieving data from various regions. In the example table above, you might place a clustered index on City so that all records in the same city are grouped together.

[b][size=18]Non-Clustered Indexes

When accessing the data though a non-clustered index, the db engine must first lookup the appropriate value in the index, then go to the table to read the actual data. While this lookup is an overhead, it is still much faster than performing a table scan (looking through the entire table) to find each record. Most database engines are clever enough to skip the lookup on the actual data table if the index contains all the columns necessary to satisfy the query. Purposely creating an index that contains all the columns a query may want is called creating a covering index, as you are effectively creating a new copy of the table sorted in a different order.



Remove unused nonclustered indexes as they slow down inserts, updates, and deletes on a table (remember that data must be inserted/updated/deleted from the index too). Unused indexes also consume space on disk and possibly in memory so remove them if they are not being utilized effectively.

[b][size=18]Table Hot Spots – (Clustered and Non-Clustered Indexes)

Be wary of creating transactional “hot-spots” on your tables in high-volume OLTP environments, where you may be inserting lots of rows at a particular point in the table – usually at the end. Common examples of this are found in tables with counter (auto-increment) fields. If the data is keyed and physically sorted by the counter field then new records are constantly appended in the same data-page (area). This adversely affects performance (creates locking contention) especially when many sessions are trying to access the area simultaneously.

[b][size=18]Distribution Statistics

An index includes distribution statistics about the data values that is used by the database engine to determine if a particular index will be useful to solve a query. When a large number of rows are added or changed after an index is defined on a table, these statistics will be inaccurate until you run an UPDATE STATISTICS statement (ms sql 7.0). Out of date statistics can mislead the server into using a poor index or a table scan to resolve a query, causing poor query performance and reduced concurrency. An update operation that uses a table scan instead of an index, for example, will lock the entire table for the duration of the transaction.



It is important to update statistics regularly on the active tables in your application. Microsoft SQL Server can be setup to do this automatically using the database maintenance wizard.




[b][size=18]Invalidating Your Indexes

If you’ve got indexes, use them – Applying functions on indexed columns is a common mistake that can invalidate the use of your indexes. A couple of examples are:

CODE

..WHERE LEFT( customer_name, 2 ) = ‘AN’              Will not use the indexes – forcing a table scan



..WHERE amount * 1.125 > $100                               Again, amount may be in the index but amount multiplied by 1.125 is not, so the index can’t be used.

These could be re-written several ways, but perhaps the following is the best

CODE

..WHERE customer_name LIKE ‘AN%’



..WHERE amount > $112.50

This rule applies in certain cases where you are joining two tables with different datatypes, or when trimming character data.

CODE

WHERE CONVERTt(CHAR (10), table1.customer_no) = CONVERT (CHAR (10), table2.customer_no)

In this instance, good database design will ensure your tables both have the correct datatype.

[b][size=18]Blocking Locks and Deadlocks

Applications use database locks to control data integrity in multiuser concurrency situations. Poor database design coupled with uncoordinated business processes can cause crippling database lock contention and destroy your application's performance.



Most databases offer support for locking records at different levels including row, page, and table locking. While normal processing in a multiuser environment can conveniently use locks, your application will run faster without locks. Considerable design effort should be applied to table design, indexing strategy, and query optimisation to avoid locking situations.



The following topics discuss blocking locks, deadlocks, and present some design strategies for avoiding them.

[b][size=18]Blocking Locks



A blocking lock occurs when one lock causes another process to wait (in a queue) until the current process is entirely done with the resources. As soon as the first process is complete, the blocked process resumes operation. In a normal server environment, infrequent blocking locks are acceptable. But if blocking locks are common (rather than infrequent), there is probably some kind of design or query implementation problem.



Some of the common design problems that cause blocking locks are very wide tables, insufficient indexes, and tables that are not completely normalised. If your application is using SQL Server, you can monitor the total number of blocking locks by using the User Activity Monitor in Microsoft SQL Server's SQL Enterprise Manager, or sp_lock in transact sql to quickly identify all locks on the system, or for a specific process.


[b][size=18]Deadlocks



Consider the situation where one partially finished transaction must wait for another transaction to complete. At the same time, the other partially finished transaction must also wait for the original transaction to complete. This is called a deadlock: when each transaction is waiting for resources used by the other. When such a deadlock occurs, the database typically cancels one of the transactions automatically.



Long simultaneous transactions that lock and unlock resources must be carefully designed to avoid deadlocks.


[b][size=18]Client / Server Deadlocks



While most databases do a good job of automatically identifying deadlocks, they will not detect a “client/server” deadlock. These occur when an application creates two connections to the database, and a transaction on one connection (possibly run asynchronously, but not necessarily) causes a transaction on the other two wait indefinitely. The net result is your application will stall either forever, or until it times out.


[b][size=18]Escalation



Escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead. Most databases automatically escalate row locks and page locks into table locks when a transaction exceeds its escalation threshold.



This can be the cause of blocking locks. If you have many page level locks; SQL may try to obtain a table level lock but may not be able to due to another process holding a single exclusive lock on a different page – even if your application many not be needing to use that page.
Lock-Avoiding Design Strategies



Most blocking problems happen because a single process holds locks for an extended period of time, causing a chain of blocked processes, all waiting on other processes for locks. There are a few design strategies that can reduce the occurrence of blocking locks and deadlocks:

· Use clustered indexes on high-usage tables.



· [b] Make sure that UPDATE and DELETE statements use an index If not, the database may not be able to perform row-level locking. Without an index at all, the entire table will be locked in a write operation, with a poor index, the page (or even table) would be locked rather than a single row.



· [b] Ensure there are not commit or rollback conflicts in nested transactions.



· [b] Use stored procedures. Stored procedures are already parsed, normalized, and compiled, and they run very quickly.



· [b]Put data service components on the database computer. Deploying data service components on the same computer as the database engine can greatly reduce network traffic and improve overall data throughput.



· [b] Keep lookup tables locally. There are usually instances in which read-only data is accessed fairly often by your application. By keeping that data locally, lookups become extremely fast.



· [b] Do not submit queries with long execution times. Commonly this happens when intermixing decision support queries and online transaction processing (OLTP) queries on the same database. Look for ways to optimise the query, by changing indexes, breaking a large, complex query into simpler queries, or running the query during off hours or on a separate computer.



· [b] Rollback or commit queries that were cancelled. If you cancel a query, for example, using the Open Database Connectivity (ODBC) sqlcancel function, also issue the required number of ROLLBACK and COMMIT statements. Cancelling the query does not automatically roll back or commit the transaction. All locks acquired within the transaction are retained after the query is cancelled. Applications must properly manage transaction nesting levels by committing or rolling back cancelled transactions.



· [b] Process all returns to completion. Especially a problem with cursors where you have asked the server for a range of rows but only fetched some of them, leaving locks on the tables potentially blocking other users.



Some Last Resort Options – Break out the emergency kit

· [b] Avoid high row count SQL statements that can cause a table lock. due to decreased performance (but increased concurrency).



· [b] Break long transactions up into many shorter transactions. With SQL Server, you can use "bound connections" to control the execution sequence of the shorter transactions. Shorter transactions are the key; bound connections should only be used as a last resort in specialist situations.

[b][size=18]SQL Server Network Libraries

When you install the SQL Server ODBC driver on a computer, the only network library usually installed is Named Pipes; for performance reasons, you should install additional libraries. The most direct way to get the additional network libraries is to install the SQL Server client utilities, which will install the additional network libraries. After installation, they are available through the ODBC Data Source Administrator. The network library with the best performance is TCP/IP.



Many larger corporate networks run multiple protocols to accommodate different generations of network expansion. Some consideration should be given to the interaction of these protocols. For example, some servers running IPX will continually announce themselves with a broadcast message. This message leads to additional network traffic that may have an adverse effect on system performance. Eliminating other protocols and just running TCP/IP may not be practical, but it may be possible to segment or isolate different sections of the network to eliminate this additional network traffic in areas your applications are running.

[alhakimie]

[b]How you can improve performance on the server

Do not be deceived by the apparent fast performance in your development environment. Unless you have a great testing and staging environment - and most people don't - performance problems often do not manifest themselves until you reach the production environment, and by then it's too late. Once the problem has reached the customer it costs at least 100 times as much to fix, so use techniques covered in this article to help prevent the problem in the first instance.

[b][size=18]Indexes

It is very important your index supports the way your query accesses the table. In the example below, the table is keyed on CustomerID only with no secondary indexes. If you look up an individual record by CustomerID performance will be optimal, however if you access the data any other combination of columns then your database performance is going to be relatively poor, no matter what techniques you employ when programming your components.

CODE

SELECT * FROM Customers WHERE City = 'London'



SELECT * FROM Customers WHERE CompanyName = 'Around the Horn'

Both example queries above force the database engine to search though every row in the table to find the record(s) matching the criteria as there are no indexes on the CompanyName or City columns. Even though the second query would only ever return one matching company name, the database engine does not know this so it searches the entire table to see if there are more matching records. This searching process is called a table scan and is reflected in the query plan shown below:



SQL Server has two types of indexes: clustered and nonclustered. A clustered index keeps the data rows of a table physically stored in index order. Because there is only one physical order to the rows in a table, there can be a maximum of one clustered index per table. All other indexes on a table are nonclustered.

[b][size=18]Clustered Indexes

A clustered index is usually the most efficient method for finding a group of rows in a table, so choosing the column(s) by which to define the clustered index is an important part of application tuning. Be careful creating clustered indexes arbitrarily as each non-clustered index automatically inherits the keyed columns from the clustered index – this can make your non-clustered indexes very large – try to minimise your use of wide clustered indexes, especially if you have multiple non-clustered indexes on the same table.



You may choose to put the clustered index on a foreign key field where you wish to quickly scan a range of values - for example a particular batch, site, or customer, rather than jumping around the table retrieving data from various regions. In the example table above, you might place a clustered index on City so that all records in the same city are grouped together.

[b][size=18]Non-Clustered Indexes

When accessing the data though a non-clustered index, the db engine must first lookup the appropriate value in the index, then go to the table to read the actual data. While this lookup is an overhead, it is still much faster than performing a table scan (looking through the entire table) to find each record. Most database engines are clever enough to skip the lookup on the actual data table if the index contains all the columns necessary to satisfy the query. Purposely creating an index that contains all the columns a query may want is called creating a covering index, as you are effectively creating a new copy of the table sorted in a different order.



Remove unused nonclustered indexes as they slow down inserts, updates, and deletes on a table (remember that data must be inserted/updated/deleted from the index too). Unused indexes also consume space on disk and possibly in memory so remove them if they are not being utilized effectively.

[b][size=18]Table Hot Spots – (Clustered and Non-Clustered Indexes)

Be wary of creating transactional “hot-spots” on your tables in high-volume OLTP environments, where you may be inserting lots of rows at a particular point in the table – usually at the end. Common examples of this are found in tables with counter (auto-increment) fields. If the data is keyed and physically sorted by the counter field then new records are constantly appended in the same data-page (area). This adversely affects performance (creates locking contention) especially when many sessions are trying to access the area simultaneously.

[b][size=18]Distribution Statistics

An index includes distribution statistics about the data values that is used by the database engine to determine if a particular index will be useful to solve a query. When a large number of rows are added or changed after an index is defined on a table, these statistics will be inaccurate until you run an UPDATE STATISTICS statement (ms sql 7.0). Out of date statistics can mislead the server into using a poor index or a table scan to resolve a query, causing poor query performance and reduced concurrency. An update operation that uses a table scan instead of an index, for example, will lock the entire table for the duration of the transaction.



It is important to update statistics regularly on the active tables in your application. Microsoft SQL Server can be setup to do this automatically using the database maintenance wizard.




[b][size=18]Invalidating Your Indexes

If you’ve got indexes, use them – Applying functions on indexed columns is a common mistake that can invalidate the use of your indexes. A couple of examples are:

CODE

..WHERE LEFT( customer_name, 2 ) = ‘AN’              Will not use the indexes – forcing a table scan



..WHERE amount * 1.125 > $100                               Again, amount may be in the index but amount multiplied by 1.125 is not, so the index can’t be used.

These could be re-written several ways, but perhaps the following is the best

CODE

..WHERE customer_name LIKE ‘AN%’



..WHERE amount > $112.50

This rule applies in certain cases where you are joining two tables with different datatypes, or when trimming character data.

CODE

WHERE CONVERTt(CHAR (10), table1.customer_no) = CONVERT (CHAR (10), table2.customer_no)

In this instance, good database design will ensure your tables both have the correct datatype.

[b][size=18]Blocking Locks and Deadlocks

Applications use database locks to control data integrity in multiuser concurrency situations. Poor database design coupled with uncoordinated business processes can cause crippling database lock contention and destroy your application's performance.



Most databases offer support for locking records at different levels including row, page, and table locking. While normal processing in a multiuser environment can conveniently use locks, your application will run faster without locks. Considerable design effort should be applied to table design, indexing strategy, and query optimisation to avoid locking situations.



The following topics discuss blocking locks, deadlocks, and present some design strategies for avoiding them.

[b][size=18]Blocking Locks



A blocking lock occurs when one lock causes another process to wait (in a queue) until the current process is entirely done with the resources. As soon as the first process is complete, the blocked process resumes operation. In a normal server environment, infrequent blocking locks are acceptable. But if blocking locks are common (rather than infrequent), there is probably some kind of design or query implementation problem.



Some of the common design problems that cause blocking locks are very wide tables, insufficient indexes, and tables that are not completely normalised. If your application is using SQL Server, you can monitor the total number of blocking locks by using the User Activity Monitor in Microsoft SQL Server's SQL Enterprise Manager, or sp_lock in transact sql to quickly identify all locks on the system, or for a specific process.


[b][size=18]Deadlocks



Consider the situation where one partially finished transaction must wait for another transaction to complete. At the same time, the other partially finished transaction must also wait for the original transaction to complete. This is called a deadlock: when each transaction is waiting for resources used by the other. When such a deadlock occurs, the database typically cancels one of the transactions automatically.



Long simultaneous transactions that lock and unlock resources must be carefully designed to avoid deadlocks.


[b][size=18]Client / Server Deadlocks



While most databases do a good job of automatically identifying deadlocks, they will not detect a “client/server” deadlock. These occur when an application creates two connections to the database, and a transaction on one connection (possibly run asynchronously, but not necessarily) causes a transaction on the other two wait indefinitely. The net result is your application will stall either forever, or until it times out.


[b][size=18]Escalation



Escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead. Most databases automatically escalate row locks and page locks into table locks when a transaction exceeds its escalation threshold.



This can be the cause of blocking locks. If you have many page level locks; SQL may try to obtain a table level lock but may not be able to due to another process holding a single exclusive lock on a different page – even if your application many not be needing to use that page.
Lock-Avoiding Design Strategies



Most blocking problems happen because a single process holds locks for an extended period of time, causing a chain of blocked processes, all waiting on other processes for locks. There are a few design strategies that can reduce the occurrence of blocking locks and deadlocks:

· Use clustered indexes on high-usage tables.



· [b] Make sure that UPDATE and DELETE statements use an index If not, the database may not be able to perform row-level locking. Without an index at all, the entire table will be locked in a write operation, with a poor index, the page (or even table) would be locked rather than a single row.



· [b] Ensure there are not commit or rollback conflicts in nested transactions.



· [b] Use stored procedures. Stored procedures are already parsed, normalized, and compiled, and they run very quickly.



· [b]Put data service components on the database computer. Deploying data service components on the same computer as the database engine can greatly reduce network traffic and improve overall data throughput.



· [b] Keep lookup tables locally. There are usually instances in which read-only data is accessed fairly often by your application. By keeping that data locally, lookups become extremely fast.



· [b] Do not submit queries with long execution times. Commonly this happens when intermixing decision support queries and online transaction processing (OLTP) queries on the same database. Look for ways to optimise the query, by changing indexes, breaking a large, complex query into simpler queries, or running the query during off hours or on a separate computer.



· [b] Rollback or commit queries that were cancelled. If you cancel a query, for example, using the Open Database Connectivity (ODBC) sqlcancel function, also issue the required number of ROLLBACK and COMMIT statements. Cancelling the query does not automatically roll back or commit the transaction. All locks acquired within the transaction are retained after the query is cancelled. Applications must properly manage transaction nesting levels by committing or rolling back cancelled transactions.



· [b] Process all returns to completion. Especially a problem with cursors where you have asked the server for a range of rows but only fetched some of them, leaving locks on the tables potentially blocking other users.



Some Last Resort Options – Break out the emergency kit

· [b] Avoid high row count SQL statements that can cause a table lock. due to decreased performance (but increased concurrency).



· [b] Break long transactions up into many shorter transactions. With SQL Server, you can use "bound connections" to control the execution sequence of the shorter transactions. Shorter transactions are the key; bound connections should only be used as a last resort in specialist situations.

[b][size=18]SQL Server Network Libraries

When you install the SQL Server ODBC driver on a computer, the only network library usually installed is Named Pipes; for performance reasons, you should install additional libraries. The most direct way to get the additional network libraries is to install the SQL Server client utilities, which will install the additional network libraries. After installation, they are available through the ODBC Data Source Administrator. The network library with the best performance is TCP/IP.



Many larger corporate networks run multiple protocols to accommodate different generations of network expansion. Some consideration should be given to the interaction of these protocols. For example, some servers running IPX will continually announce themselves with a broadcast message. This message leads to additional network traffic that may have an adverse effect on system performance. Eliminating other protocols and just running TCP/IP may not be practical, but it may be possible to segment or isolate different sections of the network to eliminate this additional network traffic in areas your applications are running.

[alhakimie]

Use the Inet control to post querystring data to a Web site

The Microsoft Internet Transfer control, or Inet, provides a great way to connect an application to the Internet without any visible GUI. In the past, you've no doubt used this control in conjunction with an FTP site. However, you can also use it to retrieve Web page HTML, or even post data to a Web site.

To retrieve a Web page, use the control's OpenUrl method. This method takes one main argument--the URL of the site. When you issue this command, the Inet control initiates a synchronous request to the Web page in question. That is, no other code will execute until the Inet control receives the Web page HTML. The following code shows how to use this method:

Dim sHTML As String
sHTML = Inet1.OpenUrl("http://www.elementkjournals.com")

You can also use the OpenUrl method to post GET data to a Web site. To do so, simply append the querystring data to the end of the URL, as it would appear in a Web browser. So, if a Web page accepted two pieces of data named SID and UName, you'd pass along a URL like this:

Dim sHTML As String
Dim URL as String

URL = "http://www.myFunkyWebSite.com?SID=2934155&UName=FredFlintstone"
sHTML = Inet1.OpenUrl(URL)

Under these circumstances, the resulting HTML in sHTML would contain whatever response www.myFunkyWebSite.com returned after posting the data. If you use this method with GET data, don't forget to URL-encode any of the passed values so they don't contain illegal characters.

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkj...ls.com/tips.asp

[alhakimie]

The Locals window can give you misleading information about ADO Parameter objects

As you may know, you can carry out many kinds of SQL operations using the ADO Command object with an associated Parameters collection. For example, you might want to execute a stored procedure that accepts three parameters. In Visual Basic, the corresponding Parameters collection would be indexed 0-3, but be warned: if you use the Locals window during your application debugging and examine the Parameters collection, there's a bug in VB that enumerates the Parameters collection using a 1-based index. So what looks in the Locals window like Parameters(1) is actually Parameters(0), and so on through the entire collection!

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp

[alhakimie]

[b]Easily determine whether a give year is a leap year in Visual Basic

Determining whether a particular year is a leap year is extremely easy in Visual Basic if you just use the IsDate function. By using this function, you can avoid programming any complex conditional to implement the algorithm for identifying leap years. Instead, just let VB do the work. Here's how:

CODE

Public Function IsLeapYear(intYear As Integer) As Boolean

IsLeapYear = IsDate("2/29/" & intYear, "mm/dd/yyyy")

End Function

You might want to add an extra check to ensure the function is called with a four-digit year, but other than that, the function will solve your leap-year problems.

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp

[alhakimie]

[b]Use the VB StrConv function to convert strings to proper case (Title Case)

As you know, the UCase and LCase functions allow you to convert the contents of string variables into all upper- and all lower-case, respectively. But what if you want to convert a string into proper case, that is, where each word begins with a capital letter? For that, you can use the often overlooked StrConv function, like this:

CODE

Dim strName

strName = "john q. public"

strName = StrConv(strName, vbProperCase)

'strName now contains "John Q. Public"

You can even use StrConv instead of UCase and LCase. Just use the vbLowerCase and vbUpperCase parameters in place of vbProperCase.

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp

[alhakimie]

When NOT to use combined conditionals in VB

In an effort to make your code sleak and elegant, you'll no doubt often combine conditions into a single statement with the help of a logical operator, as in:

CODE

If X > Y And Y > Z Then

'Do Something

End If

Keep in mind, though, that this merging of conditions may not always be appropriate. In fact, in some cases, it may even result in an error.

Because VB evaluates an entire statement, you'll most likely encounter an error when the second half of a condition relies on the existance of an object or property for which you're testing in the first half of the condition. For instance, consider the following conditional:

CODE

If Not myObj Is Nothing And myObj.Price = 4.95 Then

'Do Something

End If

If myObj is in fact Nothing, you may expect that this test will simply evaluate to False. However, VB being VB, it also tries to evaluate the second half of the condition, which uses a property of myObj--which we've already determined doesn't exist. As a result, instead of evaluating to False, VB triggers an error.

As an alternative to these combined conditionals, break them apart into nested statements, like so:

CODE

If Not myObj Is Nothing Then

If myObj.Price = 4.95 Then

'Do Something

End If

End If

[alhakimie]

[b]What if you *WANT* to display the "&" in your controls'
captions?

As you know, including an ampersand (&) in the caption of
a control, such as a CommandButton, causes the character
immediately following the ampersand to be underlined and
treated as a shortcut key for the control. Pressing the ALT
key with the underlined character for a CommandButton, for
instance, triggers its Click event.

But what if you *want* the ampersand to show up? For
example, you might want to caption a CommandButton as
"Check & Run". But if you do, you'll actually see
"Check _Run", which is clearly not right. The workaround
for this is simple: just double the ampersand. For this
example, that means setting the CommandButton's Caption
property to Check && Run. This works like a charm!

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp

[alhakimie]

Use a simple API function to convert long filenames into
short ones with VB 6.0


Using Kernel32.dll's GetShortPathName, you can easily convert
long filenames into short ones recognizable by MS-DOS. The
code to accomplish this is quite straightforward, as shown
below:

CODE

Private Declare Function GetShortPathName Lib

"Kernel32.dll" Alias "GetShortPathNameA" _

(ByVal lpszLongPath As String, ByVal

lpszShortPath As String, ByVal cchBuffer As Long) As Long



Private Function GetShortFileName(ByVal FileName

As String) As String

Dim lngLength As Long

Dim strShortPath As String

Const PATH_LEN As Long = 164



strShortPath = String$(PATH_LEN + 1, 0)

lngLength = GetShortPathName(FileName,

strShortPath, PATH_LEN)

GetShortFileName = Left$(strShortPath,

lngLength)

End Function



Private Sub Command1_Click()

Text2.Text = GetShortFileName(Text1.Text)

End Sub

Give it a try. All you need is a pair of TextBox controls and
a CommandButton. Type a long filename into the first TextBox
(C:Program FilesMicrosoft Visual StudioVB98VB6.EXE will
do the trick), then click the CommandButton and watch as the
API turns it into C:PROGRA~1MIAF9D~1VB98VB6.EXE.


This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp

[alhakimie]

[b]Play Internal and External WAV files from VB 6.0

With the help of a simple mulitmedia API function, you
can play both external WAV files as well as any of the
predefined system sounds stored in the registry. The function
is easy to use, as shown in the following code:

CODE

Private Const SND_ALIAS As Long = &H10000

Private Const SND_ASYNC As Long = &H1

Private Const SND_FILENAME As Long = &H20000

Private Const SND_NODEFAULT As Long = &H2



Public Enum SoundSource

ssFile = SND_FILENAME

ssRegistry = SND_ALIAS

End Enum



'Sounds predefined in the registry

Private Const ssDefault = ".Default"

Private Const ssGPF = "AppGPFault"

Private Const ssClose = "Close"

Private Const ssEmptyRecycleBin = "EmptyRecycleBin"

Private Const ssMailBeep = "MailBeep"

Private Const ssMaximize = "Maximize"

Private Const ssMenuCommand = "MenuCommand"

Private Const ssMenuPopUp = "MenuPopup"

Private Const ssMinimize = "Minimize"

Private Const ssOpen = "Open"

Private Const ssRestoreDown = "RestoreDown"

Private Const ssRestoreUp = "RestoreUp"

Private Const ssSystemAsterisk = "SystemAsterisk"

Private Const ssSystemExclaimation = "SystemExclaimation"

Private Const ssSystemExit = "SystemExit"

Private Const ssSystemHand = "SystemHand"

Private Const ssSystemQuestion = "SystemQuestion"

Private Const ssSystemStart = "SystemStart"



Private Declare Function PlaySoundA Lib "winmm.dll"

       (ByVal lpszName As String, ByVal hModule As Long,

       ByVal dwFlags As Long) As Long



Private Function PlaySound(SoundName As String, Source As SoundSource) As Boolean

PlaySound = False

If PlaySoundA(SoundName, 0, Source + SND_ASYNC + SND_NODEFAULT) Then

PlaySound = True

End If

End Function

To play a sound from the registry, specify one of the predefined
constants as well as a Source value of ssRegistry; for an external
WAV, provide the path and filename together with a Source of ssFile,
as shown here:

CODE

  PlaySound ssMailBeep, ssRegistry

   PlaySound "c:winntmediatada.wav", ssFile

[alhakimie]

[b]Retrieve WAV file information from VB 6.0

Ever wanted to programmatically determine the number of
stereo channels or the sampling rate of an audio clip?
Standard WAV files are merely a specific case of the more
generalized RIFF file format (a Google search will turn up
as much as you could ever want to know about this
ubiquitous file format), so you can read this information
out of the raw binary data contained in the file. The
following code snippet provides a simple demonstration:

CODE

'RIFF WAV Header

Private Type WAVHeader

RiffFormat As Long

ChunkSize As Long

ChunkID As Long

Fmt As Long

WaveFormat As Integer

Channels As Integer '0 = mono, 1 = stereo

SamplesPerSecond As Long

AverageBytesPerSecond As Long 'Divide by

1000 for kHz, e.g., 11.025kHz, 22.05kHz

BlockAlign As Integer

End Type





Private Sub GetWaveInfo(ByVal FileName As String,

ByRef wh As WAVHeader)

Dim FileNum As Integer

FileNum = FreeFile

Open FileName For Binary Access Read

As #FileNum

Get #FileNum, , wh

Close #FileNum

End Sub



Private Sub Command1_Click()

Dim wh As WAVHeader

GetWaveInfo "c:winntmediaThe Microsoft

Sound.wav", wh

MsgBox "Sample Rate: " &

wh.AverageBytesPerSecond / 1000 & " kHz"

End Sub

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp

p/s Selamat Bekerja Kepada yang telah bercuti

[alhakimie]

[b]Need a quote in VB? Use Chr (34)!


Often, when you generate text for a message box or some
other control in Visual Basic, you may actually want to
embed a quotation mark into the resulting string. Of course,
you can always quote a quotation mark, like so:

CODE

Dim str As String

str = "Hello, my so-called " & """" & "World!" & """"

MsgBox str

Here, we've placed four quotation marks in a row to get a
single quote in the resulting string.

While this method works, frankly, we find it annoying and
somewhat confusing. As an alternative, you can use the Chr()
function to insert the quote directly. A quotation mark uses
the ANSI character code 34. With this in mind, you might use
the following:

CODE

Dim str As String

'str = "Hello, my so-called " & Chr(34) & "World!" & Chr(34)

MsgBox str

Or even more handy

CODE

Dim str As String, QQ As String

QQ = Chr(34)

str = "Hello, my so-called " & QQ & "World!" & QQ

MsgBox str

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp

[alhakimie]

FREETHREADER COMPONENT FOR VB6

Though VB6 does not natively support free threading, this ActiveX library allows you to finally create multiple threads. Make them communicate, write delegate functions (similarly to VB.NET), and use Structured Exception Handling. The full source code (in VB6 and Assembler) and some sample projects are provided. Download it from the File Bank. (from VB-2-the-Max)

http://archive.devx.com/free/newsletters/d...Newsletters.asp

[alhakimie]

Display the most recently added item in a VB 6.0 ListView control

Depending on how may items you add to VB 6.0's ListView
control, they may extend beyond the control's main viewport.
At times, you may want each new item to be visible in the
display. Fortunately, VB provides an easy way to do so. In
essence, after assigning a new List Item, you tell VB to
select it. Then, you indicate that this item should be
visible. The control's SelectedItem property lets your code
select a specific item, and the EnsureVisible property
determines if an item appears in the viewport.

To see how these two properties work, launch a new VB
project and place a ListView control on the default form.
Then, right click on the form and select View Code from the
resulting menu. In the Code window, add the following code:

CODE

Private Sub Form_Load()

Dim x As Integer



With ListView1

For x = 1 To 100

ListItems.Add Key:="item" & x, Text:="List Item " & x

Next x

SelectedItem = .ListItems(.ListItems.Count)

SelectedItem.EnsureVisible

End With

End Sub

When you run the project, VB displays the last item in the list.

[alhakimie]

[b]Remove leading zeros in VB 6.0 from a numeric string

At times, your code may encounter character input that contains
leading zeros--especially when dealing with CSV or legacy
databases. To quickly strip these zeros while maintaining the
string's status as a string, try using the Format$() function,
like so:

CODE

Dim strNums As String

strNums = "012345"

strNums = Format$(strNums, "#")

The number symbol (#) tells VB to reformat the number without
any leading zeros.

This weekly tip is delivered to you as a free service from
Element K Journals, http://www.elementkjournals.com/tips.asp