Select Multiple Columns from Multiple Tables in mySQL using VB.Net

0
69

Select Multiple Columns from Multiple Tables in mySQL using VB.Net

Select Multiple Columns from Multiple Tables in mySQL using VB.Net.

In this tutorial, you will learn how to select multiple columns from multiple tables from mySQL to your Visual Basic Project.

First, we need to create our database named databasemultiple and we will add two tables with the same primary keys.

For the 1st table, here’s our line of code:

  1. CREATE TABLE IF NOT EXISTS firstname (
  2. `FirstName` VARCHAR(255) NOT NULL,
  3. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

For the 2nd table, here’s our line of code:

  1. CREATE TABLE IF NOT EXISTS middlename (
  2. `MiddleName` VARCHAR(255) NOT NULL,
  3. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Now, we have our firstname table and the middlename table. We should add two row values in each table.

Here’s the code to add two row values in our firstname table:

  1. INSERT INTO `firstname`
  2. (`FirstName`)
  3. ('John'),
  4. ('Mark');

Here’s the code to add two row values in our middlename table:

  1. INSERT INTO `middlename`
  2. (`MiddleName`)
  3. ('Jose'),
  4. ('Paul');

Assuming that we have two tables with different values but with the same primary keys or you can also use unique keys. Take note that I did not included the “ID” column in adding rows for both tables because the said column is in its auto_increment state.

Let’s create now a new project in Visual Basic, and then drag a datagridview. Set the datagridview’s AutoSizeColumnsMode to Fill.

Also, you need to have a Mysql.Data.dll in order for you to access the mySQL methods and parameters to be used in creating a mySQL code in Visual Basic Project.

Double tap your Form1 designer and try to insert this line of code:

  1. Imports MySql.Data.MySqlClient
  2.  
  3. Public Class Form1
  4.  
  5. Dim conn As MySqlConnection
  6. Dim cmd As MySqlCommand
  7. Dim da As MySqlDataAdapter
  8. Dim dt As DataTable
  9. Dim sql As String
  10.  
  11. Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
  12. Try
  13. conn = New MySqlConnection
  14. conn.ConnectionString = "server=localhost; userid=root; password=; database=databasemultiple;"
  15. conn.Open()
  16. sql = "SELECT firstname.Firstname, middlename.Middlename FROM firstname, middlename WHERE firstname.ID = middlename.ID;"
  17. cmd = New MySqlCommand(sql, conn)
  18. da = New MySqlDataAdapter
  19. dt = New DataTable
  20. da.SelectCommand = cmd
  21. da.Fill(dt)
  22. DataGridView1.DataSource = dt
  23. Catch ex As MySqlException
  24. MsgBox(ex.Message)
  25. Finally
  26. conn.Close()
  27. da.Dispose()
  28. End Try
  29. End Sub
  30. End Class

Reload your Visual Basic and see what happens. Here’s my result:

Select Multiple Columns from Multiple Tables in mySQL using VB.Net

As you can see, we have called two different columns from two different tables. The first word from “firstname.Firstname” represents your firstname table and the second word after the dot is your table’s column. You have to also call the two tables when you try to distinguish it from your “FROM” keyword.

Notice at the end part of WHERE keyword where we equalize the ID number of firstname and middlename table. This is to get the equal primary keys in two tables and let the mySQL merge the two values from different columns with the same primary or unique key.

Congratulations! You have learned today how to select multiple columns from multiple tables in mySQL using VB.Net.

For questions or any other concerns or thesis/capstone creation with documentation, you can contact me through the following:

E-Mail: [email protected]

Facebook: facebook.com/kirk.lavapiez

Contact No.: +639771069640

Ian Hero L. Lavapiez

BSIT Graduate

System Analyst and Developer

Related topic(s) that you may like:

 

Facebook Comments
(Visited 79 times, 1 visits today)

LEAVE A REPLY

Please enter your comment!
Please enter your name here