Link to home
Start Free TrialLog in
Avatar of frontback45
frontback45

asked on

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

Avatar of radcaesar
radcaesar
Flag of India image

send your code.
Avatar of frontback45
frontback45

ASKER

UI:
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"
        dtSearchParms.Columns.Add(colAttName)
        dtSearchParms.Columns.Add(colAttValue)
        dsSearch.Tables.Add(dtSearchParms)

        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
            dsSearch.Tables(0).Rows.Add(row)
        Next

        With cbSuper
            .AppName = strAppName
            .MLorCI = strAppType
            .SearchParameters = dsSearch
        End With
        dsSearchResults = cbSuper.SearchITD()
        Session("SearchResults") = dsSearchResults
        BindSearchResults(strAppType)
    End Sub

Private Sub BindSearchResults(ByVal ApplicationType As String)
        If ApplicationType = MASTERLIST Then
            Me.gvMLSearchResults.DataSource = Session("SearchResults")
            Me.gvMLSearchResults.DataBind()
        Else
            Me.gvCISearchResults.DataSource = Session("SearchResults")
            Me.gvCISearchResults.DataBind()
        End If
    End Sub

Open in new window

DAO:
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)
        da.Fill(dsSearchResults)

        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+' " & _
"INTO dbo.SEARCHRESULTS " & _
"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' " & _
"EXECUTE(@selectFromResults)"

        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?
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.
ASKER CERTIFIED SOLUTION
Avatar of frontback45
frontback45

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
No other solutions
Only available solution