Home » Asp.net

Gridview and Inner Join Query

I'm am having an issue having a column show up in GridView.  I have two Access tables, Customers and Contacts.  The Customers table has a primary key of CustomerID, as well as having all the customer information.  The Contacts table has a primary key of ContactID, a foreign key of CustomerID with a relationship set up between these two tables, as well as the rest of the contact's information.  In the GridView, I'd like to show the contact details, but I would also like to show CustomerName (a value in the Customer table).  I've created a dataset with these two tables and I've added a query with the inner join included under the ContactsTableAdapter.  When I execute this query in the query builder, it looks like it works great.  When I attempt to use this as an object data source for the gridview, all I get are the columns from the Clients table.  Any ideas?  I'm a little new to ASP.NET, so I may have missed something simple. 


2 Answers Found


Answer 1

I have solved my problem, but still not exactly sure as to why the original way I had tried did not work.  Basically what I found was that I needed to add the Customers table to the Contacts table adapter main query.  If I try to do the inner join on an added query without doing this, I guess the schema does not match (which it was giving me a warning message, but since the project was building ok, I had ignored).


Answer 2

I have same problem , please tell how you solve ?  



How do I copy a field data from one gridview to another field data from another gridview joined by a primary key.


Me.PLANTILLADataGridView.CurrentRow.Cells(4).Value = Me.FIRSTDAYFILEDataGridView.CurrentRow.Cells(3).Value

However if I use the for/next loop the plantilladatagridview only copies one value from the firstdayfiledatagridview which is the first record it encounter.  I need to copy since I have already several thousand records.  The database structure was designed by another person

Some of our Excel 2010 users ask why use a PowerPivot relationship if one could visual design a regular T-SQL query join (in the t-sql query designer in powerpivot)?  when use one versus the other?


I have a feature with three custom lists and a document library:

1) a list to store regions

2) a list to store markets with a lookup column to select a region

3) a list to store customers with a lookup column to select a market

4) a library to store customers documents with a lookup column to select the customer

we asked to me to display on the same view customers' data with their documents group by region and by markets. It seems that the same way is the using of aggregate datasource but when i see that it perform the join into the xslt, i think it can be dangerous for the scalability.

Is it possible to perform a cross list query with putting a join between the fields as we can do in SQL ?

In a custom view i must display customers with expired documents, with aggregate datasource it seems that WSS will retrieve all customers, all expired documents and after do the join. It is dammage do load 500 customers if there is only one whihc have expired documents....

Thanks for your help..

My SQL is letting me down today, and so I was hoping someone could help me:

Table A - integer column aID 

Table B - integer column bID

Table C - 2 columns - aID and bID  providing a many to many relationship of A and B

Basically I now want to query for every item A, all the bIDs which are not related to it through table C.

So if A contains IDs: 1 and 2

B contains IDs: 4,5,6

and C contains (aID, bID) pairs (1,4), (1,5), (2,4)

I would want the query to return (aID, bID) pairs: (1,6), (2,5), (2,6)

Hope that makes sense, any help most appreciated!


Hi, i want to use a condition in my query just if a variable is not null. Query includes joins so i'm not able to add something like query.Where(q=>q.TaskName == "theTask").

Basically i want to disable two queries below and write just one. Use join conditionally and/or use filter conditionally.


string theTaskName = "";

   if (taskName == "")


    var query1 = from taskList in entity.Tasks

        where taskList.TaskDate > "11.11.1111"




    var query2 = from tasklist in entity.Tasks

        join taskNames in entity.TaskNames on tasklist.ID equals taskNames.ID

        where taskList.TaskDate > "11.11.1111" && taskNames.TaskName == theTaskName




I have a few tables that need to be joined as in the tsql below:

SELECT     dbo.MealType.mealTypeName, dbo.menuCategories.menuCategoryName, dbo.MenuItems.MenuItemName, dbo.MenuItems.TodayMenu,
FROM         dbo.MenuItems INNER JOIN
                      dbo.menuCategories ON dbo.MenuItems.MenuCategoryID = dbo.menuCategories.MenuCategoryID INNER JOIN
                      dbo.MealType ON dbo.MenuItems.MealTypeID = dbo.MealType.mealTypeID AND dbo.menuCategories.MealTypeID = dbo.MealType.mealTypeID
WHERE     (dbo.MealType.mealTypeName = 'lunch') AND (dbo.MenuItems.TodayMenu = 1)

I can figure out most of the LINQ, but can not see how to express the AND condition. 

What I 9incorectly)  have is: 




myMenu = (from m in db.MenuItems



join c in db.menuCategories on m.MenuCategoryID equals c.MenuCategoryID



join t in db.MealTypes on




equals t.mealTypeID




((t.mealTypeID == MealTypeID)

&& (m.TodayMenu ==














Can a LINQ guru please help and explain?


Thank you,




I'm writing a join like statement with LINQ to SQL but I'm getting an exception. The object DBEntities.ArgumentValues is a      ObjectContext->ObjectQuery  (ie a table)

Here's the code...


string[] testValues = newstring[2];<br/>
      testValues[0] = "1";<br/>
      testValues[1] = "2";<br/>
      var results2 = from argValue in DBEntities.ArgumentValues<br/>
              from testValue in testValues<br/>
              where argValue.Value == testValue<br/>
              selectnew {argValue};


the exception is....

Unable to create a constant value of type 'Closure type'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.


A second example uses explicite joins like so...



string[] testValues = newstring[2];<br/>
      testValues[0] = "1";<br/>
      testValues[1] = "2";<br/>
    var results2 = from argValue in DBEntities.ArgumentValues
              join testValue in testValues on
              argValue.Value equals testValue
              select argValue;


gives this similar exception

Unable to create a constant value of type 'System.Collections.Generic.IEnumerable`1'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.


The only solution I can find is to convert the DBEntities.ArgumentValues databse table into a .ToList() inside the query. But I can't help but feel that this is not the most efficient syntax. Also, I see examples all over the web of people performing joins on ADO entities in queries like this and not getting errors.

Can someone identify what is wrong with my query?





Hi All,


   I've a requirement which I'm explaining below.

I've two tables.


 First Table Structure is



TypeSeq           TypeCode          Description

1                         A                     Accessories

2                         B                     Battery

3                         C                     Computer                          --------> (BLOCK 1)

4                         D                     Drives

...                       ....                     ....


Second Table is:



and the structure of it is:

Month       Customer    ItemTypeSeq        SalesDollar

201006       1234                1                            100.00

201006       1234                2                            250.00

201006       2222                1                            500.00

201005       1111                2                            400.00                       --------> (BLOCK 2)

201005       1111                3                            700.00

201005       1111                4                            800.00


When we Inner Join these Two Tables...We'll get this result      (--------> (BLOCK #3))

Month       Customer    ItemTypeSeq        SalesDollar    TypeCode          Description

201006       1234                1                            100.00              A                   Accessories

201006       1234                2                            250.00              B                   Battery

201006       2222                1                            500.00              A                   Accessories  

201005       1111                2                            400.00              B                   Battery

201005       1111                3                            700.00              C                   Computer

201005       1111                4                            800.00              D                   Drives 

But I want something similar to the below OutPut       (--------> (BLOCK #4))

Month       Customer    TypeSeq                   SalesDollar    TypeCode          Description

201006       1234                1                            100.00              A                   Accessories

201006       1234                2                            250.00              B                   Battery

201006       1234                3                                0.00             C                   Computer

201006       1234                4                                0.00             D                   Drives 

201006       2222                1                            500.00               A                   Accessories 

201006       2222                2                                0.00              B                   Battery

201006       2222                3                                0.00              C                   Computer

201006       2222                4                                0.00              D                   Drives

201005       1111                1                               0.00              A                  Accessories 

201005       1111                2                            400.00              B                   Battery

201005       1111                3                            700.00              C                   Computer

201005       1111                4                            800.00              D                   Drives 

To get this I've written a Left Outer Join statement.

Select MonthSeq,
       ISNULL(SalesDollar,0.00) As SalesDollar

From   ItemTypeTable A      

Left Join

  AggregateSalesTable B


A.TypeSeq = B.ItemTypeSeq


But It is giving the below result. (--------> (BLOCK #5))

Month       Customer    TypeSeq                   SalesDollar    TypeCode          Description

201006       1234                1                            100.00              A                   Accessories

201006       1234                2                            250.00              B                   Battery


NULL            NULL                3                                0.00             C                   Computer

NULL            NULL                4                                0.00             D                   Drives 


201006       2222                1                            500.00               A                   Accessories 

NULL           NULL                2                                0.00              B                   Battery

NULL           NULL                3                                0.00              C                   Computer

NULL           NULL                4                                0.00              D                   Drives

NULL           NULL                1                               0.00              A                  Accessories 

201005       1111                2                            400.00              B                   Battery

201005       1111                3                            700.00              C                   Computer

201005       1111                4                            800.00              D                   Drives 

How could I remove those Nulls and  Get the Desired Output I've shown in (--------> (BLOCK #4)).

    When I join 2 tables (--------> (BLOCK 1) and --------> (BLOCK 2)), I need to get --------> (BLOCK 4)..But I'm getting (--------> (BLOCK 5)).

   is there any way we can achieve it?



Good Morning, I have a question about outer joins, this is how my query works and I am wondering you have any ideas how to help. I have a list of sales ledger lines, the item requested is then linked to the inventory table through a direct link. Then using the an additional field in the inventory table (proof: this is document used to produce the images) link that to the production order table. Now we may produce this Proof several times or havent as of yet. Now I need to be able to restict my outer join to the most recent order or return blank if there isnt one. I have tried MAX(P1.ProdID) [ProdID is the main production order reference]. Now that works but when I add the order status it then ignores the MAX and produces several results. Does anybody have any suggestions:

SL1.SalesID AS 'Sales Ledegr ID',
SL1.ItemID AS 'Item Nb',
I1.OCS_ArtworkNb AS 'Proof Nb',
MAX(P1.ProdID) AS 'Production ID'

SalesLine SL1 INNER JOIN InventTable I1
    ON (SL1.ItemID = I1.ItemID and I1.DataAreaID = 001)
    LEFT OUTER JOIN ProdTable P1
    ON (I1.OCS_ArtworkNb = P1.ItemID
    AND P1.DataAreaID = 003)

SL1.DataAreaID = 003
AND SL1.SalesStatus = 1
AND SL1.SalesID = '003SA00001063'




I'm trying to retrieve two entities and need to join them into one in my silverlight application that's using the data service.

I know I can't join in the original query.

I've read that it's not adivsable to query both and then join them in my application (I'm not sure why - but I read it in the answer to this post).

If I try to retrieve one the first entity, and then retrieve the related entity in a foreach(first entity) loop, the asynchronous nature of the queries seems to cause duplicates or missing records in the returned set of data.

What are the other options? The entities I'm returning are joined by a foreign-key relationship - however, that relationship doesn't look like it has any values in it when I query either one (one one entity it's null, on the other it has a count = 0).

Can someone give me some suggestions on how to architect this process? I know it's simple, but I'm making my first foray into this arena.

BTW, these are poco classes exposed in my data service.


Hi all,

          I am new in CRM & trying to create a custom Report in Visual Studio & trying to use it in CRM. I with to show the Opportunity & the related product details in a report. For that I am using a query like this

Select CRMAF_Opportunity.New_OpportunityNumber, CRMAF_Opportunity.Name,
CRMAF_Opportunity.new_keycontactidName, CRMAF_Opportunity.AccountIdName,
OpportunityProduct.PricePerUnit, OpportunityProduct.Quantity,
OpportunityProduct.New_Cost as [Special Bid Cost],
OpportunityProduct.New_SpecialBidCost as [Cost],
OpportunityProduct.ExtendedAmount, OpportunityProduct.New_GrossProfit,
from Opportunity AS CRMAF_Opportunity Inner join OpportunityProduct
on CRMAF_Opportunity.OpportunityId = OpportunityProduct.OpportunityId
Where CRMAF_Opportunity.OpportunityId = @CRM_OpportunityId;

But this is not working & the report shows a blank report.

What should I do to create such a report. Please suggest . Thanks in advance.

With Regards





I'm trying to implement query that will join more then 2 streams. However StreamInsight engine did not accept my query and thrown an exception.

My query is something like this:

var outputStream =
 from eventType1 in eventTypes1Stream
 from eventType2 in eventTypes2Stream
 from eventType3 in eventTypes3Stream
  selectnew OutputEventType
   Value1 = eventType1.Value1,
   Value2 = eventType2.Value2,
   Value3 = eventType3.Value3

And exception I received is: 

Join result has to be projected to an event type that doesn't contain nested type. The following join result expression is not supported: 'new <>f__AnonymousType0`2(eventType1 = eventType1, eventType2 = eventType2)'.

As a workaround for this case I first created query that joined first two streams ("eventTypes1Stream" and "eventTypes2Stream") and received "preOutputStream1" output stream and then another query that joined "preOutputStream1" with third stream "eventTypes3Stream".

For joining 4, 5, 6 ... streams such workaround will require to create a lot of pre-OutputStreams which is not very good from the performance point of view I suppose, and is not very good from the point of view amount of code I need to write.

So, can I implement join of multiple streams as single query?

    Thank You.


I was wodnering whether its possible to use joins for this weird query, the subquery as i understand isnt the best for performance.


		sm.CDRValue AS [Customer Value],
		r.CDRValue AS [Supplier Value]
FROM tblProfile c 	
		leftjoin tblCustomerCDRLookup sm 
		sm.PersonURN = c.PersonURN 
		AND sm.PriceMatchCode = (select vc.PriceMatchCode from tblPricePlan vc where vc.PlanName='VOIP')		
		leftjoin tblSupplierCDRLookup r
		on 	r.PersonURN = c.PersonURN 
		AND r.PriceMatchCode = (select vc.PriceMatchCode from tblPricePlan vc where vc.PlanName='VOIP EXTRA') 
		AND r.AddressURN = c.AddressURN		


I am using a LINQ query with multiple joins, the last join does not return any values even though values exist in the database. Below is my code.

when the query returns suiteNameTrg and SuiteTypeTrg are empty, all other values are returned correctly.

string suiteNameTrg = string.Empty;
            string suiteTypeTrg = string.Empty;
            using (DataClassesDataContext db = new DataClassesDataContext())
                    var productQuery = from assets in db.ASSETs
                    join relocatableUnits in db.RELOCATABLE_UNITs on assets.RUID equals relocatableUnits.RUID into assets_units
                    from relocatableUnits in assets_units.DefaultIfEmpty()
                   join build in db.BUILDINGs on assets.BUILDING_ID equals build.BUILDING_ID into assets_bins
                   from build in assets_bins.DefaultIfEmpty()
                   join test in db.TEST_SUITEs on assets.TEST_SUITE_ID equals test.TEST_SUITE_ID into test_bins
                   from test in test_bins.DefaultIfEmpty()
                    join testTrgt in db.TEST_SUITEs on assets.TARGET_TEST_SUITE_ID equals testTrgt.TEST_SUITE_ID into testTrgt_bins
                    from testTrgt in testTrgt_bins.DefaultIfEmpty()

                    select new

                                       suiteNameTrg = testTrgt.SUITE_NAME,
                                       suiteTypeTrg = testTrgt.SUITE_TYPE






Hello All,

I have a requirement where i need to query the data from two  sharepoint lists  on a same site by doing a join between those lists
and get the result into datatable.I am doing this using sharepoint object model.Is there any possibility to do this.

Any pointers in the right direction are greatly appreciated.

Thank you



I have been trying (with considerable agitation) to plug the results of my LINQ query into a datatable. I have meticulously inspected the interwebs like a wino inspects the sidewalk for change. I have followed a sickening number of links that have yet to provide me with a simple answer to my problem. I HAVE found quote a few smug and condescending people who seem to enjoy insulting their fellow programmers. But the MSDN side is as disgustingly vague and unhelpful as the rest of the internet. I find it outright bewildering that an answer to what seems a simple question should be so difficult to find.

The Original Data:

ApplesKey | Cost Of Apples | Color Of Apples
  1 |  $1  |  Red
  2 |  $2  |  Green
  3 |  $3  |  Yellow

OrangesKey | Cost Of Oranges | Color Of Oranges
  1  |  $20  |  Orange
  2  |  $21  |  Orange
  3  |  $22  |  Orange


Nothing complex.

The Query:

Dim Matches = _
From mRows In LinqMasterTable _
Join sRows In LinqSecondTable _
On mRows(ThePrimaryKey) Equals sRows(TheForignKey) _
Select mRows, sRows


Simple Enough, right?

Mouse Over "Matches" and you get Array Data a little something like this:

+ Matches --+ Results ----+ 0 {mRows = {DataRow}, sRows = {DataRow}} ----+ 1 {mRows = {DataRow}, sRows = {DataRow}} ----+ 2 {mRows = {DataRow}, sRows = {DataRow}} ------+ mRows --------+ ItemArray {Length=3} ----------+ (0) 3.0{Double} ----------+ (1) 3D{Decimal} ----------+ (2) "Yellow"{String} ------+ sRows --------+ ItemArray {Length=3} ----------+ (0) 3.0{Double} ----------+ (1) 22D{Decimal} ----------+ (2) "Orange"{String}

Perfect. So we did the Join, Got results, and know that they're correct. Let's put them into a dataset...

After hours of examples of  trying to get "CopyToDataSet" working, I found that the problem is, "CopyToDataSet" requires a stupefying amount of tweaking to make it work. Ok, so we did that and I can now use CopyToDataSet.

Ran it and I go this:

mRows        | sRows
System.Data.DataRow | System.Data.DataRow
System.Data.DataRow | System.Data.DataRow
System.Data.DataRow | System.Data.DataRow
System.Data.DataRow | System.Data.DataRow

My Desired Result is a Datatable with the following data and format:

ApplesKey | Cost... | Color...| O...Key | Cost...| Color...
  1   |  1  | Red  |  1  | 20  | Orange
  2   |  2  | Green |  2  | 21  | Orange
  3   |  3  | Yellow |  3  | 22  | Orange


so i have a datagridview in a vb from. my plan is to display a table named emplyees, having the following fields: [employees] ([lastname], [firstname], [cost_center], [division], [job_title], [hiring_date], [exit_date], [functional_manager], [member_of], [backup_by], [out_of_office], [desk_phone], [mobile_phone], [comments]. But I need to make an inner join with 3 other tables. job_titles, divisons and rss. How should i writethe select command for this, having the inner join forthe follwing fields: division- division.name,job_title-job_titles.name , and member_of- rss.group_name.

I have no idea how to do it. pls help



I am experiencing extremely slow performance on a particular SQL query using SQL compact.  This same query completes in less than one second on my regular SQL server.  With SQL compact, I am actually not even sure if the query ever completes.  As I type this I am testing it from SQL management studio, and it's up to 17 minutes and 40 seconds still executing so far.

Since I am not experiencing this on most of my other queries, I have little doubt the slowness is due to the volume of data in these tables, coupled with the fact that I am doing the joins. 

I made sure that foreign key constraints are in place for the columns that the joins are based on.  From what I've read, this also ensures that appropriate indexes are created, although I am starting to wonder about that.

This is the query:

SELECTTOP (200) t.BusinessEntityID, t.BusinessName, t.IsTowTruck, t.IsHospital, 
            t.TowTruckLastUsed, t.BusinessLocked, 
            cn.Address1, cn.City, cn.State, cn.PostalCode, cnp.PhoneNumber as TopPhoneNo, GetDate() as LastUpdatedDate, GetDate() as CreatedDate 
       FROM tblBusinessEntity t 
       LEFTJOIN cnBusinessEntityLocation cn ON cn.cnID in 
          (SELECTTOP (1) cnID 
          FROM cnBusinessEntityLocation where cnBusinessEntityLocation.Active = 1 
          AND cnBusinessEntityLocation.BusinessEntityID = t.BusinessEntityID 
          ORDERBY cnBusinessEntityLocation.OrderLevel ) 
       LEFTJOIN cnBusinessEntityPhoneNumber cnp ON cnp.cnID in 
          (SELECTTOP (1) cnID 
          FROM cnBusinessEntityPhoneNumber where cnBusinessEntityPhoneNumber.Active = 1 
          AND cnBusinessEntityPhoneNumber.BusinessEntityID = t.BusinessEntityID ) 

The volume of rows in these tables is approximately as follows:

tblBusinessEntity - 100,000 rows
cnBusinessEntityLocation - 50,000 rows
cnBusinessEntityPhoneNumber - 50,000 rows

Any help is much appreciated, thanks!




I have 3 different tables.... Pls find the tables and sample data below. I am trying to find count by joining these 3 different tables by tblid and parenttblid. There are tables which fall under different parent tables 'tblrelation' table has this information. The 'tbl' table has tblid and the tbl_code. The 'tblreg' has the tblip and tbl_code. How do I join these 3 table to get the tblid count for different subregion tables.



*from tblrelation

tblid parenttblid

200 101

201 101

202 101

203 101

204 101

205 101

206 101

207 101



*from tbl

tblid tbl_code

200 tbl_200

201 tbl_201

202 tbl_202

203 tbl_203

204 tbl_204

205 tbl_205

206 tbl_206

207 tbl_207



*from tblreg

tblip tblreg

791 101

792 101

793 101

795 101

797 101

798 101

799 101

801 101

802 101




COUNT(ts.id), tr.tblid



tblreg tz


INNERJOIN tblrelation tr ON tr.parenttblid = td.tblid


INNERJOIN tbl t ON t.tbl = td.tblid


INNERJOIN tabsubs ts ON ts.tblip = tz.tblip


INNERJOIN vtabs vt ON vt.id = ts.id



BY tr.tblid



I'm trying to do something very simple in WCF Dataservices via EF, but having trouble.

In our data model we have posts (like newsgroup posts) and tags.  Each post can have many tags, and they are related through  a M:N relationship via a bridging table Posts <-> PostTag <-> Tags

Trying to select a post with a particular tag, but getting errors on syntax other folks say works, so I'm not clear on what I am doing:

First tried:

(assume the tag we want has been put into the variable "tag")

var queryPosts = from p in db.Posts
                                 from t2 in p.Tags
                                 where t2.TagID == tag.TagID
                                 select p;

When I run this query, I get:
System.NotSupportedException: The method 'Select' is not supported.


So, I tried reforming the query a bit to:

var queryPosts = from p in svc.Posts
 from t in p.Tags.Where(t2 => t2.TagID == tag.TagID)
 select p;

and I get:
System.NotSupportedException: The method 'SelectMany' is not supported.

Note that both of these forms work just fine with EF directly, but fail when trying to execute them over ADO.NET Data Services.




<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure