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?
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?
send your code.
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
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
ASKER
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.
ASKER
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)
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)
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No other solutions
ASKER
Only available solution