Solved

column sorting of datagrid

Posted on 2004-10-15
6
348 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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

AJAX ModalPopupExtender has a required property "TargetControlID" which may seem to be very confusing to new users. It means the server control that will be extended by the ModalPopup, for instance, if when you click a button, a ModalPopup displays,…
Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now