VBMySQL.com

Introduction

In the fourth article of our series, we covered basic data binding and used it to populate our VB.NET 2005 form with data from our MySQL database. While this worked well to move data from a table to a control on our form, it lacked the versatility needed to allow for blank status messages and custom status messages. In this article we will learn to use the MySqlDataReader class to populate our combobox manually, and then implement a mechanism to support custom status messages and blank status messages in our application.

Using the MySqlDataReader Class

Quite often when developing database applications the use of a MySqlDataAdapter and DataSet can be overkill; many times we simply need to read a series of rows from the server with no need to store the data for future updates. In such read-only situations we can use the MySqlDataReader class. The MySqlDataReader class provides read-only access to the data in a MySQL database, reading one row at a time from the server. The MySqlDataReader class can be much more memory efficient than the MySqlDataAdapter/DataSet approach because as each row is read, the previous row is cleared from memory.

Switching From a MySqlDataAdapter to a MySqlDataReader

First let’s change our frmMain_Load event to use a MySqlDataReader object instead of a MySqlDataAdapter object. This change is not major, but there are small differences in object creation that we need to address. We do not instance the MySqlDataReader at the start of our function, because the MySqlCommand object has a ExecuteReader method that will return a MySqlDataReader object:

        Dim conn As New MySqlConnection
        Dim myCommand As New MySqlCommand
        Dim myReader As MySqlDataReader
        Dim SQL As String

We have removed declarations for a MySqlDataAdapter and a DataTable and replaced them with a single declaration for a MySqlDataReader. Next we modify the code to remove reference to the old MySqlDataAdapter object and instead create our MySqlDataReader object:

Try
        myCommand.Connection = conn
        myCommand.CommandText = SQL

        myReader = myCommand.ExecuteReader

As you can see, our MySqlDataReader object is created by the ExecuteReader method of the MySqlCommand class.

Creating a Custom Class

The next challenge we face has to do with replacing the data binding employed by the cboMessage combobox: we cannot bind a MySqlDataReader as we would a DataTable. Without data binding, we need to find a way to store not only the actual status message in our combobox (i.e. ‘Gone to Lunch’), we also need to somehow retain the message_id of a selected message. With data binding, we were able to specify a DisplayMember property(message) and a ValueMember property (message_id), but these properties are not available when manually populating a combobox.

If you take a closer look at the combobox class, you will see that the Add method of the Items collection takes an object as its argument. The combobox stores these objects in the Items collection, and uses the ToString method of the object passed to determine what value to display in the actual combobox.

We can use this to our advantage by creating a custom message class which will store a message and its ID. We can pass this class to the combobox.Items.Add method and later retrieve the selected message and ID value for performing updates.

To create a custom class, right-click on your solution in the Solution Explorer and choose the Add > Class option from the drop-down menu. Set the name to something like clsMessage and click the Add button. You should have a simple template like the following:

        Public Class clsMessage

        End Class

Before our class can be of much use, we need to add variables, properties, a constructor, and a ToString function.

Creating Variables - Public vs. Private

The first thing we will do is create a couple of private variables for our class. These will be the class’s internal storage for message and ID values. We will declare these with the Private keyword so that they are inaccessible outside of the class:

        Public Class clsMessage
                        Private myID As Integer
                        Private myMessage As String
        End Class

Because these variables are declared Private they cannot be modified or read by other classes. This gives us greater control over the contents of the variables because we know for certain that they will not be externally modified. We can provide access to the contents of these variables through the use of properties.

Creating Properties

Properties work like gatekeepers that control the modification of internal variables and their presentation to external classes. In our simple application we will use them to provide direct access to our internal variables, but they can easily be expanded to provide very fine-grained control of what is allowed in and out of your class.

Our properties are declared Public, and property templates can be easily created by typing a single line, in our case Public Property Message As String. When you type the line and press enter, the following template is automatically created:

        Public Property Message() As String
                Get

                End Get
                Set(ByVal value As String)

                End Set
        End Property

The Get section of the property determines what is returned when clsMessage.Message is called to retrieve (or Get) the value, and the Set section takes an assignment from an external class and determines what is done with it. In our case we will be using a very simple pair of assignments for our property:

        Public Property Message() As String
                Get
                        Message = myMessage
                End Get
                Set(ByVal value As String)
                        myMessage = value
                End Set
        End Property

Our ID property will be almost identical:

        Public Property ID() As Integer
                Get
                        ID = myID
                End Get
                Set(ByVal value As Integer)
                        myID = value
                End Set
        End Property

With these properties in place, we can now instantiate our class, and assign the Message and ID properties.

Creating a Constructor

While we could now use our class by instantiating it, filling the properties, and then passing it to our combobox, we can make the process even smoother by adding a constructor to our class. Simply put, a constructor is a procedure that is executed as the object is created. We can pass the message and ID values to the constructor as we create the object, saving us the trouble of having to instantiate and then populate the object.

To create a constructor, create a procedure named New:

        Sub New(ByVal ID As Integer, ByVal Message As String)
                myID = ID
                myMessage = Message
        End Sub

Our constructor accepts two arguments, ID and Message, and then passes them to the internal variables. We can call the constructor by using the New keyword:

      Dim myMessage As New clsMessage(12, "My Message has an ID of 12")

Overriding the ToString Method

The last part of creating our class is to override the ToString method. Because our custom class actually inherits the Object class, it also inherits a ToString method. Because we do not want to have the ID value output in our combobox, we need to override this method and change it to not include the ID value:

        Public Overrides Function ToString() As String
                ToString = myMessage
        End Function

The Final Custom Class

Here is our finished custom class:

        Public Class clsMessage
                Private myID As Integer
                Private myMessage As String

                Sub New(ByVal ID As Integer, ByVal Message As String)
                        myID = ID
                        myMessage = Message
                End Sub

                Public Property Message() As String
                        Get
                                Message = myMessage
                        End Get
                        Set(ByVal value As String)
                                myMessage = value
                        End Set
                End Property

                Public Property ID() As Integer
                        Get
                                ID = myID
                        End Get
                        Set(ByVal value As Integer)
                                myID = value
                        End Set
                End Property

                Public Overrides Function ToString() As String
                        ToString = myMessage
                End Function
        End Class

Using the Custom Class

Now that our custom class is created, we can use it to populate our combobox. We’ll start by creating a static item and then loop through our MySqlDataReader and load the contents of our query into the combobox.

Passing the Custom Class to the ComboBox

First let’s add a custom, static message that will represent no message at all. We will use ‘No Message’ as the message and -1 as the ID:

        myCommand.Connection = conn
        myCommand.CommandText = SQL
        myReader = myCommand.ExecuteReader

        cboMessage.Items.Add(New clsMessage(-1, "No Message"))

We can also add the following line just above our Catch block to make this the default message:

        cboMessage.SelectedIndex = 0

Looping Through the MySqlDataReader

The MySqlDataReader class contains only one row at a time, and is advanced to the next record through use of the Read method. The Read method returns True every time there is a new row to be read, and returns False when it passes the last row in the result set. Looping through the result set is as simple as using a While loop:

      While myReader.Read
                        'DO SOMETHING
        End While

Accessing Individual Column Data

As we loop through our query results, we need to access the data contained within our columns. The most basic way to do this is to use the GetValue method of the MySqlDataReader. The GetValue method returns the contents of the specified column in the appropriate data type. GetValue expects an integer representing the column number you wish to query, in our case the message_id column is 0 and the message column is 1.

I generally try to avoid hard-coding column numbers into my application: if the query were to change you would need to change the column numbers or your application may encounter errors. Instead, we can use the GetOrdinal function to dynamically retrieve the column number based on the column name. The following code loops through our query result, populating a custom message class and loading it into our combobox:

        While myReader.Read
                cboMessage.Items.Add(New clsMessage(myReader.GetValue(myReader.GetOrdinal("message_id")), _
                                myReader.GetValue(myReader.GetOrdinal("message"))))
        End While

