Click here to Skip to main content
14,691,047 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to join three table that is in different database and show the result in datagrid view.

First table contain
ID, Group_Id, Voucher, Bill, Vendor_Id, Amount

Second Table Contain
Group_Id, Group_Name

Third Table Contain
Vendor_Id, Vendor_Name, Vendor_Address

I want result like
ID, Group_Name, Voucher, Bill, Vendor_Name, Vendor_Address, Amount

And after joining I want to show these data in Datagridview in C#

My problem is, I am only able to find Inner Join with same database over the internet. But I cant find Inner Join of Tables with different database on same server.

What I have tried:

str1 = "select `ID`, `Group_ID`, `Voucher_No`, `Bill_No`, `Date`, `Vendor_Id`, `Amount` from BillTable where (Group_ID= ''+ @searchsys + '')";
                cmd1 = new OleDbCommand(str1, cn);

                cmd1.Parameters.Add(new OleDbParameter("@searchsys", Convert.ToString(groupidDBS)));


                cmd1.ExecuteNonQuery();
                da1 = new OleDbDataAdapter(cmd1);
                da1.Fill(dt1);

                OleDbDataReader reader4 = cmd1.ExecuteReader();
Posted
Updated 17-Jul-20 2:47am
v2

You have tagged this as Access but refer to
Quote:
different database on same server.
therefore I am going to assume it is some kind of SQL.

You can refer to a table by connecting to a specific database and just using its name e.g.
select [ID] from Table1
but you can also give it its "full name" - including the database name and the schema name e.g.
select [ID] from [database2].[dbo].Table1
If you are using Access and the table is in a different .accdb file then use Linked Tables (intro here[^] - but don't call the service!)

EDIT - also - avoid SQL Injection attacks (see SQL Injection | OWASP[^] ) by using Parameterised Queries (see OleDbCommand.Parameters Property (System.Data.OleDb) | Microsoft Docs[^] )
   
v3
Comments
Maciej Los 17-Jul-20 7:41am
   
5ed!
The Digigraphy 17-Jul-20 7:42am
   
Hello @CHill60. Sorry to create confusion. It is Access database/
CHill60 17-Jul-20 12:34pm
   
In that case I would use Linked tables personally - that way they will "look" as if they are in the same file
[EDIT]

The Digigraphy wrote:
My Database Location of First Table is- C:\Users\Digi\Source\Soft\BillData.accdb
And Second Database Location is
C:\Users\Digi\Source\Soft\Master\GroupList.accdb

Both these database are in different location. And I want to join them


You've got two ways to achieve that:
1) create one database and
a) link all tables from all databases
or
b) move (import) all tables from different databases into one
See: Import or link to data in another Access database - Access[^]
Then use this query:
SELECT t1.ID, t2.Group_Name, t1.Voucher, t1.Bill, t3.Vendor_Name, t3.Vendor_Address, t1.Amount
FROM FirstTable t1 
    INNER JOIN SecondTable t2 ON t1.Group_Id = t2.Group_Id
    INNER JOIN ThirdTable t3 ON t1.Vendor_Id = t3.Vendor_Id
--WHERE t1.Group_Id = ?

I'd reocmmend to use this way.

2) use IN clause[^]:
You'll need to modify the body of query this way:
SELECT t1.ID, t2.Group_Name, t1.Voucher, t1.Bill, t3.Vendor_Name, t3.Vendor_Address, t1.Amount
FROM FirstTable t1 --this database is initial database (defined in connection string)
    INNER JOIN SecondTable IN 'C:\Users\Digi\Source\Soft\Master\GroupList.accdb' t2 ON t1.Group_Id = t2.Group_Id
    INNER JOIN ThirdTable IN 'C:\Users\Digi\Source\Soft\ThirdDatabase.accdb' t3 ON t1.Vendor_Id = t3.Vendor_Id
WHERE t1.Group_Id = ?

Above should also work, but it's more complicated.

So, changed code may looks like:
Dim firstFileName As String = "C:\Users\Digi\Source\Soft\BillData.accdb"
Dim secondFileName As String = "C:\Users\Digi\Source\Soft\Master\GroupList.accdb"
Dim thirdFileName As String = "C:\Users\Digi\Source\Soft\Master\ThirdDatabase.accdb"
Dim sConStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};;Persist Security Info=False;", firstFileName)
Dim dt As DataTable = New DataTable()

