Hello Everyone I'm facing a bit trouble in implementing the simple query based on SQL and ASP.NET. I've binded my datagrid with Textbox and it works fine using the following query
SELECT Id, username, first_name, last_name, email, phone, picture, user_type, joining_date FROM admins WHERE (username= @un)
here i just bind the @un with my textbox and it works perfect but as soon as i changed my query to:
SELECT Id, username, first_name, last_name, email, phone, picture, user_type, joining_date FROM admins WHERE (@opt= @un)
where @opt is binded with my Dropdownlist value property so that i can search different columns with different data for example in the first query i was searching only the username but here i want to search for other options also , so how can i achive this?
Here is the aspx for my datagrid and sqldatasource it has no code behind file as i have binded the properties through visual studio.
<asp:DropDownList CssClass="form-control" Height="38px" Width="238px" ID="search_opt_box" runat="server" AutoPostBack="True">
<asp:ListItem>All</asp:ListItem>
<asp:ListItem Value="Id">ID</asp:ListItem>
<asp:ListItem Value="username">By Username</asp:ListItem>
<asp:ListItem Value="first_name">By First Name</asp:ListItem>
<asp:ListItem Value="last_name">Last Name</asp:ListItem>
<asp:ListItem Value="phone">By Phone</asp:ListItem>
<asp:ListItem Value="email">By Email</asp:ListItem>
<asp:ListItem Value="city">By City</asp:ListItem>
<asp:ListItem Value="province">By Province</asp:ListItem>
<asp:ListItem Value="country">By Country</asp:ListItem>
<asp:ListItem Value="address">By Address</asp:ListItem>
</asp:DropDownList>
<asp:GridView ID="search_by_name" runat="server" Width="1060px" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" CssClass="table table-bordered table-striped table-hover tc-table table-primary footable" RowHeaderColumn="Id" DataKeyNames="Id">
<Columns>
<asp:TemplateField HeaderText="Picture" ControlStyle-Width="100px" ControlStyle-Height="100px">
<ItemTemplate>
<asp:Image ID="user_pic" runat="server" ImageUrl='<%# "fetch_user_img.ashx?username="+Eval("username") %>' />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Id" HeaderText="ID#" InsertVisible="False" ReadOnly="True" SortExpression="Id" />
<asp:BoundField DataField="username" HeaderText="Username" SortExpression="username" />
<asp:BoundField DataField="first_name" HeaderText="First Name" SortExpression="first_name" />
<asp:BoundField DataField="last_name" HeaderText="Last Name" SortExpression="last_name" />
<asp:BoundField DataField="user_type" HeaderText="Category" SortExpression="user_type" />
<asp:BoundField DataField="joining_date" HeaderText="Joining Date" SortExpression="joining_date" />
<asp:BoundField DataField="email" HeaderText="Email" SortExpression="email" />
<asp:BoundField DataField="phone" HeaderText="Phone#" SortExpression="phone" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DefaultConnectionString %>" SelectCommand="SELECT Id, username, first_name, last_name, email, phone, picture, user_type, joining_date FROM admins WHERE (@opt= @un) ">
<SelectParameters>
<asp:ControlParameter ControlID="search_opt_box" Name="opt" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="search_term_box" Name="un" PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
