ITSourceCode.com

We Exist to Provide 100% Free Source Code and Tutorials

Custom Search

03-Setting Up the Database Connection Between MySQL and Visual Basic

In this tutorial, I will teach you how to set up the database connection in the project. I’m going to use a module for setting up the connection of the database, declaring classes, variables and creating a method for “saving, retrieving, updating and deleting” the data in the MySQL Database. So that, it will reduce the bunch of codes and it will lessen your work.
I’m going to use the Visual Studio 2008 in creating this project.

Let’s begin:

Open the Visual Studio 2008 and create a new Windows Form Application. After that, you have to add the MySQL.Data.dll for your references so that, it won’t have encounter any errors when you are dealing with MySQL.

 

Now, go to the solution explorer and right click the file. After that, hit the “add reference” and the reference form will be pop-up. Go to browse panel, locate the MySQL.Data.dll and hit ok.
addreferenceform
After adding the reference, you have to create a Module. To do that, go to the solution explorer and right click the file. Hit the “add” and select the “Module” to create a new item. After that, the form of “add new item” will appear and name the Module “dbconnection”. Then, hit add.
moduleform
Now, in the Module, set a connection of the database, declare all the classes, variables and create a method in saving, retrieving, updating and deleting data in the database.

  1. 'SET YOUR IMPORTS
  2. Imports MySql.Data.MySqlClient
  3. Module dbconnection
  4. 'SET UP THE DATABASE CONNECTION
  5. Private Function mysqlcon() As MySqlConnection
  6. Return New MySqlConnection("server=localhost; user id=root; database=dbinformation")
  7. End Function
  8. Public con As MySqlConnection = mysqlcon()
  9. 'A SET OF COMMAND IN MYSQL
  10. Dim cmd As New MySqlCommand
  11. 'SET A CLASS THAT SERVE THE BRIDGE BETWEEN A DATASET AND DATABASE FOR SAVING AND RETRIEVING DATA.
  12. Dim da As New MySqlDataAdapter
  13. Private dt As New DataTable
  14. Public query As String
  15. Private result As Integer
  16.  
  17. #Region "CRUD"
  18. 'A SUB PRUCEDURE FOR INSERTING DATA IN THE DATABASE
  19. Public Sub mysqlCreate(ByVal query As String)
  20. Try
  21. 'OPENING THE CONNECTION
  22. con.Open()
  23. 'SET YOUR COMMANDS TO PROVIDE A TEXT-BASE INTERFACE INTO THE MYSQL DATABASE SERVER.
  24. 'AND ONCE IT'S CONNECTED, YOU CAN MAKE QUERY OR MANY OTHER OPERATION.
  25. With cmd
  26. .Connection = con
  27. .CommandText = query
  28. End With
  29. 'IT EXECUTE THE DATA
  30. result = cmd.ExecuteNonQuery
  31. 'IT NOTIFY IF THE DATA HAS EXECUTED OR NOT.
  32. If result > 0 Then
  33. MsgBox("The data has been saved in the database.")
  34. Else
  35. MsgBox("The data failed to save in the database.")
  36. End If
  37. 'CLOSING THE CONNECTION
  38. con.Close()
  39. Catch ex As Exception
  40. MsgBox(ex.Message)
  41. End Try
  42. End Sub
  43. 'A SUB PRUCEDURE FOR INSERTING,UPDATING AND DELETE DATA IN THE DATABASE AND IT NO MESSAGE WILL BE POPUP
  44. Public Sub mysqlCUDNoMessage(ByVal query As String)
  45. Try
  46. 'OPENING THE CONNECTION
  47. con.Open()
  48. 'SET YOUR COMMANDS TO PROVIDE A TEXT-BASE INTERFACE INTO THE MYSQL DATABASE SERVER.
  49. 'AND ONCE IT'S CONNECTED, YOU CAN MAKE QUERY OR MANY OTHER OPERATION.
  50. With cmd
  51. .Connection = con
  52. .CommandText = query
  53. End With
  54. 'IT EXECUTE THE DATA
  55. result = cmd.ExecuteNonQuery
  56. 'CLOSING THE CONNECTION
  57. con.Close()
  58. Catch ex As Exception
  59. MsgBox(ex.Message)
  60. End Try
  61. End Sub
  62. 'A SUB PRUCEDURE FOR RETRIEVING DATA IN THE DATABASE
  63. Public Sub mysqlRetrieve(ByVal query As String, ByVal obj As Object)
  64. Try
  65. 'OPENING THE CONNECTION
  66. con.Open()
  67. 'SET YOUR COMMANDS TO PROVIDE A TEXT-BASE INTERFACE INTO THE MYSQL DATABASE SERVER.
  68. 'AND ONCE IT'S CONNECTED, YOU CAN MAKE QUERY OR MANY OTHER OPERATION.
  69. With cmd
  70. .Connection = con
  71. .CommandText = query
  72. End With
  73. da = New MySqlDataAdapter
  74. 'SET THIS STORED PROCEDURE TO SELECT THE RECORD IN THE DATASOURCE
  75. da.SelectCommand = cmd
  76. dt = New DataTable
  77. da.Fill(dt)
  78. obj.datasource = dt
  79. 'CLOSING THE CONNECTION
  80. con.Close()
  81. da.Dispose()
  82. Catch ex As Exception
  83. MsgBox(ex.Message)
  84. End Try
  85. End Sub
  86. 'A SUB PRUCEDURE FOR UPDATING DATA IN THE DATABASE
  87. Public Sub mysqlUpdate(ByVal query As String)
  88. Try
  89. 'OPENING THE CONNECTION
  90. con.Open()
  91. 'SET YOUR COMMANDS TO PROVIDE A TEXT-BASE INTERFACE INTO THE MYSQL DATABASE SERVER.
  92. 'AND ONCE IT'S CONNECTED, YOU CAN MAKE QUERY OR MANY OTHER OPERATION.
  93. With cmd
  94. .Connection = con
  95. .CommandText = query
  96. End With
  97. 'IT EXECUTE THE DATA
  98. result = cmd.ExecuteNonQuery
  99. 'IT NOTIFY IF THE DATA HAS EXECUTED OR NOT.
  100. If result > 0 Then
  101. MsgBox("The data has been updated in the database.")
  102. Else
  103. MsgBox("The data failed to update in the database.")
  104. End If
  105. 'CLOSING THE CONNECTION
  106. con.Close()
  107. Catch ex As Exception
  108. MsgBox(ex.Message)
  109. End Try
  110. End Sub
  111. 'A SUB PRUCEDURE FOR DELETING DATA IN THE DATABASE
  112. Public Sub mysqlDelete(ByVal query As String)
  113. Try
  114. 'OPENING THE CONNECTION
  115. con.Open()
  116. 'SET YOUR COMMANDS TO PROVIDE A TEXT-BASE INTERFACE INTO THE MYSQL DATABASE SERVER.
  117. 'AND ONCE IT'S CONNECTED, YOU CAN MAKE QUERY OR MANY OTHER OPERATION.
  118. With cmd
  119. .Connection = con
  120. .CommandText = query
  121. End With
  122. 'IT EXECUTE THE DATA
  123. result = cmd.ExecuteNonQuery
  124. 'IT NOTIFY IF THE DATA HAS EXECUTED OR NOT.
  125. If result > 0 Then
  126. MsgBox("The data has been deleted in the database.")
  127. Else
  128. MsgBox("The data failed to delete in the database.")
  129. End If
  130. 'CLOSING THE CONNECTION
  131. con.Close()
  132. Catch ex As Exception
  133. MsgBox(ex.Message)
  134. End Try
  135. End Sub
  136. #End Region
  137. End Module
Facebook Comments

Check Your Domain Ranking

Leave a Reply

Your email address will not be published. Required fields are marked *

ITSourceCode.com © 2016 Frontier Theme

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!