• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1115
  • Last Modified:

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
0
srinivas_vemla
Asked:
srinivas_vemla
  • 3
  • 3
  • 3
  • +1
2 Solutions
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now