Refining an SQL query in gridview.

Using Drop Down List and or radio buttons.

Page 1 of 1

1 Replies - 3278 Views - Last Post: 31 October 2008 - 01:19 PM Rate Topic: -----

#1 AFProgMan  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 80
  • Joined: 21-October 08

Refining an SQL query in gridview.

Post icon  Posted 31 October 2008 - 08:04 AM

Hey everyone.

I decided to change up my whole display page using a gridview instead of a Build Table function. Does anybody know how to refine the List return in the gridview once an option to refine the search is applied? I have a drop down list and a radio button list created and with items in the collection/or databound. I would like the user to be able to refine their search using these options.

My code is as follows, and it could be as simple as changing the SQL query by just adding WHERE but I would like to do that ONLY IF the user has selected an option to refine. Otherwise display normal list, the whole list.

<div align="center">

   <table width="100%" cellpadding="5">
   <tr>
   <td><asp:Label ID="Label1" runat="server" Text="Find an attorney or expert witness in your area by using the directory below."></asp:Label>
	   <br />
	   You can refine your search using the options to the right.</td>
	   <td align="left"><asp:RadioButtonList ID="RadioButtonList1" runat="server">
		   <asp:ListItem Value="Lawyer">Lawyers</asp:ListItem>
		   <asp:ListItem Value="ExpertWitness">Expert Witnesses</asp:ListItem>
	   </asp:RadioButtonList>
	   
		   <asp:DropDownList ID="ddlStateRefine" runat="server" 
			   DataSourceID="getrefstates" DataTextField="State" DataValueField="Abbrev">
		   </asp:DropDownList>
		   <asp:SqlDataSource ID="getrefstates" runat="server" 
			   ConnectionString="<%$ ConnectionStrings:PLNIndexConnectionString %>" 
			   SelectCommand="SELECT * FROM [tbl_States] ORDER BY [State]">
		   </asp:SqlDataSource>
	   </td>
	 </tr>
   <tr>
	  <td width="50%">
		   <b>Select the referral from the list to view full details.</b>
		   <asp:gridview id="empsGrid" runat="server"
			cellpadding="5" font-size="10pt"
			datasourceid="referrals"
			datakeynames="refid"
			autogenerateselectbutton="True"
			allowsorting="True"
			autogeneratecolumns="False"
			selectedrowstyle-backcolor="khaki"
			  AllowPaging="True" onselectedindexchanged="empsGrid_SelectedIndexChanged">
		   
			<columns>
			   <asp:boundfield headertext="Firm Name"
				  datafield="reffirmname" SortExpression="reffirmname" />
				<asp:BoundField DataField="reffirstname" HeaderText="First Name" />
				<asp:BoundField DataField="reflastname" HeaderText="Last Name" 
					SortExpression="reflastname" />
			   <asp:boundfield headertext="Type"
				  datafield="reftype" />
				<asp:BoundField DataField="refspecialty" HeaderText="Specialty" 
					SortExpression="refspecialty" />
				<asp:BoundField DataField="refstate" HeaderText="State" 
					SortExpression="refstate" />
			</columns><SelectedRowStyle BackColor="Khaki"></SelectedRowStyle>
		   </asp:gridview>
	  </td>

	  <td width="50%" align="left">
		 <asp:formview id="empsView" runat="server"
			datasourceid="refdetails"
			width="100%">

			<headertemplate>
			   <div id="Div1" class="empname" runat="server"><b>
				  <%# Eval ( "reffirstname" ) %> <%# Eval ( "reflastname" ) %>
			   </b></div>
			</headertemplate>

			<itemtemplate>
			   <table width="100%" cellpadding="5" align="center" bgcolor="whitesmoke">
			   <tr>
				  <%--<td>
					 <img id="Img2" align="top" runat="server" border=0
						src='<%# Eval ( "photopath", "~/shared/images/{0}" ) %>' />
				  </td>--%>
				  <td>
					 <table cellpadding="5">
					 <tr>
						<td>
							
						 <tr><td><b>Type:</b></td> <td><%# Eval ( "reftype" ) %></td></tr>
						   <tr><td><b>Full Name:</b></td> <td><%# Eval ( "reffirstname" ) %> <%# Eval ( "reflastname" ) %></td></tr>
						   <tr><td><b>Firm Name:</b></td> <td><%# Eval ( "reffirmname" ) %></td></tr>
						   <tr><td><b>Specialty:</b></td> <td><%# Eval ( "refspecialty" ) %></td></tr>
						   <tr><td><b>Address:</b></td> <td><%# Eval ( "refaddress" ) %><br><%# Eval ( "refcity" ) %> <%# Eval ( "refzipcode" ) %>, <%# Eval ( "refstate" ) %></td></tr>
						   <tr><td><b>Phone Number:</b></td> <td>(<%# Eval ( "refphoneareacode" ) %>) <%# Eval ( "refphonenumber" ) %></td></tr>
						   <tr><td><b>Fax Number:</b></td> <td>(<%# Eval ( "reffaxareacode" ) %>) <%# Eval ( "reffaxnumber" ) %></td></tr>
						   <tr><td><b>Email:</b></td> <td><a href="mailto:<%# Eval ( "refemailaddress" ) %>"><%# Eval ( "refemailaddress" ) %></a></td></tr>
						   <tr><td><b>Website:</b></td> <td><a href="http://<%# Eval ( "refwebsiteaddress" ) %>"><%# Eval ( "refwebsiteaddress" ) %></a></td></tr>
						   <tr><td><b>Description:</b></td> <td><%# Eval ( "refdescription" ) %></td></tr>
						</td>
					 </tr>
					 </table>
				  </td></tr>
			   </table>
			</itemtemplate>

		 </asp:formview>
	  </td>
   </tr>
   </table>

   <asp:sqldatasource id="referrals" runat="server"
	  SelectCommand="SELECT refid, reftype, reffirstname, reflastname, reffirmname, refspecialty, refstate FROM tbl_ReferralDirectory"
	  connectionstring="<%$ ConnectionStrings:PLNIndexConnectionString %>" />

   <asp:sqldatasource id="refdetails" runat="server"
	  connectionstring="<%$ ConnectionStrings:PLNIndexConnectionString %>"
	  SelectCommand="SELECT * FROM tbl_ReferralDirectory WHERE refid = @refid">

	  <selectparameters>
		 <asp:controlparameter controlid="empsGrid" name="refid"
			propertyname="SelectedValue"
			type="String" />
	  </selectparameters>
   </asp:sqldatasource>

</div>


Any help is greatly appreciated and if I figure out the answer I will post. Thank you.

Is This A Good Question/Topic? 0
  • +

Replies To: Refining an SQL query in gridview.

#2 AFProgMan  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 80
  • Joined: 21-October 08

Re: Refining an SQL query in gridview.

Posted 31 October 2008 - 01:19 PM

Hey everyone. I figured it out by myself with some help from google and trial and error. My code is as follows. It is very important to use SELECT DISTINCT and then change the Filter Expressions as needed.

<%@ Page Language="C#" MasterPageFile="~/pln.master" AutoEventWireup="true" CodeFile="ReferralDirect.aspx.cs" Inherits="ReferralDirect" Title="Prison Legal News - Attorney and Expert Witness"%>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
	<div class="blackheader">
	<img id="Img1" src="~/images/referral_directory.gif" alt="Prison Legal News - Referral Directory" runat="server" />
</div>
<div align="center">

   <table width="100%" cellpadding="5">
   <tr>
   <td><asp:Label ID="Label1" runat="server" Text="Find an attorney or expert witness in your area by using the directory below."></asp:Label>
	   <br />
	   You can refine your search using the options to the right.</td>
	   <td align="left">
		   <asp:RadioButtonList ID="rblRefType" runat="server" 
			   onselectedindexchanged="rblRefType_SelectedIndexChanged" 
			   AutoPostBack="True" AppendDataBoundItems="True">
		   <asp:ListItem Selected="True" Value="%">All</asp:ListItem>
		   <asp:ListItem Value="Lawyer">Lawyers</asp:ListItem>
		   <asp:ListItem Value="ExpertWitness">Expert Witnesses</asp:ListItem>
	   </asp:RadioButtonList>	   
		  <asp:DropDownList ID="ddlStateRefine" runat="server" 
			   DataSourceID="getrefstates" DataTextField="State" DataValueField="Abbrev" 
			   onselectedindexchanged="ddlStateRefine_SelectedIndexChanged" 
			   AppendDataBoundItems="true" AutoPostBack="True">
			   <asp:ListItem Text="All States" Value="%"></asp:ListItem>
		   </asp:DropDownList>
		   <asp:SqlDataSource ID="getrefstates" runat="server" 
			   ConnectionString="<%$ ConnectionStrings:PLNIndexConnectionString %>" 
			   SelectCommand="SELECT * FROM [tbl_States] ORDER BY [State]">
		   </asp:SqlDataSource>
	   </td>
	 </tr>
   <tr>
	  <td width="350px" valign="top">
		   <b>Select the referral from the list to view full details.</b>
		   <asp:gridview id="empsGrid" runat="server"
			cellpadding="5" font-size="10pt"
			datasourceid="referrals"
			datakeynames="refid"
			autogenerateselectbutton="True"
			allowsorting="True"
			autogeneratecolumns="False"
			selectedrowstyle-backcolor="khaki"
			  AllowPaging="True" onselectedindexchanged="empsGrid_SelectedIndexChanged">
		   
			<columns>
				<asp:BoundField DataField="reffirstname" HeaderText="First Name" 
					SortExpression="reffirstname" />
				<asp:BoundField DataField="reflastname" HeaderText="Last Name" 
					SortExpression="reflastname" />
			   <asp:boundfield headertext="Firm Name"
				  datafield="reffirmname" SortExpression="reffirmname" />
			   <asp:boundfield headertext="Type"
				  datafield="reftype" SortExpression="reftype" />
				<asp:BoundField DataField="refspecialty" HeaderText="Specialty" 
					SortExpression="refspecialty" />
				<asp:BoundField DataField="refstate" HeaderText="State" 
					SortExpression="refstate" />
			</columns><SelectedRowStyle BackColor="Khaki"></SelectedRowStyle>
			   <AlternatingRowStyle BackColor="#CCCCCC" />
		   </asp:gridview>
	  </td>

	  <td width="400px" align="left">
		 <asp:formview id="empsView" runat="server"
			datasourceid="refdetails"
			width="100%">

			<headertemplate>
			   <div id="Div1" class="empname" runat="server" style="background-color: #800000; color: #FFFFFF"><b>
				 Full Details For: <%# Eval ( "reffirstname" ) %> <%# Eval ( "reflastname" ) %>
			   </b></div>
			</headertemplate>

			<itemtemplate>
			   <table width="100%" cellpadding="5" align="center" bgcolor="whitesmoke">
			   <tr>
				  <%--<td>
					 <img id="Img2" align="top" runat="server" border=0
						src='<%# Eval ( "photopath", "~/shared/images/{0}" ) %>' />
				  </td>--%>
				  <td>
					 <table cellpadding="3">
					 <tr>
						<td>
							
						 <tr><td><b>Type:</b></td> <td><%# Eval ( "reftype" ) %></td></tr>
						   <tr><td><b>Full Name:</b></td> <td><%# Eval ( "reffirstname" ) %> <%# Eval ( "reflastname" ) %></td></tr>
						   <tr><td><b>Firm Name:</b></td> <td><%# Eval ( "reffirmname" ) %></td></tr>
						   <tr><td><b>Specialty:</b></td> <td><%# Eval ( "refspecialty" ) %></td></tr>
						   <tr><td><b>Address:</b></td> <td><%# Eval ( "refaddress" ) %><br><%# Eval ( "refcity" ) %> <%# Eval ( "refzipcode" ) %>, <%# Eval ( "refstate" ) %></td></tr>
						   <tr><td><b>Phone Number:</b></td> <td>(<%# Eval ( "refphoneareacode" ) %>) <%# Eval ( "refphonenumber" ) %></td></tr>
						   <tr><td><b>Fax Number:</b></td> <td>(<%# Eval ( "reffaxareacode" ) %>) <%# Eval ( "reffaxnumber" ) %></td></tr>
						   <tr><td><b>Email:</b></td> <td><a href="mailto:<%# Eval ( "refemailaddress" ) %>"><%# Eval ( "refemailaddress" ) %></a></td></tr>
						   <tr><td><b>Website:</b></td> <td><a href="http://<%# Eval ( "refwebsiteaddress" ) %>"><%# Eval ( "refwebsiteaddress" ) %></a></td></tr>
						   <tr><td><b>Description:</b></td> <td><%# Eval ( "refdescription" ) %></td></tr>
						</td>
					 </tr>
					 </table>
				  </td></tr>
			   </table>
			</itemtemplate>

		 </asp:formview>
	  </td>
   </tr>
   </table>
   <asp:sqldatasource id="referrals" runat="server"
	  SelectCommand="SELECT DISTINCT refid, reftype, reffirstname, reflastname, reffirmname, refspecialty, refstate FROM tbl_ReferralDirectory"
	  connectionstring="<%$ ConnectionStrings:PLNIndexConnectionString %>" FilterExpression="reftype like '{0}%'
	and refstate like '{1}%'">
		<FilterParameters>
			<asp:ControlParameter Name="reftype" ControlID="rblRefType" 
				PropertyName="SelectedValue" />
			<asp:ControlParameter Name="refstate" ControlID="ddlStateRefine" 
				PropertyName="SelectedValue" />
		</FilterParameters>
	</asp:sqldatasource>

   <asp:sqldatasource id="refdetails" runat="server"
	  connectionstring="<%$ ConnectionStrings:PLNIndexConnectionString %>"
	  SelectCommand="SELECT * FROM tbl_ReferralDirectory WHERE refid = @refid">

	  <selectparameters>
		 <asp:controlparameter controlid="empsGrid" name="refid"
			propertyname="SelectedValue"
			type="String" />
	  </selectparameters>
   </asp:sqldatasource>

</div>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="FooterAd1" Runat="Server">
</asp:Content>

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1