ASP.net 3.5 GridView Displaying DataTextField in ItemTemplate Column

I give up.  I'm trying to create a GridView that displays a value in a related table in the Item Template field.  Here's what I have.

I created a test WebSite with the following tables

Customer = CustomerID, CustomerName, CustomerTypeCode
CustomerType = CustomerTypeCode, CustomerType

I defined the relationship between the tables in SQL Server CustomerTypeCode = CustomerTypeCode

Then I created the test web site.  I'm using Linq to Sql so I created a DBML file.  Dragged the tables to the DBML.   THe links appear as entered.  

THen I added an asp web form and dragged a Gridview to the form.    Next I set the Datasource as a Linq to SQL datasource based on the Customer table.  I changed the CustomerTypeCode column to a Template Field

THen I went to the source code and changed the eval("CustomerTypeCode") to eval("CustomerType") in the Item Template.  

When I display in Browser, I get the following message.


DataBinding: 'Customer' does not contain a property with the name 'CustomerType'.

What am I doing wrong?
deepPowdahAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

guru_samiCommented:
I don't think it will work that way. Since you are binding your GV to Customers table you won't get CustomerType.
You will have to write a separate join query that will return the Customer Columns + CustomerType in one resultset and then bind that to your GV.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
deepPowdahAuthor Commented:
I have tried that but when you add the Customer Type table the recordset is no longer updateable.  I attempted to add the CUstomerType table using a view.  So when you try to edit a customer record, say change the customer name, you get an error message that it cannot be updated.  
0
deepPowdahAuthor Commented:
Using the Linq to SQL you must choose 1 item as your data source.  So it must be either the CustomerTable by itself, or the CustomerTable and CustomerType Table in a View.  How else would you do this/
0
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Kamal KhaleefaInformation Security SpecialistCommented:
write a query like this
select
CustomerID, CustomerName, CustomerTypeCode,
CustomerTypeCode, CustomerType
from
Customer inner join
CustomerType on
Customer.CustomerTypeCode=CustomerType .CustomerTypeCode
0
deepPowdahAuthor Commented:
I used a left outer join to do this in a view.  I believe you can only use a table or view as a source for a Linq to SQL datasource.  The left outer join ensures that all customers are displayed even if they do not have a customer type.

SELECT     dbo.Customer.*, dbo.CustomerType_Lookup.CustomerType
FROM         dbo.Customer LEFT OUTER JOIN
                      dbo.CustomerType_Lookup ON dbo.Customer.CustomerTypeCode = dbo.CustomerType_Lookup.CustomerTypeCode

When I attempt to edit a record based on this view, I get the following error message.

Could not find a row that matches the given keys in the original values stored in ViewState.  Ensure that the 'keys' dictionary contains unique key values that correspond to a row returned from the previous Select operation
0
ahmadmarediaCommented:
Assuming you are using SQL Server 2005 or above, try the following query... This should generate a unique key for each row of your resultset for ASP.Net to work properly.  In the first line of the query, replace CustomerId with the column name of the PK of your Customer table.
SELECT     ROW_NUMBER() OVER (ORDER BY CustomerId ASC) AS ROWID,
dbo.Customer.*, dbo.CustomerType_Lookup.CustomerType
FROM         dbo.Customer LEFT OUTER JOIN
                      dbo.CustomerType_Lookup ON dbo.Customer.CustomerTypeCode = dbo.CustomerType_Lookup.CustomerTypeCode

Open in new window

0
deepPowdahAuthor Commented:
Thanks for all your input.  I have figured out how to do this using Linq to SQL.  When you create a data content page (dbml) by dragging tables from the Server View, the relationships you created in SQL Server are brought across too.  Then when you create the Linq DataSource for the GV, there is what appears to be an extra field for the name of each related table.   Then you create a template field for the lookup field, e.g. CustomerTypeCode. You can add a drop down list to enter and update the CustomerTypeCode.  However in the Item Template, you can display the CustomerType, not the CustomerType using the following code.

                    <ItemTemplate>
                        <asp:Label runat="server" Text='<%#Eval("CustomerType_Lookup.CustomerType") %>'></asp:Label>
                    </ItemTemplate>

Linq to SQL has its moments and this capability is one of them.  I wish someone would write a book about how to use Linq to SQL.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.