Joining 2 Tables from 2 Different Databases

  • Thread starter Thread starter John Smith
  • Start date Start date
J

John Smith

How do I create a join between these two tables and populate it into a
datagrid? I am a newbie to ASP.NET and I need to figure out how to create a
JOIN between two tables which belong in 2 different databases. I am using
MS Access 2000 for both databases and my development application is Visual
Studio.NET 2003.

I have created two OleDbConnections and two OleDbDataAdapters. I also, have
created a DataSet which holds both data adapters and built the relationship
between the two, but I don't know how to populate the data into the datagrid
(assuming that I setup my relationship correctly) Any code Samples or Help
would be greatly appreciated.

My Sample Join Tables:
 
Call DataAdapter.Fill for each of the tables. Once the datarelations are
set(it appears they are) then you set the datasource to the dataset name and
then call datagrid.DataBind();
 
Here is what I have for my code behind:

----------------------------------------------------------------------------
-------------------------------
Public Class _1
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.OleDbSelectCommand1 = New System.Data.OleDb.OleDbCommand
Me.DbConnMFG = New System.Data.OleDb.OleDbConnection
Me.daCompanies = New System.Data.OleDb.OleDbDataAdapter
Me.OleDbSelectCommand2 = New System.Data.OleDb.OleDbCommand
Me.DbConnPROJ = New System.Data.OleDb.OleDbConnection
Me.daUsers = New System.Data.OleDb.OleDbDataAdapter
Me.DsUserInformation1 = New mfg.dsUserInformation
CType(Me.DsUserInformation1,
System.ComponentModel.ISupportInitialize).BeginInit()
'
'OleDbSelectCommand1
'
Me.OleDbSelectCommand1.CommandText = "SELECT CompanyID, CompanyName,
WebsiteURL FROM tCompanies"
Me.OleDbSelectCommand1.Connection = Me.DbConnMFG
'
'DbConnMFG
'
Me.DbConnMFG.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=1;Data
Source=""C:\Inetpub\wwwroot\xxx\database\CompanyInfo.mdb"";Jet OLEDB:En" & _
"gine Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System
database=;Jet OL" & _
"EDB:SFP=False;persist security info=False;Extended Properties=;Mode=Share
Deny N" & _
"one;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System
Database=False;Jet " & _
"OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without
Replica Repai" & _
"r=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1"
'
'daCompanies
'
Me.daCompanies.SelectCommand = Me.OleDbSelectCommand1
Me.daCompanies.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "tCompanies", New
System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMap
ping("CompanyID", "CompanyID"), New
System.Data.Common.DataColumnMapping("CompanyName", "CompanyName"), New
System.Data.Common.DataColumnMapping("WebsiteURL", "WebsiteURL")})})
'
'OleDbSelectCommand2
'
Me.OleDbSelectCommand2.CommandText = "SELECT CompanyID, UserFirstName,
UserID, UserLastName, UserMI, Username, UserPass" & _
"word FROM tUsers"
Me.OleDbSelectCommand2.Connection = Me.DbConnPROJ
'
'DbConnPROJ
'
Me.DbConnPROJ.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=1;Data
Source=""C:\Inetpub\wwwroot\xxx\database\UserInfo.mdb"";Jet" & _
" OLEDB:Engine Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet
OLEDB:System databas" & _
"e=;Jet OLEDB:SFP=False;persist security info=False;Extended
Properties=;Mode=Sha" & _
"re Deny None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System
Database=F" & _
"alse;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact
Without Repl" & _
"ica Repair=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1"
'
'daUsers
'
Me.daUsers.SelectCommand = Me.OleDbSelectCommand2
Me.daUsers.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "tUsers", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("CompanyID", "CompanyID"), New
System.Data.Common.DataColumnMapping("UserFirstName", "UserFirstName"), New
System.Data.Common.DataColumnMapping("UserID", "UserID"), New
System.Data.Common.DataColumnMapping("UserLastName", "UserLastName"), New
System.Data.Common.DataColumnMapping("UserMI", "UserMI"), New
System.Data.Common.DataColumnMapping("Username", "Username"), New
System.Data.Common.DataColumnMapping("UserPassword", "UserPassword")})})
'
'DsUserInformation1
'
Me.DsUserInformation1.DataSetName = "dsUserInformation"
Me.DsUserInformation1.Locale = New
System.Globalization.CultureInfo("en-US")
CType(Me.DsUserInformation1,
System.ComponentModel.ISupportInitialize).EndInit()

End Sub
Protected WithEvents OleDbSelectCommand1 As System.Data.OleDb.OleDbCommand
Protected WithEvents DbConnMFG As System.Data.OleDb.OleDbConnection
Protected WithEvents daCompanies As System.Data.OleDb.OleDbDataAdapter
Protected WithEvents OleDbSelectCommand2 As System.Data.OleDb.OleDbCommand
Protected WithEvents DbConnPROJ As System.Data.OleDb.OleDbConnection
Protected WithEvents daUsers As System.Data.OleDb.OleDbDataAdapter
Protected WithEvents DsUserInformation1 As mfg.dsUserInformation
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid

'NOTE: The following placeholder declaration is required by the Web Form
Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
DsUserInformation1.EnforceConstraints = False
daCompanies.Fill(DsUserInformation1)
daUsers.Fill(DsUserInformation1)
DsUserInformation1.EnforceConstraints = True

DataGrid1.DataSource = DsUserInformation1
DataGrid1.DataBind()
End Sub

End Class
----------------------------------------------------------------------------
-------------------------------

But the only data that shows up is the data from:
----------------------------------------------
tUser: (UserInfo.mdb)
----------------------------------------------
UserID
UserFullName
CompanyID

I'm not sure how to get the Company Information to show up with this table
information.

Thanx for your help.
 
I found my solution, an article that explains how to do a DataSet Join.
This helped me accomplish a JOIN on 2 DataTables. It works like a charm.
Thanx to everybody who tried to help me. The link to the article is below.

I had to change the sample code a bit for VS.NET 2003 (VB). SetDataBinding
did not exist within my vs.net 2003.

I replaced: DataGrid1.SetDataBinding(ds, "EmpDept")

With: DataGrid1.DataSource = ds.Tables("EmpDept").DefaultView
DataGrid1.DataBind()

HOW TO: Implement a DataSet JOIN Helper Class in Visual Basic .NET -
http://support.microsoft.com/default.aspx?scid=kb;en-us;325688


Thanx
 
Back
Top