Datagrid Sorting

.NET
1

Hi Guys cany anyone help me this

I have datagrid setup as below

<asp:DataGrid id=“dgTest” runat=“server” AllowSorting=“True” OnSortCommand=“dgSort”></asp:DataGrid>

The code behind is as follows

[SIZE=“1”]Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then
BindData(“”)
End If
End Sub

Private Sub BindData(ByVal SortField As String)
	Dim strSQL As String
	Dim dbCommand As OleDbCommand
	'Dim dbConnection As OleDbConnection

	If Not SortField = String.Empty Then
		strSQL = "SELECT * FROM Players ORDER BY @srtField"
	Else
		strSQL = "SELECT * FROM Players ORDER BY Players.PlayerID"
	End If

	dbCommand = New OleDbCommand()
	dbCommand.CommandText = strSQL
	dbCommand.Connection = dbConnection
	Dim db_Param As OleDbParameter = New OleDbParameter()
	db_Param.ParameterName = "@srtField"
	db_Param.Value = SortField.ToString()
	db_Param.DbType = DbType.String
	dbCommand.Parameters.Add(db_Param)

	dbConnection.Open()
	Dim DataReader As OleDbDataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection)
	dgTest.DataSource = DataReader
	dgTest.DataBind()
End Sub

Public Sub dgSort(ByVal sender As Object, ByVal e As DataGridSortCommandEventArgs)
	BindData(e.SortExpression.ToString)
End Sub[/SIZE]

The databinds to the datagrid on page_load event fine but I can only sort
by a column once, any attempt after and the columns do not change order

It works if I move all sql and database stuff into the sort event handler but I would rather do it this way

Any ideas appreciated

2

Quick catch when I was reading your code, you commented the declaration of dbConnection. You think it could be the issue?

3

Sorry I should have mentioned the dbConnection is defined outside of any subs as is the connection string

Thanks anyway

4

Not sure what problem was but fixed with the following code

[SIZE=“1”]Imports System.Data.OleDb

Public Class Players
Inherits System.Web.UI.Page
Protected dbConnectionString As String = ConfigurationSettings.AppSettings(“ConnectionString”)
Protected dbConnection As OleDbConnection = New OleDbConnection(dbConnectionString)
Protected WithEvents mxDataGrid As System.Web.UI.WebControls.DataGrid

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
&lt;System.Diagnostics.DebuggerStepThrough()&gt; Private Sub InitializeComponent()

End Sub

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
	'Put user code to initialize the page here
	If Not Page.IsPostBack Then
		mxDataGrid.DataSource = GetDataReader("None")
		mxDataGrid.DataBind()
	End If
End Sub


Private Function GetDataReader(ByVal SortExp As String) As OleDbDataReader
	Dim querystring As String

	If SortExp = "None" Then
		querystring = "SELECT Players.PlayerID, Players.PlayerName, Players.Profile, Players.JoinDate "
		querystring &= "FROM Players "
		querystring &= "ORDER BY PlayerID"
	Else
		querystring = "SELECT Players.PlayerID, Players.PlayerName, Players.Profile, Players.JoinDate "
		querystring &= "FROM Players "
		querystring &= "ORDER BY " & SortExp
	End If

	Dim objDbCommand As OleDbCommand = New OleDbCommand()

	objDbCommand.Connection = dbConnection
	objDbCommand.CommandText = querystring

	dbConnection.Open()
	Dim objDbDataReader As OleDbDataReader = objDbCommand.ExecuteReader(CommandBehavior.CloseConnection)
	Return objDbDataReader
	dbConnection.Close()

End Function

Public Sub SortColumn(ByVal sender As Object, ByVal e As DataGridSortCommandEventArgs) Handles mxDataGrid.SortCommand
	mxDataGrid.DataSource = GetDataReader(e.SortExpression.ToString)
	mxDataGrid.DataBind()
End Sub

End Class
[/SIZE]