"hard" ASP.NET or SQL: how to programmatically strip first row of a datalist, or delete top row of a SqlDataSource's dataset

Report · Jun 22, 2006

I'm using ASP.NET 2.0 and C#.

I'd like to do any of the following which might solve my problem:

a) programmatically hide the first row of a datalist. I've looked carefully
at this and other resources, but have been unable to translate the advice
into functional code:
http://www.codecomments.com/archive320-2005-10-657712.html

b) I'm using a quick-and-dirty SqlDataSource. It returns a DataSet, though I
can't figure out at what point in the page lifecycle this happens, or what
that DataSet is named. If I could programmatically delete the top row, that
would do what I need.

c) If I could write an inline SQL query to grab everything but the first
record, that would work, too. Note that for the purposes of being stubborn
😉 I don't want to resort to a stored procedure, which would easily solve my
problem. I'm using SQL Server 2005.

Any suggestions, preferably with code? Should be a very simple problem...

-KF

Report · Jun 22, 2006

Ken

I have not tried to see if it works but the suggestion from your first link
of using CSS seems to be the simplest. The reason that you may have not been
able to get it to work is that the code looks like 1.1. You may find this
article useful on Custom Formating in a Gridview.

http://www.asp.net/learn/dataaccess/tutorial11cs.aspx?tabid=63

Essentially the same elements will work for the Datalist.

In answer to the second question the SQLDataSource is fired on the page
load.

--
Paul Whitham
Certified Dreamweaver MX2004 Professional
Adobe Community Expert - Dreamweaver

Valleybiz Internet Design
www.valleybiz.net

"Ken Fine" <kenfine@u.washington.edu> wrote in message
news:e7f0ob$s3i$1@forums.macromedia.com...
> I'm using ASP.NET 2.0 and C#.
>
> I'd like to do any of the following which might solve my problem:
>
> a) programmatically hide the first row of a datalist. I've looked
> carefully at this and other resources, but have been unable to translate
> the advice into functional code:
> http://www.codecomments.com/archive320-2005-10-657712.html
>
> b) I'm using a quick-and-dirty SqlDataSource. It returns a DataSet, though
> I can't figure out at what point in the page lifecycle this happens, or
> what that DataSet is named. If I could programmatically delete the top
> row, that would do what I need.
>
> c) If I could write an inline SQL query to grab everything but the first
> record, that would work, too. Note that for the purposes of being stubborn
> 😉 I don't want to resort to a stored procedure, which would easily solve
> my problem. I'm using SQL Server 2005.
>
> Any suggestions, preferably with code? Should be a very simple problem...
>
> -KF
>

Report · Jun 22, 2006

Hey Ken,

Figure out a solution for that problem. As Paul mentioned, that CSS hack
might be the way to go because of the apparent bug mentioned in the
codecomments post.

If you want to try something else, post a little code showing the dataset
retrieval and the binding to the datalist. I'll try duplicating that here
and messing with it.

Ron

"Ken Fine" <kenfine@u.washington.edu> wrote in message
news:e7f0ob$s3i$1@forums.macromedia.com...
> I'm using ASP.NET 2.0 and C#.
>
> I'd like to do any of the following which might solve my problem:
>
> a) programmatically hide the first row of a datalist. I've looked
> carefully at this and other resources, but have been unable to translate
> the advice into functional code:
> http://www.codecomments.com/archive320-2005-10-657712.html
>
> b) I'm using a quick-and-dirty SqlDataSource. It returns a DataSet, though
> I can't figure out at what point in the page lifecycle this happens, or
> what that DataSet is named. If I could programmatically delete the top
> row, that would do what I need.
>
> c) If I could write an inline SQL query to grab everything but the first
> record, that would work, too. Note that for the purposes of being stubborn
> 😉 I don't want to resort to a stored procedure, which would easily solve
> my problem. I'm using SQL Server 2005.
>
> Any suggestions, preferably with code? Should be a very simple problem...
>
> -KF
>

Report · Jun 22, 2006

Use the following code inside the datalists ItemCreated event procedure

C#
if(e.Item.ItemIndex == 0)
{
e.Item.Visible = false;
}

VB
If e.ItemIndex = 0 Then
e.Item.Visible = False
End If

