Skipping or not Displaying States

Skipping No record tables.

Page 1 of 1

14 Replies - 782 Views - Last Post: 30 October 2008 - 10:34 AM Rate Topic: -----

#1 AFProgMan  Icon User is offline

  • D.I.C Head

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

Skipping or not Displaying States

Post icon  Posted 28 October 2008 - 01:56 PM

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();
	}
}



Is This A Good Question/Topic? 0
  • +

Replies To: Skipping or not Displaying States

#2 AFProgMan  Icon User is offline

  • D.I.C Head

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

Re: Skipping or not Displaying States

Posted 29 October 2008 - 06:06 AM

Any ideas? I have tried a few if statements to no success.
Was This Post Helpful? 0
  • +
  • -

#3 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Skipping or not Displaying States

Posted 29 October 2008 - 06:18 AM

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
Was This Post Helpful? 0
  • +
  • -

#4 AFProgMan  Icon User is offline

  • D.I.C Head

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

Re: Skipping or not Displaying States

Posted 29 October 2008 - 07:11 AM

I don't really see where I am calling blank records.
Was This Post Helpful? 0
  • +
  • -

#5 AFProgMan  Icon User is offline

  • D.I.C Head

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

Re: Skipping or not Displaying States

Posted 29 October 2008 - 08:33 AM

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();
	}
}


Was This Post Helpful? 0
  • +
  • -

#6 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1526
  • View blog
  • Posts: 5,961
  • Joined: 21-March 08

Re: Skipping or not Displaying States

Posted 29 October 2008 - 09:35 AM

please post the code for "functions.GetStates()".
Was This Post Helpful? 0
  • +
  • -

#7 AFProgMan  Icon User is offline

  • D.I.C Head

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

Re: Skipping or not Displaying States

Posted 29 October 2008 - 10:22 AM

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;
	}

Was This Post Helpful? 0
  • +
  • -

#8 AFProgMan  Icon User is offline

  • D.I.C Head

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

Re: Skipping or not Displaying States

Posted 29 October 2008 - 12:07 PM

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.
Was This Post Helpful? 0
  • +
  • -

#9 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1526
  • View blog
  • Posts: 5,961
  • Joined: 21-March 08

Re: Skipping or not Displaying States

Posted 29 October 2008 - 12:26 PM

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".
Was This Post Helpful? 0
  • +
  • -

#10 AFProgMan  Icon User is offline

  • D.I.C Head

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

Re: Skipping or not Displaying States

Posted 29 October 2008 - 12:52 PM

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.
Was This Post Helpful? 0
  • +
  • -

#11 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1526
  • View blog
  • Posts: 5,961
  • Joined: 21-March 08

Re: Skipping or not Displaying States

Posted 29 October 2008 - 01:56 PM

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

Was This Post Helpful? 0
  • +
  • -

#12 AFProgMan  Icon User is offline

  • D.I.C Head

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

Re: Skipping or not Displaying States

Posted 29 October 2008 - 02:05 PM

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.
Was This Post Helpful? 0
  • +
  • -

#13 AFProgMan  Icon User is offline

  • D.I.C Head

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

Re: Skipping or not Displaying States

Posted 30 October 2008 - 05:45 AM

I had no luck getting that to work. Unless I just don't know where to put it.
Was This Post Helpful? 0
  • +
  • -

#14 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1526
  • View blog
  • Posts: 5,961
  • Joined: 21-March 08

Re: Skipping or not Displaying States

Posted 30 October 2008 - 07:21 AM

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.
Was This Post Helpful? 0
  • +
  • -

#15 AFProgMan  Icon User is offline

  • D.I.C Head

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

Re: Skipping or not Displaying States

Posted 30 October 2008 - 10:34 AM

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1