Bindng and display problem

wi5nia

hi, I have this problem which I have been trying to solve for some time but with no result :( 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 b

Wendelius

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[^]

wi5nia

so what solution would suggest?

Wendelius

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[^]

wi5nia

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

Wendelius

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, COALESCE(LastName, Company) AS ListData "
+ "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 = "ListData";

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");
dataset1.Tables["TestTable"].Columns.Add("ListData", typeof(string), "ISNULL(LastName,Company)");
listBox1.DataSource = dataset1.Tables["TestTable"];
listBox1.DisplayMember = "ListData";

Hope it helps.

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

wi5nia

the second solution works like a charm :) many thanks

Wendelius

You're welcome :)

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