mysql4u.com

Functions and Subroutines in ASP
If you read our Tutorial on Include Files (SSI) then you learned how to encapsulate ASP code within include files and the benefits it brings us.

As developers, we should endeavour to make our lives easier wherever possible… no one wants to re-invent the wheel after all.

Functions and Subroutines exist to not only save us time, but to bring power to our ASP.

They are just another way of encapsulating code, but have a lot more functionality than just ’saving some code for later’.

First, let’s look at Functions… Imagine a balloon salesman in the street. We’ve all seen them they require one piece of information when you buy a balloon, the colour.
Let say we asked for a red balloon… The balloon salesman armed with this ‘information’ then does a pretty basic action… he hands you the balloon. The balloon you received is a direct result of the information you gave the balloon seller.

Functions are just the same… they return to you a value based on the information you provided. Lets look at an example Function: -

<%
Function getBalloon(strColour)

Dim Tempstr

strColour = lcase(strColour) 'This converts the value lowercase.
Select Case strColour

Case "red" Tempstr = "Here is your red balloon"

Case "yellow" Tempstr = "Here is your yellow balloon"

Case "green" Tempstr = "Here is your green balloon"

Case "blue" Tempstr = "Here is your blue balloon"

Case Else Tempstr = "Sorry, we have sold out of that Colour"
End Select

getBalloon = Tempstr
End Function
%>
A Function is passed some information. The information we pass a Function, is known as an ‘argument’. The information we get back from a Function is known as the ‘return value’. Whilst a Function can have many arguments, it can only have one return value.

Let us look at one more example: -

<%
Function calcTax(amount, taxrate)

Dim Tempvar

Tempvar = amount * (taxrate / 100)

CalcTax = Round(Tempvar, 2) 'round the result to 2 decimal places
End Function
%>
Again, another basic example. We should notice this time that the Function accepts two arguments.

By now, we have some idea of how to write a Function. How do we use one?
Let me show you now how we can use the calcTax example.

<%
shoppingbill=goodsTotal + calcTax(goodsTotal,17.5)
Response.Write "Your shopping came to £" & goodsTotal
Response.Write "
VAT amount = £" & calcTax(goodsTotal)
Response.Write "Total Amount Due = £" & shoppingbill
%>
Above you see the example function in action… easy huh!

I have tried to make understanding Functions as easy as possible… Understanding a Subroutine (Sub) is now going to be easy for you. Imagine a block of code that performed some instructions based on information you gave it…
Sounds very much like a function, doesn?t it? Well this time, we do not get anything back. A sub does NOT pass back information it just uses the data we give it for some purpose.

I will use only one example of a Sub, and in the same example make use of the sub: -

<%
Sub Bday(strName, intAge)

Response.Write "Happy Birthday " & Name

Response.Write ", You are " & intAge & " years old today"
End Sub

'now, call the sub
bDay "Joe",26
%>
The above Sub, demonstrates my point. We put something in, it performs an action (in this case writing to the screen), but nothing is returned to us in the code. One thing that REALLY IS important when using a sub, is that we do not put brackets around the arguments… Because we do not have a return value we do not need brackets and in this case, if we try we will get an error.

Well, that just about concludes this article. We should by now be writing efficient code with the use of Functions and Subs. Don?t forget that if you use your functions and subs in multiple pages then you should really store them within include files for reasons of easy maintenance and better performance.

Like most average persons, I wanted some extra money to supplement my regular income. I had considered a part time second job at times, but hated that option because it would take me away from the family, make me more tired and probably just pay minimum wage. I also wanted to be able to work from home.

I had surfed the internet for years and had often considered the idea of participating in the internet revolution. I had read some statistics on the use of internet and the increasing numbers of homes with computers and the increasing percentage of Christmas shopping done over the internet. So I decided to jump in with both feet.

First what is ecommerce? The online world designates web sites devoted to retail sales as an e-commerce site. E-commerce sites sell either information or hard goods such as cameras or knifes. E-commerce sites also have Shopping Carts and Payment Gateways attached to them that non-sales web sites do not need.

Ok……so you want to develop an E-commerce site. You are now probably asking do I have enough skill, mental patience or aptitude to be successful?

Well if you have average intelligence, I believe that you and thousands of people just like you could have an E-commerce web site. All that is required is the willingness to learn a few things, patience, and the determination to apply yourself.

How? I am glad you asked.

Let’s begin. We first need to discuss the basics components you will need.

1. Computer 2. Internet connection 3. Site building software 4. Graphics Software 5. Web hosting company 6. Shopping cart 7. Payment gateway

The above elements are where most people stop when they think of what they will need when they want to start an ecommerce site. However, there are other elements that need to be considered, such as getting legal and product sourcing.

Items 1 and 2 above are pretty straight forward and most people can understand why they are necessary. The other items however are a little more complex. Site building software allows the beginner to construct a web site without knowing html programming code. What is Html programming Code?

Html is the programming language of the internet and stands for Hyper Text Mark Up Language. The two best software programs currently available to write Html are Microsoft’s FrontPage 2003 or Macromedia’s Dreamweaver. These programs are more correctly known as HTML Editors.

These two programs are the best ones available. I have them both but use FrontPage 2003 primarily. I think FrontPage 2003 is easier to use and does better tables then Dreamweaver. Most professional web site developers, however consider Dreamweaver the better of the two because some claim it has more features, but I find it is a little more complicated to use. I would recommend that you surf the internet and read the reviews on both of these programs and make you own choice.

Next you will need a good graphic program such as Adobe Photoshop 6. A graphics program allows you manipulate the size of images create images in different formats and create special effects. “PhotoShop 6” is the current version from Adobe and is the best software program for graphics. Adobe “Photoshop Elements” can also be used, but is a limited version of the full Photoshop software. The programs from Adobe are expensive so shop around for the best price.

Web hosting is very important and you need to comparison shop for features first, not on price. Web hosting and ecommerce are extremely related, and learned by personal experience the mistake trying to separate ecommerce from web hosting. The web host company is the company that rents room on their server for your web site, but they can much more. They can offer an integrated shopping cart.

The shopping cart is the heart of the ecommerce web site. The shopping cart for all intents and purposes runs your ecommerce web site so it is critical to get a good one. It contains and displays the products you intend to sell. It also does other neat administrative functions that will make your store easier to run. There are some stand alone shopping carts for sale but most come with web hosting service attached.

