Inner Join using VB programming – Inner join is used to combine two or more tables in a single output table in database programming. Inner join is very helpful when a join is required to merge some table in SQL query. In this tutorial we are going to join two columns of MS Access with VB programming.
Inner Join using VB programming
Before using Inner Join using VB programming, it is assumed that you have connected with your database file of MS Access. If you don’t know how to connect MS Access database file, you can visit the tutorial to connect MS Access database file using VB programming.
Connect to Database with VB Programming
Create a module.vb and rename it with “connectDatabase”, copy and paste the following code into the module
Imports System.Data.OleDb Module connectDatabase Public accessConnection As New OleDbConnection Public accessCommand As New OleDb.OleDbCommand Public accessAdopter As New OleDbDataAdapter Public accessDataReader As OleDbDataReader Public strSQL As String Sub connect() Try If accessConnection.State = ConnectionState.Closed Then accessConnection.ConnectionString = "Provider=microsoft.ace.oledb.12.0; data source=" & Application.StartupPath & "\Database1.accdb" & " ;" accessConnection.Open() Form1.Text = " Connected" Else Form1.Text = "Connection Is already Opend" End If Catch ex As Exception Form1.Text = ex.Message.ToString End Try End Sub End Module
After it, copy the following code, and paste it at form load, to connect with database
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load connectDatabase.connect() End Sub
Next First of all for Inner Join using VB programming, we require two table with same table of ITEMID. The ITEMID column will help us to identify the matching contents between two tables. For the Inner Join I have the following table.
Table1 for Inner Join: The table1 has two columns which are ITEMID and ITEMNAME. The ITEMID has unique records to identify the ITEMNAME.
Table2 for Inner Join: The table2 has three columns with ITEMID (same as Table1, for relation between two tables), PRICE, and QTY columns.
Understanding with Inner Join
The following is strSQL which is used to join above two tables, the strSQL is define in next table which its parameters. The strSQL is
strSQL = “select Table2.ITEMID, Table1.ITEMNAME, Table2.PRICE, Table2.QTY From Table1 INNER Join Table2 ON Table1.ITEMID = Table2.ITEMID”
|select||This is command syntax; you type as it is.|
|Table2.ITEMID, Table1.ITEMNAME, Table2.PRICE, Table2.QTY||Type all columns names with their table name using dot.|
|From||This is command syntax; you type as it is.|
|Table1||You can type here Table1 or Table2, no problem|
|INNER Join||This is command syntax; you type as it is.|
|Table2||You can type here Table2 or Table1, no problem|
|ON||This is command syntax; you type as it is.|
|Table1.ITEMID = Table2.ITEMID||This is the criteria, on which you are fetching data from database|
strSQL = "select Table2.ITEMID, Table1.ITEMNAME, Table2.PRICE, Table2.QTY From Table1 INNER Join Table2 ON Table1.ITEMID = Table2.ITEMID" accessCommand.CommandText = strSQL accessCommand.Connection = accessConnection accessAdopter.SelectCommand = accessCommand accessDataReader = accessCommand.ExecuteReader ListView1.Items.Clear() While (accessDataReader.Read()) With ListView1.Items.Add(accessDataReader("ITEMID")) 'Table2.ITEMID .subitems.add(accessDataReader("ITEMNAME")) 'Table1.ITEMNAME .subitems.add(accessDataReader("PRICE")) 'Table2.PRICE .subitems.add(accessDataReader("QTY")) ' Table2.QTY End With End While
Result of Inner Join
Inner Join using VB programming – after the code running of Inner Join using VB programming, the output Join operation on table1 and table2 is displayed.