Solved

column sorting of datagrid

Posted on 2004-10-15
6
353 Views
Last Modified: 2012-06-27
in the table that is returned from a SQL query, i want to be able to sort the columns.  i have the AllowSorting=True, OnSortCommand="SortEventHandler",  and the following HTML:

<Columns>
<asp:ButtonColumn Text="Details" HeaderText="Error Details"></asp:ButtonColumn>
<asp:BoundColumn Visible="False" DataField="ErrorID"></asp:BoundColumn>
<asp:BoundColumn Visible="False" DataField="CreateDate" SortExpression="E.CreateDate DESC"></asp:BoundColumn>
<asp:BoundColumn Visible="False" DataField="ProcessOrPage"></asp:BoundColumn>
<asp:BoundColumn Visible="False" DataField="Error#" SortExpression="E.ErrorNumber DESC"></asp:BoundColumn>
<asp:BoundColumn Visible="False" DataField="Status" SortExpression="ES.ErrorStatus DESC"></asp:BoundColumn>
<asp:BoundColumn Visible="False" DataField="ErrorText"></asp:BoundColumn>
</Columns>

The query joins two tables, E and ES.  i use the fully qualified name for every attribute in the SQL command.  The code behind is in VB and is as follows:

Public Sub SortEventHandler(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs)
        SortEventHandlerBindData(e.SortExpression)
    End Sub

    Public Sub SortEventHandlerBindData(ByVal sortExpr As String)
        Dim myConnection As New _
            SqlClient.SqlConnection(SQLServerConnection)
        Dim strSQL As String = "SELECT E.ErrorID, E.CreateDate, ISNULL(E.PageURL,'')+ISNULL(E.ProcessName,'') as ProcessOrPage, E.ErrorNumber as Error#, ES.ErrorDescription as Status, E.ErrorText FROM Error E join ErrorStatus ES on E.ErrorStatusID = ES.ErrorStatusID ORDER BY " & sortExpr
        Dim myCommand As New SqlClient.SqlCommand(strSQL, myConnection)
        Dim myRdr As SqlClient.SqlDataReader

        myConnection.Open()
        myRdr = myCommand.ExecuteReader(CommandBehavior.Default)
        Me.errorGrid.DataSource = myRdr
        Me.errorGrid.DataBind()
    End Sub


when the application is run, all the columns are able to be sorted except the CreateDate column.  when attempted, it brings up the error:

Exception Details: System.Data.SqlClient.SqlException: Ambiguous column name 'CreateDate'.

Source Error:


Line 70:
Line 71:         myConnection.Open()
Line 72:         myRdr = myCommand.ExecuteReader(CommandBehavior.Default)
Line 73:         Me.errorGrid.DataSource = myRdr
Line 74:         Me.errorGrid.DataBind()
 

it seems to not be recognizing the qualified name of E.CreateDate.  i have checked all the code and have not found a discrepency.  any help is appreciated....thanks
0
Comment
Question by:rawdrib
  • 4
6 Comments
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12322967
you should just use the field in the db for the sort expression.  If you bind to a dataset all the sorting logic is handled by it.

Aeros
0
 
LVL 5

Expert Comment

by:tockhoi
ID: 12323143
Try using the DataView control.

Dim dataView as New DataView(ds.Tables(0))
dataView.Sort = "[ColumnName] DESC"  ' what you would put in ORDER BY in sql
DataGrid1.DataSource = dataView
DataGrid1.DataBind()
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12323259
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim authTicket As FormsAuthenticationTicket = CType(HttpContext.Current.User.Identity, FormsIdentity).Ticket()
        HttpContext.Current.User = New GenericPrincipal(User.Identity, Split(authTicket.UserData, ","))
    End Sub
    Private Sub BindTheData(Optional ByVal LastName As String = "", Optional ByVal Phone As String = "")
        Dim cnn As New SqlClient.SqlConnection(ConfigurationSettings.AppSettings("EmeraldConnStr"))
        Dim cmd As New SqlClient.SqlCommand
        Dim da As New SqlClient.SqlDataAdapter(cmd)
        Dim ds As New DataSet
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "Miscellaneous.dbo.ContactRequestViewAll"
        cmd.Parameters.Add(New SqlParameter("@LastName", LastName))
        cmd.Parameters.Add(New SqlParameter("@Phone", Phone))
        cmd.Connection = cnn
        cnn.Open()
        da.SelectCommand = cmd
        da.Fill(ds, "ContactRequests")
        dg.DataSource = ds
        dg.DataKeyField = "ContactRequestID"
        dg.DataBind()
        cnn.Close()
        cmd.Dispose()
        cnn.Dispose()
        dg.Visible = True
    End Sub
    Private Sub dg_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles dg.PageIndexChanged
        Dim dt As DataTable = GetTableData()
        Dim dv As DataView = New DataView(dt)
        Dim dataGrid As DataGrid = source
        dataGrid.CurrentPageIndex = e.NewPageIndex
        dv.Sort = dataGrid.Attributes("SortExpression")
        If dataGrid.Attributes("SortASC") = "No" Then
            dv.Sort &= " DESC"
        End If
        dataGrid.DataSource = dv
        dataGrid.DataBind()
    End Sub
    Private Sub dg_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dg.ItemCommand
        'If e.CommandName = "Edit" Then
        'Response.Redirect("EditBroadBandRequest.aspx?crid=" & CStr(dg.DataKeys(e.Item.ItemIndex)))
        'End If
        If e.CommandName = "Assign" Then
            Response.Redirect("AssignBroadBandRequest.aspx?crid=" & CStr(dg.DataKeys(e.Item.ItemIndex)))
        End If
    End Sub
    Private Sub SearchProducts_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SearchProducts.Click
        Dim LastName, Phone As String
        Select Case FieldsToMatch.SelectedValue
            Case "LastName"
                LastName = SearchQuery.Text
                dg.CurrentPageIndex = 0
                BindTheData(LastName, "")
                FullQuery.Value = LastName
            Case "PhoneNumber"
                Phone = SearchQuery.Text
                dg.CurrentPageIndex = 0
                BindTheData("", Phone)
                FullQuery.Value = Phone
        End Select
    End Sub
    Private Sub LinkButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LinkButton2.Click
        Response.Redirect("default.aspx")
    End Sub
    Private Sub dg_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles dg.SortCommand
        Dim strSort = dg.Attributes("SortExpression")
        Dim strASC = dg.Attributes("SortASC")
        dg.Attributes("SortExpression") = e.SortExpression
        dg.Attributes("SortASC") = "Yes"
        If e.SortExpression = strSort Then
            If strASC = "Yes" Then
                dg.Attributes("SortASC") = "No"
            Else
                dg.Attributes("SortASC") = "Yes"
            End If
        End If
        Dim dt As DataTable = GetTableData()
        Dim dv As DataView = New DataView(dt)
        dv.Sort = dg.Attributes("SortExpression")
        If dg.Attributes("SortASC") = "No" Then
            dv.Sort &= " DESC"
        End If
        dg.CurrentPageIndex = 0
        dg.DataSource = dv
        dg.DataBind()
    End Sub
    Function GetTableData() As DataTable
        Dim cnn As New SqlClient.SqlConnection(ConfigurationSettings.AppSettings("EmeraldConnStr"))
        Dim cmd As New SqlClient.SqlCommand
        Dim da As New SqlClient.SqlDataAdapter(cmd)
        Dim ds As New DataSet
        da.SelectCommand = cmd
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "Miscellaneous.dbo.ContactRequestViewAll"
        cmd.Parameters.Add(New SqlParameter("@LastName", ""))
        cmd.Parameters.Add(New SqlParameter("@Phone", ""))
        cmd.Connection = cnn
        cnn.Open()
        da.Fill(ds, "ContactRequests")
        Return ds.Tables("ContactRequests")
    End Function
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:rawdrib
ID: 12323274
>>you should just use the field in the db for the sort expression.  If you bind to a dataset all the sorting logic is handled by it.

what do you mean by that
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12323289
if you set the sort expression to the name of the field and use the above code you don't have to do any additional logic handling.

0
 
LVL 17

Accepted Solution

by:
AerosSaga earned 500 total points
ID: 12323299
These are the important parts:

Private Sub dg_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles dg.PageIndexChanged
        Dim dt As DataTable = GetTableData()
        Dim dv As DataView = New DataView(dt)
        Dim dataGrid As DataGrid = source
        dataGrid.CurrentPageIndex = e.NewPageIndex
        dv.Sort = dataGrid.Attributes("SortExpression")
        If dataGrid.Attributes("SortASC") = "No" Then
            dv.Sort &= " DESC"
        End If
        dataGrid.DataSource = dv
        dataGrid.DataBind()
    End Sub
  Private Sub dg_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles dg.SortCommand
        Dim strSort = dg.Attributes("SortExpression")
        Dim strASC = dg.Attributes("SortASC")
        dg.Attributes("SortExpression") = e.SortExpression
        dg.Attributes("SortASC") = "Yes"
        If e.SortExpression = strSort Then
            If strASC = "Yes" Then
                dg.Attributes("SortASC") = "No"
            Else
                dg.Attributes("SortASC") = "Yes"
            End If
        End If
        Dim dt As DataTable = GetTableData()
        Dim dv As DataView = New DataView(dt)
        dv.Sort = dg.Attributes("SortExpression")
        If dg.Attributes("SortASC") = "No" Then
            dv.Sort &= " DESC"
        End If
        dg.CurrentPageIndex = 0
        dg.DataSource = dv
        dg.DataBind()
    End Sub
    Function GetTableData() As DataTable
        Dim cnn As New SqlClient.SqlConnection(ConfigurationSettings.AppSettings("EmeraldConnStr"))
        Dim cmd As New SqlClient.SqlCommand
        Dim da As New SqlClient.SqlDataAdapter(cmd)
        Dim ds As New DataSet
        da.SelectCommand = cmd
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "Miscellaneous.dbo.ContactRequestViewAll"
        cmd.Parameters.Add(New SqlParameter("@LastName", ""))
        cmd.Parameters.Add(New SqlParameter("@Phone", ""))
        cmd.Connection = cnn
        cnn.Open()
        da.Fill(ds, "ContactRequests")
        Return ds.Tables("ContactRequests")
    End Function
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question