user6592730 user6592730 - 3 months ago 8
SQL Question

How to combine two data fields in a database and display in a dropdownlist?

I have a query to get data in two columns and add another '-' sign to the result. Now i want to display these details in a dropdownlist. Also in another function I have to get the selected value's first part(first column of the table used to create the output) and store it in the database again. Query works perfectly.

Now my questions are,

1) How to display final result in of joined columns in the dropdownlist?

2)How to get the first column's value of the selected index of the dropdownlist?

Here is my code

public DataSet getSOCode()
{
string strQuery = @"select a.AGNTNUM+' - '+a.CLTADDR01
from AGNTDTL a, cscsuboffice b, Web_User_Group c
where
a.ARACDE=b.DESCITEM and
b.SHORTDESC=c.BRANCH and
a.DTETRM='99999999' and
a.AGNTNUM not between '00050000' and '00200000' and
a.agntnum <'00300000' and
a.agtype not in ('PA','LG','BK') and
c.EMPID='000101'
order by a.AGNTNUM";
return SqlHelper.ExecuteDataset(dwhConnString, CommandType.Text, strQuery);
}


This is the aspx.cs code

public void loadSOCode()
{
DataSet ds = new DataSet();
ds = db.getSOCode();


if (ds.Tables[0].Rows.Count > 0)
{
ddlSoCode.DataSource = ds.Tables[0];
ddlSoCode.DataTextField ="AGNTNUM" + "CLTADDR01";
ddlSoCode.DataValueField = "AGNTNUM" + "CLTADDR01";
ddlSoCode.DataBind();

ddlSoCode.Items.Insert(0, new ListItem("--Select The Agent--", "--Select The Agent--"));

}
}

Answer

Change the Query like the following:

string strQuery = @"select a.AGNTNUM , a.AGNTNUM + ' - ' + a.CLTADDR01 as DisplayItem " +
                    " from AGNTDTL  a, cscsuboffice b, Web_User_Group c" +
                    " where .. // give conditions here 

Then the code for binding will be :

ddlSoCode.DataSource = ds.Tables[0];
ddlSoCode.DataTextField ="DisplayItem";
ddlSoCode.DataValueField = "AGNTNUM";
ddlSoCode.DataBind();

So that the SelectedValue will give you the AGNTNUM and the combobox will display the combination of a.AGNTNUM and a.CLTADDR01