Solved

Gridview SQLDatasource question

Posted on 2010-11-24
2
384 Views
Last Modified: 2012-05-10
I have a directory page on a members only site and I'm using a gridview to display the information but i'm having a problem with the displayed data.  There are 3 tables involved iwth the query 1 table is the members table that pulls the members information, and the other 2 i want to do a count on the # of times the member id shows up in one specific column.  The problem is that the page is displaying both count columns within the gridview as the multiplication of the 2 counts.  I'm not sure how to get the individual parts to show up and hope someone can let me know where my sql is wrong.
<asp:GridView ID="GridView1" runat="server" DataSourceID="sdsMember" AutoGenerateColumns="false">
                                    <Columns>
                                    <asp:BoundField ItemStyle-Font-Bold="true" DataField="MEMBER_SNAME" HeaderText="Nickname" ItemStyle-Width="150px" ItemStyle-HorizontalAlign="Center" ItemStyle-VerticalAlign="Middle" HeaderStyle-Width="150" HeaderStyle-HorizontalAlign="Center" HeaderStyle-VerticalAlign="Top" />
                                    <asp:ImageField DataImageUrlField="MEMBER_PIC" HeaderText="Avatar" ItemStyle-Width="150px" ItemStyle-HorizontalAlign="Center" ItemStyle-VerticalAlign="Middle" HeaderStyle-HorizontalAlign="Center" HeaderStyle-VerticalAlign="Top"></asp:ImageField>
                                    <asp:TemplateField ItemStyle-HorizontalAlign="Center" ItemStyle-VerticalAlign="Middle" HeaderStyle-HorizontalAlign="Center" HeaderStyle-VerticalAlign="Top" ItemStyle-Width="150" HeaderStyle-Width="150">
                                    <HeaderTemplate># Shit Talk Posts</HeaderTemplate>
                                    <ItemTemplate><asp:Label ID="shitPosts" runat="server" Text='<%#Eval("NUMPOSTS") %>'></asp:Label></ItemTemplate>
                                    </asp:TemplateField>
                                    <asp:BoundField DataField="RECORD" HeaderText="Record" HeaderStyle-Width="150" HeaderStyle-HorizontalAlign="Center" HeaderStyle-VerticalAlign="Top" ItemStyle-HorizontalAlign="Center" ItemStyle-VerticalAlign="Middle" />
                                    <asp:BoundField DataField="TRADECOUNT" HeaderText="# of Acquisitions" HeaderStyle-Width="150" HeaderStyle-HorizontalAlign="Center" HeaderStyle-VerticalAlign="Top" ItemStyle-HorizontalAlign="Center" ItemStyle-VerticalAlign="Middle" />
                                    </Columns>
                                </asp:GridView>
                                <asp:SqlDataSource ID="sdsMember" runat="server"
                                    ConnectionString="<%$ ConnectionStrings:DBSFL %>"  
                                    SelectCommand="SELECT M.MEMBER_SNAME, M.MEMBER_PIC, '(' + Convert(varchar,M.MEMBER_WINS) + '-' + Convert(varchar,M.MEMBER_LOSSES) + '-' + Convert(varchar,M.MEMBER_TIES) + ')' AS RECORD, COUNT(P.SHIT_ID) AS NUMPOSTS, COUNT(A.ACQ_ID) AS TRADECOUNT, M.MEMBER_POINTS FROM MEMBER M, ACQUISITIONS A, SHITTALK P WHERE M.MEMBER_ID = A.MEMBER_ID AND M.MEMBER_ID = P.POST_MEMBER GROUP BY MEMBER_SNAME, MEMBER_PIC, MEMBER_WINS, MEMBER_LOSSES, MEMBER_TIES, A.MEMBER_ID, MEMBER_POINTS"></asp:SqlDataSource>

Open in new window

0
Comment
Question by:rondre
2 Comments
 
LVL 12

Accepted Solution

by:
HugoHiasl earned 500 total points
Comment Utility
How about using subselects?

Replace the
    count(p.shit_id)
with
    (SELECT COUNT(*) FROM ShitTalk st WHERE st.MemberId = m.MemberId)

The same for other count. Remove the 2 tables from the main query and give the Member table in the mainquery the alias m.

0
 

Author Closing Comment

by:rondre
Comment Utility
Thank you - i knew you could do the select statement within the select but wasn't sure how to link on the memberid field - worked great!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now