We pass a newly created clsMessage object to the Items.Add method, and populate the constructor of the object using the GetValue method of the MySqlDataReader. We use the GetOrdinal method of the MySqlDataReader to specify the column number for the columns so that our code is more future-proof.

The New frmMain_Load Procedure

Here is the complete frmMain_Load procedure:

        Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
                cboStatus.Items.Add("In")
                cboStatus.Items.Add("Out")
                cboStatus.SelectedIndex = 0

                dgvStatus.ReadOnly = True
                refreshStatus(dgvStatus)

                Dim conn As New MySqlConnection
                Dim myCommand As New MySqlCommand
                Dim myReader As MySqlDataReader
                Dim SQL As String

                SQL = "SELECT s.message_id, s.message " _
                 & "FROM in_out.status s " _
                 & "WHERE user_id IS NULL and deleted = 'False'"

                conn.ConnectionString = myConnString

                Try
                        conn.Open()

                        Try
                                myCommand.Connection = conn
                                myCommand.CommandText = SQL

                                myReader = myCommand.ExecuteReader

                                cboMessage.Items.Add(New clsMessage(-1, "No Message"))

                                While myReader.Read
                                        cboMessage.Items.Add(New clsMessage(myReader.GetValue(myReader.GetOrdinal("message_id")), _
                                                         myReader.GetValue(myReader.GetOrdinal("message"))))
                                End While

                                cboMessage.SelectedIndex = 0
                        Catch myerror As MySqlException
                                MsgBox("There was an error reading from the database: " & myerror.Message)
                        End Try
                Catch myerror As MySqlException
                        MessageBox.Show("Error connecting to the database: " & myerror.Message)
                Finally
                        If conn.State <> ConnectionState.Closed Then conn.Close()
                End Try
        End Sub

You should be able to test your code by running your application and checking cboMessage to see if it is populated properly.

Updating Data Using the Custom Class

Now that we have changed the method used to populate our combobox, we must also modify the procedure used to update our status. First we need to add a declaration for our custom class to the cmdUpdate_Click procedure:

        Dim myMessage As clsMessage

We populate this object by using the SelectedItem property of the cboMessage combobox. The SelectedItem property needs to be cast back into a clsMessage object before it can be used:

        myMessage = CType(cboMessage.SelectedItem, clsMessage)

Now that we have the message object for the selected message, we can use it to update the event table. We need to check whether the ID value of our message object is -1, and if so we pass a NULL value as a parameter to our update query. If the ID value of our message is not -1, we pass the ID value as a parameter to our update query:

        If myMessage.ID = -1 Then
                myCommand.Parameters.Add("?MessageID", DBNull.Value)
        Else
                myCommand.Parameters.Add("?MessageID", myMessage.ID)
        End If

Our cmdUpdate code is now ready to update a blank status message. Here is the new cmdUpdate_Click procedure:

        Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
                Dim conn As New MySqlConnection
                Dim myCommand As New MySqlCommand
                Dim myMessage As clsMessage

                myMessage = CType(cboMessage.SelectedItem, clsMessage)

                conn.ConnectionString = myConnString

                myCommand.Connection = conn
                myCommand.CommandText = "INSERT INTO event(user_id, message_id, timestamp, status, creator)" _
                 & "VALUES(?UserID, ?MessageID, NOW(), ?Status, ?Creator)"

                myCommand.Parameters.Add("?UserID", myUserID)

                If myMessage.ID = -1 Then
                        myCommand.Parameters.Add("?MessageID", DBNull.Value)
                Else
                        myCommand.Parameters.Add("?MessageID", myMessage.ID)
                End If

                myCommand.Parameters.Add("?Status", cboStatus.SelectedItem)
                myCommand.Parameters.Add("?Creator", myUserID)

                Try
                        conn.Open()
                        myCommand.ExecuteNonQuery()
                Catch myerror As MySqlException
                        MsgBox("There was an error updating the database: " & myerror.Message)
                End Try

                refreshStatus(dgvStatus)
        End Sub

