[AFProgMan]

Hey everyone, I am curious as to a display issue I am having. I would like the output only to display the records and states that have entries. If the state has no records, i would just like it to skip the display of that state altogether instead of posting a blank record list for the state.

On the front end I am just calling this query and gridview display.

Here is the code behind.

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

public partial class ReferralDirectory : System.Web.UI.Page
{
	protected void Page_Load(object sender, EventArgs e)
	{
		if (!IsPostBack)
		{
			buildtable();
		}
	}

	private void buildtable()
	{
		TableRow row = null;
		TableCell cell = null;
		string stateabbrev = string.Empty;

		foreach (DataRow dr in functions.GetStates().Tables[0].Rows)
		{
			row = new TableRow();
			cell = new TableCell();
			cell.BackColor = System.Drawing.Color.Gray;
			cell.ForeColor = System.Drawing.Color.White;
			cell.CssClass = "Boldtext";
			stateabbrev = dr["abbrev"].ToString();
			cell.Text = dr["state"].ToString();
			row.Cells.Add(cell);
			tblReferrals.Rows.Add(row);

			addlisting(stateabbrev);
		}
	}

	private void addlisting(string abbrev)
	{
		TableRow row = null;
		TableCell cell = null;
		string state = string.Empty;
		string rftype = string.Empty;

		row = new TableRow();
		cell = new TableCell();
		cell.Height = Unit.Pixel(20);
		cell.Text = "<div align=\"right\"><img src=\"images/dingbat_arrow_icon.gif\" width=\"4\" height=\"9\" border=\"0\" />&nbsp;<a href=\"#\">Back to Top</a></div>";
		row.Cells.Add(cell);
		tblReferrals.Rows.Add(row);

		foreach (DataRow dr in directorylistings.GetDirectoryListings().Tables[0].Rows)
		{
			
			rftype = dr["reftype"].ToString();

		   if (rftype == ddlRefSelType.SelectedValue)
		   //if (rftype == "Lawyer")
		   {


				state = dr["refstate"].ToString();

				if (abbrev == state)
				{
					row = new TableRow();
					cell = new TableCell();
					cell.Text = "<div style=\"margin-left:10px;\">" + dr["reffirstname"].ToString() + " " + dr["reflastname"].ToString() + "</div>";
					row.Cells.Add(cell);
					tblReferrals.Rows.Add(row);

					row = new TableRow();
					cell = new TableCell();
					cell.Text = "<div style=\"margin-left:10px;\">" + dr["reffirmname"].ToString() + "</div>";
					row.Cells.Add(cell);
					tblReferrals.Rows.Add(row);

					row = new TableRow();
					cell = new TableCell();
					cell.Text = "<div style=\"margin-left:10px;\">" + dr["refspecialty"].ToString() + "</div>";
					row.Cells.Add(cell);
					tblReferrals.Rows.Add(row);

					row = new TableRow();
					cell = new TableCell();
					cell.Text = "<div style=\"margin-left:10px;\">" + dr["refaddress"].ToString() + "</div>";
					row.Cells.Add(cell);
					tblReferrals.Rows.Add(row);

					row = new TableRow();
					cell = new TableCell();
					cell.Text = "<div style=\"margin-left:10px;\">" + dr["refcity"].ToString() + ", " + dr["refstate"].ToString() + " " + dr["refzipcode"].ToString() + "</div>";
					row.Cells.Add(cell);
					tblReferrals.Rows.Add(row);

					if (!string.IsNullOrEmpty(dr["refphoneareacode"].ToString()) && !string.IsNullOrEmpty(dr["refphonenumber"].ToString()))
					{
						row = new TableRow();
						cell = new TableCell();
						cell.Text = "<div style=\"margin-left:10px;\">(" + dr["refphoneareacode"].ToString() + ") " + dr["refphonenumber"].ToString() + "</div>";
						row.Cells.Add(cell);
						tblReferrals.Rows.Add(row);
					}

					if (!string.IsNullOrEmpty(dr["reffaxareacode"].ToString()) && !string.IsNullOrEmpty(dr["reffaxnumber"].ToString()))
					{
						row = new TableRow();
						cell = new TableCell();
						cell.Text = "<div style=\"margin-left:10px;\">(" + dr["reffaxareacode"].ToString() + ") " + dr["reffaxnumber"].ToString() + "</div>";
						row.Cells.Add(cell);
						tblReferrals.Rows.Add(row);
					}

					if (!string.IsNullOrEmpty(dr["refemailaddress"].ToString()))
					{
						row = new TableRow();
						cell = new TableCell();
						cell.Text = "<div style=\"margin-left:10px;\"><a href=\"mailto://" + dr["refemailaddress"].ToString() + "\" target=\"_blank\">" + dr["refemailaddress"].ToString() + "</a></div>";
						row.Cells.Add(cell);
						tblReferrals.Rows.Add(row);
					}

					if (!string.IsNullOrEmpty(dr["refwebsiteaddress"].ToString()))
					{
						row = new TableRow();
						cell = new TableCell();
						cell.Text = "<div style=\"margin-left:10px;\"><a href=\"http://" + dr["refwebsiteaddress"].ToString() + "\" target=\"_blank\">" + dr["refwebsiteaddress"].ToString() + "</a></div>";
						row.Cells.Add(cell);
						tblReferrals.Rows.Add(row);
					}

					if (!string.IsNullOrEmpty(dr["refdescription"].ToString()))
					{
						row = new TableRow();
						cell = new TableCell();
						cell.Text = "<div style=\"margin-left:10px;\">" + dr["refdescription"].ToString() + "</div>";
						row.Cells.Add(cell);
						tblReferrals.Rows.Add(row);
					}

					row = new TableRow();
					cell = new TableCell();
					cell.Height = Unit.Pixel(20);
					cell.Text = "<hr class=\"reddottedline\">";
					row.Cells.Add(cell);
					tblReferrals.Rows.Add(row);
				}
			}
	   }
	}
	protected void ddlRefSelType_SelectedIndexChanged(object sender, EventArgs e)
	{
		buildtable();
	}
}

