Solved

column sorting of datagrid

Posted on 2004-10-15
6
352 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
jquery progress bar 3 55
jquery datatables, asp.net MVC 9 51
Expression Evaluater 3 37
Tracing a live website down to the files which support it 2 25
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

808 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