The “Payment Gateway” is the cashier in the store and is also known as a “Merchant Account.” This is basically a business credit card account. The payment gateway allows your se store to accept credit cards and transfer money to you. Merchant Accounts are very important and you should also carefully review their services and prices. If you do not get a merchant account you are pretty much limited to papal. Search the web and check out the available shopping carts and payment gateways.

Now what are you going to sell, and where are you going to get it? You can look every where for the products you want to sell. How do you know what will sell well? You need to do some market research. Not everything sells well, including electronics. If you have no preference on what to sell, find some marketing software that will allow you to determine which products ell well online.

Next, you will need a source for those products. Assuming you are not making them yourself. The best way is to drop ship them. The trouble is finding a reliable drop ship source and avoiding the imposters.

Once you find you product source you need to open an account and stock your store and start selling. There munch more to an ecommerce store then can be explained in a brief article format. As someone who recently went from zero to an online store Cynscorion.com), I know first hand of the pit falls that await the beginning ecommerce site builder.

If you want to learn more about what to avoid in building an ecommerce site go to either: www.cynscorion.com and go to the bottom of the page and click on “Ecommerce Book For Beginners” ,or go to: http://cynscorion.com/store/cart.php?page=what_yofuture_release_of_a_very_unique_ecommerce_book This book is for people undecided on whether to start an ecommerce site and want to find one point of reference to assist them in making their decision.

Lastly, I wish you good luck and success in your ecommerce endeavors. If I can do it, so can you!

Note: In order to work with the examples in this (and the next two) columns, you must already have access to a MySQL database via a MySQL client such as the mysql application. If you do not have access to MySQL, it can be downloaded, free of charge, from the MySQL home page in both Unix and Windows flavors. For more information regarding the installation and use of the MySQL server and client, please see the MySQL web site for more information.
Principles of Relational Databases

Relational databases are the cornerstones of today’s serious web applications. They provide the “back end” — efficient and, if used properly, fast methods of storing and retrieving mass quantities of data. Although many different relational databases (formally called “Relational Database Management Systems,” or RDBMS) exist, such as Oracle and PostgreSQL, all of my discussions will focus on the MySQL RDBMS. The fundamental principles, as well as much of the coming discussion of SQL, will be relevant regardless of the database package used.

Relational databases are designed to store incredible amounts of data: addresses, email addresses, images, and whatever is required. However, RDBMS packages do not get their strength from what types of data they can store, per se. Instead, the organization of that data within an RDBMS provide the benefits to its users.

Related Reading
MySQL Pocket Reference

MySQL Pocket Reference
By George Reese
Table of Contents

Read Online–Safari Search this book on Safari:

Code Fragments only

Data is organized into one or more databases. These databases are then organized into tables that actually store the data. The best analogy to RDBMS systems is this: consider your day-to-day filing cabinet that is filled with a number of folders, each of which contains a simple table of rows and columns that store the data for that folder. From this perspective, the filing cabinet itself could be considered a database, while each folder could be considered a separate table within that database. To retrieve a specific piece of data from your filing cabinet, you must choose the correct database and folder within that cabinet. Then, you search within that folder for the data you requested.

From a RDBMS standpoint, this is a fairly accurate portrayal of your digital database. Instead of folders, there are simply named tables, and instead of manually searching each table for a specific piece (or pieces) of data, you use a language called SQL (or “Structured Query Language”) to retrieve the needed data.
The Structured Query Language

Before you begin to worry about trying to learn an entirely new language on top of attempting to learn PHP, let me calm your fears. SQL is perhaps one of the easiest languages in existence. SQL is, however, absolutely indispensable because it provides the means by which you store and retrieve information in the RDBMS. Although this is not to say SQL queries cannot become incredibly complex, be assured that the underlying principles of the language itself are easy to grasp. Since the best way to understand this is to start right in, let’s introduce a few of the major statements.

Note: As I mentioned, SQL can be a very complex language, where statements can take many different forms and, to a beginner, be quite confusing. To elevate that confusion, be aware that the following statements do not reflect the magnitude of the language and have been significantly simplified to focus on practical day to day use of SQL for a beginner. For the complete syntax and use of the following SQL statements, see the MySQL documentation.

Since chances are you have never used relational databases before, your first task is to learn the CREATE statement. This statement is used for creating databases as well as tables. Let’s start by creating a new database called fundamentals:

mysql> CREATE
DATABASE fundamentals;
Query OK, 1 row affected (0.01 sec)

This statement has essentially created a filing cabinet (database) that can then be filled with folders (tables) to store our data. Obviously, an empty filing cabinet without folders cannot do us much good in storing data, so we’ll also need to create tables within this database.

Before we can create any tables within the database, you must understand a bit more about tables. As mentioned before, tables are simply collections of rows and columns. When working with or creating database tables, every column within the table must be assigned a specific data type, governing what kind of data the column represents (such as integer, floating point, or string). Column types can even take on properties, such as a default value or an auto-incrementing number. Since each column within a table must be assigned a data type, let’s look at a few of the available data types:

*

INT[(SIZE)] [UNSIGNED] [ZEROFILL]

A simple integer between -2147483648 and +2147483647 or, if the UNSIGNED attribute is provided, between zero and 4294967295. The ZEROFILL attribute indicates that the number should be prefixed with zeros until the number is SIZE digits in length.
*

VARCHAR[(SIZE)] [BINARY]

A variable-length string that is a maximum of SIZE characters in length (where SIZE cannot exceed 255). Unless the BINARY attribute is provided, this data type is considered case-insensitive and obviously cannot hold binary data.
*

TEXT

A case-sensitive string that is a maximum of 65,535 characters in length.
*

DATETIME

A date and time ranging from 1000-01-01 00:00:00 to 9999-12-31 23:59:59 (dates are in YYYY-MM-DD HH:MM:SS format).
*

DATE

Similar to the DATETIME data type, except without the time in YYYY-MM-DD format.

Although this is not a complete set of all of the data types a column within a table can have, it gives you an idea of the ways and specific types of data that can be stored. With these in mind, let’s create a couple of tables within the fundamentals database that will keep track of books. Specifically we’d like to store the title of a book, its author, the publication date, and the home state of the author. For reasons I will explain later, I will be dividing authors and books into two separate tables, books and authors, with a common author_id column between them.

Table 1. The books table
book_id author_id title pub_date
1 1 PHP Unleashed 11-01-03
2 1 PHP4 Programming 10-01-02
3 2 Cool Stuff 03-23-02
4 3 Another book 02-01-01

Table 2. The authors table
author_id name state
1 John Coggeshall MI
2 Joe Coolguy AZ
3 Jennifer Author KS

In order to create this table within our database, we again turn to the CREATE statement. However, before we can create a table in the database, we first must tell MySQL what database we are working with using the USE statement, as shown:

mysql> USE fundamentals;
Database Changed

Now we can use the CREATE statement to create the books and authors tables:

mysql> CREATE TABLE books(
book_id INT AUTO_INCREMENT PRIMARY KEY,
author_id INT,
title VARCHAR(255),
pub_date DATE);

Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE authors(
author_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
state VARCHAR(2));

Query OK, 0 rows affected (0.02 sec)

These two statements create the two tables we will use throughout our discussion. The books table has four columns: a book id number (book_id), an author ID number (author_id), the title of the book (title), and the publication date (pub_date). The authors table only has three columns: the author ID (author_id), the author’s name (name), and the author’s state of residence (state).

Note that the book_id and author_id columns have the AUTO_INCREMENT and PRIMARY KEY attributes set. The PRIMARY KEY attribute indicates that no two rows in the table may have the same value for that column — each must be unique. In the case of our tables, this means that the book_id column and the author_id column will each contain an integer value completely unique for each and every row in their respective tables. The AUTO_INCREMENT attribute automatically provides a value for the column. As data is inserted into the tables, the first row will have a value of zero, the second a value of one, the third a value of two, and so on.

Note: Although it is there to simplify things, the AUTO_INCREMENT attribute only takes effect if the NULL value is passed for the column in question. If you specify a value, it will be used instead. Furthermore, if the value inserted is greater than the largest value for that column, the AUTO_INCREMENT attribute will use that value plus one for its next insert value.

To see how your tables are defined in detail once they are created (very useful if you have forgotten the details of a specific table), you can use the DESC (or DESCRIBE) statement:

mysql> DESC books;

+———–+————–+——————-+——+—–+———+—————-+
| Field | Type | Collation | Null | Key | Default | Extra |
+———–+————–+——————-+——+—–+———+—————-+
| book_id | int(11) | binary | YES | PRI | NULL | auto_increment |
| author_id | int(11) | binary | YES | | NULL | |
| title | varchar(255) | latin1_swedish_ci | YES | | NULL | |
| pub_date | date | latin1_swedish_ci | YES | | NULL | |
+———–+————–+——————-+——+—–+———+—————-+

4 rows in set (0.00 sec)

Now that the tables have been created, we need to populate the tables with the data reflected in Tables 1 and 2. Use the INSERT statement to insert data into a table:

mysql> INSERT INTO books VALUES(1, 1, “PHP Unleashed”, “2003-11-01″);

Query OK, 1 row affected (0.01 sec)

Repeat for each row in the books table.

mysql> INSERT INTO authors VALUES(1, “John Coggeshall”,”MI”);

Query OK, 1 row affected (0.01 sec)

Repeat for each row in the authors table.

Note: Notice that for the first set of INSERT queries, the date column is represented in YYYY-MM-DD format. This is the standard method of storing dates.

To simplify the process of data entry, make sure you manually enter values for the book_id and author_id columns in each respective table as shown above instead of taking advantage of the AUTO_INCREMENT attribute. It is important for later discussions that the tables in the database reflect those found in Tables 1 and 2 exactly.

At this point, you should have a database named fundamentals containing two tables named books and authors. To double check, you can ask the server to show all of the tables within a database with the SHOW statement:

mysql> SHOW TABLES;

+—————————–+
| Tables in fundamentals |
+—————————–+
| books |
| authors |
+—————————–+

2 rows in set (0.03 sec)

Assuming everything has gone as expected, we are now ready to start requesting data from the database.
Retrieving Data From Tables

Now that we have data in our database, let’s retrieve it. To retrieve data from the database (called a “result set”), use the SELECT statement:

mysql> select * from books;

+———+———–+——————+————+
| book_id | author_id | title | pub_date |
+———+———–+——————+————+
| 1 | 1 | PHP Unleashed | 2003-11-01 |
| 2 | 1 | PHP4 Programming | 2002-10-01 |
| 3 | 2 | Cool Stuff | 2002-03-23 |
| 4 | 3 | Another Book | 2001-02-01 |
+———+———–+——————+————+

4 rows in set (0.00 sec)

In this case, the above query has returned a result set containing all of the data stored in the books table. How does it work exactly? Although the SELECT statement is much more complex then this, the general syntax is as follows:

SELECT FROM
… (additional optional clauses)

can be any valid column name for the tables specified in
or any valid MySQL function (more on those in the future). If you wanted only a list of all of the book titles in the database, you could use the following query:

mysql> SELECT title FROM books;

+——————+
| title |
+——————+
| PHP Unleashed |
| PHP4 Programming |
| Cool Stuff |
| Another Book |
+——————+

4 rows in set (0.00 sec)

You could also select two columns from the books table by separating them by a comma:

mysql> SELECT title, pub_date FROM books;

+——————+————+
| title | pub_date |
+——————+————+
| PHP Unleashed | 2003-11-01 |
| PHP4 Programming | 2002-10-01 |
| Cool Stuff | 2002-03-23 |
| Another Book | 2001-02-01 |
+——————+————+

4 rows in set (0.00 sec)

Introduction

Lookup tables contain, in general, a fixed list of data. This data doesn’t change very often in database business applications. Examples of this data could be a product list, category type, supplier list, state name, zip code, phone area code, etc. In Windows and Internet web business applications, most of these lookup tables are graphically implemented by using ComboBox, ListBox or CheckListBox read-only controls. These controls are loaded with data using two main columns, ID and Name. For example, the USA state table, the ID could be ‘CA’ and the Name ‘California’. Some times, for standard Windows form and Internet web page we need to show data to the end-users from many of these lookup tables. A fast data loading process and defining the main column values for each lookup table is required. In this article I will show you standard lookup data loading procedure and the generic classes object to store and read-only the values of the ID and Name columns from the lookup tables. Selecting and finding the ID and Name values will be provided. Executing stored procedures with input/output parameters in MySQL 5.0/VB.NET 2005 will be covered in detail too.
Required Software

