I have a database that holds basic contact information such as business name, contact name, address and phone information. The following code is used to fill the listbox. The issue I need to resolve is this... I am using the BusinessName field to display and set the order of the listbox. The directory holds business and people contact information. For a person record the ContactName field will be filled out and the business name would be empty/null. I want the listbox to show BusinessName if it is Not Null however if the BusinessName is Null I would like the ContactName to show in the listbox.
I think I need some logic that uses the BusinessName if not null otherwise use ContactName.
Any help would be appreciated.
- Brian
string SQLConnString = "Data Source=AlphaServer\\sqlexpress;Database=CSCdb;Integrated Security=True;";
string connectionString = SQLConnString;
SqlConnection conn = new SqlConnection(connectionString);
string commandStringAll = "SELECT TypeBusiness, TypePerson, BusinessName, PhoneWork, PhoneFax, PhoneMobile, AddStreet, AddCity, AddState, AddZip, Website, Email, Category, ContactName, ContactTitle FROM tblDirectory ORDER BY BusinessName";
SqlDataAdapter dataAdapterAll = new SqlDataAdapter(commandStringAll, conn);
DataSet dsAll = new DataSet();
dataAdapterAll.Fill(dsAll, "prog");
dataTable = dsAll.Tables["prog"];
DirectorySelectLbx.ValueMember = "ID";
DirectorySelectLbx.Tag = "ID";
DirectorySelectLbx.DisplayMember = "BusinessName";
DirectorySelectLbx.DataSource = dsAll.Tables["prog"].DefaultView;
DirectorySelectLbx.SetSelected(0, true);
conn.Close();
Loading
Raja KrishnamurthyPosted Apr 7, 2011, 3:33 PM
Select
Id,
Case when Isnull(BusinessName,'') = '' then ContactName else BusinessName End as BusinessName
From
tblDirectory
Order By
BusinessName
HTH
Raja KrishnamurthyPosted Apr 7, 2011, 3:48 PM
Brian WalshPosted Apr 7, 2011, 3:42 PM
If I had your knowledge this program would be finished in hours and not the weeks/months it will take me!
That solution worked perfectly. I will move the code into a stored procedure as you suggested.
Thanks again and lookout for my next posts for help! LOL
- Brian
Brian WalshPosted Apr 7, 2011, 3:31 PM
Thank you! The code worked great with one exception... If the user enters text into the Business Name textbox and later removes the text either during the initial entry or when preforming an update the SQL database field will be blank but not null. A blank Business Name field results in the Business Name being displayed in the listbox as a blank and not showing the Contact Name.
Any advice on that?
Thanks again,
- Brian
Raja KrishnamurthyPosted Apr 7, 2011, 3:18 PM
I think you can solve the problem in your query:
Select
Id,
Case when BusinessName is null then ContactName else BusinessName End as BusinessName
From
tblDirectory
Order By
BusinessName
Two things to note here:
1. It is always better to use Stored procedure than to use inline query since you can avoid SQL Injections
2. From a performance perspective always get only those columns which you need (Don't know if you are going to use the dataset for any other purpose. If not then those two columns will do)
HTH