469,604 Members | 2,376 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,604 developers. It's quick & easy.

I'm lazy: how do I make the first databound record not display/chop off the first element from SqlDataSource

I'm using ASP.NET 2.0, C#, SQL Server 2005, and databound controls like the
DataList and the GridView. Right now I'm using SqlDataSource as part of very
quick and dirty site; I'm not looking to tier things.

I want the topmost/first data element in a particular control to NOT be
displayed. This should be possible by either telling the renderer to hide
the first element programmatically, or by deleting it from the data store
that it has grabbed. I suppose I could also write a stored procedure to only
return the data I wanted, or accomplish this through programming, but I am
curious about what solutions are possible that a) are as easy as possible,
and b) still permit me to databound controls declaratively.

Any suggestions on how I could do this?

Thank you,
Ken Fine

Jun 22 '06 #1
7 1618
Ken,

Handle PreRender event. In the event the items collection is already fully
build and you can easily navigate to the first item and hide it with either
Visible property or css rule display:none.

Eliyahu

<ke*****@nospam.nospam> wrote in message
news:uw**************@TK2MSFTNGP04.phx.gbl...
I'm using ASP.NET 2.0, C#, SQL Server 2005, and databound controls like
the DataList and the GridView. Right now I'm using SqlDataSource as part
of very quick and dirty site; I'm not looking to tier things.

I want the topmost/first data element in a particular control to NOT be
displayed. This should be possible by either telling the renderer to hide
the first element programmatically, or by deleting it from the data store
that it has grabbed. I suppose I could also write a stored procedure to
only return the data I wanted, or accomplish this through programming, but
I am curious about what solutions are possible that a) are as easy as
possible, and b) still permit me to databound controls declaratively.

Any suggestions on how I could do this?

Thank you,
Ken Fine

Jun 22 '06 #2
Thank you. I have been unable to make this work in spite of a fair bit of
reading and hacking around. This seems the most germane:
http://www.codecomments.com/archive3...10-657712.html

My efforts at being lazy are quickly being thwarted ;). I would be just as
happy knowing how to intercept the DataSet that is returned by SqlDataSource
and programmatically whacking off the topmost row. Any suggestions? One
thing that isn't clear to me is what that DataSet ends up being named, and
when it becomes accessible in the page lifecycle, when you do things
directly/lazily using SqlDataSource.

-KF

"Eliyahu Goldin" <re*************@monarchmed.com> wrote in message
news:e7*************@TK2MSFTNGP05.phx.gbl...
Ken,

Handle PreRender event. In the event the items collection is already fully
build and you can easily navigate to the first item and hide it with
either Visible property or css rule display:none.

Eliyahu

<ke*****@nospam.nospam> wrote in message
news:uw**************@TK2MSFTNGP04.phx.gbl...
I'm using ASP.NET 2.0, C#, SQL Server 2005, and databound controls like
the DataList and the GridView. Right now I'm using SqlDataSource as part
of very quick and dirty site; I'm not looking to tier things.

I want the topmost/first data element in a particular control to NOT be
displayed. This should be possible by either telling the renderer to hide
the first element programmatically, or by deleting it from the data store
that it has grabbed. I suppose I could also write a stored procedure to
only return the data I wanted, or accomplish this through programming,
but I am curious about what solutions are possible that a) are as easy as
possible, and b) still permit me to databound controls declaratively.

Any suggestions on how I could do this?

Thank you,
Ken Fine


Jun 22 '06 #3
Hi,

Thank you for your post.

SqlDataSource has a DataSourceMode property which by default set to DataSet
mode, another mode is DataReader mode which uses IDataReader object. Based
on my research, there's no way to get the internal DataSet object of
SqlDataSource when the DataSourceMode set to DataSet.

I think currently the workaround using CSS rules to hide the rows is the
most acceptable one.

Please feel free to post here if anything is unclear.

Regards,
Walter Wang
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

Jun 23 '06 #4
So I found two solutions, one OK, one good, and both in keeping with the
"quick and dirty" premise of my original question.

The OK solution is using CSS to hide an element, but it comes with a cavet
on a two-column datalist.
The good solution for my purposes is to write an inline SQL subquery to
define the datasource.

Details follow.

SOLUTION #1: DATALIST ITEM SET TO "HIDDEN"
For the CSS/Hidden hack, do as follows in the .ascx .cs code:

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>

SOLUTION #2: WRITE A SUBQUERY
I didn't want to write a sproc, but I don't mind a good query. A subquery
will do the trick

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

Thanks to everyone for their help.

-KF

<ke*****@nospam.nospam> wrote in message
news:eQ**************@TK2MSFTNGP05.phx.gbl...
Thank you. I have been unable to make this work in spite of a fair bit of
reading and hacking around. This seems the most germane:
http://www.codecomments.com/archive3...10-657712.html

My efforts at being lazy are quickly being thwarted ;). I would be just as
happy knowing how to intercept the DataSet that is returned by
SqlDataSource and programmatically whacking off the topmost row. Any
suggestions? One thing that isn't clear to me is what that DataSet ends up
being named, and when it becomes accessible in the page lifecycle, when
you do things directly/lazily using SqlDataSource.

-KF

"Eliyahu Goldin" <re*************@monarchmed.com> wrote in message
news:e7*************@TK2MSFTNGP05.phx.gbl...
Ken,

Handle PreRender event. In the event the items collection is already
fully build and you can easily navigate to the first item and hide it
with either Visible property or css rule display:none.

Eliyahu

<ke*****@nospam.nospam> wrote in message
news:uw**************@TK2MSFTNGP04.phx.gbl...
I'm using ASP.NET 2.0, C#, SQL Server 2005, and databound controls like
the DataList and the GridView. Right now I'm using SqlDataSource as part
of very quick and dirty site; I'm not looking to tier things.

I want the topmost/first data element in a particular control to NOT be
displayed. This should be possible by either telling the renderer to
hide the first element programmatically, or by deleting it from the data
store that it has grabbed. I suppose I could also write a stored
procedure to only return the data I wanted, or accomplish this through
programming, but I am curious about what solutions are possible that a)
are as easy as possible, and b) still permit me to databound controls
declaratively.

Any suggestions on how I could do this?

Thank you,
Ken Fine



Jun 23 '06 #5

Thanks much, Walter. That's an interesting limit of SqlDataSource. Is there
a design premise for why this internal dataset would not be exposed? Maybe
MSFT wants to avoid breaking something known by n00bs to be bindable in the
IDE? Or is it some other consideration?

All of the ADO.NET books go on and on about the DataSet's in-memory
capabilities; it's a little funny that you can't get at it when
SqlDataSource grabs one.

-KF

"Walter Wang [MSFT]" <wa****@online.microsoft.com> wrote in message
news:4I**************@TK2MSFTNGXA01.phx.gbl...
Hi,

Thank you for your post.

SqlDataSource has a DataSourceMode property which by default set to
DataSet
mode, another mode is DataReader mode which uses IDataReader object. Based
on my research, there's no way to get the internal DataSet object of
SqlDataSource when the DataSourceMode set to DataSet.

I think currently the workaround using CSS rules to hide the rows is the
most acceptable one.

Please feel free to post here if anything is unclear.

Regards,
Walter Wang
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no
rights.

Jun 23 '06 #6
Hi,

Thank you for your summary on this. This will benefit the community a lot.

ObjectDataSource is another DataSource object in ASP.NET 2.0 which has many
great features. You can find more information about ObjectDataSource here:

#DataSource Controls and Declarative Programming
http://www.nikhilk.net/DataSourceControlThoughts.aspx

#Data Points: Data Source Controls in ASP.NET 2.0
http://msdn.microsoft.com/msdnmag/is...01/DataPoints/

In your case about removing the top rows with the easiest way, I think
ObjectDataSource can help you do this with a strong typed DataSet. Here's
some brief steps:

1) In Visual Studio 2005, add a new DataSet object, follow the wizard to
configure it, you can choose to use SQL statements or Stored Procedures;
2) On the WebForm, add an ObjectDataSource and configure it to use the
generated DataSet's TableAdapter;
3) Bind a GridView to this ObjectDataSource;
4) Handle ObjectDataSource's Selected Event:

protected void ObjectDataSource1_Selected(object sender,
ObjectDataSourceStatusEventArgs e)
{
DataSet2.EmployeesDataTable dt = e.ReturnValue as
DataSet2.EmployeesDataTable;
for (int i = 0; i < 8; i++)
{
dt.Rows.RemoveAt(0);
}
}

This will remove top 8 rows from the result DataTable.

Hope this helps. Please feel free to post here if anything is unclear.
Regards,
Walter Wang (wa****@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

Jun 24 '06 #7
Hi,

It is by intentional design that there is no Page-level access to the data
returned from a data source control, since the interface for selecting data
is intended only for data-bound controls. If you need to have access to the
DataSet before it is returned to the data-bound control, consider using
ObjectDataSource so your SelectMethod can retrieve the DataSet in code.

Hope this helps. Please feel free to post here if anything is unclear.

Regards,
Walter Wang (wa****@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

Jun 28 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by blackhawk | last post: by
2 posts views Thread by cider123 | last post: by
1 post views Thread by David C | last post: by
reply views Thread by guiromero | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.