* MySQL Database Server 5.0.41
* MySQL Connector/NET 5.0.6
* Toad for MySQL Freeware 2.0.3
* Microsoft Visual Basic 2005 Express Edition

LoopUp Table Data Loading

Let’s look for a simple way to load the data in a ComboBox from a lookup table. Because we may have many lookup tables, it makes sense to develop a generic class for data loading from these tables. Listing 1 shows the structure of the LookUpClass public class within the LoopUpLibrary Namespace. The MySql.Data.MySqlClient library has been imported to reference the Connector/NET 5.0.6. The standard public class ObjectDisposeClass was included for releasing .NET unmanaged resources. Inside the LookUpClass class body we will develop our custom properties, methods and events.

Imports MySql.Data.MySqlClient
Namespace LoopUpLibrary
Public Class LoopUpClass
Inherits ObjectDisposeClass
‘ Developed custom properties, methods and events
End Class
#Region ” IDisposable Object …”
Public Class ObjectDisposeClass
Implements IDisposable
Private disposedValue As Boolean = False
Public Sub Dispose() Implements IDisposable.Dispose
Dispose(True)
GC.SuppressFinalize(Me)
End Sub
Protected Overridable Sub Dispose(ByVal disposing As Boolean)
If Not Me.disposedValue Then
If disposing Then
‘ TODO: free unmanaged resources when explicitly called
End If
‘ TODO: free shared unmanaged resources
End If
Me.disposedValue = True
End Sub
End Class
#End Region
End Namespace

Listing 1: LookUpClass class structure

The LoopUpDataLoad() subroutine is shown in Listing 2. This subroutine has two main input parameters, the control to be loaded (pComboBox) and the stored procedure (pStoredProcedureString) that contains the SQL Select statement of the Lookup table. Some user stored procedures examples will be explained late in this article (Listing 5 and 13). As we know, MySQL Server 5.0 introduces, for the first time, the capability of development stored procedures, functions, triggers and views database objects. Because of that, instead of passing a dynamic SQL Select statement, the stored procedure name is passed by value to the subroutine. Stored procedures offer several distinct advantages over dynamic (embedding) SQL in your application code. In the future, I’m going write a single paper about stored procedures development and implementation using MySQL 5.0/VB.NET 2005. I can see many Open Source application developers still using dynamic or/and parameterized SQL statements today with MySQL Server 5.0. It seems to me that they don’t know how to design and develop stored procedures using SQL:2003 language. Just in case, a very good reference book about this topic was published in 2006 by O’Reilly Media, Inc., ISBN: 0-596-10089-2, “MySQL Stored Procedure Programming” by Guy Harrison and Steven Feuerstein. I highly recommend reading this book for any Windows or Internet web application development with MySQL 5.0 database engine.

Public Sub LoopUpDataLoad(ByVal pComboBox As ComboBox, _
ByVal pStoredProcedureString As String, _
ByRef pErrorMsgString As String)
Dim IDString, NameString As String
Try
Using mMySqlConnection As New MySqlConnection(mMySQLConnectionString)
mMySqlConnection.Open()
Using mMySqlCommand As New MySqlCommand
With mMySqlCommand
.Connection = mMySqlConnection
.CommandType = CommandType.StoredProcedure
.CommandText = pStoredProcedureString
End With
Using mMySqlDataReader As MySqlDataReader = _
mMySQLCommand.ExecuteReader(CommandBehavior.SingleResult)
With pComboBox
.Items.Clear()
If mMySqlDataReader.HasRows Then
.BeginUpdate()
While mMySqlDataReader.Read()
IDString = mMySqlDataReader.Item(0).ToString()
NameString = mMySqlDataReader.Item(1).ToString()
If IDString.Length > 0 And NameString.Length > 0 Then
Using mListItemClass As New ListItemClass(NameString, IDString)
.Items.Add(mListItemClass)
End Using
End If
End While
.EndUpdate()
.SelectedIndex = 0
End If
End With
End Using
End Using
End Using
Catch exErr As Exception
pErrorMsgString = exErr.Message
End Try
End Sub

Listing 2: Generic data loading procedure from lookup tables

So far at this point, for the available MySQL Connector/NET 5.0.6, the fastest way to retrieve data from the MySQL Server 5.0 is by using the data reader object MySqlDataReader. XML implementation and data retrieval from MySQL database server is not available at this time. We hope to have these technologies implemented in future releases of MySQL, as Microsoft (SQL Server), Oracle (Oracle Server) and IBM (DB2 Mainframe Server) already have done. As the help file said (C:\Program Files\MySQL\MySQL Connector Net 5.0.6\Documentation\MySql.Data.chm) the data reader object provides a means of reading a forward-only stream of rows from a MySQL database. This object is created by calling the ExecuteReader() method of the command object MySqlCommand as shown in the code (Listing 2). By reading the mMySqlDataReader object, we can get the postal code (IDString) and the state name (NameString). After these two values are determined, they need to be passed to the generic class constructor ListItemClass (Listing 3) and than the entire object will be adding to the item collection of the ComboBox. I would like to mention two new methods implemented in ComboBox control in VB.NET 2005, BeginUpdate() and EndUpdate(). The BeginUpdate() method prevents the control from repainting until the EndUpdate() method is called. In this case the user will no see the flicker during the drawing of the ComboBox when the items are being added to the list.
Generic ListItemClass Class

The ListItemClass class (Listing 3) was created to store and read-only the values of the ID and Name columns from the lookup tables. In general the Name column is defined as a character data type. The values of this column will be stored in mFieldNameString variable by using the class constructor. The read-only property FieldName() will retrieve these values. The ID column could be a character or a numeric data type, depend on the table definition. In this case, the class implements two read-only properties, one for character data type FieldIDString() and another for numeric FieldIDInt32(). This generic ListItemClass class should be included in any Windows or Internet web application project and can be used with any lookup control as ComboBox, ListBox or CheckListBox.

