ASP DataGrid Sorting

[Mark Travis]

Hi all,

I have written a simple Web Application that displays a query result onto a
page using the ASP DataGrid.

To Digress
=======
Development information about the page is as follows
1. The database used is SqlServer 2000.
2. A Stored Procedure is dragged onto the page from the Server Explorer
creating a connection and command object.
3. An adapter object is dragged onto the page from the Toolbox and the
SelectCommand property is assigned the command object generated in step 2
4. A dataset object is created from the adapter object
5. In the page load, the relevent Adapter.Fill(dataset) and Grid.ReBind()
functions are called.

The above leads to a web page that displays the information retrieved by the
Stored Procedure.

The Problem
=========
I have attempted to add sorting to the Grid. I have selected what is
required to receive the Grid_SortCommand event in my page object and the
e.SortExpression is returning the name of the field selected but any
attempts to sort the data based on the SortExpression fails.
The line I am using to sort is
Dataset.Tables(0).DefaultView.Sort = e.SortExpression
This is run after the dataset has been filled by the adapter but before the
grid is rebinded to the dataset.

The Question(s)
===========
Can a grid be sorted when it is populated by a Stored Procedure? (cringe if
this is so)
Or; Have I failed to take something fundamental into account?

All the examples I have found work with dynamically created
dataviews/datatables.

Any help would be greatly appreciated.

Thanks in advance.

Mark

 

[Teemu Keiski]

Hi,

the sorting would generally happen in the datasource, DataGrid itself cannot
handle it on server automatically, that's why examples usually have
DataViews for sorting. Certainly you could sort, if you'd pass parameter to
the SP which indicates which sort order is used, but that might be bit
clumsy solution and needs lots of 'If' checking (unless you use dynamic SQL
in proc), of course this depends on what you are allowed to and can do.

This example uses the dynamic SQL and parameter passing technique:
http://www.dotnetjunkies.com/Article/B7C5AB3C-4D2E-4800-A071-6F40D57699C3.dcik

There are solutions which utilize the sorting on client-side, here is one:
http://www.eggheadcafe.com/articles/20021022b.asp

 

[Steven Cheng[MSFT]]

Hi Mark,

As for the sorting problem you mentioned, I don't think the problem is on
the DataAdapter or Stored procedure. I've also done test on my side.
Retrieving datas from sqlserver via StoreProcedure and the DataAdapter
generated via thte Wizard in VS.NET. And bing onto a webform datagrid with
sorting, it works well. I think we can check the following things first:
1. What's the SortExpression you set for the columns in the DataGrid? The
expression should noramally be the columns name in the datatable. If
they're not idential, the sorting won't take place.

2. What's the columns's datatype you're sorting on? Some particular column
type such as binary data dosn't support simple sorting. If the column's
data type is normal string or numeric type, I think it should work.

Please have a further check and feel free to post here if you have any
other findings. Thanks.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Get Preview at ASP.NET whidbey
http://msdn.microsoft.com/asp.net/whidbey/default.aspx

 

[Joe Fallon]

I have a collection object bound to my grid and after the sort command is
raised I call my doDataBind method (which mnay other events also call). In
that method I sort my collection based on the value clicked and then make it
the data source for the grid and then call databind on the grid.

 

[Mark Travis]

Hi Steven,

Thanks for the reply. I looked into the points raised by your response and I
managed to make no progress at all so what I have done is create a small
test to try a figure out where I was going wrong. I have done what I did
before but instead of using a Stored Procedure, I have dragged a table onto
the page to create the adapter (may as well start with the basics). The grid
also auto creates the columns instead of using only the columns I want.

Unfortunately, this still does not sort. I am obviously missing something
simple or misunderstanding the technology. Any help would be greatly
appreciated.

The html is as follows

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm1.aspx.vb"
Inherits="WebApplication2.WebForm1"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>WebForm1</title>
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<aspataGrid id="DataGrid1" style="Z-INDEX: 101; LEFT: 112px; POSITION:
absolute; TOP: 144px" runat="server" DataSource="<%# DataSet11 %>"
DataMember="Table1" DataKeyField="ID" AllowSorting="True" Width="272px">
</aspataGrid>
</body>
</HTML>


The code is as follows

Public Class WebForm1
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
'
' Code removed for brevity
'
End Sub

Protected WithEvents SqlSelectCommand1 As
System.Data.SqlClient.SqlCommand
Protected WithEvents SqlInsertCommand1 As
System.Data.SqlClient.SqlCommand
Protected WithEvents SqlUpdateCommand1 As
System.Data.SqlClient.SqlCommand
Protected WithEvents SqlDeleteCommand1 As
System.Data.SqlClient.SqlCommand
Protected WithEvents SqlConnection1 As
System.Data.SqlClient.SqlConnection
Protected WithEvents SqlDataAdapter1 As
System.Data.SqlClient.SqlDataAdapter
Protected WithEvents DataSet11 As WebApplication2.DataSet1
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid

'
' Code removed for brevity
'

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
If (Not Me.IsPostBack) Then _
Z_BindDataGrid()
End Sub
Private Sub DataGrid1_SortCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles
DataGrid1.SortCommand
DataSet11.Table1.DefaultView.Sort = e.SortExpression
Z_BindDataGrid()
End Sub
Private Sub Z_BindDataGrid()
SqlDataAdapter1.Fill(DataSet11)
DataGrid1.DataBind()
End Sub

End Class

Thanks

Mark

 

[Mark Travis]

Hi Teemu,

Thanks for the response. The first solution is my last resort. I had already
considered this but I thought I must be doing something wrong. It's had to
believe that Microsoft doesnt' support sorting a dataset based on Stored
Procedure. The second solution is very neat but unfortunately my data spans
pages.

As I said in my reply to Steven Cheng, I am either missing something very
simple or miss understanding the technology. All my knowledge is gained of
online documentation and the web. I think I will go out and hit a book shop
tonight.

Thanks again

Mark

 

[Steven Cheng[MSFT]]

Hi Mark,

Thanks for your response and the detailed code you provided. After reading
your code , I think you may have a check on the following point, your
grid's SortComand handler and binddata function is as below:

===================================
Private Sub DataGrid1_SortCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles
DataGrid1.SortCommand
DataSet11.Table1.DefaultView.Sort = e.SortExpression
Z_BindDataGrid()
End Sub
Private Sub Z_BindDataGrid()
SqlDataAdapter1.Fill(DataSet11)
DataGrid1.DataBind()
End Sub
=====================================

that means, you do the following things when do sorting:
1. set the datatable's DefaultView's Sort property (this is the most
important for sortin)
2. Fill the dataset again from the database via dataadapter
3. bind the default view to datagrid.

I suggest that you change it to the following order:
1. Fill the dataset again from database
2. set the datatable's defaultview's "Sort" property( after refilling the
data from dataadapter)
3.Bind to the datagrid.

In addition, I've also done a simple test on my side use the "PUBS" SAMPLE
database in sqlserver , I also use the wizard in VS.NET to generate the
connection and dataadapter( drag a table,some specified columns by CTRL+
right click the wanted columns), but I use a untyped dataset, the test page
code is as below, you may also have a test on your side if you feel
necessary:
#(Do remember to change the connection string regarding on the database
server address on your side )

=============aspx page=================
<HTML>
<HEAD>
<title>SortingGrid</title>
<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema"
content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<table width="100%" align="center">
<tr>
<td>
<aspataGrid id="dgSort" runat="server" AllowSorting="True"
AutoGenerateColumns="False">
<Columns>
<asp:BoundColumn DataField="title_id" SortExpression="title_id"
HeaderText="TitleId"></asp:BoundColumn>
<asp:BoundColumn DataField="title" SortExpression="title"
HeaderText="Title"></asp:BoundColumn>
<asp:BoundColumn DataField="type" SortExpression="type"
HeaderText="Type"></asp:BoundColumn>
<asp:BoundColumn DataField="pub_id" SortExpression="pub_id"
HeaderText="PubId"></asp:BoundColumn>
<asp:BoundColumn DataField="price" SortExpression="price"
HeaderText="Price"></asp:BoundColumn>
</Columns>
</aspataGrid></td>
</tr>
<tr>
<td></td>
</tr>
</table>
</form>
</body>
</HTML>


==========code behind======================
Public Class SortingGrid
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand
Me.conn = New System.Data.SqlClient.SqlConnection
Me.adapter = New System.Data.SqlClient.SqlDataAdapter
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "SELECT title_id, title, type,
pub_id, price FROM titles"
Me.SqlSelectCommand1.Connection = Me.conn
'
'SqlInsertCommand1
'
Me.SqlInsertCommand1.CommandText = "INSERT INTO titles(title_id,
title, type, pub_id, price) VALUES (@title_id, @titl" & _
"e, @type, @pub_id, @Price); SELECT title_id, title, type, pub_id,
price FROM tit" & _
"les WHERE (title_id = @title_id)"
Me.SqlInsertCommand1.Connection = Me.conn
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@title_id",
System.Data.SqlDbType.VarChar, 6, "title_id"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@title", System.Data.SqlDbType.VarChar,
80, "title"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@type", System.Data.SqlDbType.VarChar,
12, "type"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@pub_id",
System.Data.SqlDbType.VarChar, 4, "pub_id"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Price", System.Data.SqlDbType.Money,
8, "price"))
'
'SqlUpdateCommand1
'
Me.SqlUpdateCommand1.CommandText = "UPDATE titles SET title_id =
@title_id, title = @title, type = @type, pub_id = @p" & _
"ub_id, price = @Price WHERE (title_id = @Original_title_id) AND
(price = @Origin" & _
"al_price OR @Original_price IS NULL AND price IS NULL) AND (pub_id
= @Original_p" & _
"ub_id OR @Original_pub_id IS NULL AND pub_id IS NULL) AND (title =
@Original_tit" & _
"le) AND (type = @Original_type); SELECT title_id, title, type,
pub_id, price FRO" & _
"M titles WHERE (title_id = @title_id)"
Me.SqlUpdateCommand1.Connection = Me.conn
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@title_id",
System.Data.SqlDbType.VarChar, 6, "title_id"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@title", System.Data.SqlDbType.VarChar,
80, "title"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@type", System.Data.SqlDbType.VarChar,
12, "type"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@pub_id",
System.Data.SqlDbType.VarChar, 4, "pub_id"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Price", System.Data.SqlDbType.Money,
8, "price"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_title_id",
System.Data.SqlDbType.VarChar, 6, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "title_id",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_price",
System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "price",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_pub_id",
System.Data.SqlDbType.VarChar, 4, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "pub_id",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_title",
System.Data.SqlDbType.VarChar, 80, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "title",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_type",
System.Data.SqlDbType.VarChar, 12, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "type",
System.Data.DataRowVersion.Original, Nothing))
'
'SqlDeleteCommand1
'
Me.SqlDeleteCommand1.CommandText = "DELETE FROM titles WHERE
(title_id = @Original_title_id) AND (price = @Original_p" & _
"rice OR @Original_price IS NULL AND price IS NULL) AND (pub_id =
@Original_pub_i" & _
"d OR @Original_pub_id IS NULL AND pub_id IS NULL) AND (title =
@Original_title) " & _
"AND (type = @Original_type)"
Me.SqlDeleteCommand1.Connection = Me.conn
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_title_id",
System.Data.SqlDbType.VarChar, 6, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "title_id",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_price",
System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "price",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_pub_id",
System.Data.SqlDbType.VarChar, 4, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "pub_id",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_title",
System.Data.SqlDbType.VarChar, 80, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "title",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_type",
System.Data.SqlDbType.VarChar, 12, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "type",
System.Data.DataRowVersion.Original, Nothing))
'
'conn
'
'#####-----------------------------------------------#####
'##### REPLACE THE CONNECTIONSTRING WITH YOUR OWN #####
'#####-----------------------------------------------#####
Me.conn.ConnectionString = "workstation id=""SHA-SCHANG-01"";packet
size=4096;user id=s;integrated security=SSP" & _
"I;data source=""(local)"";persist security info=False;initial
catalog=pubs"
'
'adapter
'
Me.adapter.DeleteCommand = Me.SqlDeleteCommand1
Me.adapter.InsertCommand = Me.SqlInsertCommand1
Me.adapter.SelectCommand = Me.SqlSelectCommand1
Me.adapter.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "titles", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("title_id", "title_id"), New
System.Data.Common.DataColumnMapping("title", "title"), New
System.Data.Common.DataColumnMapping("type", "type"), New
System.Data.Common.DataColumnMapping("pub_id", "pub_id"), New
System.Data.Common.DataColumnMapping("price", "price")})})
Me.adapter.UpdateCommand = Me.SqlUpdateCommand1

End Sub
Protected WithEvents dgSort As System.Web.UI.WebControls.DataGrid
Protected WithEvents SqlSelectCommand1 As
System.Data.SqlClient.SqlCommand
Protected WithEvents SqlInsertCommand1 As
System.Data.SqlClient.SqlCommand
Protected WithEvents SqlUpdateCommand1 As
System.Data.SqlClient.SqlCommand
Protected WithEvents SqlDeleteCommand1 As
System.Data.SqlClient.SqlCommand
Protected WithEvents conn As System.Data.SqlClient.SqlConnection
Protected WithEvents adapter As System.Data.SqlClient.SqlDataAdapter

'NOTE: The following placeholder declaration is required by the Web
Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then

Dim ds As DataSet = GetDataSource()
dgSort.DataSource = ds.Tables(0).DefaultView
dgSort.DataBind()

End If
End Sub


Protected Function GetDataSource() As DataSet

Dim ds As New DataSet
Me.adapter.Fill(ds)

Return ds

End Function

Private Sub dgSort_SortCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles
dgSort.SortCommand

Dim ds As DataSet = GetDataSource()
Dim dt As DataTable = ds.Tables(0)
dt.DefaultView.Sort = e.SortExpression

dgSort.DataSource = dt.DefaultView
dgSort.DataBind()

End Sub
End Class
===========================================

Thanks.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Get Preview at ASP.NET whidbey
http://msdn.microsoft.com/asp.net/whidbey/default.aspx

 

[Mark Travis]

Hi Steve,

Thanks for the information. I have managed to solve the problem. The key was
to dynamically binding the grid to a dataview.

To Digress:
In the designer view, I had set the datasource of the datagrid to a dataset
created from an adapter. With this set, regardless of what I did to the
datasource of the datagrid, I could not sort.

The Solution:
Clearing the datasource of the datagrid allowed my code to successfully sort
the grid using the following code

Private Sub DataGrid1_SortCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles
DataGrid1.SortCommand

Z_BindDataGrid(e.SortExpression)

End Sub

Private Sub Z_BindDataGrid(Optional byval sSortExpression as string =
"")

SqlDataAdapter1.Fill(DataSet11)
If (String.Empty <> sSortExpression) then _
DataSet11.Table1.DefaultView.Sort = sSortExpression
DataGrid1.DataSource = DataSet11.Tables(0).DefaultView
DataGrid1.DataBind()

End Sub

This works a treat. It seems everything hung on the fact that I had bound
the grid to the dataset at design time.

Note 1: The DataSet11 is the dataset I created from the adapter at design
time, not an untyped dataset which is probably the reason for Note 2.
Note 2: Further testing showed that the DefaultView.Sort can be set before
filling the dataset.

Thanks to all for the responses to this thread

Thanks again Steve

Mark

 

[Steven Cheng[MSFT]]

Hi Mark,

Thanks for your followup. I'm glad that you've figured out problem. And I
shall say that I did haven't noticed that you didn't reset the DataGrid's
DAtaSource in your Z_BindDataGrid() method original and that's the excat
cause .

Private Sub Z_BindDataGrid()
SqlDataAdapter1.Fill(DataSet11)
DataGrid1.DataBind()
End Sub

Anyway, thanks again for your kind response. Have a good day!

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Get Preview at ASP.NET whidbey
http://msdn.microsoft.com/asp.net/whidbey/default.aspx