connecting two datatables within a dataset and forming relation HELP
-
I have an access Table with vendor_num and vendor_name fields. I have an oracle Table which has a vendor_num field and other fields of details. Initially, my thought was to pull the access table into a dataset and then pull the oracle table into the dataset, form a datarelation between the two, and use GetChildRows to pull the other details from the oracle table I pulled in. however... When I tried to do this I got an error b/c not all data in the child table (oracle table) had a parent... Can someone point me in the right direction into either fixing this train of thought or a new method altogether, please? Thanks Cavall Dim conn As New OdbcConnection("Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Documents and Settings\mwaters2\My Documents\VQM\testVQM.mdb;Uid=Admin;Pwd=;") Dim da As New OdbcDataAdapter("SELECT Vendor_Num FROM Top50_test", conn) Dim ds As New DataSet da.Fill(ds, "Top50") DataGrid1.DataSource = ds.Tables("Top50") DataGrid1.DataBind() Dim conn2 As New OleDbConnection("Provider=msdaora;Data Source=VQM;User Id=***;Password=***;") Dim childSQL As String childSQL = "" & _ "SELECT vendor.vendor_num, restratificationhistory.vend_accy " & _ "FROM vendor, restratificationhistory " & _ "WHERE vendor.vendor_num = restratificationhistory.vendor_num " & _ "AND restratificationhistory.dt_tm_added Between to_date('1/1/04','MM/DD/YY') AND to_date('11/10/04','MM/DD/YY')" Dim da2 As New OleDbDataAdapter(childSQL, conn2) da2.Fill(ds, "Details") Dim parentCol As DataColumn = ds.Tables("Top50").Columns("Vendor_Num") Dim childCol As DataColumn = ds.Tables("Details").Columns("Vendor_Num") Dim dr As New DataRelation("VendorToDetails", parentCol, childCol) ds.Relations.Add(dr) "Nothing is at last sacred, but the integrity of your own mind." "What lies behind us and what lies before us are nothing compared to what lies within us." - Ralph Waldo Emerson