Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 3.5 > ASP.NET 3.5 Basics
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 3.5 Basics If you are new to ASP or ASP.NET programming with version 3.5, this is the forum to begin asking questions. Please also see the Visual Web Developer 2008 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 3.5 Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 16th, 2009, 04:00 PM
bex bex is offline
Friend of Wrox
 
Join Date: Aug 2008
Location: london, , United Kingdom.
Posts: 154
Thanks: 7
Thanked 1 Time in 1 Post
Default How to select top 5 Rows in db

Hi there i have this code:
Code:
SqlDataReader MyReader;
SqlConnection MyConnection = newSqlConnection();
MyConnection.ConnectionString =
ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand MyCommand = newSqlCommand();
MyCommand.CommandText = "SELECT News_date, Year, News_body FROM News_table";
MyCommand.CommandType = CommandType.Text;
MyCommand.Connection = MyConnection;
MyCommand.Connection.Open();
MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
MyReader.Read();
lblMonth.Text = MyReader.GetString(MyReader.GetOrdinal("News_date"));
lblYear.Text = MyReader.GetString(MyReader.GetOrdinal("Year"));
lblBody.Text = MyReader.GetString(MyReader.GetOrdinal("News_body"));
 
MyCommand.Dispose();
MyConnection.Dispose();
}
i need to add Select Top 5 From... and bind the rows in 5 different sets of labels, something like this:

Code:
SqlDataReader MyReader;
SqlConnection MyConnection = newSqlConnection();
MyConnection.ConnectionString =
ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand MyCommand = newSqlCommand();
MyCommand.CommandText = "SELECT Top 5 News_date, Year, News_body FROM News_table";
MyCommand.CommandType = CommandType.Text;
MyCommand.Connection = MyConnection;
MyCommand.Connection.Open();
MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
MyReader.Read();
 
lblMonth.Text = MyReader.GetString(MyReader.GetOrdinal("News_date"));
lblYear.Text = MyReader.GetString(MyReader.GetOrdinal("Year"));
lblBody.Text = MyReader.GetString(MyReader.GetOrdinal("News_body"));
 
lblMonth1.Text = MyReader.GetString(MyReader.GetOrdinal("News_date"));
lblYear1.Text = MyReader.GetString(MyReader.GetOrdinal("Year"));
lblBody1.Text = MyReader.GetString(MyReader.GetOrdinal("News_body"));
 
lblMonth2.Text = MyReader.GetString(MyReader.GetOrdinal("News_date"));
lblYear2.Text = MyReader.GetString(MyReader.GetOrdinal("Year"));
lblBody2.Text = MyReader.GetString(MyReader.GetOrdinal("News_body"));
 
lblMonth3.Text = MyReader.GetString(MyReader.GetOrdinal("News_date"));
lblYear3.Text = MyReader.GetString(MyReader.GetOrdinal("Year"));
lblBody3.Text = MyReader.GetString(MyReader.GetOrdinal("News_body"));
 
lblMonth4.Text = MyReader.GetString(MyReader.GetOrdinal("News_date"));
lblYear4.Text = MyReader.GetString(MyReader.GetOrdinal("Year"));
lblBody4.Text = MyReader.GetString(MyReader.GetOrdinal("News_body"));
 
MyCommand.Dispose();
MyConnection.Dispose();
}
i was able to get the top 5 in a ListView but the formating coming from CSS was not applied to the labels inside the ListView, that is why i need to use Labels, the div where the labels are . have a back image that i need to display.

thanks
__________________
bx
  #2 (permalink)  
Old March 16th, 2009, 06:31 PM
Friend of Wrox
Points: 894, Level: 11
Points: 894, Level: 11 Points: 894, Level: 11 Points: 894, Level: 11
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: London, , United Kingdom.
Posts: 166
Thanks: 2
Thanked 33 Times in 33 Posts
Default

I suspect you would want to use a Repeater here and as opposed to a ListView.
Either way it appears you are more having trouble with displaying the data rather then getting it out of the database. I wasn't entirely sure what you meant with the CSS not being applied. Could you possibly post the relevant bit of the page - the containing div and the labels - so we can get a better idea?

Thanks
Phil
  #3 (permalink)  
Old March 16th, 2009, 07:16 PM
bex bex is offline
Friend of Wrox
 
Join Date: Aug 2008
Location: london, , United Kingdom.
Posts: 154
Thanks: 7
Thanked 1 Time in 1 Post
Default

