SQL - Display 2 columns as 1, but still use the ID_Column as a primary Key

I have a table with 2 columns, Product ID and Product Description. In asp.NET I am populating a dropdown list with a datatable, and the user selects a product. Therefore I still need to use the ID as the identifier, but having a product description would help so the user would know what the product is.

How is this done best?

This is what I have 开发者_如何学Goso far (C#):

    public static DataTable GetProductList()
    {
        DatabaseAdapter dba = DatabaseAdapter.GetInstance();
        string sqlQuery = ("SELECT PRODUCT_ID FROM PRODUCT");

        DataTable dt = new DataTable();
        dt.Load(dba.QueryDatabase(sqlQuery));
        return dt;
    }

This is where I am binding it (C#):

    private void PopulateProductList()
    {
        try
        {
            ProductList.DataSource = BusinessLayerHandler.GetUnitList();
            ProductList.DataTextField = "PRODUCT_ID";
            ProductList.DataValueField = "PRODUCT_ID";
            ProductList.DataBind();
        }
        catch
        {
            new Logging("E00080002");
            Alert("Failed to Import Product List");
        }
    }

The current result is a List of Product ID's: 1, 2, 3, 4 and I need ID + Description: 1 - Photo Album, 2 - File, 3 - Pencil Box

Then I would need to get the value of that list, but I don't need id + description, I only need the id as the identifier. This is where it is not yet clear, how is this done?

String productID = ProductList.SelectedValue;

I am using Oracle as my Database

Try this:

public static DataTable GetProductList()
{
    DatabaseAdapter dba = DatabaseAdapter.GetInstance();
    // Return a second, aliased column that concatenates PRODUCT_ID
    // with " - " and DESCRIPTION (alias NEW_DESCRIPTION)
    string sqlQuery = ("SELECT PRODUCT_ID, PRODUCT_ID + " - " + DESCRIPTION AS NEW_DESCRIPTION FROM PRODUCT");

    DataTable dt = new DataTable();
    dt.Load(dba.QueryDatabase(sqlQuery));
    return dt;
}

private void PopulateProductList()
{
    try
    {
        ProductList.DataSource = BusinessLayerHandler.GetUnitList();
        // Assign the NEW_DESCRIPTION column to the DataTextField
        ProductList.DataTextField = "NEW_DESCRIPTION";
        ProductList.DataValueField = "PRODUCT_ID";
        ProductList.DataBind();
    }
    catch
    {
        new Logging("E00080002");
        Alert("Failed to Import Product List");
    }
}

Essentially, select the PRODUCT_ID column and the PRODUCT_ID and DESCRIPTION columns cocatenated together - I aliased the second column for ease of reference.

Then you can assign PRODUCT_ID to the DataValueField of the DropDownList and the concatenated column (in this case NEW_DESCRIPTION) to the DataTextField.

EDIT

If you simply want to get the product id (for the DropDownList value fields) and the product description for the Drop Down List option's text, modify the Select as follows:

string sqlQuery = ("SELECT PRODUCT ID, DESCRIPTION FROM PRODUCT");

and then modify the DataTextField and DataValueField as follows:

ProductList.DataTextField = "NEW_DESCRIPTION";
ProductList.DataValueField = "PRODUCT_ID";

The syntax for the SQL might be a little different for Oracle (I'm using SQL Server syntax), but the concept is the same - if you want one column's value for the DropDownList's value field and another column's value for the DropDownList's text field, simply select both columns from the table and assign them accordingly.

This modification would produce a DropDownList with the following items:

Photo Album
File
Pencil Box

And a corresponding values for the items as:

1
2
3

I would use a query like this (pseudo SQL):

SELECT P.Product_ID, P.Product_ID + ' - ' + P.Description as 'PLabel'
FROM dbo.[Product] P
ORDER BY P.ID ASC

then, all simply, your binding becomes:

ProductList.DataTextField = "PLabel";
ProductList.DataValueField = "Product_ID";

I think you need to change your SQL query and add an expression column like this.

    string sqlQuery = ("SELECT PRODUCT_ID, PRODUCT_ID+"-"+PRODUCT_DESCRIPTION as 'ExtDescription'  FROM PRODUCT");

and then you need to set the new column 'ExtDescription' as DataTextField of your dropdown.

SQL:

SQL Server:

SELECT PRODUCT_ID, (PRODUCT_ID + " - " + Description) AS MergedDescr
FROM PRODUCT

My Sql:

SELECT PRODUCT_ID, CONCAT_WS('-', 'PRODUCT_ID', 'Description') AS MergedDescr
FROM PRODUCT

note: concat_ws will skip empty strings (useful if your Description could be null or empty). format: concat_ws('yourseparator', 'column1name', 'column2name',.........) Function description at: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_concat-ws

C#:

ProductList.DataSource = BusinessLayerHandler.GetUnitList();
ProductList.DataTextField = "MergedDescr";
ProductList.DataValueField = "PRODUCT_ID";
ProductList.DataBind();

I hope that helps