Join tables from 2 different databases Problems

[level1]

I am new to asp.net although i had a good experience working with ASP.

I m connecting to 2 different databases, i.e Database A and Database B. Then Selecting fields from Database1.TableA trying to JOIN to Database2.TableB. I then bind to Datagrid.

I used the folowing code which works fine but only if for every field in Database1.TableA there will be an IDENTICAL joined filed in Database2.TableB.

Errors occur:
1)if there are null values within one table,
2)different datatypes between the tables,
3)and most importantly if there are redundant values existing in one table but not the other.

I dont know if the following code is the best way to do it but all im trying is to get a bloody join between two databases and display the result to a Datagrid. I know in ASP things were a lot simpler by looping through recordsets using return values from on query to another and so on creating as many connections as you like. This is excactly what i need to achieve... Anyway code as folows PLEASE HELP:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Dataset1 As New DataSet
Dim Dataset2 As New DataSet
Dim Datatable As New DataTable("newdata")
Dim DataColumn1 As New DataColumn("firstname", GetType(String))
Dim DataColumn2 As New DataColumn("surname", GetType(String))
Dim DataColumn3 As New DataColumn("id", GetType(Integer))

Dim sqlConn As SqlConnection
Dim datAdapter As SqlDataAdapter
Dim i As Integer

Dataset1 = New DataSet
sqlconn = New SqlConnection("server=(local);database=MSc_Project_DB;Integrated Security=SSPI;Connect Timeout=3600")
datAdapter = New SqlDataAdapter("select * from tableA", sqlconn)
sqlconn.Open()
datAdapter.Fill(Dataset1, "TableA")

sqlconn.Close()

sqlconn = New SqlConnection("server=(local);database=MSc_Project_DB;Integrated Security=SSPI;Connect Timeout=3600")
datAdapter = New SqlDataAdapter("select * from tableB", sqlconn)
sqlconn.Open()
datAdapter.Fill(Dataset1, "TableB")
sqlconn.Close()

Dataset1.Relations.Add("TableA_TableB", Dataset1.Tables("TableA").Columns("id"), Dataset1.Tables("TableB").Columns("TableA_ID"))

Datatable.Columns.Add(DataColumn1)
Datatable.Columns.Add(DataColumn2)
Datatable.Columns.Add(DataColumn3)
Datatable.AcceptChanges()
Dataset2.Tables.Add(Datatable)

Dim dta As DataTable = Dataset2.Tables("newdata")
Dim dt_a As DataTable = Dataset1.Tables("tablea")

For i = 0 To Dataset1.Tables("tablea").Rows.Count - 1

Dim dr As DataRow = dta.NewRow
dr("firstname") = dt_a.Rows(i)("name")

Dim dr2() As DataRow = dt_a.Rows(i).GetChildRows("tablea_tableb")
dr("surname") = dr2(0)("surname")

dr("id") = dr2(0)("id")

Datatable.Rows.Add(dr)
dr = Nothing
dr2 = Nothing

Next

'DataGrid1 is the Control on the form
DataGrid1.DataSource = Dataset2.Tables("newdata")
DataGrid1.DataBind()
sqlconn.Close()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


Feel free to show me a different way if possible to do this modyfing my code completely if needed.

All i need to do is simply join 2 database tables accross different databases and put the result to a datagrid (in this case) or another data control if you think is simpler or better way of doing it.

p.s i used Local server in this example and some dummy field names i.e firstname, surname e.t.c but in real life scenaro this will not be the case...

 

[chiph]

In your SQL, you can do a cross-database join by specifying the server, database, owner, and table name:

SELECT A.CustomerName,
  B.InvoiceTotal
FROM [server1].[databasename1].[dbo].[CustomerTable] A
  LEFT OUTER JOIN [server2].[databasename2].[dbo].[InvoiceTable] B
    ON A.CustomerID = B.CustomerID

As far as I know, this is Microsoft SQL Server and MSDE specific (won't work on Oracle).

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[level1]

thats the deal i want to connect to oracle and SQL simultaneously.

 

[level1]

anyway i fixed that: see code below: unless somone has a quicker work arround without using the relationships add thing:


Dim Dataset1 As New DataSet
Dim Dataset2 As New DataSet
Dim Datatable As New DataTable("MyDataTable")
Dim DataColumn0 As New DataColumn("MACHINE_NAME", GetType(String))
Dim DataColumn1 As New DataColumn("MANUFACTURER", GetType(String))
Dim DataColumn2 As New DataColumn("SERIAL_NO", GetType(String))
Dim datAdapter As OracleDataAdapter

Dim i As Integer
Dataset1 = New DataSet
Dim oracleConn As OracleConnection = New OracleConnection
oracleConn.ConnectionString = "user id=xxx;data source=xxx;password=xxx"
oracleConn.Open()

CommandTextB = "SELECT * "
CommandTextB = CommandTextB & "FROM DIRECTORY D,
datAdapter = New OracleDataAdapter(CommandTextB, oracleConn)
datAdapter.Fill(Dataset1, "TableA")
oracleconn.Close()

oracleConn.ConnectionString = "user id=xxx;data source=xxx;password=xxx"
oracleConn.Open()


CommandTextA = "Select * "
CommandTextA = CommandTextA & "from BR_TABLE_SITE_PART BTSP "
datAdapter = New OracleDataAdapter(CommandTextA, oracleConn)
datAdapter.Fill(Dataset1, "TableB")
oracleconn.Close()


Dataset1.Relations.Add("TableA_TableB", Dataset1.Tables("TableA").Columns("MACHINE_NAME"), Dataset1.Tables("TableB").Columns("SERIAL_NO"), False)

Datatable.Columns.Add(DataColumn0)
Datatable.Columns.Add(DataColumn1)
Datatable.Columns.Add(DataColumn2)
'Datatable.Columns.Add(DataColumn3)
Datatable.AcceptChanges()
Dataset2.Tables.Add(Datatable)


Dim dt_a As DataTable = Dataset1.Tables("tablea")
Dim dta As DataTable = Dataset2.Tables("MyDataTable")

For i = 0 To dt_a.Rows.Count - 1

Dim dr As DataRow = dta.NewRow
'dr("OBJID") = dt_a.Rows(i)("OBJID")
dr("MACHINE_NAME") = dt_a.Rows(i)("MACHINE_NAME")
dr("MANUFACTURER") = dt_a.Rows(i)("MANUFACTURER")

Dim dr2() As DataRow = dt_a.Rows(i).GetChildRows("tablea_tableb")
Dim x = dr2.GetUpperBound(0)

If x > -1 Then
dr("SERIAL_NO") = dr2(0)("SERIAL_NO")
Else
dr("SERIAL_NO") = "---"

End If

Datatable.Rows.Add(dr)
dr = Nothing
dr2 = Nothing

Next

'DataGrid1 is the Control on the form
DataGrid1.DataSource = Dataset2.Tables("MyDataTable")
DataGrid1.DataBind()
oracleconn.Close()

 

[level1]

this example assumes oracle to oracle relationship. but works one oracle to sql and sql to oracle too