'1.
Using connection AS OleDbConnection = New OleDbConnection(sConStr)
    Dim sql As String = String.Format("SELECT t1.ID, t2.Group_Name, t1.Voucher, t1.Bill, t3.Vendor_Name, t3.Vendor_Address, t1.Amount{0}FROM FirstTable t1{0}    INNER JOIN SecondTable IN '{1}' t2 ON t1.Group_Id = t2.Group_Id{0}    INNER JOIN ThirdTable IN '{2}' t3 ON t1.Vendor_Id = t3.Vendor_Id{0}WHERE t1.Group_Id = ?", vbCrLf, secondFileName, thirdFileName)
    connection.Open()
    '2.
    Using command As OleDbCommand= New OleDbCommand(sql, connection)
        command.Parameters.Add(New OleDbParameter() With {.OleDbType = OleDbType.VarChar, .Value=2})
        '3.
        Dim reader As OleDbDataReader = command.ExecuteReader()
        '4.
        dt.Load(reader)
    End Using
End Using
'load data into dgv component
DataGridView1.DataSource = dt


Access connection strings - ConnectionStrings.com[^]
   
v4
Comments
The Digigraphy 17-Jul-20 7:58am
   
Hello Maciej Los, In this query how do I give the table connection?
Like Location of First Table's Database is different from Second Table's Database
Maciej Los 17-Jul-20 8:07am
   
Just replace "FirstTable", "SecondTable" and "ThirdTable" with the real names of tables.
The Digigraphy 17-Jul-20 8:48am
   
Hey, actually I'm trying to implement it in c#. I wrote following code as you suggest. But this required OleDbConnection for each Table. How do I implement this?

str = "Select t1.ID, t2.Group_Name, t1.Voucher_No, t1.Bill_No, t1.Date, t3.Vendor_Name FROM BillTable t1 INNERJOIN GroupName t2 t1.Group_ID = t2.Group_ID INNERJOIN VendorList t3 ON t1.Vendor_Id = t3.Vendor_Id ";
da = new OleDbDataAdapter(str, cn);
Maciej Los 17-Jul-20 8:57am
   
There must be only one connection! You should instantiate it before you create DataAdapter.
The Digigraphy 17-Jul-20 9:03am
   
How do I use one connection for different database?
Maciej Los 17-Jul-20 9:09am
   
Check updated answer.
The Digigraphy 17-Jul-20 9:16am
   
My Database Location of First Table is- C:\Users\Digi\Source\Soft\BillData.accdb
And Second Database Location is
C:\Users\Digi\Source\Soft\Master\GroupList.accdb

Both these database are in different location. And I want to join them
Maciej Los 18-Jul-20 2:59am
   
See updated answer.
The Digigraphy 18-Jul-20 4:09am
   
Hello Maciej, Thank for your Help. Now I'm getting Syntax error in FROM clause
The Digigraphy 18-Jul-20 10:10am
   
Till Now I tried this

DataTable dt = new DataTable();
DataSet ds = new DataSet();

ds.Tables.Add(dt);

string masterlocation = @"C:\Users\PC\source\repos\Soft\bin\Debug\MasterList.accdb";
string datalocation = @"C:\Users\PC\source\repos\Soft\bin\Debug\billdatabse.accdb";
string scontr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};;Persist Security Info=False;", datalocation);

OleDbConnection connection = new OleDbConnection(scontr);

string str = string.Format("Select a.ID, b.Group_Name, a.Voucher_No, a.Bill_No, a.Date, c.Vendor_Name, a.Amount FROM (BillTable AS a INNER JOIN GroupName IN '{1}' b ON a.Group_ID = b.Group_ID) INNER JOIN VendorList IN '{1}' c ON a.Vendor_Id = c.Vendor_Id ", datalocation, masterlocation);

connection.Open();
OleDbCommand command = new OleDbCommand(str, connection);

OleDbDataReader reader = command.ExecuteReader();

dt.Load(reader);

DataGridView1.DataSource = dt.DefaultView;

Still Getting same error

*I made few changes in database and make 3 database to 2, 1 is for all Bills and other for MasterList
Maciej Los 19-Jul-20 16:41pm
   
As i mentioned in my answer. You should use first method (all tables in one database).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900