I have GridView with associated MSSQL data source. I want the software to change parameters on the fly SelectCommand software. Is it possible to do it in principle?
Yes, it is possible. If you're using the SqlDataSource control then you can use the Selecting event to modify the select parameters before they're sent to the database. If you're using some other method to get your data then you'd need to provide that information
before anyone will be able to give you any useful information that would be specific to what you're trying to do.
Here is my grid viewer data source.
I want this same grid viewer to display data from the same source but sorted according to the progress of the program code.
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:1gb_torgConnectionString %>" SelectCommand="SELECT [Название товара] AS Название_товара, [Фотография товара] AS Фотография_товара, [Цена товара] AS Цена_товара, [Количество] FROM [Запчасти] ORDER BY
Группа">
</asp:SqlDataSource>
Suppose so - when the page loads displaying linked data in accordance with
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:1gb_torgConnectionString %>"
SelectCommand="SELECT [Название товара] AS Название_товара, [Фотография товара] AS Фотография_товара, [Цена товара] AS Цена_товара, [Количество] FROM [Запчасти] ORDER BY Группа">
</asp:SqlDataSource>
There is a field to enter a search query. I would want that in this same GridView display data from that source but sorted by the search query. I also wish that I had this GridView viewer to display the sorted data when i choose parameters from dropdownlist. I
do not understand how can I programmatically change SelectCommand and pass it parameters. Is it possible to do it at all?
The easiest way to apply filters using a SqlDataSource add some SelectParameters to the datasource control.
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:pubsConnectionString %>"
SelectCommand="SELECT * FROM [authors] WHERE ([au_id] LIKE @au_id + '%' OR @au_id ='All')">
<SelectParameters>
<asp:ControlParameter ControlID="txtAuId" Name="au_id" PropertyName="Text" DefaultValue="All"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
In this SqlDataSource I have defined the desired Where statement and then added a control parameter that points to the desired control. This could be any control, not just a textbox as in this example. You just need to make sure the PropertyName is set to
the appropriate property of the control, for a TextBox that would be Text. A DropDownList or ListBox might have SelectedValue.
If you need to change or set the select parameters programmatically then the best place to do so would probably be in the OnSelecting event of the SqlDataSource. You can manually set a select parameter as shown in the post above by Mikesdotnetting.
basil2002
Member
2 Points
21 Posts
dynamic change of parameters SelectCommand
Nov 18, 2012 05:15 PM|LINK
I have GridView with associated MSSQL data source. I want the software to change parameters on the fly SelectCommand software. Is it possible to do it in principle?
Nakor
Member
336 Points
60 Posts
Re: dynamic change of parameters SelectCommand
Nov 18, 2012 05:48 PM|LINK
Yes, it is possible. If you're using the SqlDataSource control then you can use the Selecting event to modify the select parameters before they're sent to the database. If you're using some other method to get your data then you'd need to provide that information before anyone will be able to give you any useful information that would be specific to what you're trying to do.
basil2002
Member
2 Points
21 Posts
Re: dynamic change of parameters SelectCommand
Nov 18, 2012 06:38 PM|LINK
Here is my grid viewer data source.
I want this same grid viewer to display data from the same source but sorted according to the progress of the program code.
<asp:GridView id="GridView1" runat="server" Height="772px" Style="left: 0px; position: relative;
top: 84px; z-index: 104;" Width="596px" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
ForeColor="#333333" Font-Names="Arial"
ShowHeader="False" CellPadding="4" GridLines="None" AllowPaging="True" >
<RowStyle BackColor="#F7F6F3" BorderColor="White" BorderStyle="Double" ForeColor="#333333" />
<Columns>
<asp:BoundField DataField="Название_товара" HeaderText="Название_товара" SortExpression="Название_товара" />
<asp:ImageField DataImageUrlField="Фотография_товара">
</asp:ImageField>
<asp:BoundField DataField="Цена_товара" HeaderText="Цена_товара" SortExpression="Цена_товара" />
<asp:TemplateField>
<ItemTemplate>
р.
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<EditItemTemplate>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Font-Size="X-Small" Style="z-index: 102; left: -6px;
position: relative; top: 0px" Text="выберите количество" Width="56px"></asp:Label>
<asp:DropDownList ID="DropDownList1" runat="server" Style="z-index: 101; left: -2px;
position: relative; top: 12px">
<asp:ListItem>0</asp:ListItem>
<asp:ListItem>1</asp:ListItem>
<asp:ListItem>2</asp:ListItem>
<asp:ListItem>3</asp:ListItem>
<asp:ListItem>4</asp:ListItem>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>6</asp:ListItem>
<asp:ListItem>7</asp:ListItem>
<asp:ListItem>8</asp:ListItem>
<asp:ListItem>9</asp:ListItem>
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:Button ID="Button1" runat="server" CausesValidation="false" CommandName="" Style="left: 4px;
position: relative; top: 0px" Text="Добавить в корзину" Width="116px" OnClick="Button1_Click" Font-Size="X-Small" />
<asp:Button ID="Button2" runat="server" CausesValidation="false"
CommandName="" Style="z-index: 100;
left: 4px; position: relative; top: 4px" Text="Перейти в корзину" Width="116px" OnClick="Button2_Click" Font-Size="X-Small" />
</ItemTemplate>
<ItemStyle Width="5px" />
</asp:TemplateField>
</Columns>
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:1gb_torgConnectionString %>"
SelectCommand="SELECT [Название товара] AS Название_товара, [Фотография товара] AS Фотография_товара, [Цена товара] AS Цена_товара, [Количество] FROM [Запчасти] ORDER BY Группа">
</asp:SqlDataSource>
Mikesdotnett...
All-Star
154858 Points
19858 Posts
Moderator
MVP
Re: dynamic change of parameters SelectCommand
Nov 18, 2012 07:23 PM|LINK
In principal, yes. You can set any value you like at run time in code behind.
SqlDataSource1.SelectParameters["name_of_Param"].DefaultValue = some_value;
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
Nakor
Member
336 Points
60 Posts
Re: dynamic change of parameters SelectCommand
Nov 18, 2012 07:33 PM|LINK
i'm not sure what you mean by "sorted according to the progress of the program code"
basil2002
Member
2 Points
21 Posts
Re: dynamic change of parameters SelectCommand
Nov 19, 2012 04:42 AM|LINK
Suppose so - when the page loads displaying linked data in accordance with
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:1gb_torgConnectionString %>"
SelectCommand="SELECT [Название товара] AS Название_товара, [Фотография товара] AS Фотография_товара, [Цена товара] AS Цена_товара, [Количество] FROM [Запчасти] ORDER BY Группа">
</asp:SqlDataSource>
There is a field to enter a search query. I would want that in this same GridView display data from that source but sorted by the search query. I also wish that I had this GridView viewer to display the sorted data when i choose parameters from dropdownlist. I do not understand how can I programmatically change SelectCommand and pass it parameters. Is it possible to do it at all?
Nakor
Member
336 Points
60 Posts
Re: dynamic change of parameters SelectCommand
Nov 19, 2012 03:24 PM|LINK
The easiest way to apply filters using a SqlDataSource add some SelectParameters to the datasource control.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:pubsConnectionString %>" SelectCommand="SELECT * FROM [authors] WHERE ([au_id] LIKE @au_id + '%' OR @au_id ='All')"> <SelectParameters> <asp:ControlParameter ControlID="txtAuId" Name="au_id" PropertyName="Text" DefaultValue="All" Type="String" /> </SelectParameters> </asp:SqlDataSource>In this SqlDataSource I have defined the desired Where statement and then added a control parameter that points to the desired control. This could be any control, not just a textbox as in this example. You just need to make sure the PropertyName is set to the appropriate property of the control, for a TextBox that would be Text. A DropDownList or ListBox might have SelectedValue.
If you need to change or set the select parameters programmatically then the best place to do so would probably be in the OnSelecting event of the SqlDataSource. You can manually set a select parameter as shown in the post above by Mikesdotnetting.