Public Class ListItemClass
Inherits ObjectDisposeClass
Private mFieldNameString As String
Private mFieldIDString As String
Private mFieldIDInt32 As Int32
Public Sub New(ByVal pFieldNameString As String,
ByVal pFieldIDInt32 As Int32)
mFieldNameString = pFieldNameString
mFieldIDInt32 = pFieldIDInt32
End Sub
Public Sub New(ByVal pFieldNameString As String,
ByVal pFieldIDString As String)
mFieldNameString = pFieldNameString
mFieldIDString = pFieldIDString
End Sub
Public Sub New()
mFieldNameString = Nothing
mFieldIDString = Nothing
mFieldIDInt32 = Nothing
End Sub
Public ReadOnly Property FieldName() As String
Get
Return (mFieldNameString)
End Get
End Property
Public ReadOnly Property FieldIDInt32() As Int32
Get
Return (mFieldIDInt32)
End Get
End Property
Public ReadOnly Property FieldIDString() As String
Get
Return (mFieldIDString)
End Get
End Property
Public Overrides Function ToString() As String
Return (mFieldNameString)
End Function
End Class

Listing 3: Generic ListItemClass class
LoopUp Project Example

To show a real example of lookup data loading I created a simple VB.NET 2005 solution project shown in Figure 1. The two ComboBoxes are loaded with USA States and Capitals respectively.

Load Combo box with MySQL
Figure 1: ComboBox data loading for USA States and Capitals

The load event of the VB.NET Form is shown in Listing 4. As you can see, both ComboBoxes, the States (StatesComboBox) and Capitals (CapitalsComboBox) are loaded using the same LoopUpDataLoad() subroutine shown above in Listing 2. If an error occurred, it gets stored in mErrorMsgString variable and shown to the end-users. It’s a very good programming practice to provide error handling capability in all your application code. Many published VB.NET papers do not provide this important implementation code today.

Private Sub MySQLComboBoxForm_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs)
Handles MyBase.Load
Cursor = Cursors.WaitCursor
Try
Using LoopUpObject As New LoopUpClass(mMySQLConnectionString)
Call LoopUpObject.LoopUpDataLoad(StatesComboBox, _
“usp_states_select_postal_name”, _
mErrorMsgString)
If Not IsNothing(mErrorMsgString) Then
Cursor = Cursors.Default
MessageBox.Show(mErrorMsgString, _
Me.Text, _
MessageBoxButtons.OK, _
MessageBoxIcon.Error)
End If
Call LoopUpObject.LoopUpDataLoad(CapitalsComboBox, _
“usp_states_select_postal_capital”, _
mErrorMsgString)
If Not IsNothing(mErrorMsgString) Then
Cursor = Cursors.Default
MessageBox.Show(mErrorMsgString, _
Me.Text, _
MessageBoxButtons.OK, _
MessageBoxIcon.Error)
End If
End Using
Catch exError As Exception
MessageBox.Show(exError.Message)
End Try
Cursor = Cursors.Default
End Sub

Listing 4: Load event of the form “Load ComboBox with MySQL”

The user stored procedures `usp_states_select_postal_name` and `usp_states_select_postal_capital` are passed by value and shown in Listing 5. Both procedures were developed based on the USA state table (`state`) with data (Listing 6).

DROP PROCEDURE IF EXISTS `usp_states_select_postal_name`;
CREATE PROCEDURE `usp_states_select_postal_name`( )
BEGIN
SELECT `states`.`postal`, `states`.`statename`
FROM `states`
ORDER BY `states`.`postal`;
END;

DROP PROCEDURE IF EXISTS `usp_states_select_postal_capital `;
CREATE PROCEDURE `usp_states_select_postal_capital`( )
BEGIN
SELECT `states`.`postal`, `states`.`capital`
FROM `states`
ORDER BY `states`.`capital`;
END;

Listing 5: User stored procedures to select USA States and Capitals