--
Kevin Marshall
WebXeL.com Ltd
http://www.webxel.com

ASP.NET Dreamweaver Extensions
http://www.webxel-dw.co.uk

Report · Jun 23, 2006

SQL for all data apart from first row:

select * from t where id not in (
select top 1 id from t order by id
) order by id

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

Report · Jun 23, 2006

Thanks to Kevin and Julian.

Kevin's code worked, as follows, but led to unexpected issues. First, the
solution:
protected void DataList2_ItemCreated(object sender, DataListItemEventArgs e)
{
if (e.Item.ItemIndex == 0)
{
e.Item.CssClass = "Hidden";

}
}

on the .ascx I deployed this to:

<asp:DataList ID="DataList2" runat="server" DataSourceID="SqlDataSource2"
RepeatColumns="2" CellPadding="6" Width="87%" RepeatDirection="Horizontal"
OnItemCreated="DataList2_ItemCreated">

... and on the master page, in the <head> section:

<style>
.Hidden {display:none;}
</style>

This works fine. The issue you will run into is that a DataList set to two
RepeatColumns will render somewhat strangly if you hide the topmost element.
What you want and need in most cases is to fix the data stream in the source
SQL query or in the DataSet, which Julian's code does.

Thanks for the help, everyone.

-KF

"Julian Roberts" <nospam@charon.co.uk> wrote in message
news:e7gope$54p$1@forums.macromedia.com...
> SQL for all data apart from first row:
>
> select * from t where id not in (
> select top 1 id from t order by id
> ) order by id
>
> --
> Jules
> http://www.charon.co.uk/charoncart
> Charon Cart 3
> Shopping Cart Extension for Dreamweaver MX/MX 2004
>
>
>
>

Report · Jun 23, 2006

BTW, here's a real-world subquery -- as described by Julian -- that makes
the magic happen.

SELECT ContentID, Title, Subtitle
FROM Contentitems
WHERE (ContentID NOT IN
(SELECT TOP (1) ContentID
FROM Contentitems AS
Contentitems_1
WHERE (CategoryID = 34))) AND
(CategoryID = 34)
ORDER BY PubDate DESC

-KF

"Ken Fine" <kenfine@u.washington.edu> wrote in message
news:e7hgl2$4p4$1@forums.macromedia.com...
> Thanks to Kevin and Julian.
>
> Kevin's code worked, as follows, but led to unexpected issues. First, the
> solution:
> protected void DataList2_ItemCreated(object sender, DataListItemEventArgs
> e)
> {
> if (e.Item.ItemIndex == 0)
> {
> e.Item.CssClass = "Hidden";
>
> }
> }
>
> on the .ascx I deployed this to:
>
> <asp:DataList ID="DataList2" runat="server" DataSourceID="SqlDataSource2"
> RepeatColumns="2" CellPadding="6" Width="87%" RepeatDirection="Horizontal"
> OnItemCreated="DataList2_ItemCreated">
>
> ... and on the master page, in the <head> section:
>
> <style>
> .Hidden {display:none;}
> </style>
>
> This works fine. The issue you will run into is that a DataList set to two
> RepeatColumns will render somewhat strangly if you hide the topmost
> element. What you want and need in most cases is to fix the data stream in
> the source SQL query or in the DataSet, which Julian's code does.
>
> Thanks for the help, everyone.
>
> -KF
>
>
>
>
> "Julian Roberts" <nospam@charon.co.uk> wrote in message
> news:e7gope$54p$1@forums.macromedia.com...
>> SQL for all data apart from first row:
>>
>> select * from t where id not in (
>> select top 1 id from t order by id
>> ) order by id
>>
>> --
>> Jules
>> http://www.charon.co.uk/charoncart
>> Charon Cart 3
>> Shopping Cart Extension for Dreamweaver MX/MX 2004
>>
>>
>>
>>
>
>

Report · Jun 23, 2006

> This works fine. The issue you will run into is that a DataList set to two
> RepeatColumns

I never thought of that, I am used to manipulating items like this with the
repeater control, I forgot the datalist renders columns also :-)

--
Kevin Marshall
WebXeL.com Ltd
http://www.webxel.com

ASP.NET Dreamweaver Extensions
http://www.webxel-dw.co.uk