C# Discussion Boards - CodeProject

14-Jan-09 9:52

hi,
I have this problem which I have been trying to solve for some time but with no result Frown | :(

I have a table in sql server which consists of two columns: LastName and First name, and a result for a "select * from TestTable" is:

Doe John
NULL Mark

Then a I have a piece of code in my WinForms application:

string connString = "server=.;database=Test;Integrated Security=SSPI;";
            string sql = "select LastName, FirstName from TestTable";
            SqlConnection conn = new SqlConnection(connString);
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            DataSet dataset1 = new DataSet();
            da.Fill(dataset1, "TestTable");
            DataTable dt = dataset1.Tables["TestTable"];

            listBox1.DataSource = dt;

            if (listBox1.DisplayMember.Length != 0)
            {
                listBox1.DisplayMember = "LastName";
            }
            else
            {
                listBox1.DisplayMember = "FirstName";
            }

            textBox1.DataBindings.Add("text", dt, "LastName");
            textBox2.DataBindings.Add("text", dt, "FirstName");

and it works fine, when there is a NULL value in teh LastName column, in the listBox the FirstName is diplayed. The binding works ok

Now when I alter the table and add another column (Company), the same sql query result looks like that:

Doe John NULL
Smith Mark NULL
NULL NULL Microsoft

Now when I make changes to the code to looke like this:

string connString = "server=.;database=Test;Integrated Security=SSPI;";
            string sql = "select LastName, FirstName, Company from TestTable";
            SqlConnection conn = new SqlConnection(connString);
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            DataSet dataset1 = new DataSet();
            da.Fill(dataset1, "TestTable");
            DataTable dt = dataset1.Tables["TestTable"];

            listBox1.DataSource = dt;

            if (listBox1.DisplayMember.Length != 0)
            {
                listBox1.DisplayMember = "LastName";
            }
            else
            {
                listBox1.DisplayMember = "Company";
            }

            textBox1.DataBindings.Add("text", dt, "LastName");
            textBox2.DataBindings.Add("text", dt, "FirstName");
            textBox3.DataBindings.Add("text", dt, "Company");

in the listBox I only get the Microsoft entry. The other entries are there but are not visible by the LastName but blank, the binding still works.

http://img103.imageshack.us/my.php?image=50493721ap6.jpg
http://img397.imageshack.us/my.php?image=83000105po0.jpg

Can some help to solve this?

Wendelius14-Jan-09 11:14

14-Jan-09 11:14

Since you've binded the listBox1.DisplayMember to company name, it's quite obvious that the first two rows will show nothing since the company is null. What kind of result would you expect?

The need to optimize rises from a bad design.My articles[^]

wi5nia14-Jan-09 11:22

14-Jan-09 11:22

so what solution would suggest?

Wendelius14-Jan-09 11:28

14-Jan-09 11:28

That depends what do you want to show in the list box? Now you've defined to show only the company name. If there isn't any, nothing is shown.

For example, do you want to show a combined string in the list box? Something like:
Doe, John, Unknown
Smith, Mark, Unknown
Unknown, Unknown, Microsoft

The need to optimize rises from a bad design.My articles[^]

wi5nia14-Jan-09 11:31

14-Jan-09 11:31

no, I just want to show a list of LastNames from the table, and if there is a NULL value for the LastName column in a given row then show for that row the value from the column Company

Wendelius14-Jan-09 11:44

14-Jan-09 11:44

Okay, then you have at least two options. You can modify your SQL statement and create an additional column and bind to it, like (there may be typos in the examples):

string sql = "select LastName, FirstName, Company, <code>COALESCE(LastName, Company) AS ListData </code>"
             + "from TestTable";
SqlConnection conn = new SqlConnection(connString);
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet dataset1 = new DataSet();
da.Fill(dataset1, "TestTable");
listBox1.DataSource = dataset1.Tables["TestTable"];
listBox1.DisplayMember = "<code>ListData</code>";

or after filling the dataset, you can create a computed column:

string sql = "select LastName, FirstName, Company "
             + "from TestTable";
SqlConnection conn = new SqlConnection(connString);
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet dataset1 = new DataSet();
da.Fill(dataset1, "TestTable");
<code>dataset1.Tables["TestTable"].Columns.Add("ListData", 
                                         typeof(string), 
                                         "ISNULL(LastName,Company)");</code>
listBox1.DataSource = dataset1.Tables["TestTable"];
listBox1.DisplayMember = "ListData";

Hope it helps.

The need to optimize rises from a bad design.My articles[^]

wi5nia14-Jan-09 11:53

14-Jan-09 11:53

the second solution works like a charm Smile | :)

many thanks

Wendelius14-Jan-09 12:00