locked
Question about ExecuteScalar..... RRS feed

  • Question

  • User1203612064 posted

    Hello, im tryin to make some execute but not in the aspx.cs page im tryin to make it in the aspx page with the data tools of the VS2008. How can i count the number of rows in the DB by some id? i have table which include some articles, every article belong to some category, i want to count the records for every articles in category (for example: category internet = 23 articles, category finance = 42 articles and so on) there is anyone have an idea?

    <asp:FormView runat="server" DataSourceID="SqlDataSource51">
        <ItemTemplate>
        <asp:Label ID="Label4" runat="server" ForeColor="#b8cc0b" Text='<%# Bind("articleCatID") %>' Font-Bold="true" Font-Names="Arial" style="font-size:16pt;"/><br />
        </ItemTemplate>
        </asp:FormView>
    
    <asp:SqlDataSource ID="SqlDataSource51" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT COUNT (*) FROM articles">
    <SelectParameters>
    <asp:QueryStringParameter QueryStringField="articleCarID" DbType="Int32" Name="articleCatID" />
    </SelectParameters>
    </asp:SqlDataSource>


     

    Saturday, October 17, 2009 5:34 AM

Answers

  • User1203612064 posted

    he he he... the numbers no need to be at all! it only need to be the top number which is the number of articles in this category. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 20, 2009 6:01 PM

