CODEWITHSUNDEEP
c#asp.netsql
Brad Hazelnut
Brad Hazelnut

Reputation: 1621

Dropdown list from db, how to combine columns for DataTextField?

I have a dropdown list that gets populated from a datatabase. I have it working when wanting to display only the firstname in the DataTextField of the dropdown. But if i want to display 2 fields, firstname and lastname, then it throws an error. Why is this not working or how can i get it to work?

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString);
SqlCommand cmd = new SqlCommand("SELECT * FROM tbl_customers", con);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
con.Open();
da.Fill(ds, "Customers");
CustomerList.DataSource = ds.Tables[0];
CustomerList.DataTextField = "FirstName" + " " + "LastName";
CustomerList.DataValueField = "CID";
CustomerList.DataBind();

Upvotes: 2

Views: 4167

Answers (3)

vinit rai
vinit rai

Reputation: 9

you can use database replicate method while joining the columns for the equal spacing..

 string sql = @"SELECT FirstName + replicate(' ', 20 - len(FirstName)) + LastName AS FullName, 
                          CID, FirstName, LastName  
                   FROM tbl_customers 
                   ORDER BY FullName ASC;"
    SqlCommand cmd = new SqlCommand(sql, con);
    CustomerList.DataSource = ds.Tables[0];
CustomerList.DataTextField = "FullName";
CustomerList.DataValueField = "CID";
CustomerList.DataBind();

Upvotes: 0

Joe Enos
Joe Enos

Reputation: 40413

You're going to want to define a custom column:

SqlCommand cmd = new SqlCommand("SELECT CID, FirstName + ' ' + LastName AS [FormattedName] FROM tbl_customers", con);

Then you can bind directly to that column:

CustomerList.DataTextFIeld = "FormattedName";

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460208

You have to select the "virtual" column from the database:

string sql = @"SELECT FirstName + ' ' + LastName AS FullName, 
                      CID, FirstName, LastName  
               FROM tbl_customers 
               ORDER BY FullName ASC;"
SqlCommand cmd = new SqlCommand(sql, con);
// ...
CustomerList.DataSource = ds.Tables[0];
CustomerList.DataTextField = "FullName";
CustomerList.DataValueField = "CID";
CustomerList.DataBind();

Upvotes: 5

Related Questions