Make A SQL View Showing Data From 3 Tables Like Crosstab
Nov 18, 2010
I'm using ASP.net membership system with SQL Server 2000 database. What I want to do is create a view (or stored proc if that's better) which will return a table with the following columns:
UserName | Role1 | Role2 | Role3 | .... | Role*N*
Where the columns (besides Username) are generated to include all the ASP.net Roles in the application.I need this to be generic (i.e. when I add a new Role, I want the procedure/view to compensate and list that new Role as a new column). I want the values in the Role fields to be 1 (True) or 0 (false) if a user in in that role. I can make a table that has such columns using Dynamic SQL to create the variable number of columns, but I cannot seem to populate them.
How can I accomplish this?Tables involved are shown below:
CREATE TABLE [dbo].[aspnet_Users](
[ApplicationId] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL, [code]....
View 1 Replies
Similar Messages:
Nov 30, 2010
When I am creating Strongly Typed View I get lots of Classes in View Data Drop down. Classes like Automapper, ninject, Interface..., latebound... Due this its very hard to find my project classes. Is there any way to restrict dropdown to only display my project classes?
View 1 Replies
Jan 22, 2010
I have a listview that shows the user the details of the members who have sent friendship request. The table from which the data come has two fields namely, sender_id[id of the one who sends friendship request] and receiver_id[id of the one to whom the request is sent].
There will be two columns in the listview. The first would show the pic and the name of the user. The problem is that how am I going to show the pic and the name if the table from where the data come does not contain these info. I know that the sender_id can be used to fetch the pic and name of the sender, but how should it be done?
Earlier I would do something like <%#Eval("fieldName")%>. But in the above described scenario the field is not present in the current table but the other one.
View 5 Replies
Aug 10, 2010
I have this rows in my data base "ExamTable"How can I make the above rows like this?Could any expert tell me what I should to do in this case?
View 6 Replies
Apr 29, 2010
I have a query that gets all item attributes using the query:
SELECT AttributeName, AttributeValue, ItemName from ItemAttributes
But I will need to write .Net code to cross-tabify this query's data.
How can I cross-tabify the above query using SQL Server code so AttributeName appears as table heading and AttributeValue, ItemName as column values?
View 1 Replies
May 7, 2015
I have several table (car,country,employee,language....) and StoredProcedure (Insert,Update,Delete,Select) for all.
Is it possible in one form ex. TEST.aspx and one grid load data for any table and call all storedProcedure for him.
One GridView for all....
Select work fine, but how adjust (insert,update,primary key) it is possible?
View 1 Replies
Jul 12, 2010
I would like to dropdown box to show only starting from second row of the table. How to use data reader to do it?
View 9 Replies
Apr 8, 2010
I'm using Visual Studio 2008 with SQL Server Express databases in my .net 3.5 website
I have three tables I'd like to make entries to when the user completes a wizrd i've setup on a web form.
The First Step - The user enters basic information about their "Case". The table looks like so:
db_Cases - CaseId PK auto int, CustomerId, etc fields
The Second Step - The user enters information about the "Debtors" that are related to this "Case". Think of this as being products....The only difference is these records wont be used over and over again like a product would. The user may need to enter data about several "Debtors", just like if you needed to show several different products on an invoice. For example the user may need to enter information about a husband and wife. This means two entries in the "Debtors" table. The table looks like so:
db_Debtors - DebtorId PK auto int, etc fields
The Third Step - Here's my problem. This step needs to make entries into a third table called "CaseDetails". Again, this is just like if you were creating an invoice with several different products on it, where you would want to get a subtotal of each product's cost. The table looks like so:
db_CaseDetails - CaseDeatailId PK auto int, CaseId int, DebtorId, int
* When this table is later viewed, It will reflect all "Debtors" attached to the specific "Case" being viewed.
The first two steps aren't a problem, I pretty well have that handled in the wizard. BUT How do I create the entries in the "CaseDetails" table when the user finishes?
My idea right now is..... Considering the "Debtors" will be re-used but only on rare occations, I'm thinking I should just add the "CaseId" that was created in step one, to the "Debtors" table at the same time the "Debtor" is created. So the table would look like so.
db_Debtors - DebtorId PK auto int, CaseId int, Name, Address, etc
View 1 Replies
Mar 5, 2011
I have two tables: Projects and ProjectsData and I want to execute query with join and get the result in the View.
In the Controller I have this code:
[Code]....
What I should use in the View to extract this data. I tried that:
[Code]....
but it doesn't work...
View 2 Replies
Apr 26, 2010
how can I display on a single page(View) data from two tables in my db? In my project the model is *.edmx file. i have two table one with categories, and the second one with products. All I want to do is to display on home page(view) in left column all categories, and all products in right column. After clicking on category in left column, in right column there should be products form category which was clicked....
View 7 Replies
Nov 30, 2010
I am having a hard time solving the following with an MVC view. My goal is to display data from multiple tables in a single MVC view. The bulk of the data comes from a table called Retailers. I also have another table called RetailerCategories which stores the retailerid from the Retailers table and also a categoryid linking to a Category table. Note that there are multiple records for each retailerid in the RetailerCategories table.
In the view I want to show a list of retailers and with each retailer I want to show the list of categories applicable to them. What would be the best way to accomplish this?
View 2 Replies
Dec 5, 2010
I'm fairly new to all this, but have slowly been teaching myself C# and ASP.net MVC over the past year or so...I really tried to search for this, but haven't been able to find anything - it may be I'm using the wrong terminology!I'm using ASP.NET MVC2 with EF4 connected to a SQL Express db. Situation: Without getting into too much specifics, I have one table called 'Plan.' Under 'Plan' are several different categories that are related to a single Plan item [say Category A, Category B, Category C and Category D]. Each 'Category X' table has a beginDate, endDate and other details specific to that category.
I'd like to be able to create a summary page that combines all items from Category A, Category B, Category C, and Category D and sorts by beginDate - but only need a few of the columns [eg - begin/end date, name, id].
View 1 Replies
Mar 5, 2011
I have two tables: Projects and ProjectsData and I want to execute query with join and get the result in the View. In the Controller I have this code:
ViewBag.projectsData = (from pd in db.ProjectsData
join p in db.Projects on pd.ProjectId equals p.ID
where pd.UserName == this.HttpContext.User.Identity.Name
orderby p.Name, p.ProjectNo
select new { ProjectData = pd, Project = p });
What I should use in the View to extract this data. I tried that:
@foreach (var item in ViewBag.projectsData)
{
@item.pd.UserName
}
but it doesn't work.
View 1 Replies
Feb 4, 2010
I am developing a site that use ReportViewer to show the users a SSRS report. Basically I have my report uploaded in my SQL Server Reporting Services Server (It is an SQL Server 2008 by the way).I want to set the Report dynamically so I do it this way:
Protected Sub Page_Load(ByVal sender
As
Object,
[code]...
View 1 Replies
Nov 19, 2010
I have a gridview showing values from two tables accommodation and pic (left join on accommodation). If there is no pic uploaded I want to display the row without the pic. So in the gridview I first check to see if the picid is null, if it is i don't render any 'a href' code , if it's isn't i do.
The problem that i'm having is that instead of the image showing up in the gridview itemtemplate i get
','_blank','toolbar=no,menubar=no'))" >
Here is my .aspx code
SelectCommand="SELECT pic.picid, aspnet_Users.Username, accommodation.location, accommodation.type,accommodation.description, convert(varchar,accommodation.createdate,101) as createdate
View 2 Replies
Mar 4, 2011
I want to get data from Projects(which have CourseId) and related CourseName from Courses table.
I have written following code:
var projects = from n in db.Projects
join c in db.Courses on n.CourseId equals c.ID
orderby n.Name
select new { Project = n, Course = c };
return View(projects.ToList());
and I get error:
The model item passed into the dictionary is of type 'System.Collections.Generic.List1[<>f__AnonymousType22[ProjectManager.Models.Project,ProjectManager.Models.Course]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[ProjectManager.Models.Project]'.
What I need to do in Controller and in View to display this data?
View 2 Replies
Apr 2, 2011
In my application, I need to get data from three tables and display them in 1 view. I have created a .dbml and created a ViewModel in my Models folder to deal with this. Here is my ViewModel:
[Code]....
My web application uses the Default ASP.NET Membership. So User [ u ] is of type aspnet_Users from the default Membership. This ViewModel allows all the CRUD opertation to be carried out. However on the Create and Update operations, I would LOVE some Model Validations, My question is:
1) Would the User [ u ] default validation kick into place in my ViewModel ? Cause I know by default ASP.NET Membership, has Model Validation.
2) For table SUserDetails (which I have created myself, not part of default) how do I add ViewModel validation to it for when someone does an Update or Create?
View 1 Replies
Mar 8, 2011
I am using Entity Frameowrk as the ORM. I am new to MVC and trying to develop a samll module as a learning project.
Now I am stuck with an issue for which I can't seem to find out answer on web. The issue is :
I have two tables mapped as entities - Tenant_Definition and Contact
Tenant_Definition { Tenant_ID , Desc, Contact_ID}
Contact {Contact_ID, Company_Name,Company_Address,Person_Name, Person_Phone_Mobile}
My View has to present a form that shows Tenant Desc and Contact related fields. The index view works fine and shows the desired values.
But, the create view isn't working out. In the create view, i am planning to do -
1. creating a new contact entity instance and saving it in db
2. Then creating a new tenant entity instance using the new contact_id and save it to the DB.
However, the code doesn't seem to be inserting any value inside Contacts table. It inserts null value.Below is the code snippet of my Create Controller -
[HttpPost]
public ActionResult Create(FormCollection collection)
{
var newContact = new Contact();[code]....
View 6 Replies
Feb 16, 2011
I have three tables
tbl_Cus CatID (Int) CustID(int) CabID(int)
tbl_Cat CatID(int) CatName(varchar) CusID
tbl_Cab CatId(int) CabID(int) CabName(varchar)
I am using Tree View control for the first time.
The above tables I want to bind it to a tree view control the tree view should look some thing like this.
Cus Name1
Cat Name1
CabName1
CabName2
CatName2
CabName1
CabName2
Cus Name2
Cat Name3
CabName1
CabName2
CatName4
CabName1
CabName2
View 2 Replies
Nov 26, 2010
Here is my scenario.There is a users table and each user has multiple addresses(one to many) and I want to dispalay them like the image provided below
using listview only.I am really stuck with the deign of the page.There is a show and hide feature also.I can use only list view that is my requirement.
[code]....
View 2 Replies
Jan 4, 2011
I dont want them to be able to select a listitem until they click edit .
here is a picture of what it looks like now.
View 2 Replies
Sep 15, 2010
Is it possible to show only few characters in gridview column. Lets say there are 5 columns in the gridview and one of the field is the description field. assume description field is too long. So What i want is to show that description field value to 50 character limit.
my code is like this:
<asp:BoundField DataField="ProposalDescription" HeaderStyle-CssClass="endTime" HeaderText="Proposal Description" ItemStyle-CssClass="endTime"> <HeaderStyle CssClass="endTime" /> <ItemStyle CssClass="endTime" /> </asp:BoundField>
Now here i want to insert character limit for this datafield. Is it possible ?
View 6 Replies
May 15, 2010
details view showing actual password insted of *******How can it would be **********
View 4 Replies
Aug 16, 2010
I have a datagrid view, in that I have a templete column ,inside that I have Image control and label control.
Based on the Data from one column in database, i have to show Label and Image control,in template column.
How can i do that?
I am using ASP.net 2005 and dev language is C#.
View 4 Replies
Jan 2, 2010
I have one dataset in which there are 40 tables. Now i want to make a relation between these tables and show important data in grid. How do i do this?
View 3 Replies
