Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Here is an example of doing what you want without a DataSource Control, using a code behind DataTable &amp; Cache along with a custom SortDirection function that manages the sort direction in the view state.</p> <pre><code>''' &lt;summary&gt; ''' Show Grid ''' &lt;/summary&gt; ''' &lt;param name="sender"&gt;&lt;/param&gt; ''' &lt;param name="e"&gt;&lt;/param&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; Protected Sub ShowGrid(sender As Object, e As EventArgs) Handles ShowGridButton.Click BuildGrid() End Sub ''' &lt;summary&gt; ''' Sort GridView Columns ''' &lt;/summary&gt; ''' &lt;param name="sender"&gt;&lt;/param&gt; ''' &lt;param name="e"&gt;&lt;/param&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; Protected Sub Sorting(ByVal sender As Object, ByVal e As GridViewSortEventArgs) Handles statusReportGrid.Sorting Dim dataTable As DataTable = BuildData() If DataTable IsNot Nothing Then Dim dvSortedView As New DataView(dataTable) dvSortedView.Sort = e.SortExpression + " " + getSortDirectionString() statusReportGrid.DataSource = dvSortedView statusReportGrid.DataBind() End If End Sub ''' &lt;summary&gt; ''' Get and Store GridView SortDirection ''' &lt;/summary&gt; ''' &lt;returns&gt;&lt;/returns&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; Private Function getSortDirectionString() As String Dim sortDirection As String = "ASC" If ViewState("GridSortDirection") IsNot Nothing Then If ViewState("GridSortDirection").ToString() = "ASC" Then sortDirection = "DESC" Else sortDirection = "ASC" End If End If ViewState("GridSortDirection") = sortDirection Return sortDirection End Function ''' &lt;summary&gt; ''' Build Dynamic GridView Columns ''' &lt;/summary&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; Protected Sub BuildGrid() Dim dataTable As New DataTable dataTable = BuildData() If dataTable.Rows.Count &gt; 0 Then For Each item As ListItem In chkFields.Items If item.Selected Then Dim b As New BoundField() b.DataField = item.Value b.HeaderText = item.Value b.SortExpression = item.Value statusReportGrid.Columns.Add(b) End If Next statusReportGrid.DataSource = dataTable statusReportGrid.DataBind() End If End Sub ''' &lt;summary&gt; ''' Get DataTable from DB and Use Cache to Store ''' &lt;/summary&gt; ''' &lt;returns&gt;&lt;/returns&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; Protected Function BuildData() As DataTable Dim dataTable As New DataTable If Cache("StatusDBResults") IsNot Nothing Then dataTable = TryCast(Cache("StatusDBResults"), DataTable) Return dataTable End If Dim queryString As String queryString = "SELECT * FROM [StatusTable]" Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("StatusDBConnectionString").ConnectionString) Dim adapter As New SqlDataAdapter() adapter.SelectCommand = New SqlCommand(queryString, connection) adapter.Fill(dataTable) 'Add To Cache for 2 Minutes for Sorting/Paging/Downloading Cache.Add("StatusDBResults", dataTable, Nothing, System.Web.Caching.Cache.NoAbsoluteExpiration, New TimeSpan(0, 2, 0), System.Web.Caching.CacheItemPriority.Default, Nothing) 'MY CODE - Gets the Column Names and dumps them into the ASP.NET CheckBoxList control 'There may be a better way to do this using the DataTable, but I wasn't able to get that to work. This works and it's pretty fast. connection.Open() Dim colQuery As New SqlCommand("select column_name from information_schema.columns where table_name = 'StatusTable'", connection) Dim dr As SqlDataReader = colQuery.ExecuteReader Dim colsArr As New ArrayList While dr.Read colsArr.Add(dr.Item(0)) End While chkFields.DataSource = colsArr chkFields.DataBind() Return dataTable End Using End Function </code></pre> <p>Here is my sample HTML control source that I used for the test....</p> <pre><code>&lt;asp:GridView runat="server" AutoGenerateColumns="False" AllowPaging="True" AllowSorting="True" ID="statusReportGrid"&gt; &lt;/asp:GridView&gt; &lt;asp:CheckBoxList runat="server" ID="chkFields" RepeatDirection="Horizontal" RepeatLayout="Flow" /&gt; &lt;asp:Button runat="server" Text="Button" ID="ShowGridButton" /&gt; </code></pre> <p>Let me know if this makes sense, I don't normally code in VB. The SortDirection is a known issue, because it always returns Ascending. As for the use of a DataTable vs SqlDataSource, both can be used but I find the DataTable approach easier.</p>
    singulars
    1. This table or related slice is empty.
    1. POGridView SORTING of DYNAMIC Columns and Data Source Scope issues
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USChiramisu
    1. USZachary
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POGridView SORTING of DYNAMIC Columns and Data Source Scope issues
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. CO*sigh* Didn't work unfortunately, though it does make my code a bit cleaner.
      singulars
      1. PO
      1. USChiramisu
    2. COThat sounds PERFECT. Would you please elaborate a bit on how to implement that? I'm not really familiar with the technique you're describing. I ordered a book on Data Controls, but in the meantime I'm stumped >.<
      singulars
      1. PO
      1. USChiramisu
    3. COHere is a link to a [SO post](http://stackoverflow.com/questions/6831665/how-to-store-datatable-in-cache-to-reuse-it) that shows an example of using Cache to store your DataTable.
      singulars
      1. PO
      1. USZachary
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload