asked on

Concatenating Strings in DropDownList DataTextField

Hello Friends,

I am using DataBinding to load a DropDownList with data from SQL Server 2000 Database, using a SqlDataReader. It works fine and looks something like this:

sqlComm.CommandText = "Select * from Country";

                        //dr = new SqlDataReader();
                        dr = sqlComm.ExecuteReader();
                        ddlCountry.DataSource = dr;
                        ddlCountry.DataTextField = "CountryName";
                        ddlCountry.DataValueField = "CountryCD";
                        ddlCountry.DataBind();

As we can see, the Drop down would finally show only the CountryName content. But I want it to show "<CountryName> - <CountryCD>". So instead of "United States", it should show "United State - USA". How should I do that?

Can I use databinding here or should I have to traverse through each entry in the DataReader and assign the values?

Thanks,
Srinivas

abanup

Hi,
Try to change ur sql statement:----

sqlComm.CommandText = "Select CountryName || '-' || CountryCD as Country, CountryCD from Country";

//dr = new SqlDataReader();
dr = sqlComm.ExecuteReader();
ddlCountry.DataSource = dr;
ddlCountry.DataTextField = "Country";
ddlCountry.DataValueField = "CountryCD";
ddlCountry.DataBind();

surajguptha

As far as i know there is no way it can be achieved using databind. However u can do that by manually traversing and adding the drop down items.

The query change idea of abanup is the best solution.

srinivas_vemla

ASKER

That was a great idea... but when I change my query and run it in Query Analyzer to check first, it says:

Select CountryName || CountryCD as Country, CountryCD from Country;

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '|'.

Is there some problem with the "|" stuff? Should I do something for them to work?

Thanks,
Srinivas

surajguptha

Isnt it

Select CountryName || '-' || CountryCD as Country, CountryCD from Country;

If u just want space u can try

Select CountryName || ' ' || CountryCD as Country, CountryCD from Country;

srinivas_vemla

ASKER

Thanks for the reply surajguptha...
I was just trying out with and without special characters... but none really worked...

also, can someone pls shoot me a sample piece of code of how to manually traverse through the datareader to populate the dropdownlist (without using DataBinding). I am stuck here:

                        int i=0;
                        while(dr.Read)
                        {
                              ddlCountry.Items...... <-- what property should I use???
                        }

Thanks,
Phani

LizelleFan

Another way of doing this is to first create a datatable with 3 datacolumns. Populate it and then bind it to the dropdownlist.

dt = New DataTable
dt.Columns.Add("CountryName")
dt.Columns.Add("CountryCD")
dt.Columns.Add("Name_CD")

Do While reader.Read
dr = dt.NewRow
dr.Item(0) = reader.Item(0)
dr.Item(1) = reader.Item(1)
dr.Item(2) = reader.Item(0) & " - " & reader.Item(1)
dt.Rows.Add(dr)
Loop

ddlCountry.DataSource = dt
ddlCountry.DataTextField = "Name_CD"
ddlCountry.DataValueField = "CountryCD"
ddlCountry.DataBind()

abanup

Hi
I think u r using sql server then please try + instead of ||

like this:
sqlComm.CommandText = "Select CountryName + '-' + CountryCD as Country, CountryCD from Country";

ASKER CERTIFIED SOLUTION

abanup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

surajguptha

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

srinivas_vemla

ASKER

Thanks all for your replies...

That solves...

Thanks,
Srinivas