Dynamic Datasource ASP2.0

[MycGyver]

Scenario: 2 SQLDataSource, 2 Gridview controls.

Use first gridview (gvwTables) select a record which has a field containing
the ConnectionString (and table name for the SelectCommand) of the datasource
for a second gridview. Second gridview is sortable.

When I click on the column header to sort the second gridview, the grid
refreshes empty, and the dynamic values I have set on the second datasource
are gone. I thought it would remember the set values since
EnableViewState=True should preserve the control state.

Can anyone clue me in to what I'm missing?

I realize the code below is not complete but it should give you an idea of
what I am trying to accomplish:

Protected Sub gvwTables_SelectedIndexChanged(ByVal sender As Object, ByVal
e As System.EventArgs) Handles gvwTables.SelectedIndexChanged
'Set the connectionstring to the datasource string of the selected
database.
sdsTableRows.ConnectionString = gvwDatabases.DataKeys.Item

'Set the selected statement to retrieve the rows.
sdsTableRows.SelectCommand = "SELECT * FROM [" +
gvwTables.SelectedRow.Cells(3).Text + "]"
End Sub

<asp:Label ID="lblTableMsg" runat="server" Style="font: caption"
Text="Please choose the Table you would like to modify:"></asp:Label>
<asp:SqlDataSource ID="sdsTables" runat="server"
ConnectionString="<%$ ConnectionStrings:TableMaintConnectionString %>"
SelectCommand="SELECT * FROM

WHERE ([DatabaseId] =
@DatabaseId)">
<SelectParameters>
<asp:ControlParameter ControlID="gvwDatabases"
Name="DatabaseId" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="gvwTables" runat="server" AllowSorting="True"
AutoGenerateColumns="False"
BackColor="LightGoldenrodYellow" BorderColor="Tan"
BorderWidth="1px" CellPadding="2"
DataKeyNames="TableId" DataSourceID="sdsTables"
ForeColor="Black" GridLines="None"
Style="font: messagebox">
<FooterStyle BackColor="Tan" />
<Columns>
<asp:CommandField ShowSelectButton="True"
ButtonType="Button" />
<asp:BoundField DataField="TableId" HeaderText="TableId"
InsertVisible="False" ReadOnly="True"
SortExpression="TableId" Visible="False" />
<asp:BoundField DataField="DatabaseId"
HeaderText="DatabaseId" SortExpression="DatabaseId"
Visible="False" />
<asp:BoundField DataField="Name" HeaderText="Name"
SortExpression="Name" />
<asp:BoundField DataField="Description"
HeaderText="Description" SortExpression="Description" />
<asp:BoundField DataField="RowSource" HeaderText="RowSource"
SortExpression="RowSource" />
<asp:BoundField DataField="GroupByTableId"
HeaderText="GroupByTableId" SortExpression="GroupByTableId" />
<asp:BoundField DataField="GroupByNameColumn"
HeaderText="GroupByNameColumn" SortExpression="GroupByNameColumn" />
</Columns>
<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue"
HorizontalAlign="Center" />
<SelectedRowStyle BackColor="DarkSlateBlue"
ForeColor="GhostWhite" />
<HeaderStyle BackColor="Tan" Font-Bold="True" />
<AlternatingRowStyle BackColor="PaleGoldenrod" />
</asp:GridView>
<asp:Label ID="lblTableRowsMsg" runat="server" Style="font: caption"
Text="The records for the requested table are listed below:"></asp:Label>
<asp:SqlDataSource
ID="sdsTableRows"
runat="server"
ConnectionString="<%$
ConnectionStrings:TableMaintConnectionString %>"</asp:SqlDataSource>
<asp:GridView ID="gvwTableRows" runat="server"
BackColor="LightGoldenrodYellow" BorderColor="Tan"
BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None"
Style="font: messagebox" AllowPaging="True"
DataSourceID="sdsTableRows" EnableSortingAndPagingCallbacks="True">
<FooterStyle BackColor="Tan" />
<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue"
HorizontalAlign="Center" />
<SelectedRowStyle BackColor="DarkSlateBlue"
ForeColor="GhostWhite" />
<HeaderStyle BackColor="Tan" Font-Bold="True" />
<AlternatingRowStyle BackColor="PaleGoldenrod" />
<PagerSettings Mode="NumericFirstLast" Position="TopAndBottom" />
</asp:GridView>

 

[Mauricio]

I had this same problem when using paging when I set the select command from
code. The way I found to solve this is to set the tablename to a session
variable on the SelectedIndexChange event, then on the page_load event, set
the select command again using this variable.

MensajesSource.SelectCommand = "SELECT [numero], [mensaje], [fecha] FROM ["
& CStr(Session("TableNames")) & "_SMS] WHERE TIPO = '" &
CStr(Session("ActiveServiceName")) & "' ORDER BY [fecha] DESC "

If you dont do that when you postback by selecting the sort, the gridview
wont have a select command to display.

Hope this helps.

regards

Mauricio

Scenario: 2 SQLDataSource, 2 Gridview controls.

Use first gridview (gvwTables) select a record which has a field containing
the ConnectionString (and table name for the SelectCommand) of the datasource
for a second gridview. Second gridview is sortable.

When I click on the column header to sort the second gridview, the grid
refreshes empty, and the dynamic values I have set on the second datasource
are gone. I thought it would remember the set values since
EnableViewState=True should preserve the control state.

Can anyone clue me in to what I'm missing?

I realize the code below is not complete but it should give you an idea of
what I am trying to accomplish:

Protected Sub gvwTables_SelectedIndexChanged(ByVal sender As Object, ByVal
e As System.EventArgs) Handles gvwTables.SelectedIndexChanged
'Set the connectionstring to the datasource string of the selected
database.
sdsTableRows.ConnectionString = gvwDatabases.DataKeys.Item

'Set the selected statement to retrieve the rows.
sdsTableRows.SelectCommand = "SELECT * FROM [" +
gvwTables.SelectedRow.Cells(3).Text + "]"
End Sub

<asp:Label ID="lblTableMsg" runat="server" Style="font: caption"
Text="Please choose the Table you would like to modify:"></asp:Label>
<asp:SqlDataSource ID="sdsTables" runat="server"
ConnectionString="<%$ ConnectionStrings:TableMaintConnectionString %>"
SelectCommand="SELECT * FROM

WHERE ([DatabaseId] =
@DatabaseId)">
<SelectParameters>
<asp:ControlParameter ControlID="gvwDatabases"
Name="DatabaseId" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="gvwTables" runat="server" AllowSorting="True"
AutoGenerateColumns="False"
BackColor="LightGoldenrodYellow" BorderColor="Tan"
BorderWidth="1px" CellPadding="2"
DataKeyNames="TableId" DataSourceID="sdsTables"
ForeColor="Black" GridLines="None"
Style="font: messagebox">
<FooterStyle BackColor="Tan" />
<Columns>
<asp:CommandField ShowSelectButton="True"
ButtonType="Button" />
<asp:BoundField DataField="TableId" HeaderText="TableId"
InsertVisible="False" ReadOnly="True"
SortExpression="TableId" Visible="False" />
<asp:BoundField DataField="DatabaseId"
HeaderText="DatabaseId" SortExpression="DatabaseId"
Visible="False" />
<asp:BoundField DataField="Name" HeaderText="Name"
SortExpression="Name" />
<asp:BoundField DataField="Description"
HeaderText="Description" SortExpression="Description" />
<asp:BoundField DataField="RowSource" HeaderText="RowSource"
SortExpression="RowSource" />
<asp:BoundField DataField="GroupByTableId"
HeaderText="GroupByTableId" SortExpression="GroupByTableId" />
<asp:BoundField DataField="GroupByNameColumn"
HeaderText="GroupByNameColumn" SortExpression="GroupByNameColumn" />
</Columns>
<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue"
HorizontalAlign="Center" />
<SelectedRowStyle BackColor="DarkSlateBlue"
ForeColor="GhostWhite" />
<HeaderStyle BackColor="Tan" Font-Bold="True" />
<AlternatingRowStyle BackColor="PaleGoldenrod" />
</asp:GridView>
<asp:Label ID="lblTableRowsMsg" runat="server" Style="font: caption"
Text="The records for the requested table are listed below:"></asp:Label>
<asp:SqlDataSource
ID="sdsTableRows"
runat="server"
ConnectionString="<%$
ConnectionStrings:TableMaintConnectionString %>"</asp:SqlDataSource>
<asp:GridView ID="gvwTableRows" runat="server"
BackColor="LightGoldenrodYellow" BorderColor="Tan"
BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None"
Style="font: messagebox" AllowPaging="True"
DataSourceID="sdsTableRows" EnableSortingAndPagingCallbacks="True">
<FooterStyle BackColor="Tan" />
<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue"
HorizontalAlign="Center" />
<SelectedRowStyle BackColor="DarkSlateBlue"
ForeColor="GhostWhite" />
<HeaderStyle BackColor="Tan" Font-Bold="True" />
<AlternatingRowStyle BackColor="PaleGoldenrod" />
<PagerSettings Mode="NumericFirstLast" Position="TopAndBottom" />
</asp:GridView>