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:
[mysql]
CREATE TABLE IF NOT EXISTS firstname (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`FirstName` VARCHAR(255) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
[/mysql]
For the 2nd table, here’s our line of code:
[mysql]
CREATE TABLE IF NOT EXISTS middlename (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`MiddleName` VARCHAR(255) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
[/mysql]
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:
[mysql]
INSERT INTO `firstname`
(`FirstName`)
VALUES
(‘John’),
(‘Mark’);
[/mysql]
Here’s the code to add two row values in our middlename table:
[mysql]
INSERT INTO `middlename`
(`MiddleName`)
VALUES
(‘Jose’),
(‘Paul’);
[/mysql]
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:
[vbnet]
Imports MySql.Data.MySqlClient
Public Class Form1
Dim conn As MySqlConnection
Dim cmd As MySqlCommand
Dim da As MySqlDataAdapter
Dim dt As DataTable
Dim sql As String
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Try
conn = New MySqlConnection
conn.ConnectionString = “server=localhost; userid=root; password=; database=databasemultiple;”
conn.Open()
sql = “SELECT firstname.Firstname, middlename.Middlename FROM firstname, middlename WHERE firstname.ID = middlename.ID;”
cmd = New MySqlCommand(sql, conn)
da = New MySqlDataAdapter
dt = New DataTable
da.SelectCommand = cmd
da.Fill(dt)
DataGridView1.DataSource = dt
Catch ex As MySqlException
MsgBox(ex.Message)
Finally
conn.Close()
da.Dispose()
End Try
End Sub
End Class
[/vbnet]
Reload your Visual Basic and see what happens. Here’s my result:
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: