ORDER BY in GridView. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

ORDER BY in GridView.

Author

Topic

davidshq
Posting Yak Master

119 Posts

Posted - 2005-12-02 : 22:11:02

I have an ORDER BY statement that looks like this:
select * from #tempfilter ORDER BY Type, Popularity2 DESC
Type and Popularity2 are both numeric values (1-10). What I want to happen is for all the Types of 1 to be grouped together, same for 2 and so on. Then within each group I want it to go from 10 to 1. So, for example:
title - popularity - type:
Dodgeball - 10 - 1
Floor Hockey - 8 - 1
Water Fight - 8 - 5
But what I'm getting is more like this:
Dodgeball - 10 - 1
Floor Hockey - 8 - 1
Kickball - 9 - 1
Water Fight - 8 - 5
The ORDER BY for Types is working fine, but, as the example above shows, the ORDER BY Popularity2 is not. Any ideas?
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-02 : 22:58:45

>>The ORDER BY for Types is working fine, but, as the example above shows, the ORDER BY Popularity2 is not.

Huh? The "incorrect" sample data is sorted *exactly* the way you are describing what you need.

It is ordering first by Type, then by popularity. How is it NOT ordering in that manner? Which row is out of order?

In your first example of "what you want", you left out "KickBall" completely -- where should that sort?

davidshq
Posting Yak Master

119 Posts

Posted - 2005-12-02 : 23:46:31

In the first example it should look like:
Dodgeball - 10 - 1
Kickball - 9 - 1
Floor Hockey - 8 - 1
But what I am getting is closer to:
Dodgeball - 10 - 1
Floor Hockey - 8 - 1
Kickball - 9 - 1
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

davidshq
Posting Yak Master

119 Posts

Posted - 2005-12-03 : 00:03:06

If you want to see the actual instance of this occurring. Go to gamesecretary.com, create an account, and the first page that shows the games will display the problem.
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-03 : 09:40:43

quote:
Originally posted by davidshq

In the first example it should look like:
Dodgeball - 10 - 1
Kickball - 9 - 1
Floor Hockey - 8 - 1
But what I am getting is closer to:
Dodgeball - 10 - 1
Floor Hockey - 8 - 1
Kickball - 9 - 1
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

What are the datatypes of those two sort columns? How about some actual DDL (CREATE TABLE statements) and some sample data (using INSERT) and a SELECT that re-creates the problem. Because right now, it is impossible to find out what the problem is without specific information.

davidshq
Posting Yak Master

119 Posts

Posted - 2005-12-03 : 09:59:06

Here is the Game table:
CREATE TABLE [dbo].[Game](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Title] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Popularity] [int] NULL,
[YourPopularity] [int] NULL,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Dangers] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Variants] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Source] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Last Played] [datetime] NULL,
[Type] [int] NULL,
[Approved] [smallint] NULL,
CONSTRAINT [PK_Game] PRIMARY KEY CLUSTERED
Here is the Popularity Table:
CREATE TABLE [dbo].[GamePopularity](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[uID] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[gID] [bigint] NULL,
[Popularity] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
The information in the Popularity table is dropped into a temporary table and then joined to the Game table. The Popularity field in the Game table is renamed to Popularity2 in the temporary table b/c there is already a Popularity field in the Game table.
David.

- http://www.gamesecretary.com/
- http://www.thehungersite.com/
- http://www.grid.org/

davidshq
Posting Yak Master

119 Posts

Posted - 2005-12-03 : 11:03:06

I realized - the GridView is simply ignoring my ORDER BY statement - err, only listening to half of it. It does the ORDERING of the Type column just fine, but instead of ORDERING the Popularity column, it instead ORDERS the ID column. Any ideas on how to get it to order the Popularity column instead of the Type column?
David.

- http://www.gamesecretary.com/
- http://www.thehungersite.com/
- http://www.grid.org/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-03 : 14:36:23

What code are you using to populate the grid view? Show the SQL you are executing, and the vb/c# code you are using to call that SQL and to fill up the gridview. All you need to do is specify the simple ORDER BY that you indicated that you want; the grid view itself shouldn't be doing any sorting at all. A simple DataReader as the datasource which is already sorted works great.

davidshq
Posting Yak Master

119 Posts

Posted - 2005-12-03 : 14:50:15

From what I understand, b/c I have AutoGenerateColumns set to false, it ignores my ORDER BY statement. I could be wrong? I ended up doing the following in my Page_Load section:
If Me.IsPostBack = False Then
GridView1.Sort("Type, Popularity2", SortDirection.Descending)
End If
This works fine as long as you only have two Columns, when you add three it begins doing quirky things. I think this is a bug in the GridView.Sort command.
David.

- http://www.gamesecretary.com/
- http://www.thehungersite.com/
- http://www.grid.org/

jhermiz

3564 Posts

Posted - 2005-12-03 : 21:00:18

How many times is jeff going to ask for you to post both the client and server code?

Some people just dont get it...we cant help you without code, sample data, ddl, all of that stuff. Without it we cannot help you anymore.

This goes for anyone, I'm not trying to be rude to you, but you keep telling us it doesn't work without showing any relevant code.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-03 : 22:09:51

David -- as Jon says, it may have been much easier if you actually gave relevant info to your problem. You started by saying that you were ordering using a SQL statement, even going so far as to show us a SELECT w/ an ORDER BY clause, and then only after much prodding did you post code that implied that you were using the SORT property of the gridview class... after saying over and over that you were using ORDER BY !

Your subject should have been "Trouble with SORT method of a grid view" and your question should have been "I am having trouble using 2 columns with the SORT method in a gridview object in ASP.NET 2.0. .. etc ... ". Don't you think that would have made it a little easier for us to help you out?

davidshq
Posting Yak Master

119 Posts

Posted - 2005-12-07 : 14:15:06

Sorry. I wasn't aware initially that the problem was with the GridView, I thought it was my ORDER BY statement. Only half-way through the posting conversation while I was doing further testing did I discover the problem was with the GridView SORT and not ORDER BY.
David.

- http://www.gamesecretary.com/
- http://www.thehungersite.com/
- http://www.grid.org/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources