We help IT Professionals succeed at work.

.Net GridView not displaying all columns in dataset

Medium Priority
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?

Watch Question

send your code.
This may also your problem



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 " & _
"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


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

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.


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.
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)


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.
Unlock this solution and get a sample of our free trial.
(No credit card required)


No other solutions


Only available solution
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.