[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

column sorting of datagrid

Posted on 2004-10-15
6
Medium Priority
?
357 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

650 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