[AFProgMan]

Any ideas? I have tried a few if statements to no success.

[PsychoCoder]

I would do this in your query, let the database do some of the work for you. Just query for records that have entries and leave the blanks ones at home

[AFProgMan]

I don't really see where I am calling blank records.

[AFProgMan]

Here is my current Code Behind.

I have changed the dropdown option to a radial select and have it limiting the output based on Referral Type selected from the radial button. However, It still displays ALL 50 states plus DC and PR, even if they ahve no records in them. How would I skip an empty state from even being displayed.

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

public partial class ReferralDirectory : System.Web.UI.Page
{
	protected void Page_Load(object sender, EventArgs e)
	{
		if (!IsPostBack)
		{
			buildtable();
		}
	}

	private void buildtable()
	{
		TableRow row = null;
		TableCell cell = null;
		string stateabbrev = string.Empty;

		foreach (DataRow dr in functions.GetStates().Tables[0].Rows)
		{
			row = new TableRow();
			cell = new TableCell();
			cell.BackColor = System.Drawing.Color.Gray;
			cell.ForeColor = System.Drawing.Color.White;
			cell.CssClass = "Boldtext";
			stateabbrev = dr["abbrev"].ToString();
			cell.Text = dr["state"].ToString();
			row.Cells.Add(cell);
			tblReferrals.Rows.Add(row);

			addlisting(stateabbrev);
		}
	}