Code:
<divclass="news_item">
<divclass="news_date">
<divclass="news_month"><asp:LabelID="lblMonth"runat="server"></asp:Label></div>
<!-- end news_month-->
<divclass="news_year"><asp:LabelID="lblYear"runat="server"></asp:Label></div>
<!-- end news_year-->
</div>
<!-- end news_date-->
<divclass="news_text">
<asp:LabelID="lblText"runat="server"Text="Label"></asp:Label>
.
</div>
<!--end news_text-->
</div>
this is working fine as you can see i have 3 labels on top of each other , now if i use a repeater can i have 3 labels within these div's,

the format looks like this:

lblMonth-------| lblText---------------- |
lblYear---------| |fgfgfgfgfgfgdfgdfgu |
| -------------------|jhjghjhjghjhhjghjgh|
| ========= |nnnhjgfghfgdhjdfjd|
| | -----------------|yujydjdfghjyujyujyu|

Thanks
__________________
bx

Last edited by bex; March 16th, 2009 at 07:20 PM..
  #4 (permalink)  
Old March 16th, 2009, 07:51 PM
Friend of Wrox
Points: 894, Level: 11
Points: 894, Level: 11 Points: 894, Level: 11 Points: 894, Level: 11
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: London, , United Kingdom.
Posts: 166
Thanks: 2
Thanked 33 Times in 33 Posts
Default

Err right I think I see what you're looking for. Do you want to have that layout for one news item, then the same for each of the other four beneath it? e.g.

Quote:
January 09 -- Blah blah
==================
February 09 -- More blah
==================
.
... etc
You can put pretty much whatever HTML you like within the ItemTemplate, so it's not necessarily that which is causing the issue - it depends what your CSS rules are. Controls such as ListView and DetailsView do however add their own containing elements around their content, usually a nice unhelpful table, so it may be that which is breaking the css, hence why I suggested the Repeater, which doesn't add anything.
Assuming this is what you need, here is a full page which should do the trick. The main changes are adding the Repeater and setting its DataSource to the SqlDataReader. I have also changed the Labels to Literals as these do not add <span> tags around their text.

Code:
<%@ Page Language="C#" %>

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server">
void Page_Load(object sender, EventArgs e)
{
    SqlDataReader MyReader;
    SqlConnection MyConnection = new SqlConnection();
    MyConnection.ConnectionString =
        ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    SqlCommand MyCommand = new SqlCommand();
    MyCommand.CommandText = "SELECT Top 5 News_date, Year, News_body FROM News_table";
    MyCommand.CommandType = CommandType.Text;
    MyCommand.Connection = MyConnection;
    MyCommand.Connection.Open();
    MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);

    // OLD CODE
    //MyReader.Read();
    //lblMonth.Text = MyReader.GetString(MyReader.GetOrdinal("News_date"));
    //lblYear.Text = MyReader.GetString(MyReader.GetOrdinal("Year"));
    //lblBody.Text = MyReader.GetString(MyReader.GetOrdinal("News_body"));

    // NEW CODE
    NewsItems.DataSource = MyReader;
    NewsItems.DataBind();

    MyCommand.Dispose();
    MyConnection.Dispose();

}
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Repeater</title>
</head>
<body>
    <form id="form1" runat="server">

    <asp:Repeater runat="server" ID="NewsItems">
    <ItemTemplate>
        <div class="news_item">
            <div class="news_date">
                <div class="news_month">
                    <asp:Literal ID="lblMonth" runat="server" Text='<%# Eval("News_date") %>' />
                </div>
                <div class="news_year">
                    <asp:Literal ID="lblYear" runat="server" Text='<%# Eval("Year") %>' />
                </div>
            </div>
            <div class="news_text">
                <asp:Literal ID="lblBody" runat="server" Text='<%# Eval("News_Body") %>' />
            </div>
        </div>
    </ItemTemplate>
    </asp:Repeater>

    </form>
</body>
</html>
The whole Page_Load method would probably be better wrapped up in a SqlDataSource and setting the DataSourceID of the Repeater to that, but I've just based it off your code.

HTH
Phil
The Following User Says Thank You to philip_cole For This Useful Post:
bex (March 17th, 2009)
  #5 (permalink)  
Old March 17th, 2009, 04:32 AM
bex bex is offline
Friend of Wrox
 
