SELECT documentcategoryname, documentname, documentid FROM documents
INNER JOIN documentcategory
ON documents.documentcat = documentcategory.documentcategoryid
WHERE documentsiteid = @siteid
GROUP BY documentcategoryname, documentname, documentid
Which gives the following results:
documentcategoryname | documentname | documentid
About Us doccy3 3
About Us doccy4 4
Map Of Medicine doccy5 5
Is there a way using vb.net I can just display the category name once, and the documents underneath it like below:
About Us
doccy3
doccy4
Map Of MEdicine
doccy5
My code so far is:
If pagesreader.Read() Then
' output documentcategoryname
mydata &= "<h2>" & reader.Item("documentcategoryname") & "</h2>"
'display document list here using while loop?
End if
I'm attempting to further reduce a dataset/table by various user input. Part of this process is grouping the data by a timestamp prior to a summation. I'm trying to use LINQ and cannot get the data returned that I need.
select r.Name,c.Name,s.Name,(select count(p1.id)from profiles p1 where r.id=p1.religion and c.id=p1.caste and s.id=p1.subcaste and p1.Gender=1)as Male,(select count(p1.id)from profiles p1 where r.id=p1.religion and c.id=p1.caste and s.id=p1.subcaste and p1.Gender=2)as Female from religion r,profiles p,caste c,subcaste s where r.id=p.religion and c.id=p.caste and s.id=p.subcaste group by r.Name,c.Name,s.Name,r.id,c.id,s.id
Having trouble with a web page and connecting to SQL DB. Basically I have a form view table connected up to a SQLDataSourceControl and what I want displayed in the form is only data that has been assigned to the user that is logged on now. I have a "ViewAll" page, which lists EVERY field, regardless of who it is assigned to, but the "MyTasks" page should only retrieve the data of the current user.
I have two tables in my DB, the "Users" table, with user data (including an ID number) - this is used again in the "Tasks" table as a foreign key called "AssignTo". Therefore if user Joe Bloggs with ID number 5 logs on, whenever someone assigns a task to him, they will select number 5 in the "Task" table, as this relates to his User ID.
i am currently facing some problem retrieving image for display on a gridview control. the Image data type in the data table is "image". the data that is stored inside the database is in this format "<Binary data>". may i ask if it is possible tojust retrieve a image data of this format and display in the gridview? or do i need to store the image in a image folder for retrieval?
i have to Count Data Month wise and Date format would be like this Feb-10 and iam using the below query but no luck .
SELECT REPLACE(CONVERT(CHAR(9), ADate, 6), ' ', '-') As 'MonthYear', COUNT(AID) AS 'Planned' FROM Employee where Status =1 and YEAR(Adate) = '2009' GROUP BY YEAR(Adate), MONTH(Adate) ORDER BY YEAR(Adate),MONTH(Adate)
I've written this code to display a group of events when the value is 12/19/2010. This means each day I need to change the "default value" to the current date to display the events of the day. I would like to make this such that the change occurs automatically, that is, on 12/20/2010, I'd like to see just the events for 12/20/2010.
I'm trying to create a menu structure, subpages within pages, for use with my cms. I have 3 tables in sql server, I would like to query all three tables and list those pages with a certain siteid, and subpages where the subid is the pageid. how to use the GROUP BY Statement to create the menu below? Or could I use vb.net to display it for me? Something like this:
SELECT * FROM sites LEFT JOIN pages ON sites.siteid=pages.siteid LEFT JOIN subpages ON pages.pageid=subpages.pageid WHERE sites.siteID = 1ORDER BY sites.siteid, pages.pagesub ASC
These are my tables:
sites siteID int Unchecked PK siteName nvarchar(MAX) Checked siteAdmin nvarchar(MAX) Checked siteLive bit Checked Pages
pageID int Unchecked PK siteID int Checked FK linked to siteid in sites table pageLink nvarchar(MAX) Checked pageBody ntext Checked pageSummary ntext Checked pageTitle ntext Checked pageOrder int Checked pageHome bit Checked
subpages
subID int Unchecked PK PageID int Checked FK linked to pageid in pages table subBody ntext Checked subSummary ntext Checked subTitle nvarchar(MAX) Checked subOrder nvarchar(MAX) Checked
I have some duplication that I need to clean up. I wrote a SQL query that is supposed to return duplicate customers who are located in the same city and zipcode.
I have 2 questions regarding it:
1. Is the query syntax correct to find duplicate records by same city and zipcode, data is being returned but it just returns the same customer a few times?
2. I only need to do the GROUP BY clause for Fullname and City however it gives an error when the other columns are left out. The error is Address, State and Zip not being in the aggregate function or in the group by clause.
Adding all the remaining columns to the GROUP BY clause works.
What i need to do is select the last 5 events for each group using LINQ to Objects. I tried starting with:
[Code]....
But i'm not sure how i only take the last 5 per group.
The simple way of doing this is to get the groups and then have a property on that which returns the last 5 events but if i databind this i will have to use a nested Repeater/ListView. Ideally i would like to have a row foreach event.
What will be the equivalent GroupJoin lambda expression in LINQ when I do this.
Dim fruit_salad = (From fruits In _DataContext_Fruits _ Group Join fruitRipe In _DataContext_FruitRipe On fruitRipe.fID Equals fruits.fID Into Group From fruitty_lecious In Group.DefaultIfEmpty() _
Stack-Overflow :3 I've got a kind of hard-case question.
So I'll try clearly explain my idea with my poor english :/ need select to DataTable SOME values from ???_**GetDisplayData **procedure for each day of previos month (GROUP IT) where group rules for different columns is different
I need to select some values in stored procedure from other stored procedure like this :
SELECT X FROM Y_Procedure(@ProcedureParameters)Also I need to select from dynamic SQL Procedure like
Y_Procedure=@Y+'_Procedure' SELECT X FROM Y_Procedure(@ProcedureParameters)
Also I need to load it to DataTable :-/INSERT INTO @Report (CellHorizontal, CellVertical, CellValue) --to TABLE SELECT Date,X2,X3 FROM Y_GetDisplayData(@Param)
SET NOCOUNT OFF; SELECT * FROM @Report
GetDisplayData works as select with parameters and that doesn't returns SQL DataTable And there is no way to recode GetDisplayData's procedures, it's just constant procedures for me.Finally I need to Group nodes from this table
INSERT INTO @Report (CellHorizontal, CellVertical, CellValue) SELECT T1.Date, IF ((Select grouptype...)=1) T1.X2 + T2.X2 ELSE IF ((Select grouptype...)=2) AVG(T1.X2,T2.X2), IF ((Select grouptype...)=1) T1.X3 + T2.X3 ELSE IF ((Select grouptype...)=2) AVG(T1.X3,T2.X3), (SELECT T2.Date,X2,X3 FROM Y_GetDisplayData(@Param) T2 WHERE T2.Date>T1.Date AND T2.Date>=T1.Date) FROM Y_GetDisplayData(@Param) T1 GROUP BY EVERY DAY ???--and here is epic fail
I can make all stuff on asp server :And C# allows me to use something like SelectCommand = IzmProc + "_GetDisplayData"; And then I will work (select special data) with DataTables on ASP.NET Server but it's better to make all on SQL ... But looking like it's just unrealizable on SQL >_<
I gonna think about C# realization,but my code is very weird , got errors and doesn't works >_<
I have a table that contains image data type and has more than 20,000 records.I want to change its file group to the new file group. I tried to change it using table designer, but everytime I wanted to save it, it occured an error with this message:
[Code]....
Does anyone know how to change file group using Alter Table query? I'm using SQL Server 2005.
i have 2 tables company(companyId,companyName,City,state,country) and people(peopleId,peopleName,City,state,country) i want to display the total no of contacts and Total no of companies with respect to countries
i tried with group by: select country,count(*)companyId from company group by country is displaying the total number of companies with respect to country select country,count(*)peopleId from people group by country is displaying the total number of peoplewith respect to country
but i want to combine these two quries i want the output as :
Country Companies Contacts India 10 5 USA 0 10 (usa is having only contacts ,i doesn' have the companies) UK 8 0 (ukis having only companies ,i doesn' have the contacts)
<asp:SqlDataSource ID="totalcount" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT COUNT ([CID]) FROM [WWAMembers] WHERE([CID] IS NOT NULL)"></asp:SqlDataSource>
how do I get the totals displayed with label or other control?