	private void addlisting(string abbrev)
	{
		TableRow row = null;
		TableCell cell = null;
		string state = string.Empty;
		string rftype = string.Empty;

		row = new TableRow();
		cell = new TableCell();
		cell.Height = Unit.Pixel(20);
		cell.Text = "<div align=\"right\"><img src=\"images/dingbat_arrow_icon.gif\" width=\"4\" height=\"9\" border=\"0\" />&nbsp;<a href=\"#\">Back to Top</a></div>";
		row.Cells.Add(cell);
		tblReferrals.Rows.Add(row);

		foreach (DataRow dr in directorylistings.GetDirectoryListings().Tables[0].Rows)
		{
			
			rftype = dr["reftype"].ToString();
		   if (rftype == rblRefSelect.SelectedValue)
		   //if (rftype == ddlRefSelType.SelectedValue)
		   {


				state = dr["refstate"].ToString();

				if (abbrev == state)
				{
					row = new TableRow();
					cell = new TableCell();
					cell.Text = "<div style=\"margin-left:10px;\">" + dr["reffirstname"].ToString() + " " + dr["reflastname"].ToString() + "</div>";
					row.Cells.Add(cell);
					tblReferrals.Rows.Add(row);

					row = new TableRow();
					cell = new TableCell();
					cell.Text = "<div style=\"margin-left:10px;\">" + dr["reffirmname"].ToString() + "</div>";
					row.Cells.Add(cell);
					tblReferrals.Rows.Add(row);

					row = new TableRow();
					cell = new TableCell();
					cell.Text = "<div style=\"margin-left:10px;\">" + dr["refspecialty"].ToString() + "</div>";
					row.Cells.Add(cell);
					tblReferrals.Rows.Add(row);

					row = new TableRow();
					cell = new TableCell();
					cell.Text = "<div style=\"margin-left:10px;\">" + dr["refaddress"].ToString() + "</div>";
					row.Cells.Add(cell);
					tblReferrals.Rows.Add(row);

					row = new TableRow();
					cell = new TableCell();
					cell.Text = "<div style=\"margin-left:10px;\">" + dr["refcity"].ToString() + ", " + dr["refstate"].ToString() + " " + dr["refzipcode"].ToString() + "</div>";
					row.Cells.Add(cell);
					tblReferrals.Rows.Add(row);

					if (!string.IsNullOrEmpty(dr["refphoneareacode"].ToString()) && !string.IsNullOrEmpty(dr["refphonenumber"].ToString()))
					{
						row = new TableRow();
						cell = new TableCell();
						cell.Text = "<div style=\"margin-left:10px;\">(" + dr["refphoneareacode"].ToString() + ") " + dr["refphonenumber"].ToString() + "</div>";
						row.Cells.Add(cell);
						tblReferrals.Rows.Add(row);
					}

					if (!string.IsNullOrEmpty(dr["reffaxareacode"].ToString()) && !string.IsNullOrEmpty(dr["reffaxnumber"].ToString()))
					{
						row = new TableRow();
						cell = new TableCell();
						cell.Text = "<div style=\"margin-left:10px;\">(" + dr["reffaxareacode"].ToString() + ") " + dr["reffaxnumber"].ToString() + "</div>";
						row.Cells.Add(cell);
						tblReferrals.Rows.Add(row);
					}

					if (!string.IsNullOrEmpty(dr["refemailaddress"].ToString()))
					{
						row = new TableRow();
						cell = new TableCell();
						cell.Text = "<div style=\"margin-left:10px;\"><a href=\"mailto://" + dr["refemailaddress"].ToString() + "\" target=\"_blank\">" + dr["refemailaddress"].ToString() + "</a></div>";
						row.Cells.Add(cell);
						tblReferrals.Rows.Add(row);
					}

					if (!string.IsNullOrEmpty(dr["refwebsiteaddress"].ToString()))
					{
						row = new TableRow();
						cell = new TableCell();
						cell.Text = "<div style=\"margin-left:10px;\"><a href=\"http://" + dr["refwebsiteaddress"].ToString() + "\" target=\"_blank\">" + dr["refwebsiteaddress"].ToString() + "</a></div>";
						row.Cells.Add(cell);
						tblReferrals.Rows.Add(row);
					}

					if (!string.IsNullOrEmpty(dr["refdescription"].ToString()))
					{
						row = new TableRow();
						cell = new TableCell();
						cell.Text = "<div style=\"margin-left:10px;\">" + dr["refdescription"].ToString() + "</div>";
						row.Cells.Add(cell);
						tblReferrals.Rows.Add(row);
					}

					row = new TableRow();
					cell = new TableCell();
					cell.Height = Unit.Pixel(20);
					cell.Text = "<hr class=\"reddottedline\">";
					row.Cells.Add(cell);
					tblReferrals.Rows.Add(row);
				}
			}
	   }
	}
	protected void ddlRefSelType_SelectedIndexChanged(object sender, EventArgs e)
	{
		buildtable();
	}
	protected void rblRefSelect_SelectedIndexChanged(object sender, EventArgs e)
	{
		buildtable();
	}
}

