CODEWITHSUNDEEP
c#sqlasp.net
Mike
Mike

Reputation: 1067

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--"));

     }
}

Upvotes: 1

Views: 359

Answers (2)

sujith karivelil
sujith karivelil

Reputation: 29036

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

Upvotes: 1

Hari Prasad
Hari Prasad

Reputation: 16956

You cannot set DataTextField and DataValueField with the combination of column names, in fact sql returns no column name in this case. Use alias to give a new name.

public DataSet getSOCode()
{
    string strQuery = @"select  a.AGNTNUM+' - '+a.CLTADDR01 AS NewColumn
                        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);
}

and now use that column name to set Text and Value field.

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


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

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

     }
}

Upvotes: 0

Related Questions