• At this point, we know how to use the data control and associated data bound tools to access a database. The power of Visual Basic lies in its ability to manipulate records in code. Such tasks as determining the values of particular fields, adding records, deleting records, and moving from record to record are easily done. This allows us to build a complete database management system (DBMS).
• We don’t want to change the example database, BIBLIO.MDB. Let’s create our own database to change. Fortunately, Visual Basic helps us out here. The Visual Data Manager is a Visual Basic Add-In that allows the creation and management of databases. It is simple to use and can create a database compatible with the Microsoft Jet (or Access) database engine.
• To examine an existing database using the Data Manager, follow these steps:
1. Select Visual Data Manager from Visual Basic’s Add-In menu (you may be asked if you want to add SYSTEM.MDA to the .INI file - answer No.)
2. Select Open Database from the Data Manager File menu.
3. Select the database type and name you want to examine.
Once the database is opened, you can do many things. You can simply look through the various tables. You can search for particular records. You can apply SQL queries. You can add/delete records. The Data Manager is a DBMS in itself. You might try using the Data Manager to look through the BIBLIO.MDB example database.
• To create a new database, follow these steps:
1. Select Visual Data Manager from Visual Basic’s Add-In menu (you may be asked if you want to add SYSTEM.MDA to the .INI file - answer No.)
2. Select New from the Data Manager File menu. Choose database type (Microsoft Access, Version 7.0), then select a directory and enter a name for your database file. Click OK.
3. The Database window will open. Right click the window and select New Table. In the Name box, enter the name of your table. Then define the table’s fields, one at a time, by clicking Add Field, then entering a field name, selecting a data type, and specifying the size of the field, if required. Once the field is defined, click the OK button to add it to the field box. Once all fields are defined, click the Build the Table button to save your table.
Tuesday, February 2, 2010
Data Links
• After placing a data control on a form, you set the ConnectionString property. The ADO data control can connect to a variety of database types. There are three ways to connect to a database: using a data link, using an ODBC data source, or using a connection string. In this lesson, we will look only at connection to a Microsoft Access database using a data link. A data link is a file with a UDL extension that contains information on database type.
• If your database does not have a data link, you need to create one. This process is best illustrated by example. We will be using the BIBLIO.MDB database in our first example, so these steps show you how to create its data link:
1. Open Windows Explorer.
2. Open the folder where you will store your data link file.
3. Right-click the right side of Explorer and choose New. From the list of files, select Microsoft Data Link.
4. Rename the newly created file BIBLIO.UDL
5. Right-click this new UDL file and click Properties.
6. Choose the Provider tab and select Microsoft Jet 3.51 OLE DB Provider (an Access database).
7. Click the Next button to go to the Connection tab.
8. Click the ellipsis and use the Select Access Database dialog box to choose the BIBLIO.MDB file which is in the Visual Basic main folder. Click Open.
9. Click Test Connection. Then, click OK (assuming it passed). The UDL file is now created and can be assigned to ConnectionString, using the steps below.
• If a data link has been created and exists for your database, click the ellipsis that appears next to the ConnectionString property. Choose Use Data Link File. Then, click Browse and find the file. Click Open. The data link is now assigned to the property. Click OK.
• If your database does not have a data link, you need to create one. This process is best illustrated by example. We will be using the BIBLIO.MDB database in our first example, so these steps show you how to create its data link:
1. Open Windows Explorer.
2. Open the folder where you will store your data link file.
3. Right-click the right side of Explorer and choose New. From the list of files, select Microsoft Data Link.
4. Rename the newly created file BIBLIO.UDL
5. Right-click this new UDL file and click Properties.
6. Choose the Provider tab and select Microsoft Jet 3.51 OLE DB Provider (an Access database).
7. Click the Next button to go to the Connection tab.
8. Click the ellipsis and use the Select Access Database dialog box to choose the BIBLIO.MDB file which is in the Visual Basic main folder. Click Open.
9. Click Test Connection. Then, click OK (assuming it passed). The UDL file is now created and can be assigned to ConnectionString, using the steps below.
• If a data link has been created and exists for your database, click the ellipsis that appears next to the ConnectionString property. Choose Use Data Link File. Then, click Browse and find the file. Click Open. The data link is now assigned to the property. Click OK.
Creating a Virtual Table
• Many times, a database table has more information than we want to display. Or, perhaps a table does not have all the information we want to display. For instance, in Example 8-1, seeing the Title and ISBN of a book is not real informative - we would also like to see the Author, but that information is not provided by the Titles table. In these cases, we can build our own virtual table, displaying only the information we want the user to see.
• We need to form a different SQL statement in the RecordSource property. Again, we won’t be learning SQL here. We will just give you the proper statement.
Quick Example: Forming a Virtual Table
1. We’ll use the results of Example 8-1 to add the Author name to the form. Replace the RecordSource property of the dtaTitles control with the following SQL statement:
SELECT Author,Titles.ISBN,Title FROM Authors,[Title Author],Titles WHERE Authors.Au_ID=[Title Author].Au_ID AND Titles.ISBN=[Title Author].ISBN ORDER BY Author
This must be typed as a single line in the Command Text (SQL) area that appears when you click the ellipsis by the RecordSource property. Make sure it is typed in exactly as shown. Make sure there are spaces after ‘SELECT’, after ‘Author,Titles.ISBN,Title’, after ‘FROM’, after ‘Authors,[Title Author],Titles’, after ‘WHERE’, after ‘Authors.Au_ID=[Title Author].Au_ID’, after ‘AND’, after ‘Titles.ISBN=[Title Author].ISBN’, and separating the final three words ‘ORDER BY Author’. The program will tell you if you have a syntax error in the SQL statement, but will give you little or no help in telling you what’s wrong.
Here’s what this statement does: It selects the Author, Titles.ISBN, and Title fields from the Authors, Title Author, and Titles tables, where the respective Au_ID and ISBN fields match. It then orders the resulting virtual table, using authors as an index.
2. Add a label box and text box to the form, for displaying the author name. Set the control properties.
Label3:
Caption - Author
Text1:
DataSource - dtaTitles (select, don’t type)
DataField - Author (select, don’t type)
Locked - True
Name - txtAuthor
Text - [Blank]
When done, the form should resemble this:
3. Save, then rerun the application. The author’s names will now appear with the book titles and ISBN values. Did you notice you still haven’t written any code?
I know you had to type out that long SQL statement, but that’s not code, technically speaking. Notice how the books are now ordered based on an alphabetical listing of authors’ last names
• We need to form a different SQL statement in the RecordSource property. Again, we won’t be learning SQL here. We will just give you the proper statement.
Quick Example: Forming a Virtual Table
1. We’ll use the results of Example 8-1 to add the Author name to the form. Replace the RecordSource property of the dtaTitles control with the following SQL statement:
SELECT Author,Titles.ISBN,Title FROM Authors,[Title Author],Titles WHERE Authors.Au_ID=[Title Author].Au_ID AND Titles.ISBN=[Title Author].ISBN ORDER BY Author
This must be typed as a single line in the Command Text (SQL) area that appears when you click the ellipsis by the RecordSource property. Make sure it is typed in exactly as shown. Make sure there are spaces after ‘SELECT’, after ‘Author,Titles.ISBN,Title’, after ‘FROM’, after ‘Authors,[Title Author],Titles’, after ‘WHERE’, after ‘Authors.Au_ID=[Title Author].Au_ID’, after ‘AND’, after ‘Titles.ISBN=[Title Author].ISBN’, and separating the final three words ‘ORDER BY Author’. The program will tell you if you have a syntax error in the SQL statement, but will give you little or no help in telling you what’s wrong.
Here’s what this statement does: It selects the Author, Titles.ISBN, and Title fields from the Authors, Title Author, and Titles tables, where the respective Au_ID and ISBN fields match. It then orders the resulting virtual table, using authors as an index.
2. Add a label box and text box to the form, for displaying the author name. Set the control properties.
Label3:
Caption - Author
Text1:
DataSource - dtaTitles (select, don’t type)
DataField - Author (select, don’t type)
Locked - True
Name - txtAuthor
Text - [Blank]
When done, the form should resemble this:
3. Save, then rerun the application. The author’s names will now appear with the book titles and ISBN values. Did you notice you still haven’t written any code?
I know you had to type out that long SQL statement, but that’s not code, technically speaking. Notice how the books are now ordered based on an alphabetical listing of authors’ last names
Control Structures in Visual Basic 6.0
Control Statements are used to control the flow of program's execution. Visual Basic supports control structures such as if... Then, if...Then ...Else, Select...Case, and Loop structures such as Do While...Loop, While...Wend, For...Next etc method.
If...Then selection structure
The If...Then selection structure performs an indicated action only when the condition is True; otherwise the action is skipped.
Syntax of the If...Then selection
If Then
statement
End If
e.g.: If average>75 Then
txtGrade.Text = "A"
End If
If...Then...Else selection structure
The If...Then...Else selection structure allows the programmer to specify that a different action is to be performed when the condition is True than when the condition is False.
Syntax of the If...Then...Else selection
If Then
statements
Else
statements
End If
e.g.: If average>50 Then
txtGrade.Text = "Pass"
Else
txtGrade.Text = "Fail"
End If
Nested If...Then...Else selection structure
Nested If...Then...Else selection structures test for multiple cases by placing If...Then...Else selection structures inside If...Then...Else structures.
Syntax of the Nested If...Then...Else selection structure
You can use Nested If either of the methods as shown above
Method 1
If < condition 1 > Then
statements
ElseIf < condition 2 > Then
statements
ElseIf < condition 3 > Then
statements
Else
Statements
End If
Method 2
If < condition 1 > Then
statements
Else
If < condition 2 > Then
statements
Else
If < condition 3 > Then
statements
Else
Statements
End If
End If
EndIf
e.g.: Assume you have to find the grade using nested if and display in a text box
If average > 75 Then
txtGrade.Text = "A"
ElseIf average > 65 Then
txtGrade.Text = "B"
ElseIf average > 55 Then
txtGrade.text = "C"
ElseIf average > 45 Then
txtGrade.Text = "S"
Else
txtGrade.Text = "F"
End If
Select...Case selection structure
Select...Case structure is an alternative to If...Then...ElseIf for selectively executing a single block of statements from among multiple block of statements. Select...case is more convenient to use than the If...Else...End If. The following program block illustrate the working of Select...Case.
Syntax of the Select...Case selection structure
Select Case Index
Case 0
Statements
Case 1
Statements
End Select
e.g.: Assume you have to find the grade using select...case and display in the text box
Dim average as Integer
average = txtAverage.Text
Select Case average
Case 100 To 75
txtGrade.Text ="A"
Case 74 To 65
txtGrade.Text ="B"
Case 64 To 55
txtGrade.Text ="C"
Case 54 To 45
txtGrade.Text ="S"
Case 44 To 0
txtGrade.Text ="F"
Case Else
MsgBox "Invalid average marks"
End Select
Note: In this example I have used a message box function. In later lessons you will learn how to use message box functions.
If...Then selection structure
The If...Then selection structure performs an indicated action only when the condition is True; otherwise the action is skipped.
Syntax of the If...Then selection
If
statement
End If
e.g.: If average>75 Then
txtGrade.Text = "A"
End If
If...Then...Else selection structure
The If...Then...Else selection structure allows the programmer to specify that a different action is to be performed when the condition is True than when the condition is False.
Syntax of the If...Then...Else selection
If
statements
Else
statements
End If
e.g.: If average>50 Then
txtGrade.Text = "Pass"
Else
txtGrade.Text = "Fail"
End If
Nested If...Then...Else selection structure
Nested If...Then...Else selection structures test for multiple cases by placing If...Then...Else selection structures inside If...Then...Else structures.
Syntax of the Nested If...Then...Else selection structure
You can use Nested If either of the methods as shown above
Method 1
If < condition 1 > Then
statements
ElseIf < condition 2 > Then
statements
ElseIf < condition 3 > Then
statements
Else
Statements
End If
Method 2
If < condition 1 > Then
statements
Else
If < condition 2 > Then
statements
Else
If < condition 3 > Then
statements
Else
Statements
End If
End If
EndIf
e.g.: Assume you have to find the grade using nested if and display in a text box
If average > 75 Then
txtGrade.Text = "A"
ElseIf average > 65 Then
txtGrade.Text = "B"
ElseIf average > 55 Then
txtGrade.text = "C"
ElseIf average > 45 Then
txtGrade.Text = "S"
Else
txtGrade.Text = "F"
End If
Select...Case selection structure
Select...Case structure is an alternative to If...Then...ElseIf for selectively executing a single block of statements from among multiple block of statements. Select...case is more convenient to use than the If...Else...End If. The following program block illustrate the working of Select...Case.
Syntax of the Select...Case selection structure
Select Case Index
Case 0
Statements
Case 1
Statements
End Select
e.g.: Assume you have to find the grade using select...case and display in the text box
Dim average as Integer
average = txtAverage.Text
Select Case average
Case 100 To 75
txtGrade.Text ="A"
Case 74 To 65
txtGrade.Text ="B"
Case 64 To 55
txtGrade.Text ="C"
Case 54 To 45
txtGrade.Text ="S"
Case 44 To 0
txtGrade.Text ="F"
Case Else
MsgBox "Invalid average marks"
End Select
Note: In this example I have used a message box function. In later lessons you will learn how to use message box functions.
Bound Data Tools
• Most of the Visual Basic tools we’ve studied can be used as bound, or data-aware, tools (or controls). That means, certain tool properties can be tied to a particular database field. To use a bound control, one or more data controls must be on the form.
• Some bound data tools are:
Label - Can be used to provide display-only access to a specified text data field.
Text Box - Can be used to provide read/write access to a specified text data field. Probably, the most widely used data bound tool.
Check Box - Used to provide read/write access to a Boolean field.
Combo Box - Can be used to provide read/write access to a text data field.
List Box - Can be used to provide read/write access to a text data field.
Picture Box - Used to display a graphical image from a bitmap, icon, or metafile on your form. Provides read/write access to a image/binary data field.
Image Box - Used to display a graphical image from a bitmap, icon, or metafile on your form (uses fewer resources than a picture box). Provides read/write access to a image/binary data field.
• There are also three ‘custom’ data aware tools, the DataCombo (better than using the bound combo box), DataList (better than the bound list box), and DataGrid tools, we will look at later.
• Bound Tool Properties:
DataChanged - Indicates whether a value displayed in a bound control has changed.
DataField - Specifies the name of a field in the table pointed to by the respective data control.
DataSource - Specifies which data control the control is bound to.
If the data in a data-aware control is changed and then the user changes focus to another control or tool, the database will automatically be updated with the new data (assuming LockType is set to allow an update).
• To make using bound controls easy, follow these steps (in order listed) in placing the controls on a form:
1. Draw the bound control on the same form as the data control to which it will be bound.
2. Set the DataSource property. Click on the drop-down arrow to list the data controls on your form. Choose one.
3. Set the DataField property. Click on the drop-down arrow to list the fields associated with the selected data control records. Make your choice.
4. Set all other properties, as required.
By following these steps in order, we avoid potential data access errors.
• The relationships between the bound data control and the data control are:
• Some bound data tools are:
Label - Can be used to provide display-only access to a specified text data field.
Text Box - Can be used to provide read/write access to a specified text data field. Probably, the most widely used data bound tool.
Check Box - Used to provide read/write access to a Boolean field.
Combo Box - Can be used to provide read/write access to a text data field.
List Box - Can be used to provide read/write access to a text data field.
Picture Box - Used to display a graphical image from a bitmap, icon, or metafile on your form. Provides read/write access to a image/binary data field.
Image Box - Used to display a graphical image from a bitmap, icon, or metafile on your form (uses fewer resources than a picture box). Provides read/write access to a image/binary data field.
• There are also three ‘custom’ data aware tools, the DataCombo (better than using the bound combo box), DataList (better than the bound list box), and DataGrid tools, we will look at later.
• Bound Tool Properties:
DataChanged - Indicates whether a value displayed in a bound control has changed.
DataField - Specifies the name of a field in the table pointed to by the respective data control.
DataSource - Specifies which data control the control is bound to.
If the data in a data-aware control is changed and then the user changes focus to another control or tool, the database will automatically be updated with the new data (assuming LockType is set to allow an update).
• To make using bound controls easy, follow these steps (in order listed) in placing the controls on a form:
1. Draw the bound control on the same form as the data control to which it will be bound.
2. Set the DataSource property. Click on the drop-down arrow to list the data controls on your form. Choose one.
3. Set the DataField property. Click on the drop-down arrow to list the fields associated with the selected data control records. Make your choice.
4. Set all other properties, as required.
By following these steps in order, we avoid potential data access errors.
• The relationships between the bound data control and the data control are:
Assigning Tables
• Once the ADO data control is connected to a database, we need to assign a table to that control. Recall each data control is attached to a single table, whether it is a table inherent to the database or the virtual table we discussed. Assigning a table is done via the RecordSource property.
• Tables are assigned by making queries of the database. The language used to make a query is SQL (pronounced ‘sequel,’ meaning structured query language). SQL is an English-like language that has evolved into the most widely used database query language. You use SQL to formulate a question to ask of the database. The data base ‘answers’ that question with a new table of records and fields that match your criteria.
• A table is assigned by placing a valid SQL statement in the RecordSource property of a data control. We won’t be learning any SQL here. There are many texts on the subject - in fact, many of them are in the BIBLIO.MDB database we’ve been using. Here we simply show you how to use SQL to have the data control ‘point’ to an inherent database table.
• Click on the ellipsis next to RecordSource in the property box. A Property Pages dialog box will appear. In the box marked Command Text (SQL), type this line:
SELECT * FROM TableName
This will select all fields (the * is a wildcard) from a table named TableName in the database. Click OK.
• Setting the RecordSource property also establishes the Recordset property, which we will see later is a very important property.
• In summary, the relationship between the data control and its two primary properties (ConnectionString and RecordSource) is:
• Tables are assigned by making queries of the database. The language used to make a query is SQL (pronounced ‘sequel,’ meaning structured query language). SQL is an English-like language that has evolved into the most widely used database query language. You use SQL to formulate a question to ask of the database. The data base ‘answers’ that question with a new table of records and fields that match your criteria.
• A table is assigned by placing a valid SQL statement in the RecordSource property of a data control. We won’t be learning any SQL here. There are many texts on the subject - in fact, many of them are in the BIBLIO.MDB database we’ve been using. Here we simply show you how to use SQL to have the data control ‘point’ to an inherent database table.
• Click on the ellipsis next to RecordSource in the property box. A Property Pages dialog box will appear. In the box marked Command Text (SQL), type this line:
SELECT * FROM TableName
This will select all fields (the * is a wildcard) from a table named TableName in the database. Click OK.
• Setting the RecordSource property also establishes the Recordset property, which we will see later is a very important property.
• In summary, the relationship between the data control and its two primary properties (ConnectionString and RecordSource) is:
Assigning and returning arrays in Visual Basic 6
Visual Basic 6 adds two important features to arrays. First, you can perform assignments between arrays. Second, you can write procedures that return arrays. You can assign arrays only of the same type and only if the target is a dynamic array. (The latter condition is necessary because Visual Basic might need to resize the target array.)
ReDim a(10, 10) As Integer
Dim b() As Integer
' Fill the a array with data (omitted).
b() = a() ' This works!
It's no surprise that native assignment commands are always faster than the corresponding For…Next loops that copy one item at a time. The actual increment in speed heavily depends on the data type of the arrays and can vary from 20 percent to 10 times faster. A native assignment between arrays also works if the source array is held in a Variant. Under Visual Basic 4 and 5, you could store an array in a Variant, but you couldn't do the opposite—that is, retrieve an array stored in a Variant variable and store it back in an array of a specific type. This flaw has been fixed in Visual Basic 6:
Dim v As Variant, s(100) As String, t() As String
' Fill the s() array (omitted).
v = s() ' Assign to a Variant.
t() = v ' Assign from a Variant to a dynamic string array.
You often use the capacity to assign arrays to build functions that return arrays. Notice that pair of brackets at the end of the first line in the following procedure:
Function InitArray(first As Long, Last As Long) As Long()
ReDim result(first To Last) As Long
Dim i As Long
For i = first To Last
result(i) = i
Next
InitArray = result
End Function
The new capability of returning arrays lets you write highly versatile array routines. Visual Basic 6 itself includes a few new string functions—namely Join, Split, and Filter—that rely on it. (You'll find more about these new string functions in Chapter 5). Here are two examples of what you can do with this intriguing feature:
' Returns a portion of a Long array
' Note: fails if FIRST or LAST are not valid
Function SubArray(arr() As Long, first As Long, last As Long, _
newFirstIndex As Long) As Long()
Dim i As Long
ReDim result(newFirstIndex To last _ first + newFirstIndex) As Long
For i = first To last
result(newFirstIndex + i - first) = arr(i)
Next
SubArray = result
End Function
' Returns an array with all the selected items in a ListBox
Function SelectedListItems(lst As ListBox) As String()
Dim i As Long, j As Long
ReDim result(0 To lst.SelCount) As String
For i = 0 To lst.ListCount - 1
If lst.Selected(i) Then
j = j + 1
result(j) = lst.List(i)
End If
Next
SelectedListItems = result
End Function
ReDim a(10, 10) As Integer
Dim b() As Integer
' Fill the a array with data (omitted).
b() = a() ' This works!
It's no surprise that native assignment commands are always faster than the corresponding For…Next loops that copy one item at a time. The actual increment in speed heavily depends on the data type of the arrays and can vary from 20 percent to 10 times faster. A native assignment between arrays also works if the source array is held in a Variant. Under Visual Basic 4 and 5, you could store an array in a Variant, but you couldn't do the opposite—that is, retrieve an array stored in a Variant variable and store it back in an array of a specific type. This flaw has been fixed in Visual Basic 6:
Dim v As Variant, s(100) As String, t() As String
' Fill the s() array (omitted).
v = s() ' Assign to a Variant.
t() = v ' Assign from a Variant to a dynamic string array.
You often use the capacity to assign arrays to build functions that return arrays. Notice that pair of brackets at the end of the first line in the following procedure:
Function InitArray(first As Long, Last As Long) As Long()
ReDim result(first To Last) As Long
Dim i As Long
For i = first To Last
result(i) = i
Next
InitArray = result
End Function
The new capability of returning arrays lets you write highly versatile array routines. Visual Basic 6 itself includes a few new string functions—namely Join, Split, and Filter—that rely on it. (You'll find more about these new string functions in Chapter 5). Here are two examples of what you can do with this intriguing feature:
' Returns a portion of a Long array
' Note: fails if FIRST or LAST are not valid
Function SubArray(arr() As Long, first As Long, last As Long, _
newFirstIndex As Long) As Long()
Dim i As Long
ReDim result(newFirstIndex To last _ first + newFirstIndex) As Long
For i = first To last
result(newFirstIndex + i - first) = arr(i)
Next
SubArray = result
End Function
' Returns an array with all the selected items in a ListBox
Function SelectedListItems(lst As ListBox) As String()
Dim i As Long, j As Long
ReDim result(0 To lst.SelCount) As String
For i = 0 To lst.ListCount - 1
If lst.Selected(i) Then
j = j + 1
result(j) = lst.List(i)
End If
Next
SelectedListItems = result
End Function
Subscribe to:
Posts (Atom)