locked
Join tables from two datasources display results in gridview RRS feed

  • Question

  • User-278831139 posted

    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!

    Thursday, June 5, 2014 1:57 PM

Answers

  • User79986525 posted

    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();
                     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 6, 2014 2:57 AM

All replies

  • User1191505944 posted

    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

    Thursday, June 5, 2014 2:34 PM
  • User1918509225 posted

    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.

    Friday, June 6, 2014 2:50 AM
  • User79986525 posted

    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();
                     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 6, 2014 2:57 AM
  • User-278831139 posted

    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!

    Friday, June 13, 2014 11:10 AM