//
you're reading...
ASP.NET, Database, IT-Center, MySQL, VB.NET

connecting MySQL using VB.NET

1. Connector/NET

In early 2004 MySQL AB hired Reggie Burnett of ByteFX and acquired his ByteFX .NET data provider for MySQL. The provider was renamed Connector/NET and not only is it provided free under the terms of the GNU Public License, but it is one of the most feature-rich and best performing .NET providers for MySQL that is currently available. Connector/NET is written in C# and is completely managed code, allowing it to be ported to any platform that supports .NET, including Mono. One advantage Connector/NET provides over other solutions is its use of the native MySQL protocol: many other solutions wrap the MySQL C client library and suffer a performance loss as a result.

Downloading and Installing Connector/NET

MySQL Connector/NET is available for download at http://dev.mysql.com/downloads/connector/net/. Download the version that includes an installer to your local hard-drive and extract the Zip file.

Double-click the installer file to begin the installation process. Perform a complete install to the default directory.

Visual Basic.NET

Visual Basic.NET is the new version of Visual Basic. While it shares the Visual Basic name, there are significant differences between Visual Basic 6 and Visual Basic.NET. VB.NET is now entering into its third version. The first version was Visual Basic.NET, the second was Visual Basic.NET 2003, and the new version is Visual Basic.NET 2005. Visual Basic.NET 2005 introduces a new Express version that we will use in this tutorial.

The Express version of Visual Basic.NET is essentially a stripped-down version that still retains all the functionality needed to produce basic applications (no pun intended).

2. VB.NET

Starting Visual Basic.NET

Once you have installed VB.NET 2005, look for a link in the Programs section of your Start menu named Visual Basic 2005 Express Edition Beta. Start VB.NET and click Ctrl + N to start a new project.

Creating an Event

I’m going to start by creating an event for the Cancel button. An event is something that triggers execution of code within VB.NET. In this case, the event in question will be the clicking of the Cancel button. The simplest way to create a button click event is to double click the button on the form. When you double-click the cancel button, the code view of the form will be displayed and you will see the following code appear:

Public Class frmLogin
  Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click

  End Sub
End Class

The Public Class line indicates that this class describes our form, and the Private Sub line shows that this is a subfunction that handles the Click event of the cmdCancel object. The two End lines show where the code for each of these sections ends.

We will add a single line to the subfunction to close the application when this button is clicked:

Application.Exit()

This line instructions the application to close, and will be executed when we click the Cancel button. Your code should now look like this:

Public Class frmLogin
  Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click
    Application.Exit()
  End Sub
End Class

Starting the Application

Once you have entered the code, save the project and press the F5 key to test your application. You can also choose the Start option from the Debug menu or click the green icon on the toolbar.

Your form should be displayed and if you click on the Cancel button, the form should disappear as the application is closed. If you click on the Login button nothing would happen as we have not created any code for the event of clicking on the Login button.

Importing the Connector/NET Namespace

Objects in VB.NET are organized into namespaces. Namespaces are logical grouping of objects used to help organize the various objects available in VB.NET.

To use a Connector/NET Connection object, you need to define it as MySql.Data.MySqlClient.MySqlConnection (more on this object later). This of course is a lot to type on a regular basis, and we can use the Imports statement to shorten this.

By adding Imports MySql.Data.MySqlClient to the start of the source file, we can just refer to the Connector/NET Connection object as MySqlConnection.

Adding a MySqlConnection Object

MySQL Connector/NET is essentially a collection of objects used to access a MySQL database. The first object we will use is the MySqlConnection object. The connection object serves as a broker between the other objects contained within Connector/NET and the MySQL server. The connection object handles the login process and is the object we will use to verify that a user’s login information is correct.

There are two steps to adding an object. First we declare the object, then we instanciate it. When declaring an object we assign a name that we will use to refer to it, and also indicate the scope of the object, or in other words, what functions and procedures can access the object. In our case we will want to ensure that any function or procedure within the form will have access to the connection object, so we will declare the connection object first thing within the class:

Imports MySql.Data.MySqlClient

Public Class frmLogin
 Dim conn As MySqlConnection

 Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click
   Application.Exit()
 End Sub
End Class

The Dim keyword is used when declaring objects and variables. I’m using conn as the name of my connection object. The As keyword is used to indicate what we are declaring (an object, a variable, etc). Finally, MySqlConnection is the object we are declaring.

Instanciating the MySqlConnection Object

Now that we have declared the connection object, we will instanciate it. Until we instanciate an object it is not actually available for use. We will instanciate the object within the subfunction that handles the click event for the Login button. Double-click the Login button in the design view to create the subfunction.

To instanciate an object, we use the New keyword:

Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
  conn = New MySqlConnection()
