Accessing MySQL on VB.NET using MySQL Connector/Net, Part 6: Create Connection

This entry is part 6 of 8 in the series Accessing MySQL on VB.NET

Create Connection

After I have prepared many things for showing how to access MySQL Server using VB.NET. Let’s see what I have done so far. Right now, I have a remote MySQL Server at 192.168.125.21 with port 3306 (Default port). Also, a sample database “world” and a user account “worldUser” with password “worldpassword”. Now it’s time to start Microsoft Visual Studio 2005 on a development PC.

Step-by-step

  1. On Development PC, open Microsoft Visual Studio 2005.
    Open Microsoft Visual Studio 2005
  2. Create a New Windows Application Project “SampleMySQL”.
    New Windows Application
  3. First, I need to add a MySQL library. Right-click on the project name (SampleMySQL) -> Add Reference.
    Add Reference
  4. On Add Reference, select MySQL.Data on .NET tab.
    MySQL.Data
  5. By default, the reference library (MySQL.Data) won’t be copied to the output directory. That means when you deploy the application on other PC which doesn’t have the library installed, it’ll throw error. So I have to set the Copy Local property of the library file to True. Click Show All Files icon.
    Show All Files
  6. Expand References -> Select MySQL.Data -> Change Copy Local property to True.
    Set Copy Local to True
  7. Now it’s time to coding the application. First, I have to import a namespace. Open the Code View and add this line on the top.
    Imports MySql.Data.MySqlClient

    Imports MySql.Data

  8. Add these code to the Class.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    
        Private Sub Form1_Load(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles MyBase.Load
            TestConnection()
        End Sub
     
        Public Sub TestConnection()
            Try
                Dim connStr As String = "Database=world;" & _
                        "Data Source=192.168.125.21;" & _
                        "User Id=worldUser;Password=worldpassword"
                Dim connection As New MySqlConnection(connStr)
                connection.Open()
                connection.Close()
                MsgBox("Connection is okay.")
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End Sub

    Code Explanation:

    • Line 1-4: Simple Form_Load event that call TestConnection() method. The method is invoked when the form is loaded.
    • Line: 7-17: Try-Catch scope. If there is any error in try scope, throws exception and goes to catch scope.
    • Line: 8-10: A connection string represents configuration for connect to MySQL Server. Common attributes are:
      1. Database. The database to be used after a connection is opened.
      2. Data Source. The name of the MySQL server to which to connect.
      3. Port. The port MySQL is using to listen for connections. The default value is 3306
      4. User ID. The user that use to connect to the database on MySQL Server
      5. Password. Password of the user.
      6. Connection Timeout. Time to wait while trying to establish a connection before terminating the attempt and generating an error.
    • Line 11: Create MySqlConnection object and assign connectionString property.
    • Line 12-13: Test open and close the connection to the database on MySQL Server.
    • Line 14: If there is no error, show a success message.
    • Line 16: Show the error message.
  9. Next, test the code by run the application. If the connection is successfully connected and disconnected. You’ll see the message “Connection is okay”.
    Successfully Connected
  10. If something wrongs, you’ll see message other than the previous step. The figure below is the example that mistyped the database name in the connection string.
    Unsuccessful Connection
Series Navigation<< Accessing MySQL on VB.NET using MySQL Connector/Net, Part 5: Install MySQL Connector NetAccessing MySQL on VB.NET using MySQL Connector/Net, Part 7: Perform SQL Operations >>

41 Comments

  1. Eric May 27, 2009
  2. Fernando October 21, 2009
  3. linglom October 26, 2009
  4. Ronald Louritson October 28, 2009
  5. Jose Miguel Castillo October 29, 2009
  6. linglom November 4, 2009
  7. Shanaka November 13, 2009
  8. Jake January 29, 2010
  9. aries March 5, 2010
  10. aries March 5, 2010
  11. Derek van Rhyn March 20, 2010
  12. Nur Hassana June 17, 2010
  13. lhes August 16, 2010
  14. anand August 19, 2010
  15. Saqib September 15, 2010
  16. Saqib September 16, 2010
  17. linglom September 21, 2010
  18. Saqib September 26, 2010
  19. Horison November 16, 2010
  20. linglom December 18, 2010
  21. Pharmk271 October 8, 2011
  22. Pharmk33 October 8, 2011
  23. Itdoesnotmatter January 17, 2012
  24. Dimkostav January 28, 2012
  25. Eddy Jawed January 29, 2012
  26. Bill February 4, 2012
  27. Makis February 14, 2012
  28. huejii February 14, 2012
  29. huejii February 14, 2012
  30. huejii February 14, 2012
  31. Eddy Jawed February 15, 2012
  32. smita February 26, 2012
  33. Cooksey April 7, 2012
  34. Maina Thinyai April 17, 2012
  35. Limbert May 4, 2012
  36. LARRY J GREEN September 1, 2012
  37. Reymark Manay March 11, 2013
  38. Terry Nelson May 23, 2013
  39. Luis Felipe Andrade May 29, 2013
  40. padma September 21, 2013
  41. Arun October 15, 2013

Leave a Reply