Supporting Custom Status Message Entry

Now that we have laid the foundation for supporting blank error messages, we can add support for custom error messages. Our status table has a column named user_id which is used to indicate that a given status message is a custom message and also to indicate the user that the custom status message belongs to. We need to modify our application to allow for custom user input in our combobox, and then modify our update code to create a new status table entry when the user has entered a custom status message. After we have successfully created a custom status message, we need to modify our form load procedure to show the latest two custom status messages in addition to the default status messages.

Modifying the ComboBox

The first step in working with custom status messages is to modify our combobox control to allow our user to enter custom information. This is done by changing the DropDownStyle property of our combobox from DropDownList to DropDown. The two modes are practically the same with the exception that a DropDown combobox allows the user to manually enter information into the combobox.

While the DropDownStyle property can be set at design-time by adjusting the properties of the combobox, we can also make the change at run-time in our form load event:

        cboMessage.DropDownStyle = ComboBoxStyle.DropDown

Determining Whether a User Chose an Item or Entered a Custom Message

We can identify whether a user chose an existing status message or typed in their own status message by looking at the SelectedIndex property of the combobox. If the SelectedIndex property is set to -1, it indicates that the user has typed in their own status message. If the value is greater than -1, it indicates that the user has chosen a status message from the combobox drop-down list.

We can use an If … Else … End If block to handle the different code required for custom and existing status messages:

        If cboMessage.SelectedIndex <> -1 Then
                'IN THIS CASE WE HAVE A MESSAGE THAT WAS SELECTED FROM THE DROP-DOWN LIST
                myMessage = CType(cboMessage.SelectedItem, clsMessage)
        Else
                'IN THIS CASE WE NEED TO CREATE A NEW ENTRY IN THE STATUS TABLE
        End If

Our code will not need any modification when working with an existing status message, so we just need to create code to insert a new status message when the SelectedIndex property is -1.

Creating an INSERT Statement

Once we determine that we need to add a custom status message we can build an INSERT query to add the message to the status table. We only need to specify the user_id, message and deleted values when inserting into the status table because the message_id value is an AUTO_INCREMENT value and is generated automatically. In addition, we can use the ability of MySQL Connector/NET to execute multiple statements in a batch to add a SELECT query for retrieving the auto_increment value assigned to our new row:

        myCommand.CommandText = "INSERT INTO status(user_id, message) VALUES(?userID, ?message);" _
                & "SELECT LAST_INSERT_ID()"

The LAST_INSERT_ID() function always returns the last auto_increment value returned by your connection (in this case the message_id value for the status table). Because the function operates on a per-connection level, you can always be assured this is the last value generated by your application, regardless of how many users are inserting records into a table concurrently.

We once again use parameters for the values we will be inserting, and we create these parameters in our command object:

        myCommand.Parameters.Add("?userID", myUserID)
        myCommand.Parameters.Add("?message", cboMessage.Text)

In this case we are setting the user_id column to the ID value of our logged-in user, stored in the form’s myUserID variable, and the message column to the Text property of our cboMessage combobox.

Once our parameters are set, we can execute the UPDATE and SELECT queries. Because our SELECT query will be returning a single value, we can use the ExecuteScalar method of the MySqlCommand object. As you may recall from Part 4, the ExecuteScalar method will return a single value, or the first column of the first row of a query result. We can use the output of the ExecuteScalar method to create a clsMessage object, which will be used to create a new entry in cboMessage and the event table:

        Try
                conn.Open()
                myMessage = New clsMessage(myCommand.ExecuteScalar(), cboMessage.Text)
                cboMessage.Items.Insert(1, myMessage)
               cboMessage.SelectedIndex = 1
                conn.Close()
        Catch myerror As MySqlException
                MsgBox(”There was an error updating the database: ” & myerror.Message)
        End Try