End Sub

Building the Connection String

The MySqlConnection object uses a connection string to know which server to connect to, which database to access, and what username and password to use to authenticate. The various properties are separated by semicolons. Here is a sample connection string:

server=localhost; user id=mike; password=12345; database=in_out

Of course, we need the connection string to reflect the information our user enters into the form. To do this we shall use the & character to combine multiple strings together, and the .Text value of the TextBox objects. Additionally I will use the _ character to split our code into multiple lines:

Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
  conn = New MySqlConnection()
  conn.ConnectionString = "server=" & txtServer.Text & ";" _
    & "user id=" & txtUsername.Text & ";" _
    & "password=" & txtPassword.Text & ";" _
    & "database=in_out"

End Sub

Opening the Connection

The last thing we need to do is instruct the connection object to open the connection to the MySQL server with the .Open() method of the connection object:

Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
  conn = New MySqlConnection()
  conn.ConnectionString = "server=" & txtServer.Text & ";" _
    & "user id=" & txtUsername.Text & ";" _
    & "password=" & txtPassword.Text & ";" _
    & "database=in_out"

  conn.Open()
End Sub

Using a MessageBox

Once we have successfully opened the connection, we will want to let the user know that their username and password were correct. We can do this with the MessageBox object. In its simplest form, a MessageBox will display a message to the user, with an OK button. Here’s a simple line of code to display a MessageBox:

MessageBox.Show("Connection Opened Successfully!")

Closing the Connection

When we are finished with our connection object we need to close it. By closing the connection we release the resources needed to keep the connection active. It is a good practice to close connections as soon as you are finished with them. The connection is closed with the .Close() method.

conn.Close()

Disposing of the MySqlConnection Object

Once we are completely finished with an object, it is a good practice to dispose of it, thus minimizing resource usage in our application. When we dispose of an object, the resources it occupied are freed and the object no longer exists. We dispose of an object by calling its .Dispose() method:

conn.Dispose()

Catching Errors

Our code is currently only appropriate for an ideal situation. If we cannot connect to the server or if we provide the wrong username or password the connection object will return an error, also known as an exception. To handle errors, VB.NET has a special TRY … CATCH … FINALLY syntax. We place the code with the potential error after the TRY keyword and before the CATCH keyword. The CATCH keyword is used to indicate what kind of error we anticipate we might encounter (in this case the error returned will be a Connector/NET MySqlException object). Any code present after the FINALLY keyword will be executed whether there is an error or not. If an exception is encountered, the remaining code in the TRY section will not be executed.

Here’s the final code for the procedure that handles the connection:

Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
  conn = New MySqlConnection()
  conn.ConnectionString = "server=" & txtServer.Text & ";" _
    & "user id=" & txtUsername.Text & ";" _
    & "password=" & txtPassword.Text & ";" _
    & "database=in_out"

  Try
    conn.Open()
    MessageBox.Show("Connection Opened Successfully")
    conn.Close()
  Catch myerror As MySqlException
    MessageBox.Show("Error Connecting to Database: " & myerror.Message)
  Finally
    conn.Dispose()
  End Try
End Sub

This brings all of our connection code together and allows it to handle errors without crashing. We instanciate the connection object and assign it a connection string. Within an error handling TRY … CATCH block we attempt to open the connection to the server and, if successful, we will show a MessageBox to the user indicating our success and then close the connection.

If an error occurs while connecting, the code in the CATCH block will be executed. In this case we will show the user a MessageBox with the .Message property of the MySqlException object, which contains the human-readable error message associated with the error.

In the FINALLY block we will dispose of the connection object. We do this in the FINALLY block because whether or not the connection succeeds we will want to dispose of the object.

Testing the Project

Once your code is in place, save the project and press the F5 key to begin the application in debug mode. Try using the proper server address, username, and password, then try using the wrong server address, then try the wrong password. You will notice different error messages for using the wrong address and for using the wrong password. The error message for using the wrong username is the same as the error message for using the wrong password, as a seperate error message would provide a security threat in that a potential attacker would know whether they had a correct username or not.

3. SELECT & UPDATA DATA

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.

 

About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.

Discussion

One thought on “connecting MySQL using VB.NET

  1. Wow, incredible blog layout! How long have
    you been blogging for? you made blogging look easy.
    The overall look of your site is wonderful, let alone the content!

    Posted by besttimetobuyairlinetickets.net | February 24, 2013, 11:17 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 9 other followers

December 2011
M T W T F S S
« Nov   Jan »
 1234
567891011
12131415161718
19202122232425
262728293031  

Archives

Web Statistic

Blog Stats

  • 149,551 hits
%d bloggers like this: