Have a Sql datasource named "sqldata" and a ODBC datasource named "CEdata" definded in web.config
"Sqldata" contains table "TimesheetHeader" with the fields: ID, Jobnum, Date
"CEdata" contains table "jcjob" with the fields: Jobnumber, Jobname
I want a gridview to list The ID (from sqldata.TimesheetHeader) , Date (from sqldata.TimesheetHeader) and jobname ( from the CEdata.jcjob datasource based on common "Jobnum" key)
I've tried with VS designer, but it seems it will only give me access to one datasource. I'm thinking perhaps there is some way in VB to join the two into one?
So, How can I join the two datasets together via the common "jobnum" key and report results?
This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software
or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet,
and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.
Var commanDate =(from d1 in Dt1.AsEnumerable()
from d2 in Dt2.AsEnumerable()
where d1["JobNum"]==d2["JobNumber"]
select new {
JobName=d2["JobName"],
JobNum=d1["JobNum"]
} ).ToList();
Gridview1.DataSource=commanDate ;
Gridview1.DataBind();
Thanks for all your help everyone. FYI, after much research, turns out I can add through Managment Studio to the sql Database a "Remote Server" which can be an ODBC database. I can then create a view and join the databases by refering to the dataset via:
"Remoteservername...dataset". Works fantastic!
None
0 Points
15 Posts
Join tables from two datasources display results in gridview
Jun 05, 2014 01:57 PM|UCBearcat|LINK
Have a Sql datasource named "sqldata" and a ODBC datasource named "CEdata" definded in web.config
"Sqldata" contains table "TimesheetHeader" with the fields: ID, Jobnum, Date
"CEdata" contains table "jcjob" with the fields: Jobnumber, Jobname
I want a gridview to list The ID (from sqldata.TimesheetHeader) , Date (from sqldata.TimesheetHeader) and jobname ( from the CEdata.jcjob datasource based on common "Jobnum" key)
I've tried with VS designer, but it seems it will only give me access to one datasource. I'm thinking perhaps there is some way in VB to join the two into one?
So, How can I join the two datasets together via the common "jobnum" key and report results?
Thanks in advance for your help!
Jointablesfromtwodatasources
Participant
1124 Points
408 Posts
Re: Join tables from two datasources display results in gridview
Jun 05, 2014 02:34 PM|syed.iddi|LINK
You can get the two data into a datatable and use linq to join the datatable. Bind the result datasource in the gridview
some linq examples
http://msdn.microsoft.com/en-US/vstudio/bb688086.aspx
Jointablesfromtwodatasources
All-Star
16806 Points
2777 Posts
Re: Join tables from two datasources display results in gridview
Jun 06, 2014 02:50 AM|Kevin Shen - MSFT|LINK
Hi UCBearcat,
Based on my understanding,If you are using EF context ,so it does not support cross database queries. While if not ,you can refer to the links below:
http://stackoverflow.com/questions/352949/linq-across-multiple-databases
http://www.codeproject.com/Articles/23303/LinqToSQL-Query-Multiple-Databases-in-One-LINQ-Exp
http://www.a2zmenu.com/Blogs/linq/Perform%20Cross%20Database%20LINQ%20join%20Operation.aspx
Best Regards,
Kevin Shen.
This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.
Jointablesfromtwodatasources
Contributor
2006 Points
725 Posts
Re: Join tables from two datasources display results in gridview
Jun 06, 2014 02:57 AM|sanjayverma_mca|LINK
Hi ,
Make use of Linq
Your query will be like this
Var commanDate =(from d1 in Dt1.AsEnumerable() from d2 in Dt2.AsEnumerable() where d1["JobNum"]==d2["JobNumber"] select new { JobName=d2["JobName"], JobNum=d1["JobNum"] } ).ToList(); Gridview1.DataSource=commanDate ; Gridview1.DataBind();Jointablesfromtwodatasources
None
0 Points
15 Posts
Re: Join tables from two datasources display results in gridview
Jun 13, 2014 11:10 AM|UCBearcat|LINK
Thanks for all your help everyone. FYI, after much research, turns out I can add through Managment Studio to the sql Database a "Remote Server" which can be an ODBC database. I can then create a view and join the databases by refering to the dataset via: "Remoteservername...dataset". Works fantastic!
Jointablesfromtwodatasources