CODEWITHSUNDEEP
c#asp.netsqlsql-serverwebforms
Suhaib Janjua
Suhaib Janjua

Reputation: 3574

How to access other columns from DataSource Table in dropdown list?

I have a table of countries having 10 columns. I have already assigned CityName as DataTextField to my DropDownList and Id as DataValueField. Now on SelectedIndexChanged I have to get values from other columns like CityCode. How I can access it?

ddlCity.DataSource = cboxTeam_DS.Tables["MyTable"];
ddlCity.DataTextField  = "CityName";
ddlCity.DataValueField= "Id";
ddlCity.DataBind();

On SelectedIndexChanged method, how do I access the CityCode column values ?

Upvotes: 3

Views: 7766

Answers (5)

Carlos Llano
Carlos Llano

Reputation: 1

I suggest that with the ID, retrieve again the fields you need on the database. This way you save time and effort. That was how I solved it.

Upvotes: 0

Suhaib Janjua
Suhaib Janjua

Reputation: 3574

Well here is my solution which I have developed to solve this problem and it is very effective for me.

The first thing I did, I have changed my database query

from

SELECT * FROM Cities ORDER BY CityName Asc;

to

SELECT *, convert(nvarchar(10),id) + ',' + CityCode as idCode FROM Cities ORDER BY CityName Asc;

It returns me a table containing an extra column named as idCode .

As id is int and CityCode is nvarchar, so I convert the id column and gets an extra column contains values of both columns.

And in my code, I have assigned the idCode column to my ddlCity.DataValueField.

ddlCity.DataSource = cboxTeam_DS.Tables["MyTable"];
ddlCity.DataTextField  = "CityName";
ddlCity.DataValueField = "idCode";
ddlCity.DataBind();

After that I use comma splice to to split the string into id and code into an array.

string[] strArray = this.ddlCity.SelectedValue.Split(',');

And then use it where I want to use it by accessing the Indexes.

int myId = strArray[0];
string myCode = strArray[1];

Upvotes: 2

Adil
Adil

Reputation: 148130

You have not bind the CityCode column to drop down. The data for dropdown is kept in ViewState and that does not know other columns. In desktop we can get the object from ComboBox (dropdown) but in asp.net we can not. This is probably to keep the ViewState as small as possible.

You can choose one of these method to accomplish what you need.

  • You can get the id and send a database call to retrieve the CityCode in SelectedIndexChanged.

  • You can make the Value column by combination of two columns like id_CityCode and later extract what you need.

Upvotes: 1

Shiva Saurabh
Shiva Saurabh

Reputation: 1289

I can suggest you two ways of doing this

  1. You can store one more hidden drop down with your Id and CityCode populated in it. So whenever you select the original drop down select the appropriate value from this hidden dropdown list.
  2. Second way is when you get the ID from the ddlCity.SelectedValue you need to retrieve the table again with the criteria and search in your table taking this value as reference to get the City Code.

I prefer you to go for the first one. you can also access the hidden dropdown in the first case using the javascript.

Upvotes: 2

Prashant16
Prashant16

Reputation: 1526

You can get directly from your datasource

 int CityId = (int)ddlCity.SelectedValue;
    DataTable dt = cboxTeam_DS.Tables["MyTable"];
    string CityCode = (from DataRow dr in dt.Rows
              where (int)dr["Id"] == CityId 
              select (string)dr["CityCode"]).FirstOrDefault();

Upvotes: 0

Related Questions