As usual, the code is placed in a TRY-CATCH block to catch any errors that may occur. We use the Items.Insert method to add an item at a specific position in our combobox and then set the SelectedIndex property to move our selection to the newly created item.

Note that we explicitly close the connection object because it is reused later in the cmdUpdate_Click procedure. Up until now we have allowed VB.NET to implicitly close and dispose of our Connector/NET objects.

Our Complete Update Code

Here is the completed cmdUpdate_Click procedure:

        Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
                Dim conn As New MySqlConnection
                Dim myCommand As New MySqlCommand
                Dim myMessage As clsMessage

                conn.ConnectionString = myConnString

                myCommand.Connection = conn

                If cboMessage.SelectedIndex <> -1 Then
                        'IN THIS CASE WE HAVE A MESSAGE THAT WAS SELECTED FROM THE DROP-DOWN LIST
                        myMessage = CType(cboMessage.SelectedItem, clsMessage)
                Else
                        'IN THIS CASE WE NEED TO CREATE A NEW ENTRY IN THE STATUS TABLE
                        myCommand.CommandText = "INSERT INTO status(user_id, message, deleted) VALUES(?userID, ?message, 'False');" _
                          & "SELECT LAST_INSERT_ID()"
                        myCommand.Parameters.Add("?userID", myUserID)
                        myCommand.Parameters.Add("?message", cboMessage.Text)

                        Try
                                conn.Open()
                                myMessage = New clsMessage(myCommand.ExecuteScalar(), cboMessage.Text)
                                cboMessage.Items.Insert(1, myMessage)
                               cboMessage.SelectedIndex = 1
                                conn.Close()
                        Catch myerror As MySqlException
                                MsgBox(”There was an error updating the database: ” & myerror.Message)
                        End Try
                End If

                myCommand.CommandText = “INSERT INTO event(user_id, message_id, timestamp, status, creator)” _
                 & “VALUES(?UserID, ?MessageID, NOW(), ?Status, ?Creator)”

                myCommand.Parameters.Add(”?UserID”, myUserID)

                If myMessage.ID = -1 Then
                        myCommand.Parameters.Add(”?MessageID”, DBNull.Value)
                Else
                        myCommand.Parameters.Add(”?MessageID”, myMessage.ID)
                End If

                myCommand.Parameters.Add(”?Status”, cboStatus.SelectedItem)
                myCommand.Parameters.Add(”?Creator”, myUserID)

                Try
                        conn.Open()
                        myCommand.ExecuteNonQuery()
                Catch myerror As MySqlException
                        MsgBox(”There was an error updating the database: ” & myerror.Message)
                End Try

                refreshStatus(dgvStatus)
        End Sub

You should now be able to test your application code by entering a status message into the cboMessage combobox and clicking the Update Status button. Your status grid should be updated to show the new status message, and the status table should contain a new entry with your user ID and message values. Add three custom messages for use in the next part of our tutorial.

Displaying Custom Status Messages

Now that we have created our custom status messages, we need to modify our application to show not only our pre-defined status messages, but the two most recent custom status messages as well. As you may recall, messages with a user_id value of NULL are pre-defined status messages that apply to all users, while messages with a non-NULL user_id belong to the specified user.

UNION Queries

One way to show two sets of messages would be to perform two queries, one for the status messages with a NULL user_id, and one for the latest two messages with the same user_id as our user. This would mean two trips to the server and two result sets, and we would have to process data twice to move it to the combobox.

The better solution is to use a UNION query. With a UNION query the MySQL server can combine multiple result sets from multiple queries into a single result set. The only limitation with a UNION query is that all queries must return the same number and type of columns, in the same order. Full information on using UNION can be found at http://dev.mysql.com/doc/mysql/en/union.html.

The following query retrieves the latest two custom status messages for user 1, along with all pre-defined messages:

(SELECT s.message_id, s.message
FROM in_out.status s
WHERE user_id = 1 and deleted = 'False'
ORDER BY message_id DESC LIMIT 2)
UNION
(SELECT s.message_id, s.message
FROM in_out.status s
WHERE user_id IS NULL and deleted = 'False')