All replies

  • User-455638226 posted

    Let's say you have 2 tables:

    tblCategory:

    CategoryID, CategoryName


    tblArticle:

    ArticleId, CategoryId, ArticleName


    You can have a SQL statement like this:

    select CategoryId, CategoryName, NumberOfSubArticle

    from tblCategory, (SELECT CategoryId, Count(CategoryID) as NumberOfSubArticle from tblArticle group by CategoryId) as tblCategoryArticle

    where tblCategory.CategoryId *= tblCategoryArticle.CategoryId


    Please note "*=" is left join.

    :)


    Best,

    Tim Robson

    My Free Live Chat for Website

    Comm100 - Open Source & Free Hosted Customer Service Software

    Microsoft Certified Partner


    Saturday, October 17, 2009 8:58 AM
  • User77042963 posted

    Try this:

            <asp:GridView ID="gv1" runat="server" AutoGenerateColumns="False"  DataSourceID="SqlDataSource51">
                <Columns>
                    
                    <asp:BoundField DataField="category" HeaderText="category"  />
                    <asp:BoundField DataField="catCounte" HeaderText="Category Count" />
                 </Columns>
                 </asp:GridView>
            
            
            
    <asp:SqlDataSource ID="SqlDataSource51" runat="server" 
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
    SelectCommand="SELECT COUNT (*) AS catCount,  category  FROM articles GROUP BY category ">
    </asp:SqlDataSource>


     

    Sunday, October 18, 2009 11:36 AM
  • User1203612064 posted

    Hello and thanks for replay, but the both ways did'nt work for me. I can't understand why i need to involved the category names table? im only need to count from the articles table....

    i have table call articlesCategories which have the articlesCatID (int), categoryName (varchar). and another table call articles with articleID (int), articlesCatID (int), articleTitle (varchar) and so on... i have page that show the categories names and i want to show in every categories how much articles have in this section by simple code (not the aspx.cs page):

    <asp:SqlDataSource ID="SqlDataSource50" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [articlesCategories] ORDER BY [articlesCatID]">
    </asp:SqlDataSource>
    i know how to count the all articles to show how much articles have in DB by aspx.cs code:
    SqlConnection newconn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            SqlCommand newCmd = new SqlCommand("SELECT COUNT (*) from articles", newconn);
            newconn.Open();
            int numberOfrows = (int)newCmd.ExecuteScalar();
            Label4.Text = numberOfrows.ToString();
            newconn.Close();

    but how can i count by articlesCatID?
     


     

    Tuesday, October 20, 2009 2:02 PM
  • User77042963 posted

    Try the following query for your datasource and use the datasource for databind control (FormView).

    SELECT Count(A.*) AS Articlecount,
           Ac.Categoryname
    FROM   Articles A
           LEFT JOIN Articlescategories Ac
             ON Ac.Articlescatid = A.Articlescatid
    GROUP  BY Ac.Categoryname 


     You may need to check out GROUP BY clause in T-SQL.

    Tuesday, October 20, 2009 2:32 PM
  • User1203612064 posted

    <STRIKE>Thanks but how do i present the results? (the Formview?)</STRIKE> 

    Tuesday, October 20, 2009 4:55 PM
  • User1203612064 posted

    i was used your code. with the (A.*) the compiler show "worng syntax near the *. i try to change the * to this code but the page show the number 2 in every category, and there is a lot more articles

     <asp:FormView runat="server" DataSourceID="SqlDataSource51" >
        <ItemTemplate>
        <asp:Label ID="Label6" runat="server" ForeColor="#336699" Font-Size="Small" Font-Names="Arial" Font-Bold="true" Text='<%# Bind("Articlecount") %>' /><br />
        </ItemTemplate>
        </asp:FormView>
    
    <asp:SqlDataSource ID="SqlDataSource51" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT COUNT (A.articlesCatID) AS Articlecount, Ac.categoryName FROM articles A LEFT JOIN ArticlesCategories Ac ON Ac.articlesCatID = A.articlesCatID GROUP BY Ac.categoryName">
    </asp:SqlDataSource>


     

    Tuesday, October 20, 2009 5:07 PM
  • User77042963 posted

    Use COUNT(*) or COUNT(A.articlesCat).

    The query should work.

    Here is an example you can run in your database to see how the query works.

    declare @ArticlesCategories table (articlesCatID int, categoryName  varchar(10))
    declare @articles table (id int identity(1,1), articlesCatID int,articlename varchar(50))
    
    insert into  @ArticlesCategories  values (1, 'cat1')
    insert into  @ArticlesCategories  values (2, 'cat2')
    insert into  @ArticlesCategories  values (3, 'cat3')
    insert into @articles values (1, 't1')
    insert into @articles values (1, 't11')
    insert into @articles values (1, 't111')
    insert into @articles values (1, 't1111')
    
    insert into @articles values (2, 't2')
    insert into @articles values (2, 't22')
    insert into @articles values (2, 't222')
    
    insert into @articles values (3, 't3')
    
    SELECT COUNT (A.articlesCatID) AS Articlecount, Ac.categoryName 
    FROM @articles A LEFT JOIN 
    @ArticlesCategories Ac ON Ac.articlesCatID = A.articlesCatID 
    GROUP BY Ac.categoryName
    


     

    Tuesday, October 20, 2009 5:21 PM
  • User1096912014 posted

    I don't see anything wrong in the select statement. Did you try running it first directly in SSMS? 

    Tuesday, October 20, 2009 5:22 PM
  • User1203612064 posted

    Hello, in SSMS your code work fine. but i can't make it work on the website. PLEASE take a look on the code.......

    <asp:Repeater runat="server" DataSourceID="SqlDataSource50">
    <ItemTemplate>
    <asp:Table runat="server" style="width:720px; height:70px;" BorderColor="#cccccc" BorderStyle="Dotted" BorderWidth="1" CellPadding="4" CellSpacing="0">
    <asp:TableRow>
        <asp:TableCell style="width:70px; height:70px;"><img src="images/<%#DataBinder.Eval(Container.DataItem, "categoryImg")%>" style="width:70px; height:70px; border:0px" alt="img" /></asp:TableCell>
        <asp:TableCell style="width:580px; height:70px;" VerticalAlign="Middle" HorizontalAlign="Right">
        <a href="Article_Category.aspx?ArticlesCatID=<%# Eval("ArticlesCatID") %>" style="text-decoration:none"><asp:Label ID="Label2" runat="server" Text='<%# Bind("categoryName") %>' ForeColor="#b8cc0b" Font-Names="Arial" Font-Bold="true" style="font-size:11pt;"/></a><br />
        <asp:Label ID="Label1" runat="server" Text='<%# Bind("categoryDescription") %>' ForeColor="#000000" Font-Bold="false" Font-Names="Arial" style="font-size:9pt;"/>
        </asp:TableCell>
        <asp:TableCell style="width:70px; height:70px; background-color:#ececec" HorizontalAlign="Center" VerticalAlign="Middle">
        <asp:FormView runat="server" DataSourceID="SqlDataSource51" >
        <ItemTemplate>
        <asp:Label ID="Label6" runat="server" ForeColor="#336699" Font-Size="Small" Font-Names="Arial" Font-Bold="true" Text='<%# Bind("Articlecount") %>' /><br />
        </ItemTemplate>
        </asp:FormView>
        <asp:Label ID="Label3" runat="server" Text="מאמרים" ForeColor="#000000" Font-Bold="false" Font-Names="Arial" style="font-size:9pt;"/>
        </asp:TableCell>
    </asp:TableRow>
    </asp:Table><br />
    </ItemTemplate>
    </asp:Repeater>
    <asp:SqlDataSource ID="SqlDataSource50" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [articlesCategories] ORDER BY [articlesCatID]">
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataSource51" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT COUNT (A.articlesCatID) AS Articlecount, Ac.categoryName 
    FROM articles A LEFT JOIN 
    ArticlesCategories Ac ON Ac.articlesCatID = A.articlesCatID 
    GROUP BY Ac.categoryName">
    </asp:SqlDataSource>


     

    Tuesday, October 20, 2009 5:31 PM
  • User1096912014 posted

    Your repeater is using SQLDataSource50, but the correct SQLDataSource is SQLDataSource51. Could this be a problem? Or how do you connect SQLDataSource51 with your ASPX page? 

    Tuesday, October 20, 2009 5:34 PM
  • User1203612064 posted

    Im using the repeater to show the categories inside the repeater there is a formview to show the count the repeater use 50 and the formview use 51 

    Tuesday, October 20, 2009 5:37 PM
  • User77042963 posted

    Your code works fine but you need to set your FormView AllowPaging="true" to see other pages.

    Tuesday, October 20, 2009 5:39 PM
  • User1203612064 posted

    This is my problem... idon't know how to create count in repeater (the website have CMS so he can add more and more categories). if it was Fixed category i know how to count the records... 

    Tuesday, October 20, 2009 5:43 PM
  • User1203612064 posted

    I put the Allowpages code and it show me few links you can see it on http://www.shlachli.co.il/articles.aspx (its in hebrew but you can see the results) 

    Tuesday, October 20, 2009 5:49 PM
  • User1096912014 posted

    Nice, but should the page's numbers be from left to right?  

    Tuesday, October 20, 2009 5:57 PM
  • User1203612064 posted

    he he he... the numbers no need to be at all! it only need to be the top number which is the number of articles in this category. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 20, 2009 6:01 PM
  • User1096912014 posted

    I see the problem you mentioned on that page... All numbers are 2. 

    Tuesday, October 20, 2009 6:41 PM
  • User1096912014 posted

    I re-visited the page right now and everything seems to be correct. How did you fix it exactly? 

    Wednesday, October 21, 2009 1:57 PM
  • User1203612064 posted

    I did'nt find solution so in the mean time i canceled the repeater and open 11 categories and in each category i put count code. until i'll find some solution 

    Thursday, October 22, 2009 2:21 AM
  • User1145754719 posted

    All great answers here, but I'm still a little confused about this... Anyone can explian further?

    Friday, February 10, 2012 2:44 AM