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
srinivas_vemlaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

abanupCommented:
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();
0
surajgupthaCommented:
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.

0
srinivas_vemlaAuthor Commented:
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
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

surajgupthaCommented:
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;

0
srinivas_vemlaAuthor Commented:
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
0
LizelleFanCommented:
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()
0
abanupCommented:
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";
0
abanupCommented:
Hi
CountryName , CountryCD must be of same datatype. if not  then use cast or convert.
e.g
if CountryName is nvarchar and CountryCD is int. then write sql statement as:

sqlComm.CommandText = "Select CountryName + '-' + cast(CountryCD as nvarchar) as Country, CountryCD  from Country";

Hope it will help u

bye

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
surajgupthaCommented:
Manual Traversal Code

for (int i=0; i< dtOne.Rows.Count; i++)
{
      ListItem liBuffer  = new ListItem(dtOne.Rows[i]["Text1"].ToString()+"-"+dtOne.Rows[i]["Text2"].ToString(),dtOne.Rows[i]["value"].ToString());
      DropDownList1.Items.Add(liBuffer);
}
0
srinivas_vemlaAuthor Commented:
Thanks all for your replies...

That solves...

Thanks,
Srinivas
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.