Join Date: Aug 2008
Location: london, , United Kingdom.
Posts: 154
Thanks: 7
Thanked 1 Time in 1 Post
Default

great thanks.

if i used the SqlDatasource1 from the master i got an error in child page :
cannot find sqldatasource,
it works from page-load

i have one more thing to ask

the news coming from the db are set in 4 different categories, in my CSS i have 4 classes with 4 different back colors now when data is binded to the repeater lets say row 1 and 3 are category 1, 2 and 4 category 2,, data coming from category 1 will take the style from the css class with bg green and category 2 from the class with bg blue.

something like this:

if (News_category==1)
{
div class="green"
is it possible to assign Css classes depending on the category of the news?


thanks
__________________
bx
  #6 (permalink)  
Old March 17th, 2009, 08:28 AM
Friend of Wrox
Points: 894, Level: 11
Points: 894, Level: 11 Points: 894, Level: 11 Points: 894, Level: 11
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: London, , United Kingdom.
Posts: 166
Thanks: 2
Thanked 33 Times in 33 Posts
Default

OK there's 3 things to change.
First you need to get the category of the item out in your SQL - I assume its in the same table

sql Code:
SELECT Top 5 News_date, Year, News_body, News_category FROM News_table

Next you will need to change the news_item <div> into an <asp:Panel> so that you can do stuff with it in the code. the Panel rendered as a div so the html will be pretty much identical. Also add a handler for OnItemDataBound. This will be called each time just after an item is bound so you can do whatever tweaks you require.

Code:
<asp:Repeater runat="server" ID="NewsItems" OnItemDataBound="NewsItems_ItemDataBound">
<ItemTemplate>
  <asp:Panel runat="server" ID="News_Panel" CssClass="news_item">
      ...
  </asp:Panel>
The NewsItems_ItemDataBound method will use the current result, stored as an IDataRecord, to check the category and set the appropriate class

c# Code:
protected void NewsItems_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
    IDataRecord row = (IDataRecord)e.Item.DataItem;
    // setup the class name
    string catClass = "cat_" + row["News_category"].ToString();
    // add class name to current class on panel
    ((Panel)e.Item.FindControl("News_Panel")).CssClass += " " + catClass;
}

Note how we add to the CssClass so it will keep the general news_item style
  #7 (permalink)  
Old March 17th, 2009, 10:12 AM
bex bex is offline
Friend of Wrox
 
Join Date: Aug 2008
Location: london, , United Kingdom.
Posts: 154
Thanks: 7
Thanked 1 Time in 1 Post
Default

it works but i get back 4 rows instead of 5 ?

In VS 2008 in design view it shows 5 fields but in the browser it only displays 4
__________________
bx

Last edited by bex; March 17th, 2009 at 10:18 AM..
  #8 (permalink)  
Old March 17th, 2009, 12:30 PM
Friend of Wrox
Points: 894, Level: 11
Points: 894, Level: 11 Points: 894, Level: 11 Points: 894, Level: 11
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: London, , United Kingdom.
Posts: 166
Thanks: 2
Thanked 33 Times in 33 Posts
Default

VS shows 5 rows as an example of how it will look - it does not necessarily mean there will be 5 returned from the database. Just double check the SQL query you are running in SSMS and make sure it does actually get 5 rows.
If not, I'm not sure what's gone wrong between creating the Repeater and changing it

Last edited by philip_cole; March 17th, 2009 at 01:00 PM.. Reason: translated from outer mongolian [typos]
  #9 (permalink)  
Old March 17th, 2009, 02:00 PM
bex bex is offline
Friend of Wrox
 
Join Date: Aug 2008
Location: london, , United Kingdom.
Posts: 154
Thanks: 7
Thanked 1 Time in 1 Post
Default

sort it i used a SqlDatasource in design and it is working fine,

Thanks Phil
__________________
bx
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL to get all rows but top vjs2445 SQL Language 22 May 18th, 2007 06:44 AM
Trying to retrieve top 1 for multiple rows MonicaM SQL Language 2 May 16th, 2006 03:29 AM
SELECT TOP n NOT SELECTING TOP n! ibi SQL Language 8 March 30th, 2005 08:08 PM
Getting the Top 5 Rows from a datatable flyin General .NET 9 June 14th, 2004 08:36 AM
move rows to top with cells in columnA filled RED alienscript Excel VBA 2 December 2nd, 2003 12:51 PM



All times are GMT -4. The time now is 07:39 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.