[eclipsed4utoo]

please post the code for "functions.GetStates()".

[AFProgMan]

Here is the functions.GetStates() code as requested.

public static DataSet GetStates()
	{
		string oString = "Select StateID, State, Abbrev "
			+ "From tbl_States "
			+ "Order by State ";

		string DataTable_Name = "tbl_States";

		DataSet DS = new DataSet();
		SqlConnection oconn = new SqlConnection(oConn);
		SqlDataAdapter oAdapter = new SqlDataAdapter(oString, oConn);
		oAdapter.Fill(DS, DataTable_Name);
		return DS;
	}

[AFProgMan]

Okay, i now have a dropdown box refining the the directory with whatever state is selected on the list. The output once the parameters are chosen work great. It only displays the state that is selected. For this I used this code.

foreach (DataRow dr in functions.GetStates().Tables[0].Rows)
			{
				
					row = new TableRow();
					cell = new TableCell();
					cell.BackColor = System.Drawing.Color.Gray;
					cell.ForeColor = System.Drawing.Color.White;
					cell.CssClass = "Boldtext";
					stateabbrev = dr["abbrev"].ToString();
					cell.Text = dr["state"].ToString();
				if (stateabbrev == ddlStateRefine.SelectedValue)
				{
					row.Cells.Add(cell);
					tblReferrals.Rows.Add(row);

					addlisting(stateabbrev);
				}

			}

however, on the page load, now instead of displaying a whole empty list of 50 states, it displays nothing until you choose the referral type and state you would like. I would really like to have the initial table load all of the references that are there but not even bother to list the states without any. I am having some real trouble with this.

[eclipsed4utoo]

if you are using a parameterized query, you can construct a sql statement like this...

SELECT StateID, State, Abbrev FROM tbl_States
WHERE State = CASE @State WHEN 'All' THEN State ELSE @State
ORDER BY State

This means that if you send 'All' as the chosen state, it will return all states. If you choose a state, it will return only that state. This is how I normally handle the situation of "All or specific".

Also, populate the first item in the dropdown box to "All".

[AFProgMan]

I was thinking something right along those lines but I had no clue how to attempt going about that. So then I can put that SQL query in an If statement or do I just subsitute my query for that one and it should take care of the If statement directly.

Also, how to populate first item to be all or at least 'please select'?

I was attempting to use
ddlStateRefine.Items.Insert(0, "Please Select");
but it did not seem to populate that anywhere in the list, no less as it's first choice.

[eclipsed4utoo]

I use this code to add an item to a drop down list.

ListItem li = new ListItem();
li.Text = "All";
li.Value = "All";
dropDownList1.Items.Add(li);

then add the rest of your items.

This post has been edited by eclipsed4utoo: 29 October 2008 - 01:57 PM

[AFProgMan]

Yes but I use a datasource and am pooling my info from an SQL db table that has all the states listed. My list is not populated items, it is a table. I do not believe this method works for this. But I could be wrong and I will try it.

[AFProgMan]

I had no luck getting that to work. Unless I just don't know where to put it.

[eclipsed4utoo]

I don't think it will work if you are using a DataSource. I don't exactly know how to add an extra item to a dropdownlist that is databound.

[AFProgMan]

Yeah I know, it is weird. in 2 other places throughout my app, I have databound Dropdowns and I am using this
ddlStateRefine.Items.Insert(0, "Please Select");
as the way to do that. But, I think it has something to do with WHEN that string is executed. Because it does not work in this instance, probably because the List performs a function asap.