Columns Of Two Related Database Tables In One GridView With EntityDataSource
Feb 17, 2010
I have two SQL Server tables with Primary Keys (PK) and a Foreign Key (FK) linking the two tables:
1) Table "Order"
OrderID, int, PK
AddressID, int, FK
...
2) Table "Address"
AddressID, int, PK
City, nvarchar(50)
...
Then I've created an (ADO.NET) Entity Data Model out of those two tables. Now on my (ASP.NET) webpage I put a GridView with an EntityDataSource. In my GridView I want to display two columns:
OrderID City (belonging to that order and linked by the AddressID-key) How can I do that? My problem is: When I configure the Entity Data Source I can select an "EntitySetName" which can be either "Order" or "Address" but not both, nor can I select any kind of relationship. If I select "Order" as EntitySetName
then in the GridView I can add the columns
OrderID
Address
Address.AddressID
Adding the column "Address" displays empty cells. Adding "OrderID" and "Address.AddressID" displays the expected IDs. But how can I add the "City" of the related address to my GridView? Edit: Clarification:
The Entity Framework has created a class "Order" and a class "Address" corresponding to the database tables. The class "Order" has a reference to an "Address" object as a navigation property, corresponding to the 1-n relationship between Address and Order table. Basically I want to have a column in my GridView which displays Order.Address.City. I have tried to add a bound field with "Address.City" as data field to the GridView but it results in a runtime error ("no such property...").
I have a formview with an entityDataSource. I need to include columns from related fields. I have seen many demos, with AdventureWorksDB, that imply that you can do this by using INCLUDE in the entitydatasource declaration. I have tried made many attempts and it just does not seem to work as demonstrated. I have attached the errors below and the EDS. I have also attached a JPEG of the model I am working in. Three errors demonstrating 3 different attempts to include a related table:
DataBinding: 'TmModel.tbICObjectBase' does not contain a property with the name 'Group' the code declaration for error 1: <asp:Label ID="Label1" runat="server" Text='<%# Bind("Group") %>' /> DataBinding: 'TmModel.tbICObjectBase' does not contain a property with the name 'tbICTagBase'. the code declaration for error 2: <asp:Label ID="Label1" runat="server" Text='<%# Bind("tbICTagBase.Group") %>' /> DataBinding: 'System.Data.Objects.DataClasses.EntityCollection`1[[TmModel.tbICTagBase, TmModel, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]' does not contain a property with the name 'Group'. the code declaration for error 3: <asp:Label ID="tbICTagBasesLabel" runat="server" Text='<%# Bind("tbICTagBases.Group") %>' /> EntityDataSource <asp:EntityDataSource ID="entDSObjectBase" runat="server" AutoGenerateWhereClause="True" ConnectionString="name=IntouchDBContainer" Include="tbICTagBases" DefaultContainerName="IntouchDBContainer" EnableFlattening="False" EntitySetName="tbICObjectBases" EnableDelete="True" EnableInsert="True" EnableUpdate="True" EntityTypeFilter="tbICObjectBase"> <WhereParameters> <asp:SessionParameter SessionField="Tag" Name="RecID" Type="Int64" /> </WhereParameters> </asp:EntityDataSource>
I am using an ADO Entity Model for my ms-sql database.
My database has a Resellers table which has ResellerID (primary key, int) and Name columns along with other data.
I also have an Orders table which contains OrderID (primary key, int), OrderDate, ResellerID (foreign key to Resellers.ReselerID).
If I just do a query like:
[Code]....
I am able to reference order.Reseller.Name just fine with no problems.
However I have an EntityDataSource on my page as follows:
[Code]....
Notice the Include="Reseller" attribute in the datasource... I also have a GridView tied to that datasource as follows: I cut some of it off for breavity:
[Code]....
Now what I have listed above will run with no errors, but what I want to do is replace the column that displays "ResellerID" with the equivilant to order.Reseller.Name. In other words I want my grid to display the reseller's name rather than the numeric ID.
I have tried to change the DataField to "Reseller.Name" but that will generate the following error:
A field or property with the name 'Reseller.Name' was not found on the selected data source.
Is there anyway to do this without me writing a custom select statement in my code behind?
i have this xml file which i read using the DataSet.ReadXml( path ) method . The tables created from the Xml file is Related in such a maner that each product has many options. Product is in one table and Options are in another.Now all i want to do is display the related tables in One Gridview. For your reference below is the products Xml file,
[Code]....
Now this is what i have done in the Code behind , have a look below
[Code]....
Now the Grid works Great if i want to Display Individual tables out from Dataset but not when they are related . Please do shine your Knowledge on what should be done to display Child ~ Master relationship. By the way , its a definite NO for someone who would show me that link from msdn about displaying XML data using Xmldatasource and gridview [That's a lousy way to display data]
What would you all suggest a database with less number of tables and more number of columns or more number of tables with less number of columns. I am developing a web application using Visual stuio 2005 and Sql Server 2005
I want to know that how many tables we can have in database and what is the maximum no. of columns.I also want to know the maximum size for each datatype.
I am developing the Home page of a Client. Apart from various things on this home page I have a text box and search button. Having said that, I have a database in which I have almost 12 tables with varying number of columns. Now my question is,Is there anyway to search a word typed by a user in the textbox to search it in all the tables(all columns) of the database.
I want to know the procedure of binding a gridview. but the condition is that thefre are three columns in this gridview. if I wud bind it from one table then it wud ok for me. but now I need to bind it from three different tables. onw column from one table, another from 2nd.. and so on.
User, Details, Optional User has fields: ID, FirstName, LastName Details has fields: ID, MyDetails1, MyDetails2, User_ID Optional has fields: ID, MyOptions1, MyOptions2, User_ID There is a 1 to many relationship with User & Detail (User ID & Detail User_ID) There is a 1 to many relationship with User & Optional (User ID & Optional User_ID)
With that said, one user can have many details and/or many optionals
Now I'm trying to build a linq query that will say:
"where user id = x, select FirstName, LastName, all MyDetails1 from every record where User_ID == x, all MyOptions1 from every record where User_ID == x"
After the merge I should have one string that lists the distict user & all of their many fields:
I'm working with asp.net 3.5 WebForms and LINQ to Entities.
I created the database with their relationships, but when I go to import the data with LinqToEntities, some reports and have not seen any reports 1 to 1.
I can create them after the reports have imported the tables LinqToEntities?
I would like to update new records that get inserted in DataSet to Sql Server Database. Below is a working example of how to do this if you use "DataAdapter.Fill(ds)".
But i don't want to fill data, becouse i don't need data, i only need shema (I will use this dataset to insert new data to Sql), so i would like to use "DataAdapter.FillSchema(ds)"
Let say you read a customer record into Customer object. Do it read all the related records across tables at the same time Or it only reads when you do from o in customerobject.orders
After running the aspnet_regsql.exe tool, i notice a bunch of different tables pertaining to the ASPNET security (e.g. aspnet_Applications, aspnet_Membership, aspnet_Paths, aspnet_Personalations, aspnet_Profile, aspnet_Roles, aspnet_SchemaVersion, aspnet_Users, aspnet_UsersInRoles). Do the different providers (AspNetSqlMembershipProvider, AspNetSqlProfileProvider, AspNetSQLRoleProvider) relate or associated with certain aspnet_xyz tables? I imagine the AspNetSQLProfileProvider is responsible for the aspnet_Profile table correct? The AspNetSQLRoleProvider probably is tied to the aspnet_Users, aspnet_Roles, aspnet_UsersInRoles table? Any links documenting the relationship between teh providers and the aspnet_xyz tables?
I am using ASP.NET (C# and sql server express 2005) for my web application project. I have three related tables. I want to do an insert using typed datasets,tableadapters and three tier architecture. I wish to know how to do simultaneous inserts in 3 tables. So i wish to insert a row in first table and get its primary key. Same with the second table. I will use the primary keys of those first two tables to do the insert in the third table. How can i accomplish this using formview control and what methods should i use??
My dataset table data is from multiple tables (showing on a gridview),How to update the database tables using batch udpate on clicking submit, if the data is from a signle table, no problem, but I am not sure how to handle if the datais from mulitiple tables? If I have related tables, do I need put them in differet tables in the dataset?
I've created a membership system based on asp.net 2. There are other tables that store user information, such as emails sent etc. I've created a users table based on the TableProfileProvider. My question is concerning deleting the member
Is it OK to delete the member from the aspnet_membership table and roles related tables using Membership.DeleteUser, though keep all the custom tables and the information including the information in the Users table created by the TableProfileProvider? or can you foresee problems?
I want to display a crystal report in my vb.net application. Suppose I have tables named student details, student marks, student address, etc... Nowif I want to display all these details (fields of all tables) in one crystal report (with page breaks if necessary) then how will I achieve it. I will be providing a combo box in my application that contains list of student names.How can I link this combo box with the cystal report to dynamically display report for different student on selected index change of combo box?
What would be the best database/technique to use if I'd like to create a database that can "add", "remove" and "edit" tables and columns? I'd like it to be scaleable and fast. Should I use one table and four columns for this (Id, Table, Column, Type, Value) - Is there any good articles about this. Or is there any other solutions? Maybe three tables: One that holds the tables, one that holds the columns and one for the values?
Maybe someone already has created a db for this purpose? My requirements is that I'm using .NET (I guess the database don't have to be on windows, but I would prefer that)
using sql server 2005 and vb.net 2005. I am writing a console application to import a mass amount of data from a non-sql database table into a sql server database table. The destination db table (sql server) has fewer tables than the source db table so I need to map the columns. does anyone have an example of how to do this?
I have a partitioned db where several tables have the exact same structure. I'm able to query the tables using linq, but only by quering the individual tables 1 at a time and by using the explicit table name. For example at the moment, I have 8 tables so I have something similar to the following:
[Code]....
Is there any way, that based on a certain value (stringValue), I could determine which table to query and then code the linq query just once?
is there an easy way to compare two columns in two datatable to see if they match? I need to check if the values in column["x"] of datatable1 occur in column ["y"] of datatable2, without iterating through both tables (for each value in column of dt1, check column of dt2). I tried:
foreach (DataRow dr in dt1.rows)
if (dt2.rows.find(dr["x"]) but this checks the whole row and if finds an "x" in any column, "if" condition is true. I need to only check one column of dt2. Tables have different columns but the same primary column and that's what I need to check
Entity ID (PK), int Name Descrip Users ID (PK) EntityID, int (this is not connected to Entity table)
Now I am using LINQ to pull the records which has a Entity.ID = something. Which will show me couple of records in my GridView. Here is my LINQ statement:
protected void Page_Load(object sender, EventArgs e) { string getEntity = Request.QueryString["EntityID"]; int getIntEntity = Int32.Parse(getEntity); OISEntityTestingDataContext db = new OISEntityTestingDataContext(); //OISLinqtoSQLDataContext db = new OISLinqtoSQLDataContext(); var tr = from r in db.Users join s in db.Entities on r.UserID equals s.ID where s.ID == getIntEntity select new { //To Show Items in GridView! }; GridView1.DataSource = tr; GridView1.DataBind(); }
Now here I am getting an error mesg on 'join' The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join What does that mean?
