.Net GridView not displaying all columns in dataset

Posted on 2011-04-23
Last Modified: 2013-11-26
I have a dataset that has two or more columns depending on the user's query.  I am binding the dataset to a GridView, and showing the user the results.  

The dataset contains all of the columns/rows that I need when I view the dataset in the Visual Studio debugger.  However, when I bind it to a GridView, and it renders to the browser, two of the columns are missing.  

I have the GridView set to Auto-generate fields, and the first two columns display, but for some reason the last two columns do not appear.  I can create a Template Field, bind the field to the name of the column in the dataset, and it appears perfectly!  However, this is not a viable solution because the dataset is going to have different column names depending on the query.

Any ideas?

Question by:frontback45
    LVL 9

    Expert Comment

    send your code.
    LVL 9

    Expert Comment

    This may also your problem

    Author Comment

    Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
            Dim lbl As Label
            Dim txt As TextBox
            Dim strAttName As String
            Dim strAttValue As String
            Dim strAppName = Session("SearchAppName")
            Dim strAppType = Session("SearchAppType")
            Dim cbSuper As New cBSuper
            Dim dsSearchResults As New DataSet
            'Build search parameters
            Dim dsSearch As New DataSet
            Dim dtSearchParms As New DataTable
            Dim colAttName As New DataColumn
            Dim colAttValue As New DataColumn
            Dim row As DataRow
            colAttName.ColumnName = "AttName"
            colAttValue.ColumnName = "AttValue"
            For i As Integer = 0 To gvAttSearch.Rows.Count - 1
                lbl = gvAttSearch.Rows(i).Cells(0).FindControl("lblAttName")
                txt = gvAttSearch.Rows(i).Cells(1).FindControl("txtAttValue")
                strAttName = lbl.Text
                strAttValue = txt.Text
                row = dsSearch.Tables(0).NewRow
                row.Item("AttName") = strAttName
                row.Item("AttValue") = strAttValue
            With cbSuper
                .AppName = strAppName
                .MLorCI = strAppType
                .SearchParameters = dsSearch
            End With
            dsSearchResults = cbSuper.SearchITD()
            Session("SearchResults") = dsSearchResults
        End Sub
    Private Sub BindSearchResults(ByVal ApplicationType As String)
            If ApplicationType = MASTERLIST Then
                Me.gvMLSearchResults.DataSource = Session("SearchResults")
                Me.gvCISearchResults.DataSource = Session("SearchResults")
            End If
        End Sub

    Open in new window

    Public Function SearchApplication(ByVal ApplicationName As String, ByVal ApplicationType As String) As DataSet
            Dim dsSearchResults As New DataSet
            Dim cmd As New SqlCommand(ProvideSearchSQLCommand())
            Dim sqlcon As New SqlConnection(GetConnectionString)
            Dim intReturnVal As Integer
            'Get the Return Val from the Stored Procedure
            Dim sqlReturnVal As SqlParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int)
            sqlReturnVal.Direction = ParameterDirection.ReturnValue
            With cmd
                .CommandTimeout = 30
                .Connection = sqlcon
                .CommandType = CommandType.Text
            End With
            Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
            intReturnVal = (cmd.Parameters("@ReturnVal").Value)
            Return dsSearchResults
        End Function
        Private Function ProvideSearchSQLCommand() As String
            Dim strSqlCmd As String
            strSqlCmd = "DECLARE @columns VARCHAR(8000) " & _
    "SELECT @columns = COALESCE(@columns + ',[' + cast(AttributeName as varchar) + ']'," & _
            "'[' + cast(AttributeName as varchar)+ ']')" & _
    "FROM dbo.CIAppAttValue " & _
    "WHERE ApplicationName = 'SymantecBackupExec' " & _
    "GROUP BY AttributeName " & _
    "DECLARE @query VARCHAR(8000) " & _
    "SET @query = " & _
           " 'IF OBJECT_ID (''dbo.SEARCHRESULTS'', ''U'') IS NOT NULL " & _
        "DROP TABLE dbo.SEARCHRESULTS; " & _
    "SELECT ApplicationName, AppAttValueLink, '+@columns+' " & _
    "FROM (SELECT ApplicationName, AppAttValueLink, AttributeName,AppAttValue FROM dbo.CIAppAttValue) t1 " & _
    "PIVOT " & _
    "(MAX(AppAttValue) " & _
    "FOR [AttributeName] " & _
    "IN (' + @columns + ') " & _
    ") " & _
    "AS p' " & _
    "EXECUTE(@query) " & _
    "Declare @selectFromResults VARCHAR(8000) " & _
    "SET @selectFromResults = 'SELECT * FROM dbo.SEARCHRESULTS' " & _
            Return strSqlCmd
        End Function

    Open in new window


    Author Comment

    When I use a different SQL query, the GridView renders correctly.  Is the SQL Pivot/Temp table query the issue?
    LVL 10

    Expert Comment

    by:Asim Nazir
    yes. There is no issue with grid/binding. With auto-generate set to true, it simply shows what is there in datasource. So just check your query.

    Author Comment

    I get the correct results when I run the query in SSMS, and when I view the dataset in the debugger.  However, columns are lost when I bind the dataset.
    LVL 7

    Expert Comment

    could it be because you've autoset the two columns known at run time, but it only finds out about the extra two in the databinding event?
    Having the template fields ready with boxes to display the extra columns allows it to display them, saving you the effort of adding the columns under the databound event.
    (not sure if that's the problem, but if code and query are right, it's all I can think of pre-coffee)

    Author Comment

    Even though I know about the first two columns, and I could create two template columns, I am using auto-generate instead.  

    The most confusing part about this issue is that everything looks good in the debugger, but something happens when it renders.  Is there a dataset property that could hide these column?  I am willing to try any solution at this point.

    Accepted Solution

    I found a "solution" to the issue.  I am using a function to manually rebuild the dataset.

    Private Function ProcessSearchResults(ByVal SearchResultsFromDB As DataSet) As DataSet
            Dim ds As New DataSet("ProcessedSearch")
            Dim dt As New DataTable("SearchResults")
            Dim row As DataRow
            Dim col As DataColumn
            Dim strColumnName As String
            For i As Integer = 0 To SearchResultsFromDB.Tables(0).Columns.Count - 1
                strColumnName = SearchResultsFromDB.Tables(0).Columns.Item(i).ColumnName.ToString()
                col = New DataColumn
                col.ColumnName = strColumnName
            For rowIndex As Integer = 0 To SearchResultsFromDB.Tables(0).Rows.Count - 1
                row = dt.NewRow
                For colIndex As Integer = 0 To SearchResultsFromDB.Tables(0).Columns.Count - 1
                    row(colIndex) = SearchResultsFromDB.Tables(0).Rows(rowIndex).Item(colIndex).ToString
            Return ds
        End Function

    Open in new window

    This works, but I am not sure why this works.  Can anyone explain?  Is this some sort of bug with datasets and gridviews?

    You are a true "Expert" if you have an answer to this one.

    Author Comment

    No other solutions

    Author Closing Comment

    Only available solution

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project ( automates most of the tasks discussed in this article. You can even fin…
    Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
    This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
    THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

    746 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

    19 Experts available now in Live!

    Get 1:1 Help Now