[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 598
  • Last Modified:

.Net GridView not displaying all columns in dataset

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?

1 Solution
send your code.
This may also your problem

frontback45Author Commented:
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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

frontback45Author Commented:
When I use a different SQL query, the GridView renders correctly.  Is the SQL Pivot/Temp table query the issue?
Asim NazirCommented:
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.
frontback45Author Commented:
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)
frontback45Author Commented:
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.
frontback45Author Commented:
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.
frontback45Author Commented:
No other solutions
frontback45Author Commented:
Only available solution

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now