As you can see, each individual query can be sorted and limited independently, and if we wished, we could then sort and limit the entire result set as well.

All that we need to do now is replace the existing query in our frmMain_Load procedure with the new UNION query:

        SQL = "(SELECT s.message_id, s.message " _
                 & "FROM in_out.status s " _
                 & "WHERE user_id = ?userID and deleted = 'False' " _
                 & "ORDER BY message_id DESC LIMIT 2) " _
                 & "UNION " _
                 & "(SELECT s.message_id, s.message " _
                 & "FROM in_out.status s " _
                 & "WHERE user_id IS NULL and deleted = 'False')"

Once our query is modified, we just need to add a parameter to our command object to populate the appropriate user ID value:

        myCommand.Parameters.Add("?userID", myUserID)

The Complete frmMain_Load Procedure

Once again, here is the completed frmMain_Load procedure:

Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
                cboMessage.DropDownStyle = ComboBoxStyle.DropDown

                cboStatus.Items.Add("In")
                cboStatus.Items.Add("Out")
                cboStatus.SelectedIndex = 0

                dgvStatus.ReadOnly = True
                refreshStatus(dgvStatus)

                Dim conn As New MySqlConnection
                Dim myCommand As New MySqlCommand
                Dim myReader As MySqlDataReader
                Dim SQL As String

                SQL = "(SELECT s.message_id, s.message " _
                 & "FROM in_out.status s " _
                 & "WHERE user_id = ?userID and deleted = 'False' " _
                 & "ORDER BY message_id DESC LIMIT 2) " _
                 & "UNION " _
                 & "(SELECT s.message_id, s.message " _
                 & "FROM in_out.status s " _
                 & "WHERE user_id IS NULL and deleted = 'False')"

                conn.ConnectionString = myConnString

                Try
                        conn.Open()

                        Try
                                myCommand.Connection = conn
                                myCommand.CommandText = SQL
                                myCommand.Parameters.Add("?userID", myUserID)

                                myReader = myCommand.ExecuteReader

                                cboMessage.Items.Add(New clsMessage(-1, "No Message"))

                                While myReader.Read
                                        cboMessage.Items.Add(New clsMessage(myReader.GetValue(myReader.GetOrdinal("message_id")), _
                                                         myReader.GetValue(myReader.GetOrdinal("message"))))
                                End While

                                cboMessage.SelectedIndex = 0
                        Catch myerror As MySqlException
                                MsgBox("There was an error reading from the database: " & myerror.Message)
                        End Try
                Catch myerror As MySqlException
                        MessageBox.Show("Error connecting to the database: " & myerror.Message)
                Finally
                        If conn.State <> ConnectionState.Closed Then conn.Close()
                End Try
        End Sub

Changing Focus

Let’s add one tweak to the login form before we go. If you have been using your application you may notice that in spite of the fact that you have the Remember Me box checked, the login form still loads with the initial focus on the Username textbox, meaning that you must press the tab key before you can enter your password. We can solve this by using the Focus() method of the textbox class.

The only challenge we face is that the Focus method can only act on visible controls, and the login form does not display until the form load event is finished. We can however use the form activated event, which fires after the form is visible and active:

        Private Sub frmLogin_Activated(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Activated
                If My.Settings.Username <> "" Then
                        txtPassword.Focus()
                End If
        End Sub

Now our form will focus on the password field by default when there is a stored username value.

Conclusion

Our application has become fairly usable at this point: Our users can login, they can choose between no status message, a pre-defined status message, or they can specify their own status message. Our form will show the latest two custom status messages for our user to re-use. Our login form allows a user to save their username and will fill in the username and change focus to the password field accordingly.

In our next tutorial, we will develop an application installer so that we can deploy our application on multiple machines in our office.

Source code for this installment can be found at http://www.vbmysql.com/wp-content/uploads/vb-mysql-tutorial-part-6.zip