DROP TABLE IF EXISTS `states`;
CREATE TABLE `states` (
`statename` varchar(20) NOT NULL,
`abbrev` varchar(10) NOT NULL,
`postal` char(2) NOT NULL,
`capital` varchar(20) NOT NULL,
PRIMARY KEY (`postal`),
KEY `statename` (`statename`),
KEY `capital` (`capital`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Alaska’,'Alaska’,'AK’,'Juneau’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Alabama’,'Ala.’,'AL’,'Montgomery’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Arkansas’,'Ark.’,'AR’,'Little Rock’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Arizona’,'Ariz.’,'AZ’,'Phoenix’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’California’,'Calif.’,'CA’,'Sacramento’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Colorado’,'Colo.’,'CO’,'Denver’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Connecticut’,'Conn.’,'CT’,'Hartford’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Dist. of Columbia’,'D.C.’,'DC’,'Washington’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Delaware’,'Del.’,'DE’,'Dover’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Florida’,'Fla.’,'FL’,'Tallahassee’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Georgia’,'Ga.’,'GA’,'Atlanta’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Guam’,'Guam’,'GU’,'Agaña’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Hawaii’,'Hawaii’,'HI’,'Honolulu’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Iowa’,'Iowa’,'IA’,'Des Moines’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Idaho’,'Idaho’,'ID’,'Boise’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Illinois’,'Ill.’,'IL’,'Springfield’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Indiana’,'Ind.’,'IN’,'Indianapolis’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Kansas’,'Kans.’,'KS’,'Topeka’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Kentucky’,'Ky.’,'KY’,'Frankfort’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Louisiana’,'La.’,'LA’,'Baton Rouge’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Massachusetts’,'Mass.’,'MA’,'Boston’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Maryland’,'Md.’,'MD’,'Annapolis’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Maine’,'Maine’,'ME’,'Augusta’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Michigan’,'Mich.’,'MI’,'Lansing’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Minnesota’,'Minn.’,'MN’,'St Paul’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Missouri’,'Mo.’,'MO’,'Jefferson City’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Mississippi’,'Miss.’,'MS’,'Jackson’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Montana’,'Mont.’,'MT’,'Helena’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’North Carolina’,'N.C.’,'NC’,'Raleigh Durham’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’North Dakota’,'N.D.’,'ND’,'Bismarck’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Nebraska’,'Nebr.’,'NE’,'Lincoln’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’New Hampshire’,'N.H.’,'NH’,'Concord’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’New Jersey’,'N.J.’,'NJ’,'Trenton’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’New Mexico’,'N.M.’,'NM’,'Santa Fe’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Nevada’,'Nev.’,'NV’,'Carson City’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’New York’,'N.Y.’,'NY’,'Albany’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Ohio’,'Ohio’,'OH’,'Columbus’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Oklahoma’,'Okla.’,'OK’,'Oklahoma City’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Oregon’,'Ore.’,'OR’,'Salem’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Pennsylvania’,'Pa.’,'PA’,'Harrisburg’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Puerto Rico’,'P.R.’,'PR’,'San Juan’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Rhode Island’,'R.I.’,'RI’,'Providence’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’South Carolina’,'S.C.’,'SC’,'Columbia’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’South Dakota’,'S.D.’,'SD’,'Pierre’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Tennessee’,'Tenn.’,'TN’,'Nashville’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Texas’,'Tex.’,'TX’,'Austin’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Utah’,'Utah’,'UT’,'Salt Lake City’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Virginia’,'Va.’,'VA’,'Richmond’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Virgin Islands’,'V.I.’,'VI’,'Charlotte Amalie’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Vermont’,'Vt.’,'VT’,'Montpelier’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Washington’,'Wash.’,'WA’,'Olympia’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Wisconsin’,'Wis.’,'WI’,'Madison’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’West Virginia’,'W.Va.’,'WV’,'Charleston’);
insert into `states`(`statename`,`abbrev`,`postal`,`capital`) values
(’Wyoming’,'Wyo.’,'WY’,'Cheyenne’);

Listing 6: State table definition and SQL insert data statements
Getting Selected ID and Name Values

After the ComboBoxes are loaded with data, the users can choice any items (States and Capitals) by clicking and selecting on them. The selected ID and Name values must be known by the program for any possible selection. In general this ID represents a foreign key in the master table to keep data integrity, inserting and updating records. The Name values are showing to the user and some times it requires data validation depending on application business rules. In the last couple of years I have gotten a lot of requests from many users to load the combination of the ID and Name ([ID] – [Name]) as a general shown Name field in the ComboBox, ListBox and/or CheckListBox read-only controls. It seems to me that this combination sometimes gives the end-users more business meaning about the select data than a single Name only. I guess they may start understanding the purpose of the ID value as a primary key of a table in business applications development. Let’s look at the code (Listing 7) of the Postal Code button in Figure 1. This code is calling the LoopUpGetIDString() function (Listing to retrieve the value of the postal code by the selected state. For example, for the ‘Alaska’ state we got ‘AK’ postal code. If an error does not occur the postal code is shown to the user by using the Show() method of the MessageBox class object.

Private Sub PostalCodeButton1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs)
Handles PostalCodeButton1.Click
Cursor = Cursors.WaitCursor
Dim PostalCodeString As String
Using LoopUpObject As New LoopUpClass()
PostalCodeString = LoopUpObject.LoopUpGetIDString(StatesComboBox, _
mErrorMsgString)
If Not IsNothing(mErrorMsgString) Then
Cursor = Cursors.Default
MessageBox.Show(mErrorMsgString, _
Me.Text, _
MessageBoxButtons.OK, _
MessageBoxIcon.Error)
Else
Cursor = Cursors.Default
MessageBox.Show(”Postal Code: ” & PostalCodeString, _
Me.Text, _
MessageBoxButtons.OK, _
MessageBoxIcon.Information)
End If
End Using
Cursor = Cursors.Default
End Sub

Listing 7: Click event of the Postal Code button in Figure 1

The LoopUpGetIDString() and LoopUpGetIDInt32() functions (Listing return the ID value for a character and a numeric data type respectively. The LoopUpGetNameString() function (Listing returns the Name as a character data type only. These functions use the SelectedIndex() property to position the item number and the CType() explicit conversion function converts the selected ComboBox item object into ListItemClass class (Listing 3). Using the read-only properties FieldIDString, FieldIDInt32 and FieldName (Listing 3) the ID and the Name values can be retrieved.

Public Function LoopUpGetIDString(ByVal pComboBox As ComboBox, _
ByRef pErrorMsgString As String) As String
Dim IndexNumberInt32 As Int32, GetIDString As String
Try
IndexNumberInt32 = pComboBox.SelectedIndex()
GetIDString = CType(pComboBox.Items(IndexNumberInt32), ListItemClass).FieldIDString
Return (GetIDString)
Catch exErr As Exception
Return (Nothing)
pErrorMsgString = exErr.Message
End Try
End Function

Public Function LoopUpGetIDInt32(ByVal pComboBox As ComboBox, _
ByRef pErrorMsgString As String) As Int32
Dim IndexNumberInt32 As Int32, GetIDInt32 As Int32
Try
IndexNumberInt32 = pComboBox.SelectedIndex()
GetIDInt32 = CType(pComboBox.Items(IndexNumberInt32), ListItemClass).FieldIDInt32
Return (GetIDInt32)
Catch exErr As Exception
pErrorMsgString = exErr.Message
Return (Nothing)
End Try
End Function

Public Function LoopUpGetNameString(ByVal pComboBox As ComboBox, _
ByRef pErrorMsgString As String) As String
Dim IndexNumberInt32 As Int32, GetNameString As String
Try
IndexNumberInt32 = pComboBox.SelectedIndex()
GetNameString = CType(pComboBox.Items(IndexNumberInt32), ListItemClass).FieldName
Return (GetNameString)
Catch exErr As Exception
pErrorMsgString = exErr.Message
Return (Nothing)
End Try
End Function

Listing 8: Lookup ID functions for a character and a numeric data type
Finding ID and Name Values

As I explained above, the ListItemClass generic class (Listing 3) was created to store and read-only the values of the ID and Name columns from the lookup tables. In business applications with lookup tables in the database server finding the ID and Name values is a must. To find a string Name item, for example, in a ComboBox, the FindString() method is the easiest way to do so. With two lines of code (Listing 9) the string Name can be found by returning the Index number from the FindString() method and the Index position by using the SelectedIndex() property.

Public Sub LoopUpFindNameString(ByVal pComboBox As ComboBox, _
ByVal pFindNameString As String, _
ByRef pErrorMsgString As String)
Dim IndexNumberInt32 As Int32
Try
IndexNumberInt32 = pComboBox.FindString(pFindNameString)
pComboBox.SelectedIndex() = IndexNumberInt32
Catch exErr As Exception
pErrorMsgString = exErr.Message
End Try
End Sub

Listing 9: Lookup find string Name subroutine

Finding the ID value in a ComboBox requires more codes. I did not find any ID method implemented in the ComboBox control. In this particular case we need to loop through the entire Item collection and compare two string values as shown in Listing 10. The Compare() method of the String class object compares two strings and returns a Int32 zero value if both strings are equal. If the strings are equal the SelectedIndex() property position the Index in the ComboBox as I explained before. If the strings are not equal, the ComboBox does not show any data by setting SelectedIndex() equal -1.

Public Sub LoopUpFindIDString(ByVal pComboBox As ComboBox, _
ByVal pFindIDString As String, _
ByRef pErrorMsgString As String)
Dim LoopInt32, CompareInt32 As Int32
Dim GetIDString As String
Dim IsFoundBoolean As Boolean = False
Try
For LoopInt32 = 0 To pComboBox.Items.Count - 1
GetIDString = CType(pComboBox.Items(LoopInt32), ListItemClass).FieldIDString
CompareInt32 = String.Compare(GetIDString, pFindIDString)
If CompareInt32 = 0 Then
pComboBox.SelectedIndex() = LoopInt32
IsFoundBoolean = True
Exit For
End If
Next LoopInt32
If Not IsFoundBoolean Then
pComboBox.SelectedIndex() = -1
End If
Catch exErr As Exception
pErrorMsgString = exErr.Message
End Try
End Sub

Listing 10: Lookup find string ID subroutine
Finding the State and Capital Names by Postal Code

When the user selects an item in the ComboBox the ID is determined by using any of the functions in Listing 8 as we discussed before (LoopUpGetIDString() and LoopUpGetIDInt32()). Often, based on the selected ID, we need to find and load with data another control from different table. In my solution project example (Figure 1), when the user select a State the Capital TextBox control is loaded data. As you can see for ‘Alaska’ state the capital is ‘Juneau’, and for the capital ‘Agaña’ the state is ‘Guam’. In both case the Postal Code represents the selected ID as the primary key in `state` table (Listing 6). How to implement this approach in Windows environment? One of the easiest and fastest ways in MySQL 5.0/VB.NET 2005 is by using the SelectedIndexChanged() event of the ComboBox and MySQL stored procedures. I remember in the old days of VB 6.0 we used to use the Click() event and dynamic SQL statements with MDAC technology. Listing 11 and 12 shows the SelectedIndexChanged() events of the Sates and Capitals ComboBoxes. As you can see, in both events, the Postal Code ID (PostalCodeString) is determined first by using the LoopUpGetIDString() function.

Private Sub StatesComboBox_SelectedIndexChanged(ByVal sender As System.Object. _
ByVal e As System.EventArgs)
Handles StatesComboBox.SelectedIndexChanged
Dim PostalCodeString As String
Dim CapitalNameString As String = Nothing
Using LoopUpObject As New LoopUpClass(mMySQLConnectionString)
PostalCodeString = LoopUpObject.LoopUpGetIDString(StatesComboBox, _
mErrorMsgString)
Call LoopUpObject.LoopUpGetCapitalName(PostalCodeString, _
“usp_states_capital_by_postal”, _
CapitalNameString, _
mErrorMsgString)
CapitalTextBox.Text = CapitalNameString
End Using
End Sub

Listing 11: SelectedIndexChanged() event of the States ComboBox

Private Sub CapitalsComboBox_SelectedIndexChanged(ByVal sender As System.Object, _
ByVal e As System.EventArgs)
Handles CapitalsComboBox.SelectedIndexChanged
Dim PostalCodeString As String
Dim StateNameString As String = Nothing
Using LoopUpObject As New LoopUpClass(mMySQLConnectionString)
PostalCodeString = LoopUpObject.LoopUpGetIDString(CapitalsComboBox,
mErrorMsgString)
Call LoopUpObject.LoopUpGetStateName(PostalCodeString, _
“usp_states_name_by_postal”, _
StateNameString, _
mErrorMsgString)
StateTextBox.Text = StateNameString
End Using
End Sub

Listing 12: SelectedIndexChanged() event of the Capitals ComboBox

The user stored procedures shown in Listing 13 (`usp_states_capital_by_postal` and `usp_states_name_by_postal`) have Postal Code as input parameter (par_postalcode) and Capital and State (par_capital and par_statename) as output parameters respectively. Both procedures were developed and tested using Toad for MySQL 2.0.3 freeware version from Quest Software, Inc.

DROP PROCEDURE IF EXISTS `usp_states_capital_by_postal`;
CREATE PROCEDURE `usp_states_capital_by_postal`(
IN par_postalcode CHAR(2),
OUT par_capital VARCHAR(20)
)
BEGIN
SELECT `states`.`capital` INTO par_capital
FROM `states`
WHERE `postal` = par_postalcode;
END;

DROP PROCEDURE IF EXISTS `usp_states_name_by_postal`;
CREATE PROCEDURE `usp_states_name_by_postal`(
IN par_postalcode CHAR(2),
OUT par_statename VARCHAR(20)
)
BEGIN
SELECT `states`.`statename` INTO par_statename
FROM `states`
WHERE `postal` = par_postalcode;
END;

Listing 13: User stored procedures to retrieve State and Capital name by Postal Code

The subroutines LoopUpGetCapitalName() and LoopUpGetStateName() are shown in Listing 14 and 15 respectively.

Public Sub LoopUpGetCapitalName(ByVal pPostalCodeString As String, _
ByVal pStoredProcedureString As String, _
ByRef pCapitalNameString As String, _
ByRef pErrorMsgString As String)
Dim MySqlParameterPostal As New MySqlParameter
Dim MySqlParameterCapital As New MySqlParameter
Try
Using mMySqlConnection As New MySqlConnection(mMySQLConnectionString)
mMySqlConnection.Open()
Using mMySqlCommand As New MySqlCommand
With mMySqlCommand
.Connection = mMySqlConnection
.CommandType = CommandType.StoredProcedure
.CommandText = pStoredProcedureString
With MySqlParameterPostal
.ParameterName = “?par_postalcode”
.Direction = ParameterDirection.Input
.MySqlDbType = MySqlDbType.VarChar
.Size = 2
.Value = pPostalCodeString
End With
.Parameters.Add(MySqlParameterPostal)
With MySqlParameterCapital
.ParameterName = “?par_capital”
.Direction = ParameterDirection.Output
.MySqlDbType = MySqlDbType.VarChar
.Size = 20
.Value = pCapitalNameString
End With
.Parameters.Add(MySqlParameterCapital)
.ExecuteNonQuery()
mObjectjValue = .Parameters(”?par_capital”).Value
If Not IsDBNull(mObjectjValue) Then
pCapitalNameString = mObjectjValue.ToString
Else
pCapitalNameString = String.Empty
End If
End With
End Using
End Using
Catch exErr As Exception
pErrorMsgString = exErr.Message
Finally
If Not IsNothing(MySqlParameterPostal) Then
MySqlParameterPostal = Nothing
End If
If Not IsNothing(MySqlParameterCapital) Then
MySqlParameterCapital = Nothing
End If
End Try
End Sub

Figure 14: Retrieve Capital name subroutine by Postal Code

Public Sub LoopUpGetStateName(ByVal pPostalCodeString As String, _
ByVal pStoredProcedureString As String, _
ByRef pStateNameString As String, _
ByRef pErrorMsgString As String)
Dim MySqlParameterPostal As New MySqlParameter
Dim MySqlParameterState As New MySqlParameter
Try
Using mMySqlConnection As New MySqlConnection(mMySQLConnectionString)
mMySqlConnection.Open()
Using mMySqlCommand As New MySqlCommand
With mMySqlCommand
.Connection = mMySqlConnection
.CommandType = CommandType.StoredProcedure
.CommandText = pStoredProcedureString
With MySqlParameterPostal
.ParameterName = “?par_postalcode”
.Direction = ParameterDirection.Input
.MySqlDbType = MySqlDbType.VarChar
.Size = 2
.Value = pPostalCodeString
End With
.Parameters.Add(MySqlParameterPostal)
With MySqlParameterState
.ParameterName = “?par_statename”
.Direction = ParameterDirection.Output
.MySqlDbType = MySqlDbType.VarChar
.Size = 20
.Value = pStateNameString
End With
.Parameters.Add(MySqlParameterState)
.ExecuteNonQuery()
mObjectjValue = .Parameters(”?par_statename”).Value
If Not IsDBNull(mObjectjValue) Then
pStateNameString = mObjectjValue.ToString
Else
pStateNameString = String.Empty
End If
End With
End Using
End Using
Catch exErr As Exception
pErrorMsgString = exErr.Message
Finally
If Not IsNothing(MySqlParameterPostal) Then
MySqlParameterPostal = Nothing
End If
If Not IsNothing(MySqlParameterState) Then
MySqlParameterState = Nothing
End If
End Try
End Sub

Figure 15: Retrieve State name subroutine by Postal Code
Executing Stored Procedures in MySQL 5.0/VB.NET 2005

After the article ?Define and Store MySQL ADO Connection String in VB.NET 2005” was published online, I got many questions from many Open Source application developers around the world. One of the main questions was how to execute a MySQL 5.0 stored procedure in VB.NET 2005 with input and output parameters. They were looking for a simple code implementation and a good explanation using the latest Connector/NET 5.0.6. In quality of example, let’s look carefully at the subroutine LoopUpGetCapitalName() in Listing 14. Looking for a better explanation, I have decided to divide this code in five main blocks from Listing 14.1 to Listing 14.5. The first Listing 14.1 uses the Using statement to create the Connection object mMySqlConnection and initialize it. The variable mMySQLConnectionString represents the MySQL Connection String defined in the app.config file as Server=xxx;Database=xxx;Uid=xxx;Pwd=xxx. After the connection is open with the Open() method the Command object mMySqlCommand is created and initialized with the Using statement. Three main properties of the Command object needs to be determined: 1.Connection property sets to Connection object, 2. CommandType property sets to stored procedure type (CommandType.StoredProcedure) and 3. CommandText property sets to the stored procedure object name developed in the MySQL database server.

Using mMySqlConnection As New MySqlConnection(mMySQLConnectionString)
mMySqlConnection.Open()
Using mMySqlCommand As New MySqlCommand
With mMySqlCommand
.Connection = mMySqlConnection
.CommandType = CommandType.StoredProcedure
.CommandText = pStoredProcedureString
End With
End Using
End Using

Listing 14.1

Because the user stored procedure `usp_states_capital_by_postal` (Listing 13) has two parameters we need to create, initialize, set and add these parameters to the Command object parameters collection. Listing 14.2 and 14.3 shows the code for Postal Code (MySqlParameterPostal) and Capital Name (MySqlParameterCapital) MySQL parameters. As you can see, for the Connector/NET 5.0.6, the question sign (?) should be included before the parameter name. The Postal Code parameter direction is setup to Input and the Capital Name is setup to Output as required from the user stored procedure `usp_states_capital_by_postal` definition.

Dim MySqlParameterPostal As New MySqlParameter
With MySqlParameterPostal
.ParameterName = “?par_postalcode”
.Direction = ParameterDirection.Input
.MySqlDbType = MySqlDbType.VarChar
.Size = 2
.Value = PostalCodeString
End With
.Parameters.Add(MySqlParameterPostal)

Listing 14.2

Dim MySqlParameterCapital As New MySqlParameter
With MySqlParameterCapital
.ParameterName = “?par_capital”
.Direction = ParameterDirection.Output
.MySqlDbType = MySqlDbType.VarChar
.Size = 20
.Value = pCapitalNameString
End With
.Parameters.Add(MySqlParameterCapital)

Listing 14.3

After the Command object is executed with ExecuteNonQuery() method the output parameter value Capital Name can be retrieved as shown in Listing 14.4. The object variable mObjectjValue stores the Capital Name and it needs to be checked for Null database value. To do that the IsDBNull() function was used. If the Capital Name value is not Null, it’s gets stored in the pCapitalNameString function by reference parameter. On the other hand, if the value is Null the pCapitalNameString is set to empty string (String.Empty) using the String class object.

.ExecuteNonQuery()
mObjectjValue = .Parameters(”?par_capital”).Value
If Not IsDBNull(mObjectjValue) Then
pCapitalNameString = mObjectjValue.ToString
Else
pCapitalNameString = String.Empty
End If

Listing 14.4

After everything is done we need to release .NET used unmanaged resource as required. The Using statement takes care of releasing the resources for the mMySqlConnection Connection object and mMySqlCommand command object. The MySQL parameter objects MySqlParameterPostal and MySqlParameterCapital needs to be destroyed by setting them to Nothing in the Finally block (Listing 14.5). Setting to Nothing these objects will enable the Garbage Collection (GC) to release them.

Finally
If Not IsNothing(MySqlParameterPostal) Then
MySqlParameterPostal = Nothing
End If
If Not IsNothing(MySqlParameterCapital) Then
MySqlParameterCapital = Nothing
End If
End Try